python扩展库pymysql

1.pymysql安装

安装:pip install pymysql

补充命令：

查看当前已经安装的所有模块和库：pip list

卸载pymysql库：pip uninstall pymysql

更新某个库：pip install 库名 -U

2.pymysql基础操作

import pymysql

#连接mysql数据库
conn = pymysql.connect(
    host='192.xxx.xxx.xxx',
    port=3307,
    user='root',
    password='123456',
    database='test_1',
    charset='utf8'
)

# 创建游标, 查询数据默认为元组类型
cursor = conn.cursor()

sql = """create table user(
     username varchar(20),
     password varchar(20),
     email varchar(30)
     );"""
#执行sql语句，创建一张user表
cursor.execute(sql)
#执行SQL，给表插入数据并返回受影响行数（使用pymysql的参数化语句防止SQL注入）
row = cursor.executemany("insert into user(username, password, email)values(%s, %s, %s)",
                          [("ceshi3", '333', 'ceshi3@11.com'), ("ceshi4", '444', 'ceshi4@qq.com')])
print(row)

# 执行SQL，并返回收影响行数
row1 = cursor.execute("update users set password = '123'")
print(row1)
# 执行SQL，并返回受影响行数
row2 = cursor.execute("update users set password = '456' where id > %s", (1,))
print(row2)

# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

# 创建游标, 查询数据默认为元组类型
cursor = conn.cursor()
cursor.execute("select * from users")

# 获取第一行数据
row_1 = cursor.fetchone()
print(row_1)
# 获取前n行数据
row_n = cursor.fetchmany(3)
print(row_n)
# 获取所有数据
row_3 = cursor.fetchall()
print(row_3)


# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

# 创建游标, 查询数据默认为元组类型
cursor = conn.cursor()

cursor.executemany("insert into users(username, password, email)values(%s, %s, %s)", [("ceshi3", '333', 'ceshi3@11.com'), ("ceshi4", '444', 'ceshi4@qq.com')])
new_id = cursor.lastrowid
print(new_id)

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
 
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 左连接查询
r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2")
result = cursor.fetchall()
print(result)

# 查询一个表的所有字段名
c = cursor.execute("SHOW FULL COLUMNS FROM users FROM blog")
cc = cursor.fetchall()

class MySQLHandler:
    def __init__(self,host,port,db,charset='utf8'):
        self.host=host
        self.port=port
        self.db=db
        self.charset=charset
        self.conn=connect(self.host,self.port,self.db,self.charset)
    def exc1(self,sql):
        return self.conn.execute(sql)

    def exc2(self,sql):
        return self.conn.call_proc(sql)


obj=MySQLHandler('127.0.0.1',3306,'db1')
obj.exc1('select * from tb1;')
obj.exc2('存储过程的名字')

3.pymysql防注入

 3.1字符串拼接查询，造成注入

 正常查询语句：

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')
cursor = conn.cursor()
username = "ceshi1"
password = "ceshi1passwd"
# 正常构造语句的情况
sql = "select username, password from users where user='%s' and pass='%s'" % (username, password)
# sql = select username, password from users where user='ceshi1' and pass='ceshi1passwd'
row_count = cursor.execute(sql) 
row_1 = cursor.fetchone()
print row_count, row_1

构造注入语句：

import pymysql

#连接mysql数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')
username = "u1' or '1'-- "
password = "u1pass"
sql="select username, password from user where username='%s' and password='%s'" % (username, password)
#拼接语句被构造成下面这样，永真条件，此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。
#select username, password from user where username='u1' or '1'-- ' and password='u1pass'
cursor = conn.cursor()
row_count = cursor.execute(sql)
row_1 = cursor.fetchone()
print(row_count,row_1)

3.2 避免注入，使用pymysql提供的参数化语句
正常参数化查询　

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')
cursor = conn.cursor()
username="u1"
password="u1pass"
#执行参数化查询
row_count=cursor.execute("select username,password from tb7 where username=%s and password=%s",(username,password))
row_1 = cursor.fetchone()
print row_count,row_1

构造注入，参数化查询注入失败。　

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')
cursor = conn.cursor()
 
username="u1' or '1'-- "
password="u1pass"
#执行参数化查询
row_count=cursor.execute("select username,password from users where username=%s and password=%s",(username,password))
#内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。
# sql=cursor.mogrify("select username,password from users where username=%s and password=%s",(username,password))
# print sql
#select username,password from users where username='u1\' or \'1\'-- ' and password='u1pass'被转义的语句。
 
row_1 = cursor.fetchone()
print row_count,row_1

　结论：excute执行SQL语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞。

4.使用with简化连接过程

# 使用with简化连接过程，每次都连接关闭很麻烦，使用上下文管理，简化连接过程
import pymysql
import contextlib


# 定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def mysql(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8'):
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        yield cursor
    finally:
        conn.commit()
        cursor.close()
        conn.close()

# 执行sql
with mysql() as cursor:
    # 左连接查询
    r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2")
    result = cursor.fetchall()
    print(result)