安装
pip install pymysql 注：连接前要有可使用的账户及有权限、可操作的数据库 先来一个栗子：
import pymysql # 连接database
conn = pymysql.connect(
host=“你的数据库地址”,
user=“用户名”,
password=“密码”,
database=“数据库名”,
charset=“utf8” ) # 获取一个可以执行SQL语句的光标对象
cursor = conn.cursor() # 将结果作为字典返回的游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 定义要执行的SQL语句
sql = """
CREATE TABLE USER1 (
id INT auto_increment PRIMARY KEY ,
name CHAR(10) NOT NULL UNIQUE,
age TINYINT NOT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;
""" cursor.execute(sql) # 执行SQL语句
cursor.close() # 关闭光标对象
conn.close() # 关闭数据库连接
增删改操作：
import pymysql conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
cursor = conn.cursor() # 默认获取的数据是元祖类型
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 游标设置为字典类型 try:
# 返回受影响行数
# cursor.execute(sql,[user,pwd]) # 传参 列表，元组，字典皆可
effect_row = cursor.execute("update tb1 set pwd = '123' where id = %s", (11,))
# sql = "select * from userinfo where username=%(use)s and password=%(pas)s"
# cursor.execute(sql,{'use':user,'pas':pwd}) # 批量执行多条SQL语句，列表套元组传参[(,),(,)]
effect_row = cursor.executemany("insert into tb1(user,pwd,age)values(%s,%s,%s)", [("user1","pwd1","111"),("user2","pwd2","222")]) # 增删改都提交，不然无法保存数据
conn.commit() # 提交之后，可获取刚插入的数据的ID，插入多条数据拿到的是最后一条的ID
last_id = cursor.lastrowid except Exception as e: # 插入数据失败时, 回滚事务
conn.rollback() cursor.close()
conn.close()
查：
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')
cursor = conn.cursor()
cursor.execute(sql) row_1 = cursor.fetchone() # 获取单条查询数据
row_2 = cursor.fetchmany(3) # 可以获取指定数量的数据
row_3 = cursor.fetchall() # 获取多条查询数据 注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置
## 光标按绝对位置移动1
## cursor.scroll(1, mode="absolute")
## 光标按照相对位置(当前位置)移动1
## cursor.scroll(1, mode="relative") cursor.close()
conn.close()
简单封装：
import pymysql
from mypy3 import settings class SqlHelper(object):
def __init__(self):
self.host = settings.host
self.port = settings.port
self.user = settings.user
self.passwd = settings.passwd
self.db = settings.db
self.charset = settings.charset
self.connect() def connect(self):
self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db, charset=self.charset)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) def create(self,sql,args):
'''创建'''
self.cursor.execute(sql,args)
self.conn.commit()
return self.cursor.lastrowid def get_one(self, sql, args):
'''fetchone'''
self.cursor.execute(sql, args)
return self.cursor.fetchone() def get_list(self, sql, args=None):
'''fetchall'''
self.cursor.execute(sql, args)
return self.cursor.fetchall() def modify(self,sql,args):
'''增删改'''
self.cursor.execute(sql,args)
self.conn.commit() def multiple_modify(self,sql,args):
'''批量增删改'''
# self.cursor.executemany('insert into bd(id,name)values(%s,%s)',[(1,'name1'),(2,'name2')])
self.cursor.executemany(sql,args)
self.conn.commit() def close(self):
self.cursor.close()
self.conn.close() def __del__(self):
self.close()