pymysql模块

支持python代码操作数据库MySQL

#安装, 不用考虑版本
pip3 install pymysql

1  链接、执行sql、关闭（游标）

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

#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db_test',
    charset='utf8')

#生成一个游标对象，类似终端的光标，等待输入sql命令
cursor=conn.cursor()                                  #执行完毕返回的结果集默认以元组显示,无法区分数据的含义
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #加入参数，表示将查询结果以字典的形式返回

#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，execute返回的是你当前sql语句所影响/作用的行数，表示命令执行成功，该返回值一般不用
print(res)

cursor.close()
conn.close()

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

2 execute()之sql注入 

什么是sql注入？

利用一些语法的特性，书写一些特定的语句实现固定的语法，sql注入有很多种形式，注释语法是最有代表性的，MySQL利用的是MySQL的注释语法。

日常生活中很多软件在注册的时候都不能含有特殊符号，因为怕你构造出特定的语句入侵数据库，不安全。

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha, 用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';  则--之后的条件被注释掉了

在上例中，输入用户名与密码时，sql语句是自己拼接的，如果书写如下特定语句，在MySQL中会识别为注释语法
#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符 
select * from user where name ='egon' --abcdefg and password=''  #--相当于把后面的语句注释了，因此不输密码也能登录


#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符
select *from user where name = 'xxx' or 1=1 --abcdefg and password =''  #1=1成立，修改了name的条件，密码注释掉了，没有用户名和密码也能登录

解决方法：

敏感的数据不要自己做拼接操作，交由固定模块帮你去过滤数据，防止sql注入，pymysql中利用的是execute

# 原来是我们对sql进行字符串拼接
sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
print(sql)
res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
#不要手动拼接数据，先用%s占位，之后将需要拼接的数据直接交给execute方法即可，注意：只能识别%s
sql="select * from userinfo where name=%s and password=%s"   #注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,(user,pwd))  #自动识别sql里面的%s用后面元组里面的数据替换

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

查不涉及数据修改，增删改的操作涉及到数据修改，因此在输入命令后不能直接执行，需要二次确认

3.1 insert

import pymysql
#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db_test',
    autocommit=True) #commit改为自动提交
#游标
cursor=conn.cursor()

#执行sql语句
#part1
sql='insert into userinfo(name,password) values("root","123456");'
res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
print(res)

#part2 
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
print(res)

#part3  一次性增加多条数据,列表套元祖的格式
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
print(res)

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

3.2 delete

import pymysql
#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db_test',
    autocommit=True) #commit改为自动提交
#游标
cursor=conn.cursor()

#执行sql语句
sql='delete from user where id=1'
rows=cursor.execute(sql)  #执行sql语句，返回sql影响成功的行数
print(rows)

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

3.3 update

import pymysql
#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db_test',
    autocommit=True) #commit改为自动提交
#游标
cursor=conn.cursor()

#执行sql语句
sql='update user set name='egon_sb' where id=1'
rows=cursor.execute(sql)  #执行sql语句，返回sql影响成功的行数
print(rows)

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

4 查：fetchone，fetchmany，fetchall

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

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

#获取命令的执行结果
#读取数据类似文件光标的移动，同时执行五条命令，每取一条数据，光标就往后移动
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)

cursor.close()
conn.close()

'''
(1, 'root', '123456')
(2, 'root', '123456')
(3, 'root', '123456')
((4, 'root', '123456'), (5, 'root', '123456'))
((6, 'root', '123456'), (7, 'lhf', '12356'), (8, 'eee', '156'))
rows in set (0.00 sec)
'''

控制光标移动的两种方式

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动，相对于数据的开头(0个数据的位置)，往后移动三位
# cursor.scroll(3,mode='relative') # 相对当前位置移动，相对于当前位置往后移动三位

5 获取插入的最后一条数据的自增ID

import pymysql
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db_test')

cursor=conn.cursor()

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

conn.commit()
cursor.close()
conn.close()