背景
之前一直使用tornado做项目,数据库一直使用mongo与redis居多,凭借其优异的异步特性工作的也很稳定高效,最近的项目需要使用 mysql ,由于之前在使用mongo与redis时所使用的moto与aioredis来异步的执行数据库操作,所以在网上查询了异步操作mysql的库, 本文记录一下异步操作中所遇到的问题与相应的解决方案。
Aiomysql 介绍
我们在使用tornado开发网站的时候,利用python3中新加入的异步关键词 async/await , 我们使用各种异步操作为来执行各种异步的操作,如使用 aiohttp 来代替 requests 来执行异步的网络请求操作,使用 motor 来代替同步的 pymongo 库来操作mongo数据库,同样,我们在开发同步的python程序时,我们会使用PyMySQL来操作mysql数据库,同样,我们会使用aiomysql来异步操作mysql 数据库。
Aiomysql 连接
docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7
我们先准备一些测试数据,创建一个mytest的数据库,创建一个user表,里面有三个字段,id, username, age, 简单的三个字段,并且添加两条数据。
#coding: utf-8
import aiomysql
import asyncio
loop = asyncio.get_event_loop()
async def test():
conn = await aiomysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='mytest',
loop=loop
)
cur = await conn.cursor()
await cur.execute("select * from user")
r = await cur.fetchall()
if r:
for i in r:
print(i)
else:
print("no data")
await cur.close()
conn.close()
loop.run_until_complete(test())
我们来看下代码,来顺一下执行流程
1. 创建连接
首先我们使用 aiomysql.connect() 创建一个连接对象conn,代码里只是使用了最常用的连接选项,这个connect() 方法返回一个Connection类对象,这个对象里的参数非常多,我们在后面的代码中,如果遇到会进行相应的介绍。
2. 创建游标
之后我们使用 conn 这个对象的cursor方法获取Cursor对象cur,我们只有使用cursor对象才能对数据库进行各种操作。
3. 执行SQL语句
我们使用cur 对象的execute() 方法执行SQL语句。这里执行 select * from user ,这个方法返回影响的行数,对于查询而言,是命中查询的数据量,我们也可以根据这里的返回值,如果是0的话则说明没有符合查询条件的数据。
cur = await conn.cursor()
count = await cur.execute("select * from user where id = 4")
print("count:{}".format(count))
if count:
r = await cur.fetchall()
for i in r:
print(i)
else:
print("no data")
await cur.close()
conn.close()
5. 关闭连接conn
注意conn对象的关闭函数不是协程,直接调用close() 即可。
async with conn.cursor() as cur:
count = await cur.execute("select * from user")
if count:
r = await cur.fetchall()
for i in r:
print(i)
else:
print("no user")
Aiomysql简单的CURD
上面我们简单地使用游标对象进行了查询,这节我们来看看更多CURD操作,其实这里已经和aiomysql没有太多的关系,主要是考查各位的mysql能力了,一个execute方法走天下。但是这里我们来看一个老生常谈的问题,sql注入问题。
SQL注入的问题
username = "yyx"
async with conn.cursor() as cur:
sql = "select * from user where username = '%s'" % username
print(sql)
count = await cur.execute(sql)
if count:
r = await cur.fetchall()
for i in r:
print(i)
else:
print("no user")
如何避免SQL注入
async def execute(self, query, args=None):
"""Executes the given operation
Executes the given operation substituting any markers with
the given parameters.
For example, getting all rows where id is 5:
cursor.execute("SELECT * FROM t1 WHERE id = %s", (5,))
:param query: ``str`` sql statement
:param args: ``tuple`` or ``list`` of arguments for sql query
:returns: ``int``, number of rows that has been produced of affected
"""
conn = self._get_db()
while (await self.nextset()):
pass
if args is not None:
query = query % self._escape_args(args, conn)
await self._query(query)
self._executed = query
if self._echo:
logger.info(query)
logger.info("%r", args)
return self._rowcount
execute有二个参数,一个是query, 另外是args,我们看注释,query是 sql的语句, args是 tulpe 或者 list 类型的参数。如果args非空,脚本会通过 query = query % self._escape_args(args, conn) 重新组织query, 再来看下 _escape_args(args, conn) 的实现
def _escape_args(self, args, conn):
if isinstance(args, (tuple, list)):
return tuple(conn.escape(arg) for arg in args)
elif isinstance(args, dict):
return dict((key, conn.escape(val)) for (key, val) in args.items())
else:
# If it's not a dictionary let's try escaping it anyways.
# Worst case it will throw a Value error
return conn.escape(args)
如果是list或者tuple,则返回使用 conn.escape 转换之后的tuple, 如果是dict字典类型的话,则返回一个字典,key还是原来的key, value为 conn.escape(val) , 最终都是使用 conn.escape() 函数来进行转换,再来看下这个函数的实现
def escape(self, obj):
""" Escape whatever value you pass to it"""
if isinstance(obj, str):
return "'" + self.escape_string(obj) + "'"
return escape_item(obj, self._charset)
def escape_string(self, s):
if (self.server_status &
SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):
return s.replace("'", "''")
return escape_string(s)
函数将在传入的字符串两边加上两个单引号 ’ , 并且将 字符串中的单引号替换成两个单引号,这样就可以避免大多的sql注入问题,我们修改一下脚本
username = 'yanyanxin'
async with conn.cursor() as cur:
count = await cur.execute("select * from user where username = %s", username)
if count:
r = await cur.fetchall()
for i in r:
print(i)
else:
print("no user")
此时转换后的SQL语句为 select * from user where username = ‘yyx’ or 1=1#’ 已经将单引号进行了转义,此时就不会查找到用户了。
注意为了避免SQL注入的问题,我们一定不要自己进行拼接SQL语句,一定要对用户的输入进行检查转义
多参数的查询
select * from user WHERE age >19 and age<29
我们使用aiomysql的实现
async with conn.cursor() as cur:
count = await cur.execute("select * from user where age>%s and age<%s", (19, 29))
if count:
r = await cur.fetchall()
for i in r:
print(i)
else:
print("no user")
联合查询
我们再创建一个表,表示用户表中用户的职业, 创建三条数据, userid对应于user表中的id, 这里之所以没有用外键,之后再讨论,只是记住,这里的userid只是一个普通的列,它表示user表中的id。
SELECT jobs.jobs, user.username from jobs INNER JOIN user ON user.id=jobs.userid where user.username='yyx'
async with conn.cursor() as cur:
sql = 'SELECT jobs.jobs, user.username from jobs INNER JOIN user ON user.id=jobs.userid where user.username=%s'
count = await cur.execute(sql, ('yyx',))
if count: