pymysql是python直接和mysql进行交互的模块
数据库连接
首先建立一个数据库test和一张测试表userinfo
CREATE TABLE userinfo(
id INT AUTO_INCREMENT PRIMARY KEY ,
username CHAR(10) UNIQUE ,
password CHAR(20)
)engine innodb DEFAULT CHARSET utf8
import pymysql
conn = pymysql.connect(
host='146.56.194.28',
port=3306,
database="test",
user='root',
password='xxxxx',
charset='utf8'
)
cursor = conn.cursor() #获得mysql操作对象
sql = "select * from userinfo"
res = cursor.execute(sql)
基础操作
查询
指定返回数据的格式
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#指定以字典的形式返回数据
查询多条数据
cursor = conn.cursor() #获得mysql光标操作对象
sql = "select * from userinfo where username=%s and passowrd=%s"
res = cursor.execute(sql,[username,pwd])
data = cursor.fetchall() #当不指定格式时,默认返回的是一个元祖
获取单条数据
cursor = conn.cursor() #获得mysql操作对象
sql = "select * from userinfo where username=%s and passowrd=%s"
res = cursor.execute(sql,[username,pwd])
data = cursor.fetchone()
进阶用法
- cursor.fetchmany(3):一次性获取指定数量数据
- cursor.scroll(1,mode=“absolute”):将光标移动至绝对位置
- cursor.scroll(1,mode=“relative”):将光标移动至相对位置
增加
cursor = conn.cursor()
sql = "INSERT INTO userinfo(username,password) VALUES(%s,%s)"
res = cursor.execute(sql,[username,password])
conn.commit() #注意需要执行提交操作
cursor.close()
conn.close()
插入条目失败需要回滚的情况
try:
res = cursor.execute(sql,[username,password])
conn.commit()
except Exception as e:
print(“插入失败,进行回滚”,,str(e))
conn.close()
cursor.close()
conn.close()
批量插入
cursor = conn.cursor()
sql = "INSERT INTO userinfo(username,password) VALUES(%s,%s)"
data = [ ("a","123"),("b","345"),("c","567")]
cursor.executemany(sql,data)
conn.commit()
cursor.close()
conn.close()
注意传入的数据必须是可迭代对象
删
cursor = conn.cursor()
sql = "DELETE FROM userinfo WHERE username=%s"
cursor.execute(sql,username)
conn.commit()
改
cursor = conn.cursor()
sql = "UPDATE userinfo SET password=%s WHERE username=%s"
cursor.execute(sql,[password,username])
conn.commit()