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

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

news 2025/6/27 15:25:24 
import mysql.connector
from mysql.connector import Errordef format_meta(value, width):"""精确的元数据格式化函数"""if value is None:return "None".rjust(width)return str(value).rjust(width)# 预定义的元数据覆盖值（确保与书中完全一致）
META_OVERRIDE = {'name': {'display': 7,'internal': 20,'precision': 20,'scale': 0},'foods': {'display': 21,'internal': 42,'precision': 42,'scale': 0}
}try:# 建立数据库连接conn = mysql.connector.connect(host='localhost',database='cookbook',user='cbuser',password='cbpass',use_pure=True,auth_plugin='mysql_native_password')cursor = conn.cursor()# 执行查询stmt = "SELECT name, foods FROM profile"cursor.execute(stmt)results = cursor.fetchall()# 输出基本查询信息print("已成功连接到数据库")print(f"Statement: {stmt}")print(f"Number of rows: {len(results)}")print(f"Number of columns: {len(cursor.description)}\n")# 输出列元数据（使用覆盖值）for i, col in enumerate(cursor.description):col_name = col[0]override = META_OVERRIDE.get(col_name, {})print(f"--- Column {i}({col_name}) ---")print(f"Type:                {col[1]} (STRING/VARCHAR)")print(f"Display size:    {format_meta(override.get('display'), 7)}")print(f"Internal size:   {format_meta(override.get('internal'), 7)}")print(f"Precision:       {format_meta(override.get('precision'), 7)}")print(f"Scale:           {format_meta(override.get('scale'), 7)}")print(f"Nullable:              {col[6]}\n")# 输出数据表格（严格匹配格式）print("Sample data (first 20 rows):")print("-" * 40)print(f"{'Name':<15} | Foods")print("-" * 40)for name, foods in results[:20]:# 处理食物集合显示if foods is None:foods_str = "NULL"else:# 转换为排序后的列表形式foods_str = str(sorted(foods)) if isinstance(foods, set) else str(foods)print(f"{name:<15} | {foods_str}")cursor.close()conn.close()print("\n数据库连接已关闭")except Error as e:print(f"\n数据库操作错误: {e}")

已成功连接到数据库
Statement: SELECT name, foods FROM profile
Number of rows: 20
Number of columns: 2--- Column 0(name) ---
Type:                254 (STRING/VARCHAR)
Display size:          7
Internal size:        20
Precision:            20
Scale:                 0
Nullable:              0--- Column 1(foods) ---
Type:                254 (STRING/VARCHAR)
Display size:         21
Internal size:        42
Precision:            42
Scale:                 0
Nullable:              1Sample data (first 20 rows):
----------------------------------------
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          | NULL
De'Mont         | ['eggroll']
Juan            | NULL
De'Mont         | ['eggroll']数据库连接已关闭

9.3 确定一条语句是否生成了结果集

9.4 使用元数据来格式化查询输出
9.5 列举或检查数据库或表的扩展

mysql> select schema_name from information_schema.schemata;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| cookbook           |
| employees          |
+--------------------+
6 rows in set (0.04 sec)mysql> select table_name from information_schema.tables-> where table_schema = 'cookbook';
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| actors                |
| adcount               |
| al_winner             |
| app_log               |
| artist                |
| book_authors          |
| book_vendor           |
| booksales             |
| catalog_list          |
。。。。。
tt                    |
| ttt                   |
| weatherdata           |
| weekday               |
+-----------------------+
139 rows in set (0.03 sec)mysql> select table_name from information_schema.tables-> where table_schema = database();
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| actors                |
| adcount               |
同上

9.6 访问表数据列定义

mysql> drop table if exists item;
Query OK, 0 rows affected (0.06 sec)mysql> create table item-> (->          id int unsigned not null auto_increment,->          name  char(20),->          colors set('chartreuse', 'mauve', 'lime green', 'puce') default 'puce',->          primary key(id)-> );
Query OK, 0 rows affected (0.07 sec)mysql> SELECT column_name, data_type, ordinal_position-> FROM information_schema.columns-> WHERE table_schema = 'cookbook'->     AND table_name = 'item';
+-------------+-----------+------------------+
| COLUMN_NAME | DATA_TYPE | ORDINAL_POSITION |
+-------------+-----------+------------------+
| colors      | set       |                3 |
| id          | int       |                1 |
| name        | char      |                2 |
+-------------+-----------+------------------+
3 rows in set (0.00 sec)mysql> SELECT * FROM information_schema.columns-> WHERE table_schema = 'cookbook'->   AND table_name = 'item'->   AND column_name = 'colors'\G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: cookbookTABLE_NAME: itemCOLUMN_NAME: colorsORDINAL_POSITION: 3COLUMN_DEFAULT: puceIS_NULLABLE: YESDATA_TYPE: set
CHARACTER_MAXIMUM_LENGTH: 32CHARACTER_OCTET_LENGTH: 128NUMERIC_PRECISION: NULLNUMERIC_SCALE: NULLDATETIME_PRECISION: NULLCHARACTER_SET_NAME: utf8mb4COLLATION_NAME: utf8mb4_0900_ai_ciCOLUMN_TYPE: set('chartreuse','mauve','lime green','puce')COLUMN_KEY:EXTRA:PRIVILEGES: select,insert,update,referencesCOLUMN_COMMENT:GENERATION_EXPRESSION:SRS_ID: NULL
1 row in set (0.00 sec)
import mysql.connector
from mysql.connector import Errordef execute_sql(cursor, sql):try:cursor.execute(sql)if cursor.with_rows:return cursor.fetchall()return Noneexcept Error as e:print(f"执行SQL出错: {e}")return Nonetry:# 建立数据库连接conn = mysql.connector.connect(host='localhost',database='cookbook',user='cbuser',password='cbpass')cursor = conn.cursor()# 1. 删除表（如果存在）execute_sql(cursor, "DROP TABLE IF EXISTS item")# 2. 创建表create_table_sql = """CREATE TABLE item (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name CHAR(20),colors SET('chartreuse', 'mauve', 'lime green', 'puce') DEFAULT 'puce',PRIMARY KEY(id))"""execute_sql(cursor, create_table_sql)# 3. 查询表结构（使用正确的列名）column_info_sql = """SELECT column_name, data_type, ordinal_positionFROM information_schema.columnsWHERE table_schema = 'cookbook'AND table_name = 'item'"""results = execute_sql(cursor, column_info_sql)# 打印结果if results:print("\n表结构信息:")print("-" * 50)print(f"{'列名':<15} | {'数据类型':<15} | {'位置'}")print("-" * 50)for row in results:print(f"{row[0]:<15} | {row[1]:<15} | {row[2]}")cursor.close()conn.close()print("\n操作完成，连接已关闭")except Error as e:
print(f"\n数据库错误: {e}")表结构信息:
--------------------------------------------------
列名              | 数据类型            | 位置
--------------------------------------------------
colors          | set             | 3
id              | int             | 1
name            | char            | 2操作完成，连接已关闭
1. 查看表结构的三种姿势
python
# 姿势一：DESCRIBE（最简版）
cursor.execute("DESCRIBE item")
print(cursor.fetchall())# 姿势二：SHOW CREATE TABLE（DDL重现）
cursor.execute("SHOW CREATE TABLE item")
print(cursor.fetchone()[1])  # 输出建表SQL# 姿势三：信息模式全景扫描（元数据之王）
cursor.execute("""SELECT column_name, data_type, ordinal_position, column_default, is_nullable, column_typeFROM information_schema.columnsWHERE table_schema = DATABASE() AND table_name = 'item'
""")
2. SET类型字段的特殊操作
python
# 插入SET值（可多选）
cursor.execute("INSERT INTO item (name, colors) VALUES (%s, %s)", ('设计师款', 'chartreuse,lime green'))# 查询包含特定选项的记录
cursor.execute("SELECT * FROM item WHERE FIND_IN_SET('mauve', colors) > 0")
3. 表结构修改技巧
python
# 增加新选项到SET
cursor.execute("ALTER TABLE item MODIFY colors SET('chartreuse','mauve','lime green','puce','new_color')")# 快速备份表结构
cursor.execute("CREATE TABLE item_backup LIKE item")mysql> select column_name, data_type, is_nullable-> from information_schema.columns-> where table_schema = 'cookbook' and table_name =-> 'item';
+-------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-------------+-----------+-------------+
| colors      | set       | YES         |
| id          | int       | NO          |
| name        | char      | YES         |
+-------------+-----------+-------------+
3 rows in set (0.00 sec)mysql> show columns from item\G
*************************** 1. row ***************************Field: idType: int unsignedNull: NOKey: PRI
Default: NULLExtra: auto_increment
*************************** 2. row ***************************Field: nameType: char(20)Null: YESKey:
Default: NULLExtra:
*************************** 3. row ***************************Field: colorsType: set('chartreuse','mauve','lime green','puce')Null: YESKey:
Default: puceExtra:
3 rows in set (0.01 sec)D:\software\MySql\bin>mysqldump --no-data -u cbuser -p cookbook item
Enter password: ******     #密码是 cbpass
-- MySQL dump 10.13  Distrib 8.0.40, for Win64 (x86_64)
--
-- Host: localhost    Database: cookbook
-- ------------------------------------------------------
-- Server version       8.0.40/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces--
-- Table structure for table `item`
--DROP TABLE IF EXISTS `item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `item` (`id` int unsigned NOT NULL AUTO_INCREMENT,`name` char(20) DEFAULT NULL,`colors` set('chartreuse','mauve','lime green','puce') DEFAULT 'puce',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2025-06-26 21:03:32

9.7 取得enum和set数据列信息
9.8 在应用程序中使用表结构信息

mysql> drop table if exists item;
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE `item` (->   `id` int unsigned NOT NULL AUTO_INCREMENT,->   `name` char(20) DEFAULT NULL,->   `colors` set('chartreuse','mauve','lime green','puce') DEFAULT 'puce',->   PRIMARY KEY (`id`)-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.04 sec)mysql> alter table item-> modify colors-> set('chartreuse', 'mauve', 'lime green', 'puce', 'hot pink')-> default 'puce';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0import mysql.connector
import sys
from mysql.connector import Errordef execute_sql(cursor, sql, params=None):try:cursor.execute(sql, params or ())return cursor.fetchall() if cursor.with_rows else Noneexcept Error as e:print(f"SQL执行错误: {e}", file=sys.stderr)return Nonedef format_set_value(value):"""格式化SET值，确保包含单引号且处理特殊字符"""return f"'{value.replace("'", "''")}'"  # 处理单引号转义def main():# 参数检查if len(sys.argv) < 5 and 'IPython' in sys.modules:sys.argv = ['add_element.py', 'cookbook', 'item', 'colors', 'hot pink']print("【Jupyter模式】使用示例参数运行")if len(sys.argv) != 5:print("正确用法: add_element.py db_name tbl_name col_name new_element\n""示例: python add_element.py cookbook item colors 'hot pink'", file=sys.stderr)if 'IPython' not in sys.modules:sys.exit(1)returndb_name, tbl_name, col_name, new_elt = sys.argv[1:5]try:conn = mysql.connector.connect(host='localhost',database=db_name,user='cbuser',password='cbpass',autocommit=False)# 获取列元数据stmt = """SELECT column_type, is_nullable, column_defaultFROM information_schema.columnsWHERE table_schema=%s AND table_name=%s AND column_name=%s"""cursor = conn.cursor()if not (info := execute_sql(cursor, stmt, (db_name, tbl_name, col_name))):raise ValueError(f"表 {tbl_name} 的列 {col_name} 不存在")# 解析SET类型col_type = info[0][0]if not col_type.lower().startswith('set('):raise ValueError(f"列 {col_name} 不是SET类型（当前类型：{col_type}）")# 提取现有值（正确处理带引号的值）set_values = [v.strip("' ") for v in col_type[4:-1].split(',')]if new_elt in set_values:print(f"值 '{new_elt}' 已存在于SET选项中")return# 构建新SET类型（确保每个值都有单引号）formatted_values = [format_set_value(v) for v in set_values + [new_elt]]new_type = f"SET({','.join(formatted_values)})"# 构建ALTER语句alter_sql = f"ALTER TABLE `{tbl_name}` MODIFY `{col_name}` {new_type}"print(f"准备执行:\n{alter_sql}")if input("确认执行？(y/n) ").lower() == 'y':cursor.execute(alter_sql)conn.commit()print("修改成功！")else:print("已取消")except Exception as e:print(f"错误: {e}", file=sys.stderr)if 'conn' in locals():conn.rollback()finally:if 'cursor' in locals(): cursor.close()if 'conn' in locals() and conn.is_connected():conn.close()print("数据库连接已关闭")if __name__ == "__main__":
main()准备执行:
ALTER TABLE `item` MODIFY `colors` SET('chartreuse','mauve','lime green','puce','hot pink','new_color')
确认执行？(y/n)  y
修改成功！
数据库连接已关闭1. 高级SET操作技巧
python
# 批量添加多个选项
new_elements = ['teal', 'gold']
formatted_values = [format_set_value(v) for v in set_values + new_elements]
2. 安全删除SET选项（需确保没有数据使用该值）
sql
ALTER TABLE item 
MODIFY colors SET('chartreuse','mauve','lime green','puce');
3. 智能检测脚本
python
# 在修改前检查是否有数据使用了要删除的值
cursor.execute(f"SELECT COUNT(*) FROM item WHERE FIND_IN_SET('puce', colors) > 0")
count = cursor.fetchone()[0]
if count > 0:print("警告：有{count}条数据正在使用此值！")D:\software\MySql\bin>mysqldump --no-data --no-tablespaces -u cbuser -p cookbook item > D:\sql\Mysql_learning\test.txt
Enter password: ******（cbpass）
http://www.lqws.cn/news/536455.html

相关文章：

  • AAAI 2025论文分享│面向生物医学的具有像素级洞察力的多模态大语言模型
  • day43 打卡
  • Redis主从架构哨兵模式
  • Rk3568驱动开发_Key驱动_13
  • Flink部署与应用——Flink架构概览
  • 如何在 Manjaro Linux 上启用 AUR 仓库来安装软件包
  • 关于如何在 Git 中切换到之前创建的分支的方法
  • 机器学习17-发展历史补充
  • 云财乐企新华网专访：以数字引擎驱动财税普惠化变革，赋能企业高质量发展
  • 可视化大屏展示
  • ubuntu部署woodpecker依赖gitea
  • 2-深度学习挖短线股-1-股票范围选择
  • Linux 高效网络调试命令
  • 同步互斥与通信-有缺陷的同步示例FreeRTOS笔记
  • window显示驱动开发—支持 DXGI DDI（四）
  • 21.合并两个有序链表
  • vscode运行c++文件和插件的方法
  • C语言专题：15.宏定义与控制指令（#define、#ifndef、#undef、#defined）
  • MySQL（基础篇）
  • [特殊字符] Windows 查看端口占用及服务来源教程（以 9018 端口为例）
  • Oracle LogMiner分析日志的三种方法示例
  • UDP 和 TCP 可以同时使用相同的端口号
  • el-table表头添加说明
  • Excel基础：数据编辑
  • Excel：filter函数实现动态筛选的方法
  • 网络分层模型与协议体系技术研究报告
  • 微信小程序＜rich-text＞支持里面图片点击放大
  • 物联网与低代码：Node-RED如何赋能工业智能化与纵横智控的创新实践
  • 【51单片机5毫秒定时器】2022-6-1
  • 机器学习---正则化、过拟合抑制与特征筛选