Mysql:PDBC(Python操作数据库-mysql)
资料
https://pymysql.readthedocs.io/en/latest/index.html
安装
pip install pymysql
⚠️:MySQL Server – one of the following:
#!/usr/bin/env/python3
# -*- coding:utf-8 -*-
"""
@project: demo
@author: zy7y
@file: pymysql_demo.py
@ide: PyCharm
@time: 2020/8/26
"""
import pymysql
# 连接到数据库:创建 操作数据库对象
connection = pymysql.connect(host='192.168.0.222',
user='root',
password='root',
db='school',
charset='utf8',
# pymysql.cursors.DictCursor 以字典形式返回游标
cursorclass=pymysql.cursors.DictCursor)
sql = "select * from student"
# 创建 执行sql对象
cursor = connection.cursor()
# 执行sql
cursor.execute(sql)
# 增加、删除、修改 需要使用到 cursor.commit() 来提交
# fetchall() 返回所有结果 , fetchone() 返回第一个结果, fetchmany(返回结果条数)
result = cursor.fetchall()
print(result)
# 关闭数据库对象
connection.close()
官方推荐(部分已被我修改,完整的可查看上方资料)
#!/usr/bin/env/python3
# -*- coding:utf-8 -*-
"""
@project: demo
@author: zy7y
@file: pymysql_demo.py
@ide: PyCharm
@time: 2020/8/26
"""
import pymysql
# 连接到数据库:创建 操作数据库对象
connection = pymysql.connect(host='192.168.0.222',
user='root',
password='root',
db='school',
charset='utf8',
# pymysql.cursors.DictCursor 以字典形式返回游标
cursorclass=pymysql.cursors.DictCursor)
# 官网推荐写法:使用 try ... finally,以及with上下文管理
try:
# 查询
with connection.cursor() as cursor:
sql = "select * from student"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 修改
with connection.cursor() as cursor:
sql = "update student set loginpwd=%s where studentno = 1000"
# 测试回滚
zero = 0/0
# 此处root 将被替换进sql字符串中第一个%s,最终sql等于:update student set loginpwd='root' where studentno = 1000
cursor.execute(sql, ('root12345611',))
# 增加、删除、修改 需要提交, 这里不会自动提交事务
connection.commit()
# 查看改变行数,如果没有改变则返回0, 改变则返回行数
row = cursor.rowcount
if row:
print('修改成功')
else:
print('没有任何修改.')
except Exception as e:
# 发生错误 回滚
connection.rollback()
print(f'发生错误{e},已执行回滚')
finally:
# 关闭游标对象
cursor.close()
# 最后关闭数据库连接
connection.close()