# pymysql
安装
pip install pymysql
创建连接
import pymysql.cursors
connection = pymysql.connect(host='127.0.0.1',
user='root',
password='123456',
database='wss_demmo',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor #使返回类型变成字典类型
)
通过使用with语法可以自动回收数据库连接
查询数据
通过cursor来执行sql和获取sql查询结构;注意这个cursor是游标。
with connection:
with connection.cursor() as cursor:
cursor.execute('select * from test');
for item in cursor:
print(item)
获取的数据可以通过遍历cursor来获取,也可以通过下面的方法来获取:
cursor.fetchone()#获取一条数据
cursor.fetchall()#全部数据
cursor.fetchmany(3)#获取指定数量的数据
传参
cursor.execute('select * from test where name = %s','张三')
修改数据
cursor.execute("UPDATE test SET `name`='李四' WHERE id=%s",1)
connection.commit()#提交数据
批量修改数据
data_list = ((3, '李四'), (4, '王五'))
cursor.executemany("insert into test(id,name) values (%s,%s)", data_list)
connection.commit()#提交数据
事务
try:
connection.begin()
data_list = ((3, '李四'), (4, '王五'))
cursor.executemany("insert into test(id,name) values (%s,%s)", data_list)
connection.commit()
except:
connection.rollback()