Python - pymysql
About pymysql
在Python2.x中,Python连接MySQL服务器使用mysqldb库,但是它只支持到Python2.x,在Python3.x中由pymysql模块代替。
PyMySQL 遵循 Python 数据库 API v2.0 规范,并包含了 pure-Python MySQL 客户端库。
Install
pip install pymysql # 备用地址 pip install -i https://pypi.doubanio.com/simple pymysql
准备
在正式操作前,这里默认你有了一个良好的环境,包括MySQL服务,Python环境。
建立连接
import pymysql conn = pymysql.connect( host='localhost', # 连接的服务器ip user='username', # 用户名 password='password', # 密码 database='day31', # 你想连接的数据库 charset='utf8' # 指定字符编码,不要加杠,如:utf-8 ) cursor = conn.cursor() # 获取游标 # 一顿操作后......别忘了 cursor.close() # 关闭游标 conn.close() # 关闭连接对象
创建数据库
首先,我们要手动在MySQL中创建表:
create table info( id int primary key auto_increment, user varchar(10) not null, pwd varchar(10) );
一群增删改查 略
SQL注入
什么是SQL注入呢?先来看个示例:
import pymysql def connection(user=None, password=None, database=None, host='localhost', charset='utf8'): """ 建立连接并将游标返回 """ conn = pymysql.connect( host=host, user=user, password=password, database=database, charset=charset ) return conn.cursor() def login(): user = input('username: ').strip() pwd = input('password: ').strip() sql = 'select user, pwd from info where user= "%s" and pwd = "%s";' % (user, pwd) print(sql) result = cursor.execute(sql) # 查询结果自带布尔值,查询成功返回1,查询失败返回0 if result: print('login successful') else: print('login error') if __name__ == '__main__': cursor = connection(user='root', password='root!admin', database='day31') login() conn.commit() cursor.close() conn.close()
在上述的登录示例中了,我们输入正确的用户名和密码肯定都没问题:
username: 张开2 password: zhangkai2 select user, pwd from info where user="张开2" and pwd = "zhangkai2"; login successful
但是,如果有人在输入用户名和密码时,做了手脚:
username: 张开2"; -- aswesasa password: select user, pwd from info where user="张开2"; -- aswesasa" and pwd = ""; login successful
可以看到,再输入用户名的时候,在用户名后面跟了"; -- aswesasa
这些东西,再看打印的SQL语句,不难发现。判断语句现在变成了,只要是用户名对了就算成功。后面的密码部分,被--
注释掉了,你写啥都无所谓了。
这就是SQL注入的方式之一。另外一种情况,就是用户名和密码都有问题,也能登录成功:
username: xxx" or 1 -- xxx password: select user, pwd from info where user="xxx" or 1 -- xxx" and pwd = ""; login successful
当用户名错误和密码错误时,依然登录成功。什么原因呢?由打印的SQL语句可以看到,用户名错了不要紧,我们使用or语句,后跟一个真值,这样用户名无论如何都会成立。当然,后面的密码部分已经被注释掉了。
以上就是SQL注入的两种方式,那么怎么解决呢?解决办法,就是我们不手动的拼接SQL字符串,而是交给pymysql来完成:
def login(): user = input('username: ').strip() pwd = input('password: ').strip() sql = 'select user, pwd from info where user = %s and pwd = %s;' # 注意,%s这里要去掉引号,因为pymysql会帮我们加上的 result = cursor.execute(sql, (user, pwd)) if result: print('login successful') else: print('login error')
上述代码修改后,无论我们输入什么,pymysql都会把输入内容拼成普通的字符串,然后校验。所以,以后碰到这种拼接SQL语句的事情,都交给pymysql来做,而不是我们手动的拼接。
事务
也就是回滚机制,将一连串的执行当成一个原子性的操作,要么全部执行成功,要么全部执行失败。
我们演示执行两条命令,一条执行成功,一条执行失败,执行失败的话,就回滚到之前最开始的状态。
先来看正常的:
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql1 = 'insert into info(user, pwd) values(%s, %s);' sql2 = 'insert into info(user, pwd) values(%s, %s);' try: cursor.execute(sql1, (("小王", "123"))) cursor.execute(sql2, (("小李", "123"))) except Exception as e: print(e) conn.rollback() cursor.execute('select * from info where user like %s;', '小%') print(cursor.fetchall()) # [{'id': 210, 'user': '小王', 'pwd': '123'}, {'id': 211, 'user': '小李', 'pwd': '123'}] conn.commit() cursor.close() conn.close()
如果try语句中的两个SQL语句都执行成功,则最终执行成功。
我们把刚才插入的删掉再测试。
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql1 = 'insert into info(user, pwd) values(%s, %s);' sql2 = 'insert into info(user, pwd) values(%s, %s);' cursor.execute('delete from info where user like "小_"') conn.commit() try: cursor.execute(sql1, (("小王", "123"))) cursor.execute(sql2, (("小李", "123"))) raise 1 except Exception as e: print(e) # exceptions must derive from BaseException conn.rollback() cursor.execute('select * from info where user like %s;', '小%') print(cursor.fetchall()) # () conn.commit() cursor.close() conn.close()
可以看到,在一串的执行中,遇到了错误,就回滚到之前的状态。
存储过程
接下来来看在pymysql中使用存储过程。
创建存储过程
delimiter\\ CREATE PROCEDURE t1 () BEGIN SELECT * FROM t1; END\\ delimiter ;
使用存储过程
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.callproc('t1') conn.commit() print(cursor.fetchall()) cursor.close() conn.close()