pymysql

驱动

与mysql通信就是典型的cs模式。Server就是服务端，使用客户端先建立连接，数据库编程时，这个客户端就变成了程序。

mysql基于TCP协议之上开发，传输的数据必须遵循mysql的协议。

封装好mysql协议的包，习惯上称为驱动程序。

MYSQL的驱动

• MySQLdb

最有名的库，对Mysql的C Client封装实现，支持Python2，不更新了，不支持python3

• mysqlclient
  • 在mysqldb的基础上，增加了对python3的支持
• Mysql官网Connector
  • Mysql官网 https://dev.mysql.com/downloads/connector/
• pymsql
  • 语法兼容mysqldb，使用纯python写的mysql客户端库，支持python3
  • cpython2.7、3.4
  • mysql5.5+、mariadb5.5

pymysql使用

安装

pip install pymsql
pip isntall simplejson

simplejson处理json文件方便。

创建数据库和表

CREATE DATABASE IF NOT EXISTS school;
SHOW DATABASES;
USE school;
CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(30) NOT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

连接connect

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

connection初始化常用参数	说明
host	主机
user	用户名
password	密码
database	数据库
port	端口

Connection.ping()方法，测试数据库服务器是否活着。有一个参数reconnect 表示断开与服务器连接是否重连。连接关闭抛出异常。

{
  "host": "127.0.0.1",
  "user": "wayne",
  "password": "wayne",
  "database": "test",
  "port": 3306
}

import pymysql
import simplejson

conn = None
with open('conn.json') as f:
    conf = simplejson.load(f)
try:
    conn = pymysql.connect(**conf)
    couse = conn.cursor()  #获取一个游标
    sql = "insert into user (name,mgr_id) values ('mmm',20)"
    rows = couse.execute(sql)
    print(rows)
    conn.commit()
finally:
    if conn:
       conn.close()

发现数据库中没有数据提交成功，为什么？

原因在于，在Connection类的__init__方法的注释中有这么一句话

Text
:param autocommit: Autocommit mode. None means use server default. (default: False)

所以不用开启自动提交功能，一般需要手动管理事务

事务管理

Connection类有三个方法：

begin开始事务

commit将变更提交

rollback 回滚事务

批量增加数据

import pymysql
import simplejson
with open('conn.json') as f:
    conf = simplejson.load(f)
conn = None
cursor = None
try:
    conn = pymysql.connect(**conf)
    cursor = conn.cursor() # 获取一个游标
    for i in range(10):
        sql = "insert into student (name, age) values('tom{}', {})".format(i+1, 20+i)
        rows = cursor.execute(sql)
    conn.commit() # 事务提交
except:
    conn.rollback() # 事务回滚
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

一般流程

• 建立连接
• 获取游标
• 执行sql
• 提交事务
• 释放资源

查询

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

Text
import  pymysql
import simplejson

conn = None
cursor = None

with open('conn.json') as f:
    conf = simplejson.load(f)

try:
    conn = pymysql.connect(**conf)
    cursor = conn.cursor()
    sql = 'select * from user '
    cursor.execute(sql)
    print(cursor.fetchone(),cursor.rowcount)
    print('='*30)
    print(cursor.fetchmany(2),cursor.rowcount)
    print('='*30)
    print(cursor.fetchall(),cursor.rowcount)
    print('='*30)
    cursor.rownumber = 0
    all = cursor.fetchall()
    print(all)
finally:
    if conn:
        conn.close()
    if cursor:
        cursor.close()
#输出
(1, 'tom', None) 14
==============================
((2, 'jerry', 1), (3, 'ben', 2)) 14
==============================
((5, 'mmm', 20), (6, 'mmm0', 1), (7, 'mmm1', 2), (8, 'mmm2', 3), (9, 'mmm3', 4), (10, 'mmm4', 5), (11, 'mmm5', 6), (12, 'mmm6', 7), (13, 'mmm7', 8), (14, 'mmm8', 9), (15, 'mmm9', 10)) 14
==============================
((1, 'tom', None), (2, 'jerry', 1), (3, 'ben', 2), (5, 'mmm', 20), (6, 'mmm0', 1), (7, 'mmm1', 2), (8, 'mmm2', 3), (9, 'mmm3', 4), (10, 'mmm4', 5), (11, 'mmm5', 6), (12, 'mmm6', 7), (13, 'mmm7', 8), (14, 'mmm8', 9), (15, 'mmm9', 10))

名称	说明
fetchone()	获取结果集的下一行
fetchmany(size=None)	size指定返回的行数的行，None则返回空元祖
fetchall()	返回剩余所有行，如果走到末尾，就返回空元祖，否则返回一个元组，其元素是每一行的记录封装的一个元组
cursor.rownumber	返回当前行号，可以修改，支持负数
cursor.rowcount	返回的总行数

注意：fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候查询已经结束了

带列名查询

Cursor类有一个Mixin的子类DictCursor

from pymysql.cursors import DictCursor
cursor = conn.cursor(DictCursor)
# 返回结果
{'name': 'tom', 'age': 20, 'id': 4} 
{'name': 'tom0', 'age': 20, 'id': 5}

返回一行，是一个字典。

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

SQL注入攻击

找出用户id为6的用户信息的sql语句如下

select * from student where id = 6

现在，要求可以找出某个id对应用户的信息，代码如下

userid = 5 #用户id可以变
sql = 'select * from student where id = {}'.format(userid)

userid 可以变，例如从客户端request请求中获取，直接拼接到查询字符串中，可以如果userid = ‘5’ or 1=1 呢？

sql = 'SELECT * from student WHERE id = {}'.format('5 or 1=1')

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

如何解决sql注入攻击

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

cursor.execute(query,args=None)

query查询字符串使用c printf风格。args，必须是元组、列表或字典。如果查询字符串使用%(name)s，就必须使用字典

import pymysql
import simplejson
with open('conn.json') as f:
    conf = simplejson.load(f)
conn = None
cursor = None
try:
    conn = pymysql.connect(**conf)
    cursor = conn.cursor() # 获取一个游标
    sql = "select * from student where id=%s"
    userid = '2 or 1=1'
    rows = cursor.execute(sql, userid) # (userid,)
    print(cursor.fetchall())
    print('-' * 30)
    sql = "select * from student where name like %(name)s and age > %(age)s"
# 仅测试用，通常不要用like
    cursor.execute(sql, {'name': 'tom%', 'age': 25})
    print(cursor.fetchall())
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

参数化查询为什么提交效率？

原因就是——sql语句缓存。

数据库服务器一般会对sql语句编译和缓存，编译只对sql语句部分，所以参数中就算有sql指令也不会被当做指令执行。

编译过程，需要词法分析、语法分析、生成AST、优化、生成执行计划等过程，比较耗费资源。

服务端会先查找是否对同一条插叙你语句进行缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成本，降低了内存消耗。

可以认为sql语句字符串是一个key，如果使用拼接方式，每次发过去的sql语句都不愿意，都需要编译并缓存。

开发时，应该使用参数化查询，主要目的不是为了语句缓存，而是为了有效消除注入攻击。

注意：这里说的查询字符串的缓存，不是查询结果的缓存。

上下文支持

查看连接类和游标类的源码

# 连接类，老版本中
class Connection(object):
    def __enter__(self):
        """Context manager that returns a Cursor"""
        return self.cursor()
    def __exit__(self, exc, value, traceback):
        """On successful exit, commit. On exception, rollback"""
        if exc:
            self.rollback()
        else:
            self.commit()
# 连接类，新版做了修改
class Connection(object):
    def __enter__(self):
        return self
    def __exit__(self, *exc_info):
        del exc_info
        self.close()
# 游标类
class Cursor(object):
    def __enter__(self):
        return self
    def __exit__(self, *exc_info):
        del exc_info
        self.close()

连接类进入上下文的时候会返回一个游标对象，退出时如果没有异常会提交更改。

游标类也使用上下文，在退出时关闭游标对象。

import pymysql
import simplejson
with open('conn.json') as f:
    conf = simplejson.load(f)
conn = None
try:
    conn = pymysql.connect(**conf)
    with conn.cursor() as cursor: # 获取一个游标
        sql = "select * from student where id=%s"
        userid = 2
        rows = cursor.execute(sql, userid)
        print(cursor.fetchall())
        print('-' * 30)
        cursor.close() # 手动关闭
finally:
    # 注意连接未关闭
    if conn:
        conn.close()

conn的with进入是返回一个新的cursor对象，退出时，只是提交或回滚了事务，并没有关闭cursor和conn

不关闭cursor就可以接着用，省的反复创建它。

如果想关闭cursor对象，这样写

import  pymysql
import simplejson
from pymysql.cursors import DictCursor

conn = None

with open('conn.json') as f:
    conf = simplejson.load(f)

try:
    conn = pymysql.connect(**conf)
    with conn:
        with conn.cursor(DictCursor) as cursor:
            sql = 'select * from user where id = %s'
            cursor.execute(sql,2)
            all = cursor.fetchall()
            print(all)
        with conn.cursor(DictCursor) as cursor:
            sql2 = 'select * from user where name like %(name)s and mgr_id > %(age)s'
            cursor.execute(sql2,{'name':'mmm%','age':4})
            print(cursor.fetchall())
except Exception as e:
    print(e)

通过上面的实验，我们应该知道，连接应该不需要反反复复创建销毁，应该是多个cursor共享一个conn。

mysqlclient

pip install mysqlclient

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1', user='wayne', password='wayne', 
port=3306,
                database='test'
                #autocommit=False # 缺省
               )
print(type(conn), conn)
cursor = conn.cursor()
with cursor:
    x = cursor.execute('select * from employees')
    print(x)
    print(cursor.fetchall())
conn.close()