Python数据库开发之-pymysql模块

数据库开发

驱动

MySQL基于TCP协议之上开发，但网络连接后，传输的数据必须遵循MySQL协议，封装好MySQL协议的包，就是驱动程序。

• MySQL的驱动

  • MySQLdb

    最有名的库。对MySQL的C Client封装实现，支持Python2。已经不再更新和支持。

  • MySQL官方Connector

  • pymysql

    语法兼容MySQLdb，使用Python写的库，支持python3

pymysql使用

安装：$ pip install pymysql

创建数据库和表

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

连接Connect

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

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

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

Connection.ping()方法，测试数据服务器是否存活。

其中reconnect表示断开与服务器连接后，是否重连。

import pymysql

try:
    conn = pymysql.connect('192.168.2.100','root','hanna','school')
    print(type(conn))
    print(conn.ping(False))
finally:
    if conn:
        conn.close()
    print(conn.ping(False))

游标Cursor

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

Connection.cursor(cursor=None)方法返回一个新的游标对象。连接没有关闭前，游标对象可以反复使用。

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

操作数据库

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

新增记录

使用insert into语句插入数据。

import pymysql

try:
    conn = pymysql.connect('192.168.2.100','root','hanna','school')
    print(conn.ping(False))
    cursor = conn.cursor()
    insert_sql = "insert into student1(name,age) values('tom',20)"
    rows = cursor.execute(insert_sql)
    print(rows)
finally:
    if conn:
        conn.close()

注意，在Connection类的__init__方法的注释中

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

一般不开启自动提交功能，而是手动管理实务。

事务管理

Connection类有三个方法：

• begin 开始事务
• commit 提交变更
• rollback 回滚事务

import pymysql

try:
    conn = pymysql.connect('192.168.2.100','root','hanna','school')
    print(conn.ping(False))
    cursor = conn.cursor()
    insert_sql = "insert into student1(name,age) values('jerry',18)"
    rows = cursor.execute(insert_sql)
    print(rows)
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

一般流程：

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

查询

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

import pymysql

conn = pymysql.connect('192.168.2.100','root','hanna','school')
cursor = conn.cursor()

sql = 'select * from student'
rows = cursor.execute(sql)

print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchmany(2))
print(cursor.fetchall())

if cursor:
    cursor.close()
if conn:
    conn.close()

输出：

(1, 'tom', 20)
(2, 'tom', 20)
((10, 'jerry', 38), (11, 'tom', 20))
((12, 'tom', 21), (13, 'tom', 21), (18, 'jaywin', 18))

fetchone()方法：获取结果集的下一行

fetchmany(size=None)方法：size指定返回的行数的行，None则返回空元组

fetchall()方法：获取所有行

返回多行时，如果走到末尾，就会返回空元组。否则返回一个元组。其中元素就是每一行的记录。每一行的记录会封装在一个元组中。

cursor.rownumber：返回当前行号，可以修改，支持负数

cursor.rowcount：返回的总行数

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

带列名查询

Cursor类有一个Mixin的子类DictCursor。

只需要cursor = conn.cursor(DictCursor)即可。

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect('192.168.2.100','root','hanna','school')
cursor = conn.cursor(DictCursor)

输出：

{'id': 1, 'name': 'tom', 'age': 20}
{'id': 2, 'name': 'tom', 'age': 20}
[{'id': 10, 'name': 'jerry', 'age': 38}, {'id': 11, 'name': 'tom', 'age': 20}]
[{'id': 12, 'name': 'tom', 'age': 21}, {'id': 13, 'name': 'tom', 'age': 21}, {'id': 18, 'name': 'jaywin', 'age': 18}]

返回一行，是一个字典。

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

SQL注入攻击

要求找出某个id对应的用户信息，代码如下：

userid = 4sql = 'SELECT * FROM student1 WHERE id = {}'.format(userid)

userid可以变，比如从客户端的request请求中获取，直接拼接到查询字符串中。可是，如果userid=‘4 or 1=1’

结果返回了全部数据。

这就是SQL注入攻击，通过猜测后台数据库的查询语句使用拼接字符串的方式，从而经过设计为服务器传参，拼接出特殊的字符串，返回用户想要的结果。

千万不要相信客户端传来的数据是规范且安全的。

如何解决注入攻击？

参数化查询

有效防止注入攻击，并提高查询效率。

Cursor.execute(query, args=None)

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

userid='4 or 1=1'sql = 'SELECT * FROM student1 WHERE id = %s'cursor.execute(sql,(userid,)) # 参数化查询print(cursor.fetchall())sql = 'SELECT * FROM student1 WHERE name like %(name)s and age >= %(age)s'cursor.execute(sql,{'name':'tom%','age':18})print(cursor.fetchall())

参数化查询为什么提高效率？原因是SQL语句缓存。

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

编译过程需要词法分析、语法分析、生成AST、优化、生成执行计划等过程，耗费资源。而服务端先查找是否对同一条查询语句进行了缓存，如果缓存未失效则无需再次编译，从而降低编译成本，降低内存消耗。

可认为SQL语句字符串就是key，如果使用拼接方案，每次发过去的语句是不同的，都需编译并缓存。当大量查询时，首选使用参数化查询以节省资源。

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

上下文支持

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

# 连接类class Connection(object):    def __enter__(self):        return self.cursor()    def __exit__(self, exc, value, traceback):        """On successful exit, commit. On exception, rollback"""        if exc:            self.rollback()        else:            self.commit()       # 游标类class Cursor():    def __enter__(self):        return self    def __exit__(self, *exc_info):        del exc_info        self.close()

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

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

import pymysqlfrom pymysql.cursors import DictCursorconn = pymysql.connect('192.168.2.100','root','hanna','school')with conn as cursor:    for i in range(3):        insert_sql = "insert into student1 (name,age) values ('tom{0}',20+{0})".format(i)        rows = cursor.execute(insert_sql)sql = "select * from student1"cursor.execute(sql)print(cursor.fetchall())cursor.close()conn.close()

conn的with进入是返回一个新的cursor对象。退出时，只是提交或者回滚了事务，并没有关闭cursor对象。如果需要多次使用cursor，则无需关闭它。

如果想关闭它，则如此：

with conn as cursor:	with cursor:		...conn.close() # 关闭

连接不应该反复创建和销毁，应该多个cursor共享一个connection。