pymysql模块

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

Django中也可以使用PyMySQL连接MySQL数据库。

我们以前的方法是这样的:

在文档中写上

xiaomei 123456
jianchao 123456
liyan    45696

代码为:

username = input('请输入您的姓名:>>>')
password = input('请输入您的密码:>>>')
with open('student-info', 'r', encoding="utf-8") as f:
    for i in f:
        # print(i.strip().split())
        if i.strip():  #去掉空行 和每段中的空字符
            if i.split()[0] == username and i.split()[1] == password:
                print('登录成功')
                break
    else:
        print('登录失败')

现在让我们把这个数据放到数据库中来实现

连接数据库创建列表

import pymysql
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
#得到一个可以执行的SQL语句的光标
cursor=conn.cursor()
#写数据库语句
sql="""
create table student2 ( 
id int auto_increment primary key,
name char(10) not NULL ,
password int not null) engine=innodb DEFAULT charset="utf8"
"""
#执行SQL语句
cursor.execute(sql)
#关闭光标对象
cursor.close()
#关闭数据库连接
conn.close()

插入数据

import pymysql
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
#得到一个可以执行的SQL语句的光标
cursor=conn.cursor()
#写SQL语句
sql="""
insert into student2 (name,password)VALUES ('xiaomei',123456),('liyan',123456)
"""
#执行SQL语句
cursor.execute(sql)
#像数据库提交数据
conn.commit()
#关闭光标对象
cursor.close()
#关闭数据库连接
conn.close()

input 插入数据

import pymysql
username=input('姓名>>>:')
password=input('密码>>>:')
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
#得到一个可以执行的SQL语句的光标
cursor=conn.cursor()
#写数据库语句
sql="insert into student2(name,password) VALUES (%s,%s);"#这里不要自己拼接字符串 会发生SQL注入现象
#执行SQL语句
cursor.execute(sql,[username,password]) #在这里拼接字符串必须这样写
#提交数据
conn.commit()
#关闭光标对象
cursor.close()
#关闭数据库连接
conn.close()

 SQL注入问题

SQL注入问题: 它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库

当把上边的例子的SQL这样改动:

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

就会发生SQL注入问题

如果我们这样输入的话

用户名>>:liyan' --hehe
密码:

这样就会登录成功,因为我们输入的数据进入到SQL中就会变成这样

sql=" select * from student2 where username='liyan' --hehe' and password ='';

#我们知道--在SQL中是注释的意思,这样用户只需要用户名就可以登录系统了

第二种情况这样写也可以登录成功:

用户名: alex' or 1=1 --hhe

sql语句就变成这样了

sql=" select * from student2 where username='alex' or 1=1 --hehe' and password ='';
#不需要用户名就可以登录成功了

那么如何避免这个问题呢?

sql语句不要这么写了

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

这么写:

sql=" select * from student2 where username='%s' and password ='%s';"

cursor.execute(sql,[username,password]) #pymyslq会帮你过滤掉特殊的字符,防止SQL注入

 

批量插入数据(executemany)

import pymysql
conn=pymysql.connect(host='localhost', user='root',password='123456',database='s8' ,charset='utf8')
cusor=conn.cursor()
sql="insert into student2(name,password)VALUES (%s,%s);"
data=[('alex',18),("egon",20), ("yuanhao",21)]
try:
    #批量执行多条插入SQL语句
    cusor.executemany(sql,data) #使用executemany函数 一次连接多次插入SQL语句
    conn.commit()
except Exception as e:
    conn.rollback()
cusor.close()
conn.close()

插入数据失败回滚 意思是失败的数据如果没有插入成功也会占用一个id编号,导致下一次正确插入时编号出现断代现象

import pymysql
#插入数据失败回滚 意思是失败的数据如果没有插入成功也会占用一个id编号,导致下一次正确插入时编号出现断代现象
username=input('姓名>>>:')
password=input('密码>>>:')
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
#得到一个可以执行的SQL语句的光标
cursor=conn.cursor()
#写数据库语句
sql="insert into student2(name,password) VALUES (%s,%s);"#这里不要自己拼接字符串 会发生SQL注入现象
try:
    # 执行SQL语句
    cursor.execute(sql, [username, password])  # 在这里拼接字符串
    # 提交数据
    conn.commit()
except Exception as e:
    # 有异常回滚事务
    conn.rollback()  #使用rollback回滚函数
#关闭光标对象
cursor.close()
#关闭数据库连接
conn.close()

删

import pymysql
conn=pymysql.connect(host='localhost', user='root',password='123456',database='s8' ,charset='utf8')
cusor=conn.cursor()
sql="delete from student2 where id=%s;"
try:
    cusor.execute(sql,[4])
    conn.commit()
except Exception as e:
    conn.rollback()
cusor.close()
conn.close()

修改:

import pymysql
conn=pymysql.connect(host='localhost', user='root',password='123456',database='s8' ,charset='utf8')
cusor=conn.cursor()
sql="update student2 set password =%s WHERE name=%s;"
username='alex'
password=80
try:
    #批量执行多条插入SQL语句
    cusor.execute(sql,[password,username])
    conn.commit()
except Exception as e:
    conn.rollback()
cusor.close()
conn.close()

 

查询:

 

# 导入pymysql模块
import pymysql
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT * from student2 ;"
# 执行SQL语句
print(cursor.execute(sql))
cursor.close()
conn.close()
# 打印下查询结果

结果

#执行sql语句，返回sql查询成功的记录数目
那么如何返回查询结果呢,用cursor.fetchone或cursor.fetchall

fetchone() 和fetchall()的区别?

注意: 这里是关键 fetchone() 得到的是一元元组, fetchall()得到的是两元元组

规则:

cursor.execute("select * from user")

如果select本身取的时候有多条数据时：

cursor.fetchone()：将只取最上面的第一条结果，返回单个元组如('id','title')，然后多次使用cursor.fetchone()，依次取得下一条结果，直到为空。

cursor.fetchall() :将返回所有结果，返回二维元组，如(('id','title'),('id','title')),

如果select本身取的时候只有一条数据时：

cursor.fetchone()：将只返回一条结果，返回单个元组如('id','title')。

cursor.fetchall() :也将返回所有结果，返回二维元组，如(('id','title'),),

 

 

# 导入pymysql模块
import pymysql
# 连接database
conn=pymysql.connect(host='localhost',user='root',password='123456',database='s8', charset="utf8")
cursor = conn.cursor() 
# 查询数据的SQL语句
sql = "SELECT * from student2 ;"
# 执行SQL语句
cursor.execute(sql)
# 获取单条查询数据
ret = cursor.fetchone()  # 得到单条信息后,会记录下当前位置,下次执行会继续当前位置下去,如果想得到一个字典可以这样写 cursor =conn.cursor(cursor=pymysql.cursors.DictCursor)
#获取多条查询数据
ret = cursor.fetchall()
cursor.close()
conn.close()
# 打印下查询结果
print(ret)

结果

 

((5, 'liyan', 123456), (6, 'xiaomei', 123456), (7, 'liyan', 123456), (8, '张', 286933867),
 (9, 'alex', 80), (10, 'egon', 20), (11, 'yuanhao', 21), (12, 'alex', 18), 
(13, 'egon', 20), (14, 'yuanhao', 21))

进阶用法:

# 可以获取指定数量的数据结果.
cursor.fetctmany(3)
# 光标按绝对位置移动1
cursor.scroll(1, mode="absolute")
# 光标按照相对位置(当前位置)移动1
cursor.scroll(1, mode="relative")

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

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
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()

 sql注入问题

#要这样写
cursor.execute("select id,name from student WHERE  NAME =%s and password=%s",[user,password])#注意sql注入的问题

#不要这样写
cursor.execute("select id,name from student WHERE  NAME =egon or 1==1 and password=%s"%(user,password))#这样就会引发SQL注入问题,不管你写的name等于什么值,这个等式都会成立的