python操作mysql的库--Pymysql

安装

pip install pymysql

安装json，用于加载json配置文件参数

pip install simplejson

创建实验用表

MariaDB [test]> create table student ( 
    id int not null auto_increment primary key,
    name varchar(20) not null,
    age int default null
);
Query OK, 0 rows affected (0.007 sec)

-- 查看表结构
MariaDB [test]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)

连接Connect

首先，必须建立一个传输数据通道——连接。

pymysql.connect()方法返回的是Connections模块下的Connection类实例。connect方法传参就是给Connection类的__init__提供参数

Connection初始化常用参数	说明
user	用户名
password	密码
host	主机
database	数据库
port	端口
autocommit	自动提交事务

游标Cursor

操作数据库，必须使用游标，需要先获取一个游标对象。

Connection.cursor(cursor=None) 方法返回一个新的游标对象。

连接没有关闭前，游标对象可以反复使用。

cursor参数，可以指定一个Cursor类。如果为None，则使用默认Cursor类。

操作数据库

数据库操作需要使用Cursor类的实例，提供execute() 方法，执行SQL语句，成功返回影响的行数。

配置文件

config.json

{
  "user": "root",
  "password": "111111",
  "host": "10.0.0.4",
  "database": "test",
  "port": 3306
}

新增记录

使用insert into语句插入数据。

import pymysql
import simplejson
from pathlib import Path

p = Path().absolute() / 'config.json'  # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
    # print(f.read())
    conf = simplejson.load(f)  # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor()  # 新建游标，类似指针
    sql = """insert into student (name,age) values('abc','18')"""
    x = curses.execute(sql)  # 执行sql语句
    print(x)  # 返回的影响行数
    conn.commit()  # 提交事务，因为默认autocommit=False
except Exception as e:
    conn.rollback()  # 一旦发生异常，回滚事务
    print(e)
finally:
    if curses:
        curses.close()#关闭游标
    if conn:
        conn.close()#关闭连接

MariaDB [test]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | abc  |   18 |
+----+------+------+
1 row in set (0.000 sec)

事务管理

Connection类有三个方法：

​ begin 开始事务

​ commit 将变更提交

​ rollback 回滚事务

批量添加数据

import pymysql
import simplejson
from pathlib import Path

p = Path().absolute() / 'config.json'  # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
    # print(f.read())
    conf = simplejson.load(f)  # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor()  # 新建游标，类似指针
    for i in range(2, 6):
        sql = """insert into student (name,age) values('abc{0}','{0}')""".format(i)
        x = curses.execute(sql)  # 执行sql语句
        print(x)  # 返回的影响行数
    conn.commit()  # 提交事务，因为默认autocommit=False
except Exception as e:
    conn.rollback()  # 一旦发生异常，回滚事务
    print(e)
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()

MariaDB [test]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | abc  |   18 |
|  2 | abc  |   18 |
|  3 | abc2 |    2 |
|  4 | abc3 |    3 |
|  5 | abc4 |    4 |
|  6 | abc5 |    5 |
+----+------+------+
6 rows in set (0.000 sec)

executemany()方法,也可以批量添加数据

executemany(str, list) ----> int

第一个参数时sql语句

第二个参数是列表，由数据组成的列表

import pymysql
import simplejson
from pathlib import Path

p = Path().absolute() / 'config.json'  # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
    # print(f.read())
    conf = simplejson.load(f)  # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor()  # 新建游标，类似指针
    data = []
    for i in range(7, 10):
        data.append((f'abc{i}', i))
    sql = """insert into student (name,age) values(%s,%s)"""
    #values中的值都应该是%s，为字符串
    print(data)
    y = curses.executemany(sql, data)
    print(y)
    conn.commit()  # 提交事务，因为默认autocommit=False
except Exception as e:
    conn.rollback()  # 一旦发生异常，回滚事务
    print(e)
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()
        
"""        
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
[('abc7', 7), ('abc8', 8), ('abc9', 9)]
3
"""

一般流程

​ 建立连接

​ 获取游标

​ 执行SQL

​ 提交事务

​ 释放资源

查询

Cursor类的获取查询结果集的方法有fetchone()、fetchmany(size=None)、fetchall()。

方法	说明
fetchone()	获取游标所指的下一行
fetchmany(size=None)	获取游标所致的下size行，没有size则返回空元组
fetchall()	获取剩余所有行
rownumber	返回当前游标所处行号，可以修改，支持负数
rowcount	返回总行数

import pymysql
import simplejson
from pathlib import Path

p = Path().absolute() / 'config.json'  # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
    # print(f.read())
    conf = simplejson.load(f)  # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor()  # 新建游标，类似指针
    sql = """select * from student where id>2"""
    x = curses.execute(sql)  # 执行sql语句
    print(x)  # 返回的影响行数
    print(curses.rownumber, curses.rowcount)
    print('----------拿一个')
    print(curses.fetchone())
    print(curses.rownumber, curses.rowcount)
    print('----------再拿一个')
    print(curses.fetchone())
    print(curses.rownumber, curses.rowcount)
    print('----------再拿三个个')
    print(curses.fetchmany(3))
    print(curses.rownumber, curses.rowcount)
    print(curses.fetchall())
    print('---------全拿完了-----')
    print(curses.fetchone())
    print('-' * 20)
    print('-------游标调回1')
    curses.rownumber = 1
    print(curses.fetchall())
    conn.commit()  # 提交事务，因为默认autocommit=False
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()
        
"""        
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
7
0 7
----------拿一个
(3, 'abc3', 3)
1 7
----------再拿一个
(4, 'abc4', 4)
2 7
----------再拿三个个
((5, 'abc5', 5), (6, 'abc6', 6), (7, 'abc7', 7))
5 7
((8, 'abc8', 8), (9, 'abc9', 9))
---------全拿完了-----
None
--------------------
-------游标调回1
((4, 'abc4', 4), (5, 'abc5', 5), (6, 'abc6', 6), (7, 'abc7', 7), (8, 'abc8', 8), (9, 'abc9', 9))
"""

带列名查询

缺省的cursor返回的都是元组，不带列名

pymysql.cursors类有一个Mixin的子类DictCursor。

可以返回列名和value组成的字典之后组成的列表

只需要cursor = conn.cursor(DictCursor)就可以了

import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor #导入DictCursor

p = Path().absolute() / 'config.json'  # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
    # print(f.read())
    conf = simplejson.load(f)  # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  
    curses = conn.cursor(DictCursor)  # 使用DictCursor
    sql = """select * from student where id>2"""
    x = curses.execute(sql)  # 执行sql语句
    print(x)  # 返回的影响行数
    print(curses.fetchone())
    print(curses.fetchall())
    conn.commit()  # 提交事务
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()
        
"""        
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
7
{'id': 3, 'name': 'abc3', 'age': 3}
[{'id': 4, 'name': 'abc4', 'age': 4}, {'id': 5, 'name': 'abc5', 'age': 5}, {'id': 6, 'name': 'abc6', 'age': 6}, {'id': 7, 'name': 'abc7', 'age': 7}, {'id': 8, 'name': 'abc8', 'age': 8}, {'id': 9, 'name': 'abc9', 'age': 9}]
"""

返回一行，是一个字典。

返回多行，放在列表中，元素是字典，代表一行。

SQL注入攻击

import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor

p = Path().absolute() / 'config.json'
with open(p, encoding='utf8') as f:
    conf = simplejson.load(f)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor(DictCursor)  # 新建游标，类似指针
    sql = """select * from student where id={}""".format('2 or 1=1')
    x = curses.execute(sql)
    print(curses.fetchall())
    conn.commit()  # 提交事务
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()
"""        
[{'id': 1, 'name': 'abc1', 'age': 1}, {'id': 2, 'name': 'abc2', 'age': 2}, {'id': 3, 'name': 'abc3', 'age': 3}, {'id': 4, 'name': 'abc4', 'age': 4}, {'id': 5, 'name': 'abc5', 'age': 5}, {'id': 6, 'name': 'abc6', 'age': 6}, {'id': 7, 'name': 'abc7', 'age': 7}, {'id': 8, 'name': 'abc8', 'age': 8}, {'id': 9, 'name': 'abc9', 'age': 9}]
"""

sql = """select * from student where id={}""".format('2 or 1=1')

期望是来一个字符，但是sql注入了一个拼接后保证查询条件为True的字符串，导致全部数据被暴露

SQL注入攻击

猜测后台数据库的查询语句使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击者想要的结果。

参数化查询，可以有效防止注入攻击，并提高查询的效率。

Cursor.execute(query, args=None) args，必须是元组、列表或字典。如果查询字符串使用%(name)s，就必须使用字典。

import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor

p = Path().absolute() / 'config.json'
with open(p, encoding='utf8') as f:
    conf = simplejson.load(f)
conn = None
curses = None
try:
    conn = pymysql.connect(**conf)  # 新建连接
    curses = conn.cursor(DictCursor)  # 新建游标，类似指针
    sql = """select * from student where id=%s"""
    x = curses.execute(sql, ('2 or 1=1',))
    print(curses.fetchall())
    conn.commit()  # 提交事务
finally:
    if curses:
        curses.close()
    if conn:
        conn.close()
    
[{'id': 2, 'name': 'abc2', 'age': 2}]