python编程-数据库pymysql

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()