veelion / ezmysql Goto Github PK

源码如下:
@user.get("userList")
@login_required
async def userList(request):
"""用户列表"""
info = request.app.ctx.logger.info
rsjson = {"status": 0, "msg": "ok", "data": {"items": [], "total": 0}}
sql = """SELECT
a.id,
a.login_name,
a.user_name,
a.mobile,
b.name AS role_name,
c.org_name,
a.status,
a.update_time
FROM
t_user AS a
INNER JOIN t_role AS b ON a.role_id = b.id
INNER JOIN t_org AS c ON a.org_id = c.id
WHERE
a.deleted = 0 """
where = ""
if request.args.get("login_name"):
where = where+" AND login_name like %s" % ("'%%"+request.args.get("login_name")+"%%'")
if request.args.get("name"):
where = where+" AND a.user_name like %s" % ("'%%"+request.args.get("user_name")+"%%'")
if request.args.get("org_name"):
where = where+" AND c.org_name like %s" % ("'%%"+request.args.get("org_name")+"%%'")
if request.args.get("mobile"):
where = where+" AND a.mobile like %s" % ("'%%"+request.args.get("mobile")+"%%'")

sql=sql+where
info("拼接查询语句后:"+sql)
if request.args.get("orderBy"):
    sql += " ORDER BY " + \
        request.args.get("orderBy")+" "+request.args.get("orderDir")
else:
    sql += " ORDER BY id DESC"
sql =sql+ ''' LIMIT %s,%s;'''
info("格式化后:"+sql % ((int(request.args.get("perPage"))*int(request.args.get("page")))-int(request.args.get("perPage")), int(request.args.get("perPage"))))
data = await request.app.ctx.db_pt.query(sql,(int(request.args.get("perPage"))*int(request.args.get("page")))-int(request.args.get("perPage")), int(request.args.get("perPage")))
count_sql = "SELECT count(id) as sum FROM `t_user` WHERE deleted=0 "
if where!="":
    count_sql = count_sql+where
# info("查询记录总数:"+count_sql)
count_data = await request.app.ctx.db_pt.query(count_sql)
# info(count_data)
if len(data) == 0:
    rsjson["status"] = 1
    rsjson["msg"] = "没有查询到符合条件的数据"
    rsjson["data"] = {}
else:
    rsjson["data"]["items"] = data
    rsjson["data"]["total"] = count_data[0]["sum"]
return response.json(rsjson, ensure_ascii=False,
                     indent=4, default=str)

执行结果如下:

拼接查询语句后:SELECT
a.id,
a.login_name,
a.user_name,
a.mobile,
b.name AS role_name,
c.org_name,
a.status,
a.update_time
FROM
t_user AS a
INNER JOIN t_role AS b ON a.role_id = b.id
INNER JOIN t_org AS c ON a.org_id = c.id
WHERE
a.deleted = 0 AND c.org_name like '%%org1%%'
[2023-04-02 22:03:42 +0800] [3732] [INFO] 格式化后:SELECT
a.id,
a.login_name,
a.user_name,
a.mobile,
b.name AS role_name,
c.org_name,
a.status,
a.update_time
FROM
t_user AS a
INNER JOIN t_role AS b ON a.role_id = b.id
INNER JOIN t_org AS c ON a.org_id = c.id
WHERE
a.deleted = 0 AND c.org_name like '%org1%' ORDER BY id DESC LIMIT 0,10;
[2023-04-02 22:03:42 +0800] [3732] [ERROR] Exception occurred while handling uri: 'http://127.0.0.1:8080/api/user/userList?page=1&org_name=org1&perPage=10'
Traceback (most recent call last):
File "d:\py\smart_electricity\venv\lib\site-packages\sanic\app.py", line 968, in handle_request
response = await response
File "D:\py\smart_electricity\libs\lib.py", line 126, in decorated_function
response = await f(request, *args, **kwargs)
File "D:\py\smart_electricity\api\user.py", line 64, in userList
count_data = await request.app.ctx.db_pt.query(count_sql)
File "d:\py\smart_electricity\venv\lib\site-packages\ezmysql\connection_async.py", line 83, in query
await cur.execute(query, kwparameters or parameters)
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\cursors.py", line 239, in execute
await self._query(query)
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\cursors.py", line 457, in _query
await conn.query(q)
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\connection.py", line 469, in query
await self._read_query_result(unbuffered=unbuffered)
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\connection.py", line 672, in _read_query_result
await result.read()
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\connection.py", line 1153, in read
first_packet = await self.connection._read_packet()
File "d:\py\smart_electricity\venv\lib\site-packages\aiomysql\connection.py", line 641, in _read_packet
packet.raise_for_error()
File "d:\py\smart_electricity\venv\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "d:\py\smart_electricity\venv\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'c.org_name' in 'where clause'")
[2023-04-02 22:03:42 +0800] - (sanic.access)[INFO][127.0.0.1:61559]: GET http://127.0.0.1:8080/api/user/userList?page=1&org_name=org1&perPage=10 500 2338
[2023-04-02 22:03:50 +0800] [3732] [DEBUG] KeepAlive Timeout. Closing connection.

My mysql server port is 8889 instead of 3306. How can I change it?

I use db in torndb_ Db. begin() starts the transaction. How to do it in ezmysql?

What should I do when I set autocommit to True in the connect constructor and some logic needs to manually start a transaction?