当前位置： 首页 > news >正文

板凳-------Mysql cookbook学习 （十--10）

news 2025/6/26 9:10:36

第9章：获取和使用元数据
9.0 引言
9.1 获取受语句影响的数据行数目

import mysql.connector
from mysql.connector import Errortry:conn = mysql.connector.connect(host='localhost',database='cookbook',user='cbuser',password='cbpass',client_flags=[mysql.connector.constants.ClientFlag.FOUND_ROWS])if conn.is_connected():print('已成功连接到数据库')# 获取数据库信息cursor = conn.cursor()cursor.execute('SELECT VERSION()')version = cursor.fetchone()print(f'MySQL版本: {version[0]}')# 列出所有表cursor.execute('SHOW TABLES')tables = cursor.fetchall()print('数据库中的表:')for table in tables:print(f'- {table[0]}')# 使用实际存在的customers表进行查询stmt = "SELECT * FROM customers LIMIT 5"  # 修改为实际存在的表名cursor.execute(stmt)print(f'查询结果数量: {cursor.rowcount}')# 获取并打印查询结果results = cursor.fetchall()for row in results:print(row)cursor.close()conn.close()print('数据库连接已关闭')except Error as e:
print(f'数据库操作错误: {e}')

已成功连接到数据库
MySQL版本: 8.0.40
数据库中的表:
- actors
- adcount
- al_winner
- app_log
- artist
- book_authors
- book_vendor
- booksales
- catalog_list
- cd
- city
- color
- cow_color
- cow_order
- current_dept_emp
- customers
- date_val
- datetbl
- datetime_val
- department
- dept_emp
- dept_emp_latest_date
- dept_manager
- dialogue
- dialogue_analysis
- die
- doremi
- drawing
- driver_log
- employee
- employees_2023
- event
- expt
- formula1
- goods_characteristics
- goods_shops
- groceries
- groceries_order_items
- hitcount
- hitlog
- hostip
- hostname
- housewares
- housewares2
- housewares3
- housewares4
- httpdlog
- httpdlog2
- hw_category
- image
- ingredient
- insect
- inv_item
- invoice
- item
- kjv
- limbs
- limbs_backup
- limbs_stats
- mail
- mail2
- mail_view
- marathon
- mark_log
- metal
- money
- movies
- movies_actors
- movies_actors_link
- mytable
- name
- news
- newsstaff
- nt_verses
- numbers
- obs
- occasion
- painting
- passtbl
- passwd
- patients
- perl_session
- person
- php_session
- phrase
- player_stats
- player_stats2
- poi
- poll_vote
- profile
- profile_contact
- psalms
- rainfall
- rand_names
- rank
- ranks
- reviews
- roster
- ruby_session
- salary
- sales_region
- sales_tax_rate
- sales_volume
- sibling
- some table
- standings1
- standings2
- states
- str_val
- sundays
- t
- t2
- taxpayer
- tb1
- tbl_name
- temporal_val
- test
- testscore
- testscore_withmisses
- testscore_withmisses2
- time_val
- timestamp_val
- title
- tmp
- tomcat_role
- tomcat_session
- tomcat_user
- top_names
- trip_leg
- trip_log
- trip_summary_view
- ts
- tsdemo
- tsdemo1
- tsdemo2
- tt
- ttt
- weatherdata
- weekday
查询结果数量: 0
('CUST001', 'John', 'Doe', 'john.doe@example.com', '+1-800-123-4567', '123 Main St.', None, 'Springfield', 'IL', '62704', 'USA', datetime.datetime(2025, 5, 18, 11, 42, 53), datetime.datetime(2025, 5, 18, 11, 42, 53))
('CUST002', 'Jane', 'Smith', 'jane.smith@example.com', '+1-800-987-6543', '456 Elm St.', None, 'Shelbyville', 'KY', '40065', 'USA', datetime.datetime(2025, 5, 18, 11, 42, 53), datetime.datetime(2025, 5, 18, 11, 42, 53))
('CUST003', 'Alice', 'Johnson', 'alice.johnson@example.com', '+1-800-555-1212', '789 Oak Ave.', None, 'Capital City', 'TX', '76101', 'USA', datetime.datetime(2025, 5, 18, 11, 42, 53), datetime.datetime(2025, 5, 18, 11, 42, 53))
数据库连接已关闭

9.2 获取设置元数据的结果

import mysql.connector
from mysql.connector import Error
from mysql.connector import constants  # 导入constants模块try:conn = mysql.connector.connect(host='localhost',database='cookbook',user='cbuser',password='cbpass',client_flags=[constants.ClientFlag.FOUND_ROWS])if conn.is_connected():print('已成功连接到数据库')stmt = "SELECT name, foods from profile"print("Statement: ", stmt)cursor = conn.cursor()cursor.execute(stmt)print(f'查询结果数量: {cursor.rowcount}')if cursor.description is None:ncols = 0else:ncols = len(cursor.description)print("Number of columns: ", ncols)if ncols == 0:print("Note: statement has no result set")# 打印列信息 - 使用修正后的类型获取方式for i in range(ncols):col_info = cursor.description[i]col_name = col_info[0]col_type_code = col_info[1]# 通过类型代码获取类型名称col_type_name = constants.FieldType.get_info(col_type_code)print(f"Column {i+1}: {col_name} ({col_type_name})")# 获取并打印查询结果results = cursor.fetchall()for row in results:print(row)cursor.close()conn.close()print('数据库连接已关闭')except Error as e:
print(f'数据库操作错误: {e}')已成功连接到数据库
Statement:  SELECT name, foods from profile
查询结果数量: 0
Number of columns:  2
Column 1: name (STRING)
Column 2: foods (STRING)
('Fred', {'lutefisk', 'fadge', 'pizza'})
('Mort', {'curry', 'eggroll', 'burrito'})
('Brit', {'curry', 'pizza', 'burrito'})
('Carl', {'eggroll', 'pizza'})
('Sean', {'curry', 'burrito'})
('Alan', {'curry', 'fadge'})
('Mara', {'lutefisk', 'fadge'})
('Shepard', {'curry', 'pizza'})
('Dick', {'lutefisk', 'fadge'})
('Tony', {'pizza', 'burrito'})
('Alison', {'eggroll'})
("De'Mont", {'eggroll'})
("De'Mont", {'eggroll'})
("De'Mont", {'eggroll'})
("De'Mont", {'eggroll'})
("De'Mont", {'eggroll'})
('Amabel', None)
("De'Mont", {'eggroll'})
('Juan', None)
("De'Mont", {'eggroll'})
数据库连接已关闭import mysql.connector
from mysql.connector import Error
from mysql.connector import constantstry:conn = mysql.connector.connect(host='localhost',database='cookbook',user='cbuser',password='cbpass',client_flags=[constants.ClientFlag.FOUND_ROWS])if conn.is_connected():print('已成功连接到数据库')stmt = "SELECT name, foods FROM profile"print("执行查询:", stmt)cursor = conn.cursor(dictionary=True)cursor.execute(stmt)# 获取所有结果results = cursor.fetchall()total_records = len(results)print(f"查询返回 {total_records} 条记录")if cursor.description:print(f"列信息:")for i, col in enumerate(cursor.description):col_type = constants.FieldType.get_info(col[1])print(f"  {i+1}. {col[0]} ({col_type})")if total_records > 0:print("\n格式化显示结果:")print("=" * 40)print(f"{'Name':<15} | {'Foods':<30}")print("-" * 40)unique_names = set()empty_foods_count = 0for row in results:name = row['name']foods = row['foods']if foods is None:foods_display = "None"empty_foods_count += 1else:if isinstance(foods, set):foods_display = ", ".join(sorted(foods))else:foods_display = str(foods)if name in unique_names:name_display = f"{name} (重复)"else:name_display = nameunique_names.add(name)print(f"{name_display:<15} | {foods_display:<30}")print("=" * 40)print(f"统计:")print(f"  - 不重复姓名数量: {len(unique_names)}")print(f"  - 总记录数: {total_records}")print(f"  - 无食物偏好记录数: {empty_foods_count}")else:print("查询结果为空")cursor.close()conn.close()print('\n数据库连接已关闭')except Error as e:print(f'数据库操作错误: {e}')

已成功连接到数据库
执行查询: SELECT name, foods FROM profile
查询返回 20 条记录
列信息:1. name (STRING)2. foods (STRING)格式化显示结果:
========================================
Name            | Foods                         
----------------------------------------
Fred            | fadge, lutefisk, pizza        
Mort            | burrito, curry, eggroll       
Brit            | burrito, curry, pizza         
Carl            | eggroll, pizza                
Sean            | burrito, curry                
Alan            | curry, fadge                  
Mara            | fadge, lutefisk               
Shepard         | curry, pizza                  
Dick            | fadge, lutefisk               
Tony            | burrito, pizza                
Alison          | eggroll                       
De'Mont         | eggroll                       
De'Mont (重复)    | eggroll                       
De'Mont (重复)    | eggroll                       
De'Mont (重复)    | eggroll                       
De'Mont (重复)    | eggroll                       
Amabel          | None                          
De'Mont (重复)    | eggroll                       
Juan            | None                          
De'Mont (重复)    | eggroll                       
========================================
统计:- 不重复姓名数量: 14- 总记录数: 20- 无食物偏好记录数: 2数据库连接已关闭
http://www.lryc.cn/news/574993.html

相关文章：

  • LeetCode 3258.统计满足K约束的子字符串数量1
  • HTML表单元素
  • 线性结构之链表
  • 深度学习实战112-基于大模型Qwen+RAG+推荐算法的作业互评管理系统设计与实现
  • 机器学习01
  • SpringBoot高校党务系统
  • SpringBoot项目快速开发框架JeecgBoot——数据访问！
  • ros （二） 使用消息传递点云+rviz显示
  • Happy-LLM-Task06 ：3.1 Encoder-only PLM
  • C++设计模式（GOF-23）——04 C++装饰器模式（Decorator）（一个类同时继承和组合另一个类）解决类爆炸问题、模板装饰器
  • python3文件操作
  • Node.js特训专栏-实战进阶：8. Express RESTful API设计规范与实现
  • python的智慧养老院管理系统
  • klayout db::edge 里的 crossed_by_point 的坑点
  • mbedtls ssl handshake error,res:-0x2700
  • 从零开始的云计算生活——第二十三天，稍作休息，Tomcat
  • Excel数据转SQL语句（增删改查）
  • 阿里云Web应用防火墙3.0使用CNAME接入传统负载均衡CLB
  • DDNS-GO 使用教程：快速搭建属于自己的动态域名解析服务（Windows 版）
  • 大语言模型的通用局限性与全球技术演进
  • React Native【实战范例】账号管理（含转换分组列表数据的封装，分组折叠的实现，账号的增删改查，表单校验等）
  • 【版本控制教程】如何使用Unreal Engine 5 + UE源代码控制（Perforce P4）
  • 【GPU RAM】实时监控GPU内存分配（一）
  • 微信小程序中scss、ts、wxml
  • 如何在 Manjaro Linux 上安装 Docker 容器
  • 云计算-Azure Functions ：构建事件驱动的云原生应用报告
  • 《Effective Python》第十章 健壮性——警惕异常变量消失的问题
  • Encoder-only PLM RoBERTa ALBERT （BERT的变体）
  • 【大模型学习 | 量化】pytorch量化基础知识（1）
  • webpack5 css-loader 配置项中的modules