mysql五：pymysql模块
一、介绍
之前都是通过MySQ自带的命令行客户端工具Mysql来操作数据库，那如何在Python程序中操作数据库呢？这就需要用到pymysql模块了。
这个模块本质就是一个套接字客户端软件，使用前需要实现安装
pip3 install pymysql
二、链接、执行sql、关闭游标
通过Navicat创建一个db9数据库并创建userinfo表
mysql> desc userinfo; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user | char(16) | NO | | NULL | | | pwd | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ mysql> select * from userinfo; +----+------+------+ | id | user | pwd | +----+------+------+ | 1 | egon | 123 | | 2 | alex | 456 | +----+------+------+ """ """ # 登录数据库，允许远程访问 mysql> grant all on *.* to 'root'@'%' identified by '1234'; Query OK, 0 rows affected, 1 warning (0.01 sec) # 立即刷新数据库，配置立即生效 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
mysql模块的基本使用：
# -*- coding:utf-8 -*- __author__ = 'Qiushi Huang' import pymysql # 套接字 user = input('user>>:').strip() pwd = input('pwd>>:').strip() # 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿到游标 course=conn.cursor() # 执行完毕返回的结果集默认以元组显示 # 执行sql语句 sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd) rows = course.execute(sql) # 游标的execute接口传入sql语句,返回sql查询成功的记录数 # 资源回收 course.close() # 关闭游标 conn.close() # 关闭连接 # 进行判断 if rows: print('登录成功') else: print('登录失败') """ user>>:egon pwd>>:123 登录成功 """
三、execute()之sql注入
注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符
# -*- coding:utf-8 -*- __author__ = 'Qiushi Huang' # pip3 install pymysql import pymysql # 套接字 user = input('user>>:').strip() pwd = input('pwd>>:').strip() # 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿到游标 course=conn.cursor() # 执行完毕返回的结果集默认以元组显示 # 执行sql语句 sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd) print(sql) # 查看被注入的sql rows = course.execute(sql) # 游标的execute接口传入sql语句,返回sql查询成功的记录数 # 资源回收 course.close() # 关闭游标 conn.close() # 关闭连接 # 进行判断 if rows: print('登录成功') else: print('登录失败') """ user>>:egon" -- xxxx # 双"-"之后全都变为注释 pwd>>:123 select * from userinfo where user = "egon" -- xxxx" and pwd="123" 登录成功 """ # 不用输入密码直接登录，"和sql语句中的"拼接 ， 或判断与1=1 # user>>:xxx" or 1=1 -- hahahwdda" and pwd="" # pwd>>: # select * from userinfo where user = "xxx" or 1=1 -- hahahwdda and pwd=""" and pwd="" # 登录成功
根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'
解决办法：
# 原来是我们对sql进行字符串拼接 # sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd) # print(sql) # res=cursor.execute(sql) #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了） sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上 res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
# -*- coding:utf-8 -*- __author__ = 'Qiushi Huang' # pip3 install pymysql import pymysql # 套接字 user = input('user>>:').strip() pwd = input('pwd>>:').strip() # 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿到游标 course=conn.cursor() # 执行完毕返回的结果集默认以元组显示 # 执行sql语句 # sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd) # print(sql) # 查看被注入的sql sql = 'select * from userinfo where user = %s and pwd=%s' rows = course.execute(sql, (user, pwd)) # 不使用字符串拼接，采用execute在后面传值的方式 # 资源回收 course.close() # 关闭游标 conn.close() # 关闭连接 # 进行判断 if rows: print('登录成功') else: print('登录失败') """ user>>:xxx" or 1=1 -- hahahwdda" and pwd="" pwd>>: 登录失败 """
四、增、删、改：conn.commit()
增、删、改的操作相似。以对表格添加记录为例。
# -*- coding:utf-8 -*- __author__ = 'Qiushi Huang' import pymysql # 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿游标 cursor=conn.cursor() # 执行sql # 增删改 sql='insert into userinfo(user,pwd) values(%s,%s);' # 单一操作 # res=cursor.execute(sql,("wxx","123")) #执行sql语句，返回sql影响成功的行数 # print(res) # 批量操作 cursor.executemany(sql,[('yxx','123'), ('egon1','123'), ('zurong','123')]) conn.commit() # 注意：提交后记录才会插入成功 # 关闭链接 cursor.close() conn.close() """ +----+--------+------+ | id | user | pwd | +----+--------+------+ | 1 | egon | 123 | | 2 | alex | 456 | | 3 | hqs | 123 | | 5 | wxx | 123 | | 8 | yxx | 123 | | 9 | egon1 | 123 | | 10 | zurong | 123 | +----+--------+------+ """
五、查：fetchone,fetchmany,fetchall
# 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿游标 cursor=conn.cursor() # 执行sql查询 rows = cursor.execute('select * from userinfo;') # 把sql发给服务端 # print(rows) # 显示的仅仅是受影响的行数 # 为了得到执行结果，需要使用fetchone()函数，一次得到一条服务端执行记录 print(cursor.fetchone()) print(cursor.fetchone()) """ (1, 'egon', '123') (2, 'alex', '456') """ conn.commit() # 注意：提交后记录才会插入成功 # 关闭链接 cursor.close() conn.close()
可以看到上面的记录没有字段信息，很难分辨数据具体属于哪个字段。因此需要在拿游标时，使用：cursor=conn.cursor(pymysql.cursors.DictCursor) 得到基于字典的游标。
conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿游标 cursor=conn.cursor(pymysql.cursors.DictCursor) # 基于字典的游标 # 执行sql查询 rows = cursor.execute('select * from userinfo;') # print(cursor.fetchone()) # print(cursor.fetchone()) """ {'id': 1, 'user': 'egon', 'pwd': '123'} {'id': 2, 'user': 'alex', 'pwd': '456'} """ # 一次取多个 print(cursor.fetchmany(3)) """ [{'id': 1, 'user': 'egon', 'pwd': '123'}, {'id': 2, 'user': 'alex', 'pwd': '456'}, {'id': 3, 'user': 'hqs', 'pwd': '123'}] """ # 全部取出 print(cursor.fetchall()) # 如果是和上面的和语句一起执行，则是取出id=3之后的数据 print(cursor.fetchall()) # 上一条取完所有数据，游标已经到了记录结尾，此次为空 """ [{'id': 1, 'user': 'egon', 'pwd': '123'}, {'id': 2, 'user': 'alex', 'pwd': '456'}, {'id': 3, 'user': 'hqs', 'pwd': '123'}] [{'id': 5, 'user': 'wxx', 'pwd': '123'}, {'id': 8, 'user': 'yxx', 'pwd': '123'}, {'id': 9, 'user': 'egon1', 'pwd': '123'}, {'id': 10, 'user': 'zurong', 'pwd': '123'}] [] """ conn.commit() # 注意：提交后记录才会插入成功 # 关闭链接 cursor.close() conn.close()
从上例中可以学习到fetchone，fetchmany，fetchall这三种方法的使用和区别。但是由此也看到，读取记录存在游标位置问题。
相对绝对位置移动：cursor.scroll(3,mode='absolute')
相对当前位置移动：cursor.scroll(3,mode='relative')
conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) # 拿游标 cursor=conn.cursor(pymysql.cursors.DictCursor) # 基于字典的游标 # 执行sql查询 rows = cursor.execute('select * from userinfo;') print(cursor.fetchall()) # 先移动到最后 # 游标移动——相对绝对位置移动 cursor.scroll(2,mode='absolute') # 相对绝对位置移动——从结果最开始起到第2条数据结尾 print(cursor.fetchone()) print(cursor.fetchone()) """ [{'id': 1, 'user': 'egon', 'pwd': '123'}, ...] {'id': 3, 'user': 'hqs', 'pwd': '123'} {'id': 5, 'user': 'wxx', 'pwd': '123'} """ # 游标移动——相对当前位置移动 cursor.scroll(2,mode='relative') # 相对当前位置移动——从id=5这个结果开始，移动到id=9最后 print(cursor.fetchone()) print(cursor.fetchone()) """ {'id': 10, 'user': 'zurong', 'pwd': '123'} None # 没有数据了还查询，即返回None """ conn.commit() # 注意：提交后记录才会插入成功 # 关闭链接 cursor.close() conn.close() """ +----+--------+------+ | id | user | pwd | +----+--------+------+ | 1 | egon | 123 | | 2 | alex | 456 | | 3 | hqs | 123 | | 5 | wxx | 123 | | 8 | yxx | 123 | | 9 | egon1 | 123 | | 10 | zurong | 123 | +----+--------+------+ """
六、获取插入的最后一条数据的自增ID
# -*- coding:utf-8 -*- __author__ = 'Qiushi Huang' import pymysql # conn=pymysql.connect(host='127.0.0.1',user='root',password='1234',database='egon') conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='1234', db='db9', charset='utf8' # mysql指定utf-8的格式是没有杠的 ) cursor=conn.cursor() sql='insert into userinfo(user,pwd) values("xxx","123");' rows=cursor.execute(sql) print(cursor.lastrowid) # 插入之前id走到的数字（下一条即将插入的记录的id） conn.commit() cursor.close() conn.close() """ 11 +----+--------+------+ | id | user | pwd | +----+--------+------+ | 1 | egon | 123 | | 2 | alex | 456 | | 3 | hqs | 123 | | 5 | wxx | 123 | | 8 | yxx | 123 | | 9 | egon1 | 123 | | 10 | zurong | 123 | +----+--------+------+ """
