day 35 MySQL基础、pymysql模块
MySQL基础
文档：MySQL 简单语句.note
链接：http://note.youdao.com/noteshare?id=12a1b3f29691065ad702e5f854cb5a2b&sub=F6867B5A6B6F485CB447AF8254BFC4A5
文档：数据类型.note
链接：http://note.youdao.com/noteshare?id=6fc535e5da39abc8eba647ee229db106&sub=355C83259E53493B8FE2B9CDDEBF06D1
文档：约束条件.note
链接：http://note.youdao.com/noteshare?id=abf9d67f7ece25ef02114d6537bc6f17&sub=6076879C36024939B18A6633DF0AED1A
文档：表关系.note
链接：http://note.youdao.com/noteshare?id=fab9bd9e77a3a5a9503a93dc48a3b849&sub=386FCC60E8854E1F9DDFDCB8206A5551
文档：单表查询-简单查询.note
链接：http://note.youdao.com/noteshare?id=eae244f1bd93552d611420d9af7784bb&sub=47DC12E0E62B4F96BBFC46DF877D749C
文档：单表查询-关键字.note
链接：http://note.youdao.com/noteshare?id=d77814944d26084d1eb45f0a07c41653&sub=9D23E660917B4CD896200E9528A11E92
文档：多表查询-链接.note
链接：http://note.youdao.com/noteshare?id=446685fa345e2bf6eb89b3093ef31229&sub=40BAFCE73DA24260AC5840265E196564
文档：权限管理.note
链接：http://note.youdao.com/noteshare?id=55822dae1a6d179ca88a48e87dc477c1&sub=B7FC2200FE274C91BAC0D5DB1254E23C
文档：MySQL 内置功能.note
链接：http://note.youdao.com/noteshare?id=7e0d006831062553d5215b9ae4596164&sub=491F9C7F65F14CC59439D987B2E5BD9D
文档：索引.note
链接：http://note.youdao.com/noteshare?id=acd2d5d92e9460135cc848bec3363e1f&sub=F1C0504642DD42CA9CBE1A1E327D7882
pymysql模块
介绍
之前我们都是通过MySQL自带的命令行客户端工具mysql来操作数据库，那如何在python程序中操作数据库呢？这就用到了pymysql模块，该模块本质就是一个套接字客户端软件，使用前需要事先安装。
pip3 install pymysql
链接、执行sql、关闭（游标）
# 基本使用 import pymysql user = input('>>>').strip() pawd = input('>>>').strip() # 建立连接 info = pymysql.connect( host='192.168.42.23', port=3306, user='root', password='123123', db='TestDB', charset='utf8' ) # 获取游标 cur = info.cursor() # 执行SQL sql = 'select * from info where user = "%s"and pawd = "%s"' % (user, pawd) rows = cur.execute(sql) # 获取执行成功后的行数 print(rows) # 关闭游标，释放资源 cur.close() info.close() if rows: print('登录成功') else: print('登录失败')
execute()之sql注入
注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符
根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'
最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了 #1、sql注入之：用户存在，绕过密码 egon' -- 任意字符 #2、sql注入之：用户不存在，绕过用户与密码 xxx' or 1=1 -- 任意字符
解决方法：
# 原来是我们对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的规矩来。
# 修改如下：在执行SQL部分，代码拼接使用 execute() 进行拼接 # 执行SQL sql = 'select * from info where user = %s and pawd = %s' rows = cur.execute(sql,(user,pawd)) # 获取执行成功后的行数 print(rows)
增、删、改：conn.commit()
增删改的用法一致。
# sql 执行部分 # 增 删 改 sql = 'insert into info(user,pawd) values(%s,%s) ' row = cursor.execute(sql,('qwe','123123')) # 插入一条 print(row) # 1，受影响的行数 row = cursor.executemany(sql, [('test', '123123'), ('test2', '123123'), ('test3', '123123')]) print(row) # 3
获取插入的最后一条数据的自增ID
# sql 执行部分 # 增 删 改 sql = 'insert into info(user,pawd) values(%s,%s)' row = cursor.executemany(sql, [('test4', '123123'), ('test5', '123123'), ('test6', '123123')]) print(cursor.lastrowid) # 获取插入数据之前关标的位置，库中现在有6条数据，那么关标的位置就是 7 。
查：fetchone，fetchmany，fetchall
默认返回元组列表。在取数据时，每取到一次数据，关标也会跟随移动。
# sql 执行部分 # 查 rows = cursor.execute('select * from info') # 执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询 # print(cursor.fetchone()) # (1, 'ysg', '123') # print(cursor.fetchone()) # (2, 'czx', '123') print(cursor.fetchone()) # (3, 'pei', '123') print(cursor.fetchall()) # ((1, 'ysg', '123'), (2, 'czx', '123'), (3, 'pei', '123'), (9, 'qwe', '123123')...) print(cursor.fetchmany(3)) # ((1, 'ysg', '123'), (2, 'czx', '123'), (3, 'pei', '123'))
pymysql.cursors.DictCursor A cursor which returns results as a dictionary，将结果作为字典返回的游标
# 获取游标 # A cursor which returns results as a dictionary 将结果作为字典返回的游标 cursor = conn.cursor(pymysql.cursors.DictCursor) # 查 rows = cursor.execute('select * from info') print(cursor.fetchone()) # {'id': 1, 'user': 'ysg', 'pawd': '123'} print(cursor.fetchall()) # [{'id': 2, 'user': 'czx', 'pawd': '123'}, {'id': 3, 'user': 'pei', 'pawd': '123'}...] print(cursor.fetchmany(3)) # [{'id': 1, 'user': 'ysg', 'pawd': '123'}, {'id': 2, 'user': 'czx', 'pawd': '123'}, {'id': 3, 'user': 'pei', 'pawd': '123'}]
关标的移动
cursor.scroll(2, mode='relative') ，默认为 mode='relative'，相对位置。
# sql 执行部分 # 查 rows = cursor.execute('select * from info') print(cursor.fetchone()) cursor.scroll(2, mode='relative') # 默认为 mode='relative'，相对位置 print(cursor.fetchone()) # 执行结果 {'id': 1, 'user': 'ysg', 'pawd': '123'} {'id': 4, 'user': 'qwe', 'pawd': '123123'}
cursor.scroll(6, mode='absolute') # 绝对位置
# sql 执行部分 # 查 rows = cursor.execute('select * from info') print(cursor.fetchone()) cursor.scroll(2, mode='absolute') # 绝对位置 print(cursor.fetchone()) # 执行结果 {'id': 1, 'user': 'ysg', 'pawd': '123'} {'id': 3, 'user': 'pei', 'pawd': '123'}
存储过程
无参数
mysql 存储过程
-- 格式 delimiter // create PROCEDURE p1() BEGIN select * from info; END // delimiter; -- 执行 call p1();
有参数
mysql 存储过程
delimiter // create procedure p2(in t1 int,in t2 int,out res_t int) -- in 表示传入值，out 表示返回值 begin select * from info f where f.id>=t1 and f.id<=t2; set res_t = 1; -- 设置执行成功后修改的返回值 end // delimiter; set @x=0 -- 设置初始返回值 call p2(3,5,@x); -- 传参 select @x; -- 查询执行后的返回值
pymysql 执行存储过程
import pymysql conn = pymysql.connect( host='192.168.42.23', port=3306, user='root', password='123123', db='TestDB', charset='utf8' ) cursor = conn.cursor() # row = cursor.callproc('p1') # 调用无参数的存储过程 # print(cursor.fetchall()) # ((1, 'ysg', '123'), (2, 'czx', '123'), (3, 'pei', '123'), (4, 'qwe', '123123')...) cursor.callproc('p2', (3, 5, 0)) # 调用有参数的存储过程 print(cursor.fetchall()) # ((3, 'pei', '123'), (4, 'qwe', '123123'), (5, 'test', '123123')) print(cursor.execute('select @_p2_2=0')) # 1 cursor.close() conn.close()
注意：pymysql 在调用有参数的存储过程时，cursor.callproc('p2', (3, 5, 0)) 中的值表示 @_p2_0=3,@_p2_1=5,@_p2_2=0
