1、数据库开发驱动简介
与MySQL通信就是典型的CS模式。Server就是服务器端，使用客户端先建立连接，数据库编程时，这个客户端变成了程序。
MySQL基于TCP协议之上开发，传输的数据必须遵循MySQL的协议。
封装好MySQL协议的包，习惯上称为驱动程序。
MySQL的驱动
MySQLdb
最有名的库。对MySQL的C Client封装实现，支持Python 2，不更新了，不支持Python3
mysqlclient
在MySQLdb的基础上，增加了对Python 3的支持
MySQL官方Connector
Mysql官网 https://dev.mysql.com/downloads/connector/
pymysql
语法兼容MySQLdb，使用纯Python写的MySQL客户端库，支持Python 3
CPython 2.7 、3.4+
MySQL 5.5+、MariaDB 5.5+
2、pymysql使用
2.1、模块安装
pip install pymysql
pip install simplejson
2.2、创建数据库和表
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;
2.3、连接Connect
2.3.1、简介
pymysql.connect()方法返回的是connections模块下的Connection类实例。connect方法传参就是给Connection类的 __init__ 提供参数
2.3.2、初始化参数介绍
Connectio初始化常用参数 说明
host 主机
user 用户名
password 密码
database 数据库
port 端口
Connection.ping()方法，测试数据库服务器是否活着。有一个参数reconnect表示断开与服务器连接是否重连。连接关闭抛出异常。
2.3.3、创建初始化json文件
# conn.json
{
"host": "192.168.10.8",
"user": "test",
"password": "test",
"database": "school",
"port": 3306
}
2.3.4、连接代码
import pymysql
import simplejson
conf = simplejson.load(open('conn.json'))
print(conf)
conn = None
try:
conn = pymysql.connect(**conf)
print(type(conn), conf)
print(conn.ping(False)) # ping不同抛异常，True重连
finally:
if conn:
conn.close()
2.4、游标Cursor
操作数据库，必须使用游标，需要先获取一个游标对象。
Connection.cursor(cursor=None) 方法返回一个新的游标对象。
连接没有关闭前，游标对象可以反复使用。
cursor参数，可以指定一个Cursor类。如果为None，则使用默认Cursor类。
Cursor类的实例，使用execute() 方法，执行SQL语句，成功返回影响的行数。
2.5、新增记录
import pymysql
import simplejson
with open('conn.json') as f:
conf = simplejson.load(f)
conn = None
try:
conn = pymysql.connect(**conf)
cursor = conn.cursor() # 获取一个游标
sql = "insert into student (name, age) values('tom', 20)"
rows = cursor.execute(sql)
print(rows)
finally:
if conn:
conn.close()
发现数据库中没有数据提交成功，为什么？
原因在于，在Connection类的 __init__ 方法的注释中有这么一句话
autocommit: Autocommit mode. None means use server default. (default: False)
那是否应该开启自动提交呢？不用开启，一般我们需要手动管理事务。
2.6、事务管理
2.6.1、简介
Connection类有三个方法：
begin 开始事务
commit 将变更提交
rollback 回滚事务
2.6.2、示例：批量增加数据
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()
2.7、一般流程
建立连接
获取游标
执行SQL
提交事务
释放资源
2.8、示例：查询
Cursor类的获取查询结果集的方法有fetchone()、fetchmany(size=None)、fetchall()。
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"
rows = cursor.execute(sql)
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.rownumber, cursor.rowcount)
print('1 -----------')
print(cursor.fetchmany(2))
print(cursor.rownumber, cursor.rowcount)
print('2 ~~~~~~~~~~~')
print(cursor.fetchmany(2))
print(cursor.rownumber, cursor.rowcount)
print('3-----------')
print(cursor.fetchall())
print(cursor.rownumber, cursor.rowcount)
for x in cursor.fetchall():
print(x, '~~~~')
cursor.rownumber = 0 # 正负都支持
for x in cursor.fetchall():
print(x, '----')
finally:
if cursor:
cursor.close()
if conn:
conn.close()
2.9、函数介绍
名称 说明
fetchone() 获取结果集的下一行
fetchmany(size=None) size指定返回的行数的行，None则返回空元组
fetchall() 返回剩余所有行，如果走到末尾，就返回空元组，否则返回一个元组，其元素是每一行的记录封装的一个元组
cursor.rownumber 返回当前行号。可以修改，支持负数
cursor.rowcount 返回的总行数
注意：fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候，查询已经结束了。
2.10、带列名查询
Cursor类有一个Mixin的子类DictCursor。
from pymysql.cursors import DictCursor
cursor = conn.cursor(DictCursor)
# 返回结果
{'name': 'tom', 'age': 20, 'id': 4}
{'name': 'tom0', 'age': 20, 'id': 5}
返回一行，是一个字典。
返回多行，放在列表中，元素是字典，代表一行。
2.11、SQL注入攻击
2.11.1、攻击示例
# 找出用户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语句，返回攻击者想要的结果。
永远不要相信客户端传来的数据是规范且安全的！！！
2.11.2、如何解决注入攻击？
参数化查询，可以有效防止注入攻击，并提高查询的效率。
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()
2.11.3、参数化查询为什么提高效率？
原因就是——SQL语句缓存。
数据库服务器一般会对SQL语句编译和缓存，编译只对SQL语句部分，所以参数中就算有SQL指令也不会
被当做指令执行。
编译过程，需要词法分析、语法分析、生成AST、优化、生成执行计划等过程，比较耗费资源。
服务端会先查找是否对同一条查询语句进行了缓存，如果缓存未失效，则不需要再次编译，从而降低了
编译的成本，降低了内存消耗。
可以认为SQL语句字符串就是一个key，如果使用拼接方案，每次发过去的SQL语句都不一样，都需要编
译并缓存。
开发时，应该使用参数化查询。主要目的不是为了语句缓存，而是为了有效消除注入攻击。
注意：这里说的是查询字符串的缓存，不是查询结果的缓存。
2.12、上下文支持
2.12.1、查看连接类和游标类的源码
# 连接类，老版本中
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对象，这样写
2.12.2、代码示例
import pymysql
import simplejson
with open('conn.json') as f:
conf = simplejson.load(f)
try:
conn = pymysql.connect(**conf)
with conn:
with conn.cursor() as cursor: # 获取一个游标
sql = "select * from student where id=%s"
userid = 2
rows = cursor.execute(sql, userid)
print(cursor.fetchall())
print('-' * 30)
with conn.cursor() as cursor:
sql = "select * from student where id=6"
cursor.execute(sql)
print(cursor.fetchall())
except Exception as e:
print(e)
通过上面的实验，我们应该知道，连接应该不需要反反复复创建销毁，应该是多个cursor共享一个conn。
3、mysqlclient
3.1、安装模块
3.2、示例
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()
浙公网安备 33010602011771号