pymysql 模块
用于python代码与数据库交互的模块
一、基础使用
使用方式:
① 连接数据库,返回一个连接,存储在变量中
② 获取光标对象(只有拿到光标才能执行sql语句)
③ sql语句
④ 使用光标对象执行sql语句
⑤ 关闭光标对象,关闭连接
'''登陆验证'''import pymysqluser = input('请输入用户名:').strip()pwd = input('请输入密码:').strip()# 连接数据库sqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'pymysqldb', charset = 'utf8')# 获取光标对象sqlcur = sqlconn.cursor()# sql语句sql = "select * from account where username = '%s' and password = '%s';" % (user,pwd)print(sql)# 使用光标对象执行sql语句ret = sqlcur.execute(sql)# 关闭光标对象sqlcur.close()# 关闭数据库连接sqlconn.close()if ret:print('登陆成功')else:print('登陆失败')
二、sql注入
恶意的sql语句,利用--绕过验证,后端拿到用户输入的内容不做检测直接左字符串的凭借,得到一个和预期不一致的语句
# 注入语句请输入用户名:xiaoming'-- '请输入密码:1select * from account where username = 'xiaoming'-- '' and password = '1'登陆成功
# 注入语句2请输入用户名:xiao' or 1=1 -- '请输入密码:1select * from account where username = 'xiao' or 1=1 -- '' and password = '1'登陆成功
解决方案:
对输入的内容做检测,pymysql内置了检测,让pymysql拼接sql语句即可
按照pymysql模块的写法定义好占位符,利用pymysql模块拼接sql语句
sql语句用占位符,光标对象.execute(sql,[参数,参数])
'''登陆验证'''import pymysqluser = input('请输入用户名:').strip()pwd = input('请输入密码:').strip()# 连接数据库sqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'pymysqldb', charset = 'utf8')# 获取光标对象sqlcur = sqlconn.cursor()# sql语句(按照pymysql的语句定义占位符)sql = "select * from account where username=%s and password=%s;"# 利用光标执行sql语句(利用pymysql拼接sql语句)ret = sqlcur.execute(sql,[user,pwd])# 关闭光标对象sqlcur.close()# 关闭数据库连接sqlconn.close()if ret:print('登陆成功')else:print('登陆失败')
三、数据库的增删改
设计数据库的操作,必须用commit() 方法
'''数据库的增删改'''import pymysql# 连接数据库sqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'pymysqldb', charset = 'utf8')# 获取光标对象sqlcur = sqlconn.cursor()# sql语句(按照pymysql的语句定义占位符)sql = "insert into account values (%s,%s)" # 增sql1 = "update account set password = %s where username = %s;" # 改sql2 = "delete from account where username = %s" # 删# 利用光标执行sql语句(利用pymysql拼接sql语句)sqlcur.execute(sql,['xiao','123'])sqlcur.execute(sql1,['456','xiao'])sqlcur.execute(sql2,['xiao'])# 操作数据库需要提交sqlconn.commit()# 关闭sqlcur.close()sqlconn.close()
四、其他方法
光标对象.fetchall() 获取查询到的所有结果,以元组的形式返回
光标对象.fetchone() 获取查询到的一个结果,类似于生成器,一个一个获取
光标对象.fetchmany(N) 指定获取查询到的N条记录
import pymysqlsqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'pymysqldb', charset = 'utf8')sqlcur = sqlconn.cursor()sql = "select * from account"sqlcur.execute(sql)ret = sqlcur.fetchall()print(ret) # (('dog', '123'), ('fish', '123'), ('monkey', '123'), ('pig', '123'), ('xiaobai', '123'), ('xiaoming', '123'))sqlcur.close()sqlconn.close()'''结果(('dog', '123'), ('fish', '123'), ('monkey', '123'), ('pig', '123'), ('xiaobai', '123'), ('xiaoming', '123'))'''
import pymysqlsqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '123456', database = 'pymysqldb', charset = 'utf8')sqlcur = sqlconn.cursor()sql = "select * from account"sqlcur.execute(sql)# ret = sqlcur.fetchall()# print(ret) # (('dog', '123'), ('fish', '123'), ('monkey', '123'), ('pig', '123'), ('xiaobai', '123'), ('xiaoming', '123'))ret = sqlcur.fetchmany(2)print(ret) # (('dog', '123'), ('fish', '123'))ret = sqlcur.fetchone()print(ret) # ('monkey', '123')sqlcur.close()sqlconn.close()
连接对象.rollback() 回滚,撤销操作
光标对象.scroll(1,mode='absolute') 绝对位置,移动到第一行末尾,从第二行开始查
光标对象.scroll(1,mode='relative') 相对位置,基于当前位置往后移动(可以使用负数,往前移动),但索引越界会报错
获取id
光标对象.lastrowid 获取最后一条记录的id
修改返回的结果为字典类型
sqlcur = sqlconn.cursor(cursor = pymysql.cursors.DictCursor)