0x00
尽管ORM很好用,单很多情况下有很大局限性,常常还是会需要用到原生sql查询。下面就吧django的原生sql查询封装几个常用方法(django底层是使用MySQLdb或mysqlclient,所以他们的封装类似,也可以参考)。
0x01
博客这个markdown格式化python代码的三双引号有问题,方法注释复制到编辑器查看更清晰
# coding=utf-8
from django.db import connections
def exec_sql(sql, params=None, db='default'):
"""
执行sql,例如insert和update
:param sql: sql语句
:param params: sql语句参数
:param db: Django数据库名
"""
cursor = connections[db].cursor()
cursor.execute(sql, params)
cursor.close()
cursor.last_insert_id()
return True
def fetchone_sql(sql, params=None, db='default', flat=False):
"""
返回一行数据
:param sql: sql语句
:param params: sql语句参数
:param db: Django数据库名
:param flat: 如果为True,只返回第一个字段值,例如:id
:return: 例如:(id, 'username', 'first_name')
"""
cursor = connections[db].cursor()
cursor.execute(sql, params)
fetchone = cursor.fetchone()
cursor.close()
if fetchone:
fetchone = fetchone[0] if flat else fetchone
return fetchone
def fetchone_to_dict(sql, params=None, db='default'):
"""
返回一行数据
:param sql: sql语句
:param params: sql语句参数
:param db: Django数据库名
:return: 例如:{"id": id, "username": 'username', "first_name": 'first_name'}
"""
cursor = connections[db].cursor()
cursor.execute(sql, params)
desc = cursor.description
row = dict(zip([col[0] for col in desc], cursor.fetchone()))
cursor.close()
return row
def fetchall_sql(sql, params=None, db='default', flat=False):
"""
返回全部数据
:param sql: sql语句
:param params: sql语句参数
:param db: Django数据库名
:param flat: 如果为True,只返回每行数据第一个字段值的元组,例如:(id1, id2, id3)
:return: 例如:[(id, 'username', 'first_name')]
"""
cursor = connections[db].cursor()
cursor.execute(sql, params)
fetchall = cursor.fetchall()
cursor.close()
if fetchall:
fetchall = tuple([o[0] for o in fetchall]) if flat else fetchall
return fetchall
def fetchall_to_dict(sql, params=None, db='default'):
"""
返回全部数据
:param sql: sql语句
:param params: sql语句参数
:param db: Django数据库名
:return: 例如:[{"id": id, "username": 'username', "first_name": 'first_name'}]
"""
cursor = connections[db].cursor()
cursor.execute(sql, params)
desc = cursor.description
object_list = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
cursor.close()
return object_list
def demo():
sql = "select id, username, first_name from auth_user where status=%(status)s"
params = {"status": 1}
# 例如一条数据数据为:
# [
# {
# "id": 1,
# "username": "111",
# "first_name": "aaa",
# "status": 1
# },
# {
# "id": 2,
# "username": "222",
# "first_name": "bbb",
# "status": 1
# },{
# "id": 3,
# "username": "333",
# "first_name": "ccc",
# "status": 0
# }
# ]
print fetchone_sql(sql, params)
# 输出示例:
# (1, "111", "aaa")
print fetchone_sql(sql, params, flat=True)
# 输出示例:
# 1
print fetchone_to_dict(sql, params)
# 输出示例:
# {"id": 1, "username": "111", "first_name": "aaa"}
print fetchall_sql(sql, params)
# 输出示例:
# [(1, "111", "aaa"), (2, "222", "bbb")]
print fetchall_sql(sql, params, flat=True)
# 输出示例:
# (1, 2)
print fetchall_to_dict(sql, params)
# 输出示例:
# [{"id": 1, "username": "111", "first_name": "aaa"}, {"id": 2, "username": "222", "first_name": "bbb"}]
if __name__ == "__main__":
# 如果是django封装,不能使用该方法启动脚本,必须放入django视图中,否则没有django配置无法运
# 如果是MySQLdb或者mysqlclient则可以直接运行
demo()