Python语言Django开发框架执行自定义原生sql语句方法总结

Python语言Django开发框架执行自定义原生sql语句方法常用的有：Model.objects.extra()、Model.objects.raw(sql)、django框架内置函数connection。

使用Model.objects.extra()

Model.objects.extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

参数select用法：
users = User.objects.extra(select={“is_recent”: " createdate > ‘2023-09-23’ "})
执行sql：select (createdate > ‘2023-10-23’) as is_recent, data_user.id, data_user.loginname, data_user.password, data_user.createdate fromdata_user;
ps：参数select即添加额外的查询字段，额外添加的字段is_recent是一个布尔类型，条件 " createdate > ‘2023-09-23’ " 也可以是一个 sql语句，例如：users = User.objects.extra(select= {“scorecount”: “select count(*) from data_userscore where userid = data_user.id”})。
为防止sql注入，使用参数select_params优化：
users = User.objects.extra(select={“is_recent”: “createdate > %s”}, select_params=[“2023-09-23”])

参数where用法：
users = User.objects.extra(where=[“loginname like ‘%%user%%’ or fullname like ‘%%user%%’”])
如果使用的有filter(**params)，即：
users = User.objects.extra(where=[“loginname like ‘%%user%%’ or fullname like ‘%%user%%’”]).filter(id=1)，
那么filter与extra()是and并列关系。
为防止sql注入，可使用参数params，类似参数select_params用法。
但是当使用like “%param%“模糊查询时，需要注意%的问题，like ‘%%user%%‘中django会将%%转义为%，然后交由mysql运行，如果同时使用参数select_params，以下写法不正确：
users = User.objects.extra(where=[“loginname like ‘%%%s%%’ or fullname like ‘%%%s%%’”], params=[“user”, “user”])
需要使用以下写法：
users = User.objects.extra(where=["loginname like concat(’%%’, %s, ‘%%’) or fullname like concat(‘%%’, %s, ‘%%’)”], params=[“user”, “user”])
ps：concat是mysql内置函数，链接字符串
或者
users = User.objects.extra(where=[“loginname like %s or fullname like %s”], params=[”%“+“user”+”%“, “%”+“user”+”%"])

参数tables用法：
movies = Movie.objects.extra(tables=[“movietype”])
执行sql：select movie.id from movie, movietype，即额外查询其他表，这里暂不做举例，处理的业务相对复杂。

参数order_by用法：
users = User.objects.extra(order_by=[“-id”, “createdate”])
执行sql：select data_user.id, data_user.loginname, data_user.password, data_user.createdate fromdata_user order by id desc, createdate asc;
当需要使用特定排序时，例如：id按照1,3,2排序，sql语句如下：
select user.id from user order by field(id, 1, 3, 2)；
这时就不能够使用order_by参数，最好使用以下执行自定义原生sql语句方法。

使用Model.objects.raw(sql)

sql = " select user.id from user "
users = User.objects.raw(sql)，返回RawQuerySet数据类型，集合中的数据是模型类对象，RawQuerySet继承QuerySet，但是此方法有一些限制：
1、查询结果必须要有模型类主键id；
2、查询结果不能直接转字典类型，需要手动遍历转化，即不能够使用values()方法，但是QuerySet可以使用；
3、如果要分页，是先查询完再分页，即如果一次查询的数据很多，将这些数据查询完后再进行分页，分页是以list的形式分页，查询效率很低，但是QuerySet分页是执行的最终分页的sql，有数量限制。

django框架内置函数connection

使用django框架提供的connection对象实现数据库查询：
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(sql, params) 执行sql语句，返回的数据中，字段和数据是分开的，没有一一对应
封装，将查询结果与数据库字段映射：

  # 查询所有数据，参数：params即查询参数
  from django.db import connection
  def query(sql, params=None):
      # 定义返回的结果
      query_data = list()
      # 查询，使用django框架提供的connection对象实现数据库查询
      with connection.cursor() as cursor:
          # 执行sql语句，返回的数据中，字段和数据是分开的，没有一一对应
          cursor.execute(sql, params)
          # 获取查询结果的字段名
          # description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
          query_field_names = [k[0] for k in cursor.description]
          # 获取所有数据值，格式[(字段1的值,字段2的值....)，(字段1的值,字段2的值....)]，是一个由元组组成的列表
          query_field_values = cursor.fetchall()
          # 将字段名和字段值一一对应并转dict格式
          for query_field_value in query_field_values:
              result = dict(zip(query_field_names, query_field_value))
              query_data.append(result)
      return query_data