TOC
数据库开发
数据库工具是一个提供服务的程序，像MySQL这个数据库，是典型的C/S模型，同时，它的数据传输必须遵循MySQL协议，MySQL协议是基于TCP协议衍生出的一种协议，即MySQL协议，因为它是基于TCP协议开发的，我们也可以基于Python的socket库开发一个MySQL客户端，但是我们需要知道MySQL协议内部的详细内容，开发难度极大，比如使用socket发送过去的二进制流之后，MySQL服务端会对这个二进制流进行解析，第一个字节代表什么，第二个字节代表什么，分析完成之后，MySQL服务端就能知道这个请求具体是要做什么操作了；
所以，也正是因为考虑到自行开发客户端程序较为复杂，所以MySQL官方就给各大流行语言提供了官方库，这个库将所有的客户端与服务端交互数据的功能都封装成函数，这些函数可以直接和MySQL服务端通信，我们无需关心它底层的逻辑，这种库，我们后来就称之为驱动，我们直接调用这个驱动即可，这个驱动里面有很多的函数，我们只需要按需来调用函数就可以直接和MySQL服务端进行数据交互；
数据库驱动
上面也说了，要和MySQL通信就必须遵循MySQL的协议，那么我们要使用编程语言去与数据库进行交互，也得调用驱动程序才能与MySQL进行通信，所以MySQL官方就专门为各大编程语言都提供实现了MySQL协议的MySQL客户端驱动程序包，即驱动库，我们只需要引用这个驱动就可以直接和MySQL数据库进行交互了，在Python下面有三个比较流行的驱动库，如下；
MySQLdb：MySQLdb又叫MySQL-python，是 Python 连接 MySQL 的一个驱动，很多框架都也是基于此库进行开发，遗憾的是它只支持 Python2.x，而且安装的时候有很多前置条件，因为它是基于C开发的库，而且在Windows平台安装非常不友好，经常出现失败的情况，现在基本不推荐使用，取代的是它的衍生版本；
MySQLclient：MySQL官方提供的库，但是由于 MySQLdb 年久失修，后来出现了它的 Fork 版本 mysqlclient，完全兼容 MySQLdb，同时支持 Python3.x，是 Django ORM的依赖工具，如果你想使用原生 SQL 来操作数据库，那么推荐此驱动。Mysqlclient是一个C扩展模块,编译安装可能会导致报各种错误；
PyMySQL：PyMySQL是一个第三方库，是一个纯Python实现并遵循ODBC协议的驱动，兼容Python 3，用于代替MySQLdb；
PyMySQL
下图就是Python借助PyMySQL库与数据库连接的大体流程，首先需要建立与数据库的连接，然后利用连接创建一个游标，然后利用游标来进行与数据库的交互，需要注意的是，当事务完成之后不要忘记提交、关闭游标和连接，以免造成浪费资源的情况；
常用方法
PyMySQL这个库还是比较好用的，并且使用也非常简单，主要是两个对象，即connect对象和cursor对象，connect主要是负责与数据库进行交互的对象，cursor主要是针对交互结果集操作的对象，如下；
connect():连接到指定的MySQL服务器，返回连接对象；
host(str):MySQL服务器地址;
port(int):MySQL服务器端口号;
user(str):用户名;
passwd(str):密码;
db(str):数据库名称;
charset(str):连接编码;
autocommit:是否自动提交；
connect():对象支持的方法:
cursor():使用该连接创建并返回游标;
commit():提交当前事务;
rollback():回滚当前事务;
close():关闭连接;
ping():测试连接数据库；
cursor对象支持的方法:
close():关闭游标对象;
fetchone():该方法获取下一个查询结果集。结果集是一个对象，一次只获取一个结果；
fetchmany(self, size=None):接收size条返回结果行，如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据，获取指定条数的结果；
fetchall():接收全部的返回结果行，一次性获取所有结果；
commit:提交数据，当要修改数据的时候就必须使用commit，否则不会执行该语句，但是经过测试，如果表中有自增则会影响则增；
scroll(self, value, mode='relative|absolute'):移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一 行移动value条.
execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数，一次性提交一条数据；
executemany(self, query, args):执行单挑sql语句，但是它会重复执行参数列表里的参数,返回值为受影响的行数，一次性提交多条数据；
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数；
rownumber：表示游标当前所在的位置；
callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数；
nextset(self):移动到下一个结果集；
lastrowid:获取最后一条数据的自增ID，如果遇到executemany那么lastrowid这个值就是最后一条数据的ID；
基础使用
在建立与数据库连接之后，如果我们需要和数据库进行交互，就必须拿到一个对象，即数据库游标对象，这个游标就是未来得到一个对象，这个对象就是一个结果集，我们需要使用这个游标对象对这个结果集进行操作，我们去读取第一行第二行第三行进行读取，这个时候就需要游标来指定一下行数，它就是个游标，是个指针；
另外，游标的参数可以指定一个Cursor类，如果为None，默认为Cursor类；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce")
cursor=conn.cursor()
cursor.execute("select * from caichangen;")
print(cursor.fetchall())
cursor.close()
conn.close()
# (('1', 'cce', 26),)
上下文管理
针对PyMySQL创建出来的连接和连接创建出来的游标也支持上下文管理，但是需要知道的是，连接和游标的上下文管理有点特殊，它们的__exit__方法，并不会将连接关闭，它主要是用来做数据提交和回滚的，它会判断当前SQL执行是否会抛出异常，如果存在异常，则rollback，反之commit；
# 连接
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 Cursor(object):
def __enter__(self):
return self
def __exit__(self, *exc_info):
del exc_info
self.close()
但是我们通过上面的源码可以看到，创建连接的__enter__方法会返回一个cursor对象，也就是说我们如果利用上下文管理，可以直接拿到一个cursor对象，但是对于关闭连接对象还是需要手动关闭；
那么，如果我们利用链接对象的__enter__方法，获取cursor，我们就必须手动关闭cursor，因为利用链接对象创建的cursor对象，走的就是链接对象的__exit__，而链接对象的__exit__方法，也不支持关闭cursor；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
result=cursor.execute("select * from caichangen;")
print(cursor.fetchall())
cursor.close()
conn.close()
可以看到上述源码，我们的Cursor对象是支持关闭当前cursor的，所以，我们还可以对上面进行一层改进，在使用链接对象的上下文管理基础之上，再使用链接创建出来的游标对象的上下文管理，从而实现游标的自动关闭；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
with cursor:
result=cursor.execute("select * from caichangen;")
print(cursor.fetchall())
conn.close()
- 注意：
由此我们就，利用上了PyMySQL的上下文管理，实现关闭链接和游标对象，但是需要注意的是，with语法后面是谁，就用谁的上下文管理方法；
批量提交
对于批量提交，我们可以直接进行多次插入，相当于我们将这个SQL加入到队列中了，最后一次性commit就可以实现批量提交，这也属于一个事务；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
with cursor:
for i in range(5):
rows=cursor.execute("insert into caichangen(name,age) values (%s,%s)"%(str(i),18+i))
print("受影响的行数为%s"%rows)
conn.close()
# 受影响的行数为1
# 受影响的行数为1
# 受影响的行数为1
# 受影响的行数为1
# 受影响的行数为1
PyMySQL也为我们提供了一个批量提交更加便捷的方式，调用PyMySQL库的executemany就可以实现批量提交，通过源码可以看到，当我们传递一个参数化的SQL语句和参数之后，executemany会调用_do_execute_many方法，进行迭代，循环执行SQL，最后将结果拼接起来返回，所以它内部其实也是execute只不过是加了个循环；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce")
with conn as cursor:
with cursor:
rows=cursor.executemany(query="insert into caichangen(name,age) values(%s,%s); ",args=[["cce",26],["cfj",18]])
print(rows)
conn.close()
# 2
获取结果集
连接主要是负责如何利用驱动与数据库之间通信，而操作结果集则由游标负责，所以就需要调用游标的方法来获取查询的结果集，获取结果集有三个方法，即fetchall、fetchmany和fetchone，顾名思义，一个是获取所有数据，一个是获取指定行数据，一个是获取所有行数据；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce")
with conn as cursor:
with cursor:
cursor.execute("select * from caichangen;")
print(cursor.fetchone()) # 获取一行数据
print(cursor.fetchmany(2)) # 获取指定行数据
print(cursor.fetchall()) # 获取所有数据
conn.close()
# (1, '0', 18)
# ((2, '1', 19), (3, '2', 20))
# ((4, '3', 21), (5, '4', 22))
游标Cousor
之前说过Cousor是对结果集的操作，这个结果集就是数据库返回的结果，那么同时，也可以看到上面代码的返回结果和下面的图，我们在fetchone之后进行fetchmany，fetchmany默认从主健为2开始，这主要是因为游标在作怪，上面也说过，游标的主要作用就是指针，当从结果集中获取一条数据之后，游标就会指向下一行数据，其实游标就是指针，只不过它是数据库记录的操作指针，并且游标默认是不可以回头的；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce")
with conn as cursor:
with cursor:
cursor.execute("select * from caichangen;")
print(cursor.fetchmany(2)) # 获取2行数据
cursor.rownumber=0 # 将当前的游标指重置为0
print(cursor.fetchall()) # 获取所有数据
conn.close()
# ((1, '0', 18), (2, '1', 19))
# ((1, '0', 18), (2, '1', 19), (3, '2', 20), (4, '3', 21), (5, '4', 22))
cursor有一个rownumber属性，它代表的就是当前游标所在的位置，默认从0开始，即第一行数据，虽然它默认是不可回头的，但是我们可以手动修改这个属性，去实现游标回头，如果我们给定游标为负数，和0是一样的，如果我们为一个大于总行数，则为最后一行，和文件对象的seek有点类似；
- 注意:
一般流程就是建立连接，获得游标，进行操作，然后进行事务提交，最后关闭游标和连接，这就是PyMySQL与MySQL的大致交互流程；
结果集
那么对于fetch来将，它操作的是结果集，C/S这种模型，当C对S发起的查询请求时，返回的结果会存储在C的内存空间，然后我们每次fecth，实际上是操作客户端内存空间的结果集，所以我们每次fetch实际上操作的不是MySQL端，而是本地，因为查询的结果早就已经返回了；
当我们向MySQL发起了execute执行查询之后，查询结果集会直接返回，保存在cursor里面，然后我们就可以通过cursor就可以对cursor中保存的结果集进行操作，结果集是在客户端，这点很重要；
结果集应用
可以看到，对于cursor这个方法来讲，它接受一个参数，即cursor，它与返回的结果集是有关系的，当参数cursor为None时，默认值为Cousor类，默认以元祖套元祖的方式返回，以元祖的方式返回我们只能拿到数据，那么当我们想拿到数据和字段时，我们需要将这个cursor的参数更改为DictCursor，这样结果集就会以列表包字典的方式返回，如下；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce")
with conn:
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) # 修改cursor类型
with cursor:
cursor.execute("select * from caichangen;")
print(cursor.fetchall())
conn.close()
# [{'age': 18, 'id': 1, 'name': '0'}, {'age': 19, 'id': 2, 'name': '1'}, {'age': 20, 'id': 3, 'name': '2'}, {'age': 21, 'id': 4, 'name': '3'}, {'age': 22, 'id': 5, 'name': '4'}]
SQL注入攻击
SQL注入是在我们使用PyMySQL进行项目开发时，绝对会遇到的一个比较棘手的问题，在SQL注入主要的原因是因为程序员在编写SQL时的不严谨导致的，在此主要说明SQL注入其中的一种比较常见的攻击方式，我们的SQL语法已经在我们的代码中早就编写好了，然后通过参数传递的方式将字符串传递给SQL语句后面的where条件，然后进行字符串格式化，最后生成一段SQL语句，继而发往MySQL获取查询结果；
那么这就有问题了，既然是字符串格式化，那就有问题了，我们没有做任何的限制，也就是说，这个字符串可以是任意合法的可用类型，那么如果后端同时也暴露出这个接口，让我们传递参数，并且它能够通过传递的参数从MySQL中去获取数据，最后返回数据，那就危险了；
如下，这种SQL语句在数据库中去执行是会拿到整张表的所有数据的，危险就产生在这里，那么当我们如果where后面的条件，是通过字符串来传递的话，如果不做任何防范那么就很可能存在所有数据被窃取的危险
select * from caichangen where 1=1;
select * from caichangen where True;
如下，我们在使用字符串传参时，当我们传递了一个不恰当的参数，就能直接获取整张表的所有数据，这就很危险了，所有的数据全部暴露在外部，整个信息系统的数据全部泄漏；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
with cursor:
sql="select * from caichangen where id=%s;"%("1 or True") # 理想值为id字段的值，但是恶意攻击者在id字段的值后面技加了一个or True
cursor.execute(sql)
for row in cursor.fetchall():
print(row)
conn.close()
# {'age': 18, 'id': 1, 'name': '0'}
# {'age': 19, 'id': 2, 'name': '1'}
# {'age': 20, 'id': 3, 'name': '2'}
# {'age': 21, 'id': 4, 'name': '3'}
# {'age': 22, 'id': 5, 'name': '4'}
参数化查询
为了解决SQL注入攻击，那么我们就引入了参数化查询，就可以有效的避免的SQL注入攻击的可能性，参数化查询也是程序员在日常项目开发必须使用的方式之一，它有效的避免了SQL编写者不严谨的SQL语法带来的SQL攻击漏洞，也就是参数化查询是我们目前日常开发必须使用的SQL规范之一；
可以看到在execute提交SQL时，支持一个args参数，它可以是str、tuple、list和dict，直接将参数使用这种方式查询，如下，和上面同样的SQL使用参数化查询就避免了全表返回的问题；
同时它还提示了一段话，意思是PyMySQL给我们在原有的SQL基础之上截取了一段不规范的值，将后面的or True截取掉了，从而就有效的解决拼接SQL语句带来的SQL注入的问题；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
with cursor:
sql="select * from caichangen where id=%s;" # 理想值为id字段的值，但是恶意攻击者在id字段的值后面技加了一个or True
cursor.execute(sql,args=("1 or True"))
for row in cursor.fetchall():
print(row)
conn.close()
# 309: Warning: Truncated incorrect DOUBLE value: '1 or True'
# {'id': 1, 'name': '0', 'age': 18}
参数化查询也支持以字典的键值的方式传入，一对一进行传入；
conn=pymysql.connect(host="47.104.197.46", user="cce", password="caichangen",database="cce",cursorclass=pymysql.cursors.DictCursor)
with conn as cursor:
with cursor:
sql="select * from caichangen where id=%(i)s and name=%(n)s;" # 理想值为id字段的值，但是恶意攻击者在id字段的值后面技加了一个or True
cursor.execute(sql,args={"i":1,"n":"0"}) # 传入参数
for row in cursor.fetchall():
print(row)
conn.close()
# {'id': 1, 'age': 18, 'name': '0'}
