mysql--pymysql 模块

pymysql模块

一.安装

cmd中使用pip或者, pycharm中控制台选择Terminal输入下面的命令,即可安装pymysql模块

pip3 install pymysql

 

二. 连接,执行sql,关闭(游标)

连接

import pymysql

conn = pymysql.connect(             # 创建连接
    host = '127.0.0.1',　　　　　　  #服务器地址
    port = 3306,　　　　　　　　　  #端口
    user = 'root',　　　　　　　　　 # 用户名
    password = '123',　　　　　　   # 密码
    database = 'db2',　　　　　　　 # 数据库名
    charset = 'utf8'                        #  字符编码
)

执行sql语句

sql = 'select * from db1;'
cursor.excute(sql)

关闭游标

cursor.close()

在数据库中准备这样一张表

 

示例: 

import pymysql

conn = pymysql.connect(             # 创建连接
    host = '127.0.0.1',　　　　　　　 #服务器地址
    port = 3306,　　　　　　　　　　　 #端口
    user = 'root',　　　　　　　　　　 # 用户名
    password = '123',　　　　　　　　 # 密码
    database = 'db2',　　　　　　　　 # 数据库名
    charset = 'utf8'               #  字符编码
)
cursor = conn.cursor()              # 创建游标
sql = 'show databases;'             # sql语句

res = cursor.execute(sql)           # 执行sql语句
res2 = cursor.fetchall()            #通过 fetchall(),fetchone(),fetchmany(个数) 获取结果
print(res)　　　　　　　　　　　　　　　 # 这是受查询影响的结果个数
print(res2)
cursor.close()
conn.close()

结果

7
(('information_schema',), ('db1',), ('db2',), ('mysql',), ('performance_schema',), ('test',), ('testdb',))

　

三.游标的移动

　　游标就相当于获取了cmd窗口中的mysql>

创建游标

cursor = conn.cursor()   # conn  是你创建的连接对象

获取字典格式的结果

cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

执行sql语句

cursor.execute(sql语句)

通过游标拿到返回的结果

cursor.fetchall()             拿到所有
 
cursor.fetchone()           拿到一行

cursor.fetchmany(行数)   拿到指定行数

游标的移动

cursor.scroll(1 , 'relative')  # 相对移动 从当前游标位置开始 把游标移动一行

cursor.scroll(2  , 'absolute')  # 绝对移动  从数据起始位置 移动指定行数 

 详解:

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接，指定ip地址和端口，本机上测试时ip地址可以写localhost或者自己的ip地址或者127.0.0.1，然后你操作数据库的时候的用户名，密码，要指定你操作的是哪个数据库，指定库名，还要指定字符集。不然会出现乱码
conn=pymysql.connect(host='localhost',port=3306,user='root',password='123',database='student',charset='utf8') #指定编码为utf8的时候，注意没有-，别写utf-8，数据库为
#得到conn这个连接对象
#游标
cursor=conn.cursor() #这就想到于mysql自带的那个客户端的游标mysql> 在这后面输入指令，回车执行
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #获取字典数据类型表示的结果：{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'} {'字段名':值}


#然后给游标输入sql语句并执行sql语句execute
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号，执行这句sql的前提是医药有个userinfo表，里面有name和password两个字段，还有一些数据，自己添加数据昂
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目，是个数字，是受sql语句影响到的记录行数，其实除了受影响的记录的条数之外，这些记录的数据也都返回了给游标,这个就相当于我们subprocess模块里面的管道PIPE，乘放着返回的数据
#all_data=cursor.fetchall()  #获取返回的所有数据，注意凡是取数据，取过的数据就没有了，结果都是元祖格式的
#many_data=cursor.fetchmany(3) #一下取出3条数据，
#one_data=cursor.fetchone()  #按照数据的顺序，一次只拿一个数据，下次再去就从第二个取了，因为第一个被取出去了，取一次就没有了，结果也都是元祖格式的
  fetchone：(1, '男', 1, '理解')
  fetchone：(2, '女', 1, '钢蛋')
  fetchall：((3, '男', 1, '张三'), (4, '男', 1, '张一')）

#上面fetch的结果都是元祖格式的，没法看出哪个数据是对应的哪个字段，这样是不是不太好看，想一想，我们可以通过python的哪一种数据类型，能把字段和对应的数据表示出来最清晰，当然是字典{'字段名':值}
#我们可以再创建游标的时候，在cursor里面加上一个参数：cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)获取的结果就是字典格式的，fetchall或者fetchmany取出的结果是列表套字典的数据形式

上面我们说，我们的数据取一次是不是就没有了啊，实际上不是的，这个取数据的操作就像读取文件内容一样，每次read之后，光标就移动到了对应的位置，我们可以通过seek来移动光标
同样，我们可以移动游标的位置，继续取我们前面的数据,通过cursor.scroll(数字，模式)，第一个参数就是一个int类型的数字，表示往后移动的记录条数，第二个参数为移动的模式，有两个值：absolute：绝对移动，relative：相对移动
#绝对移动：它是相对于所有数据的起始位置开始往后面移动的
#相对移动：他是相对于游标的当前位置开始往后移动的

#绝对移动的演示
#print(cursor.fetchall())
#cursor.scroll(3,'absolute') #从初始位置往后移动三条，那么下次取出的数据为第四条数据
#print(cursor.fetchone())

#相对移动的演示
#print(cursor.fetchone())
#cursor.scroll(1,'relative') #通过上面取了一次数据，游标的位置在第二条的开头，我现在相对移动了1个记录，那么下次再取，取出的是第三条，我相对于上一条，往下移动了一条
#print(cursor.fetchone())

print(res) #一个数字

cursor.close() #关闭游标
conn.close()   #关闭连接

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

 　

四.execute()之sql注入

首先我们来写一个简单的登陆认证

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db2',
    charset='utf8'
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
uname = input('请输入用户名：')
pword = input('请输入密码：')

sql = "select * from userinfo where username='%s' and password='%s';"%(uname,pword)

res = cursor.execute(sql) #res我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说明存在

print(res) #如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1
if res:
    print('登陆成功')
else:
    print('用户名和密码错误！')

#通过上面的验证方式，比我们使用文件来保存用户名和密码信息的来进行验证操作要方便很多。

　我们来看一个,直接连,不用知道用户名和密码,依然可以成功登陆的例子.如果在登陆时这样输入

第一种输入方式:

asd' -- xx
这样输入的话等同于
select * from userinfo where username='asd' -- xxx' and password='';

第二种输入方式:

请输入用户名：xxx' or 1=1 -- xxxxxx
请输入密码：
select * from userinfo where username='xxx' or 1=1 -- xxxxxx' and password='';
登陆成功

我们只输入了一个xxx' 加or 加 1=1 加 -- 加任意字符串
看上面被执行的sql语句你就发现了，or 后面跟了一个永远为真的条件，那么即便是username对不上，但是or后面的条件是成立的，也能够登陆成功。

总结咱们刚才说的两种sql注入的语句

#1、sql注入之：用户存在，绕过密码
asd' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

 

为了解决这个问题,pymysql提供了excute()方法来帮我们拼接,并防止mysql注入　　

　　excute([字符串1,字符串2])

# 原来是我们对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的规矩来。

　　

示例:

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',#localhost
    port=3306,
    user='root',
    password='222',   #字符串
    database='shanku',
    charset='utf8'
)

username = input('请输入用户名')
pwd = input('请输入密码')

# cursor = conn.cursor() #获取元组类型数据的游标
cursor = conn.cursor(pymysql.cursors.DictCursor) #获取字典类型数据的游标

# sql = "select * from userinfo where username='%s' and password='%s';"%(username,pwd)  # 之前我们这样写sql语句
sql = "select * from userinfo where username=%s and password=%s;"   # 现在我们把这两个变量从sql语句中去掉,写进exectue()里面
print('>>>>>',sql)
res = cursor.execute(sql,[username,pwd])  #执行sql语句, res是受影响的行

print(res)
if res:

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

# conn.commit()　

 

四.conn.commit() 提交

1.提交

　　　　cursor.execute() 只能查询 如果需要修改表的内容或者向表中插入数据的话 , 应该在末尾再使用方法 conn.commit() 来提交, 数据表中的内容才会改变

　　　　commit()的效果就是将对数据库的操作保存下来

　　　　如果执行了查询语句而结尾并没有使用commit()提交时,表中的自增(auto_increment)字段还是会计数的.

　　　　

　　　　这时的id为4 如果插入数据的话 id会自动变为5.(设置了id字段为自增),第一次插入数据的时候没有提交,表中数据没有更新,但是id还是会记录

　　　　第二次使用commit提交时 id会在上一次没有写入表的id数字上加1,结果如下

　　　　

 

示例:

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',#localhost
    port=3306,
    user='root',
    password='123',   #字符串
    database='db2',
    charset='utf8'
)

cursor = conn.cursor(pymysql.cursors.DictCursor) #获取字典类型数据的游标

sql = "insert into userinfo values(4,'user4',123);"
res = cursor.execute(sql)  #res受影响的行
print(res)

conn.commit() #凡是增删改的操作都需要commit

　　

2.增、删、改：conn.commit()

import pymysql
#链接
conn=pymysql.connect(host='localhost',port='3306',user='root',password='123',database='crm',charset='utf8')
#游标
cursor=conn.cursor()

#执行sql语句
#part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)
# print(cursor.lastrowid) #返回的是你插入的这条记录是到了第几条了

#part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)
#还可以进行更改操作：
#res=cursor.excute("update userinfo set username='taibaisb' where id=2")
#print(res) #结果为1
#part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数，一次插多条记录
print(res)
#上面的几步，虽然都有返回结果，也就是那个受影响的函数res，但是你去数据库里面一看，并没有保存到数据库里面，
conn.commit() #必须执行conn.commit,注意是conn，不是cursor，执行这句提交后才发现表中插入记录成功，没有这句，上面的这几步操作其实都没有成功保存。
cursor.close()
conn.close()

　　

四 查：fetchone，fetchmany，fetchall

import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='db2')
#游标
cursor=conn.cursor()

#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)
res5=cursor.fetchall()
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)
print('%s rows in set (0.00 sec)' %rows)

conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

　　

五 获取插入的最后一条数据的自增ID cursor.lastrowid 

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='db1')
cursor=conn.cursor()

sql='insert into userinfo(name,password) values("user1","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()

cursor.close()
conn.close()