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

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

news 2025/6/26 10:36:14

第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.lqws.cn/news/518815.html

相关文章：

  • 笔记02：布线-差分对的设置与添加
  • 定制开发开源AI智能名片与S2B2C商城小程序的内容分发体系构建：基于“1+N“素材复用模型的创新实践
  • 旧物回收小程序：让旧物重获新生的魔法钥匙
  • 14.Linux Docker
  • Mac安装Apache CXF的时候报错：/Library/Internet: No such file or directory
  • 淘宝API安全合规指南：避免数据泄露与封禁
  • 智能质检对呼叫中心职场有什么作用
  • 深入剖析 Spring AOP
  • 迁移学习—基于猫狗数据集
  • 【DataWhale组队学习】AI办公实践与应用-数据分析
  • Ubuntu 物理桌面远程访问教程（基于 RealVNC / mstsc）
  • 北斗导航 | 基于CNN-LSTM-PSO算法的接收机自主完好性监测算法
  • Spring Boot 项目文档编写工具推荐
  • 聚焦OpenVINO与OpenCV颜色通道转换的实践指南
  • UniApp 开发第一个项目
  • 防静电地板更换不是建材更新，而是重铸安全防线！
  • nn.Embedding 和 word2vec 的区别
  • 基于LangChat搭建RAG与Function Call结合的聊天机器人方案
  • Catchadmin 使用相关问题
  • Android11 深休后系统定时唤醒导致网络请求服务器过载
  • 数据结构篇-二分图
  • Class00.2线性代数
  • 【评估指标】IoU 交并比
  • Day.42
  • 高等数学》（同济大学·第7版）第七章 微分方程 第五节可降阶的高阶微分方程
  • 【网站内容安全检测】之1：获取网站所有链接sitemap数据
  • Web3D技术协议的AI革命：生成式模型如何改写交互标准？
  • 操作系统之内存管理(王道)
  • LeeCode349. 两个数的交集
  • 基于大模型的甲状腺结节预测及综合诊疗技术方案大纲