mysql在py中的使用（pymysql）

0 安装模块，pip install pymysql

1 pymql的作用是在python程序中操作mysql，同样是一个客户端，需要链接服务端后进行数据操作

import pymysql
#链接需要指定所链接的ip地址和端口号，本地客户端可以直接写localhost，
#建立链接需要指定字符集编码，此时为utf8，不是utf-8
conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     #database='test',
                     charset='utf8')
cursor = conn.cursor()

sql = 'show databases;'
res = cursor.execute(sql)
print(cursor.fetchall())

2 数据查看方式和游标的定位的修改

import pymysql

conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     database='test', #此处指定了库之后，不需要再使用use进入库中，可以直接进行相关表操作
                     charset='utf8')
#j建立游标，开始相关操作
#cursor = conn.cursor()  #此时拿到的结果为元组类型，且不显示字段名
cursor = conn.cursor(pymysql.cursors.DictCursor)  #此时拿到的结果为字典类型，键为字段名

sql = "select * from test_1;"
res = cursor.execute(sql)  #res的值为：这些sql影响到的行数

print(cursor.fetchall()) #取所有数据
print(cursor.fetchone()) #取第一条数据
print(cursor.fetchmany(2)) #取多条数据，注意，每次取完之后，游标下移，不自动返回，类似读文件

#重新阅读数据则需要定位游标
#定位由两种模式，第一种，绝对定位
cursor.scroll(0,'absolute')  #定位到第0,1...n行数据
print(cursor.fetchmany(2))

#定位由两种模式，第二种，相对定位
cursor.scroll(-1,'relative')  #定位到当前游标的后n行数据，若为负数则往前
print(cursor.fetchmany(2))

 3 需要字符串拼接的时候，要防止sql注入。

 sql注入指得是熟悉sql操作的程序员通过在输入的内容中加注释符号、与‘1’求并集等操作，使其恒成立，绕开一些设置。

例如在用户名密码的登录验证中输入'--'等，

python针对字符串拼接这种高危操作，提供了自己的方法，在语句后加一个列表，内含所拼接内容，不符合则不执行

import pymysql
user = input('请输入用户名：').strip()
password = input('请输入密码：').strip()


conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     database='test',
                     charset='utf8')

cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "select * from test_1 where name=%s and password=%s"
res = cursor.execute(sql,[user,password])

if res:
    print('登陆成功')
else:
    print('登录失败')

 4 增删改数据需要刷新才生效

方法：链接.commit()

import pymysql
conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     database='test',
                     charset='utf8')

cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "delete from test_1 where password = '4'"

res = cursor.execute(sql)

conn.commit()

sql = "select * from test_1"
res = cursor.execute(sql)
print(cursor.fetchall())

 5 存储过程举例（无参数）

import pymysql
conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     #database='test',
                     charset='utf8')
cursor = conn.cursor()


cursor.callproc('p3')  #字符串是存储过程的名字
conn.commit()   #直接执行存储过程
print(cursor.fetchall())

 6 存储过程举例（有参数）

6.1 创建输入存储过程

delimiter //        --声明结束符
create procedure p2(in n1 int,in n2 int)  --声明变量和变量类型
begin
select * from test_1 where password>n1;
end//
delimiter ;

调用输入存储过程

import pymysql
conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     database='test',
                     charset='utf8')
cursor = conn.cursor()

cursor.callproc('p2',(3,4))  #以元组的形式传入多个参数
conn.commit()   
print(cursor.fetchall())

6.2 创建输出存储过程

delimiter //
create procedure p3(in n1 int,out res int)
begin
select * from test_1 where password>n1;
set res = 1;
end//
delimiter
mysql调用时
set @res=0;
call p3(3,@res);   注意@的位置，外面加@
select @res；      查看变量值

调出输出存储过程

import pymysql
conn=pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='407509',
                     database='test',
                     charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)

cursor.callproc('p3',(3,0))  
#以元组的形式传入多个参数，out参数的输入直接为具体数据

conn.commit()
print(cursor.fetchall())
sql = 'select @_p3_1;'  #注意查看方法，@加_加过程名_加序号
res = cursor.execute(sql)
print(cursor.fetchall())

具体应用：与事务合用。先建立监测，出问题则改变输出的标志值，并回滚事务。

最后执行不出问题的事务，成功后置位。

调用时，给输出的变量随便赋值标志位之外的数，查看即可获知语句的执行情况

delimiter //
create procedure p4(out p_return_code tinyint)
begin
declare exit handler for sqlexception  #监测，一旦出现异常，执行下面的内容
begin
set p_return_code = 1;
rollback;  -- 回滚事务
end;
declare exit handler for sqlwarning
begin
set p_return_code = 2;
rollback;
end;
start transaction;
delete from tb1;
commit;
set p_return_code = 0;
end//
delimiter ;

#在mysql中调用

set @x=3;
call p4(@x);
select @x;
#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

6.3 inout参数

 定义一个inout参数，可以两用，仅此而已，没有别的用法，是两种用法的和，没区别。