源码中对 PyMySQL 的定义：
PyMySQL: A pure-Python MySQL client library
数据库的连接
手工关闭数据库的 connection
import pymysql
# 连接数据库一种比较优雅的写法
config = {
'host': 'localhost',
'user': 'root',
'password': 'xx',
'database': 'dbx',
'charset': 'utf8mb4',
'port': 3306 # 注意端口为int 而不是str
}
connection = pymysql.connect(**config)
cursor = connection.cursor()
try:
# 创建一张表
c_sql = '''CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`employee_id` int NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=1'''
cursor.execute(c_sql)
# 插入一条记录
i_sql = "INSERT INTO `users` (`email`, `password`) VALUES ('cancy', 'very-secret', 28)"
cursor.execute(i_sql)
# 在同一个cursor上统一提交
connection.commit()
except Exception as e:
connection.rollback()
print('Failed:', e)
finally:
cursor.close()
connection.close()
executemany 方法
同时添加多个数据时使用 executemany 方法
try:
with connection.cursor() as cursor:
# 在写sql语句时，不管字段为什么类型, 占位符统一使用 %s, 且不能带引号
sql = "INSERT INTO `users` (`username`, `password`, `employee_id`) VALUES (%s, %s, %s)"
cursor.executemany(sql, [('sam', 'secret1', 29), ('Bob', 'secret2', 32)])
connection.commit()
finally:
connection.close()
但是值得注意的有两点：
- 在写sql语句时，不管字段为什么类型, 占位符统一使用 %s, 且不能带引号
- 添加数据的格式必须为 list[tuple(), tuple(), tuple()] 或者 tuple(tuple(), tuple(), tuple())
- 在资源足够的情况下，当然可以将需要插入的所有数据一次性全部放入 list 或者tuple 中，然后执行 executemany 方法，但实际中这种方法是不可取的，因为服务端能够接收的最大数据包是有限制的
DictCursor
游标类型有 Cursor、DictCursor、SSCursor、SSDictCursor等4种类型，但是常用的是前面两种
- Cursor默认类型，查询返回 list，如果是默认Cursor，那么在 cursorclass 中就不需要指定
- DictCursor类型，查询返回dict，包括属性名
import pymysql.cursors
# 连接数据库
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
query查询
# 引入这个模块就ok
import pymysql.cursors
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'user',
'password': 'passwd',
'database': 'db',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.Cursor,
}
# pymysql.cursors.Cursor 或者 pymysql.cursors.DictCursor
# 连接数据库
connection = pymysql.connect(**config)
try:
with connection.cursor() as cursor:
sql = 'SELECT * FROM commodity WHERE price > 100 ORDER BY price'
count = cursor.execute(sql) # 影响的行数
print(count)
results = cursor.fetchall() # 取出所有行
for row in results: # 打印结果
print(row)
# 查询不是事物的，不需要commit
# connection.commit()
except Exception as e:
# connection.rollback() # 对于事物型的操作，比较适合commit后，如果失败则回滚，在本例中仅是查询，无须回滚
logger.info(e)
finally:
connection.close()
关于fetch结果：
- cursor.fetchone() # 获取1条数据
- cursor.fetchmany(n) # 获取n条数据
- cursor.fetchall() # 获取所有数据
delete 与 update 操作
delete和update操作显然都是事物的，下面以delete为例，伪码如下：
cursor = connection.cursor()
d_sql = 'DELETE FROM students WHERE age < 24'
try:
cursor.execute(d_sql)
connection.commit()
logger.info('delete success')
except:
connection.rollback()
logger.info('delete error')
print('delete rowcount = %d' %cursor.rowcount)
cursor游标的移动
connection = pymysql.connect(**config)
try:
with connection.cursor() as cursor:
sql = 'SELECT * FROM commodity WHERE price > 100 ORDER BY price'
cursor.execute(sql)
result1 = cursor.fetchone() # 取出第一个结果
result2 = cursor.fetchall() # 执行了上述代码后，游标会向下移动一个，此时获取到的是剩下n-1个结果
for row in result2: # 打印结果
print(row)
except Exception as e:
logger.info(e)
finally:
connection.close()
cursor.scroll(0, mode = ‘absolute’)
相对于绝对位置移动，例如在上例中，使用了 fetchone之后，游标向下移动了一个位置，要想 fetchall 得到全部的结果，此时可以使用 cursor.scroll(0, mode = ‘absolute’) 将游标主动移动到绝对位置为0的位置，然后再使用 fetchallcursor.scroll(1, mode = ‘relative’)
相对于当前位置移动
MySQL连接池
https://blog.csdn.net/kai404/article/details/78900220
https://github.com/jkklee/pymysql-pool
