简介

官方文档

https://pymysql.readthedocs.io/en/latest/index.html

安装

pip install PyMySQL

说明

connection对象

连接对象，可创建数据库连接,创建对象的类为class pymysql.connections.Connection，该对象的操作如下

cursor对象

主要使用class pymysql.cursors.Cursor(connection),方法or属性说明如下

其他cursor对象详见文档

使用

连接数据库

初始化数据库

# 导入pymysql
import pymysql
# 创建连接
conn = pymysql.connect(host='localhost', 
                       user='root', 
                       password='root',
                       database='tst', 
                       charset='utf8')
# 创建游标
cursor = conn.cursor()  

数据查询

select_sql = 'select * from content'
cursor.execute(select_sql)
res = cursor.fetchall()
print(res)

增删改

# 要执行的增删改sql语句
update_sql = 'UPDATE content SET name`=%s WHERE id=%s'
# 执行语句
cursor.execute(update_sql, ['xiaixai',2])
# 提交
conn.commit()

示例

# 创建连接
conn = pymysql.connect(host='localhost', user='root', password='root', database='tb', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 查询数据
select_sql = 'select * from content'
cursor.execute(select_sql)
# res = cursor.fetchone()
res = cursor.fetchall()
# res = cursor.fetchmany(2)
print(res)

# 插入数据
insert_sql = 'insert into content(name) values(%s)'
# res = cursor.execute(insert_sql, ['xiaozhong'])
res = cursor.executemany(insert_sql, ['xiaoxi', 'xiaobei', 'xiaonan'])  # 批量插入
print(res) # 影响条数
conn.commit()
#
# 修改数据
update_sql = 'UPDATE content SET name=%s WHERE id=%s'
# res = cursor.execute(update_sql, ['xiaodong', 1])
res = cursor.executemany(update_sql, [('xiaozhong', 1), ('小小xiao', 2)])
print(res)
conn.commit()

# 删除数据
delete_sql = 'delete from content WHERE id=%s'
# res = cursor.execute(delete_sql, [1])
res = cursor.executemany(delete_sql, [1, 2, 3,4])
print(res)
conn.commit()

# 注意处理数据后记得关闭游标和连接
cursor.close()
conn.close()