Python操作MySQL数据库完整指南：pymysql使用与存储引擎详解

pymysql

pymysql是Python中用于操作MySQL数据库的第三方模块，其API设计与MySQLdb高度相似，开发者可以沿用类似的操作习惯进行数据库交互。

下载安装

pip3 install pymysql

使用操作

1、执行SQL

import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

2、获取新创建数据自增ID

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
# 获取最新自增ID
new_id = cursor.lastrowid

3、获取查询数据

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()

注意：在通过fetch方法获取数据时，需按顺序进行。若需移动游标至特定位置，可使用cursor.scroll(num, mode)方法，例如：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

默认情况下，fetch获取的数据类型为元组。若希望获取字典类型的数据，可通过设置游标参数实现，即：

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()

数据库补充

导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据

mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构

导入现有数据库数据：

mysqldump -uroot -p密码 数据库名称 < 文件路径

MySQL中四种常用存储引擎的介绍

（1）：MyISAM存储引擎：不支持事务处理，也不支持外键约束。其优势在于访问速度较快，适用于对事务完整性无严格要求或以SELECT、INSERT操作为主的应用场景，此类应用可基本采用该引擎创建表。

支持3种不同的存储格式，分别是：静态表；动态表；压缩表

静态表：表中的字段均为非变长字段，因此每条记录长度固定。优点是存储速度极快，易于缓存，且故障后恢复相对容易；缺点是占用空间通常较大（存储时会按列定义宽度补足空格）。需注意：读取数据时默认会去除字段后的空格，若未留意，可能会忽略数据本身包含的空格。

动态表：记录长度不固定，优点是占用空间相对较少；缺点是频繁进行更新或删除操作易产生碎片，需定期执行OPTIMIZE TABLE或myisamchk -r命令以改善性能。

压缩表：由于每条记录单独压缩，因此访问开销非常小。

（2）InnoDB存储引擎*

该存储引擎提供具备提交、回滚及崩溃恢复能力的事务安全特性。但与MyISAM引擎相比，其写入处理效率较低，且会占用更多磁盘空间以保留数据和索引。 InnoDB存储引擎的特点：支持自动增长列，支持外键约束

(3)：MEMORY存储引擎

Memory存储引擎利用内存内容创建表。每个memory表仅对应一个磁盘文件，格式为.frm。由于数据存储在内存中，Memory类型的表访问速度极快，且默认使用HASH索引。但服务关闭后，表内数据将丢失。 MEMORY存储引擎的表可选择使用BTREE索引或HASH索引，两种不同类型的索引各有其适用场景。

Hash索引优点：

Hash索引结构特殊，检索效率极高。其索引检索可一次定位，无需像B-Tree索引那样从根节点遍历至枝节点再到页节点进行多次IO访问，因此Hash索引的查询效率远高于B-Tree索引。 Hash索引缺点：对于不精确查找（如“like”等范围查找），由于Hash算法基于等值计算，因此无效且不支持。

Memory类型的存储引擎主要适用于内容变化不频繁的字典表，或作为统计操作的中间结果表，以便高效分析中间结果并得出最终统计结果。对存储引擎为memory的表进行更新操作需谨慎，因数据未实际写入磁盘，务必考虑服务重启后如何获取这些修改后的数据。

（4）MERGE存储引擎

Merge存储引擎是一组MyISAM表的集合，这些MyISAM表必须具有完全相同的结构。Merge表本身不存储数据，对Merge类型表的查询、更新和删除操作实际上是对内部MyISAM表执行的操作。

总结

以上介绍了Python操作MySQL数据库的方法，希望有所帮助。如有疑问请留言，小编会及时回复。感谢大家对本站的支持！