Django笔记二十一之使用原生SQL查询数据库

本文首发于微信公众号：Hunter后端。

原文链接：Django笔记二十一之使用原生SQL查询数据库

Django 提供了两种方式来执行原生 SQL 代码。

一种是使用 raw() 函数，一种是 使用 connection.cursor()。

但是官方还是推荐在使用原生 SQL 之前，尽量的先去探索一下 QuerySet 提供的各种 API。

目前而言，官方文档提供的各种 API 是能够满足绝大多数的应用场景的。

以下是本篇笔记的目录：

1. raw()
2. connection.cursor()
3. 多数据库操作

1、raw()

这个方法可以用来操作原生 SQL，然后返回 model 实例：

我们以 Blog 作为示例，使用的代码如下：

for blog in Blog.objects.raw("select * from blog_blog"):
    print(blog)

上面这段代码的作用效果跟 Blog.objects.all() 获取的结果是一样的，但是在一些操作上又不太一样，比如 all().count() 可以获取到 总数，但是 raw() 就不能进行这种操作。

需要注意的是，raw() 并不会去检测输入的 SQL 代码，即使我们使用 Blog 这个model 来查询 Entry 的数据，也能够返回结果，不过返回的都是 Entry 表的属性：

for blog in Blog.objects.raw("select * from blog_entry"):
    print(blog.__dict__)  # __dict__ 输出的都是 blog_entry 的字段

也就是说 在 Blog.objects.raw() 中，真正起作用的只有 raw() 这一个函数，前面的 Blog 不过是一个架子，或者途径，是为了引出 raw() 这个函数。

自定义返回字段

在 raw() 函数的 SQL 代码里，我们可以自定义选中的字段，如果需要使用没有选中的字段，那么系统会再次访问数据库来获取，这个操作过程就跟前面介绍的 defer() 函数一样。

for item in Blog.objects.raw("select id from blog_blog"):
    print(item.__dict__)
    print(item.id, item.name)
    print(item.__dict__)

{'_state': <django.db.models.base.ModelState object at 0x7fbd4165b6a0>, 'id': 2}
2 hunter
{'_state': <django.db.models.base.ModelState object at 0x7fbd4165b6a0>, 'id': 2, 'name': 'hunter'}

可以看到，返回的结果中先输出的数据只有 id，后面，当我们访问 name 字段的时候，又去获取了 name 字段的数据。

自定义字段必须包含主键

当我们自定义字段返回的时候，必须是要包含主键字段的，否则在我们获取信息的时候会报错，比如下面的操作：

for blog in Blog.objects.raw("select name from blog_blog"):
    print(blog.__dict__)

在 print(blog.__dict__) 的时候就会报错，数据中没有主键信息

自定义返回新字段

可以跟 QuerySet 的 annotate 操作一样，自定义新字段返回，获取的时候可以直接根据属性值返回，比如：

entry = Entry.objects.raw("select *, date_format(pub_date, '%%Y-%%m') as date_1 from blog_entry")[0]
print(entry.date_1)

传递变量

给输入的 SQL 语句传递变量：

name = "python"
Blog.objects.raw("select * from blog_blog where name = '%s'", [name])

2、connection.cursor()

Django 推出了一种更加直接执行 SQL 的方式，用到的模块是 django.db.connction，用到的 cursor 和 pymysql 的库是一样的用法，官方给出的示例如下：

from django.db import connection


def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

需要注意，如果有参数传入 SQL 中，需要对一些符号进行转义之后才可以使用，比如：

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

其中，第二条语句的 % 需要进行转义

适配fetch的数据

通过 fetchone 和 fetchall() 返回的数据是只有 value 值的，没有对应的字段 key，如果可以适当的牺牲性能和内存，来换取获取数据的便利和准确性，官方提供了这样一种方式：

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

在我们执行完 cursor.execute(sql) 之后，把 cursor 作为参数传入 dictfetchall 函数中，就可以返回一个字典列表

介绍一下 cursor.description，这个返回的是一个元组数据，里面的元素也是一个元组，元组的第一个元素就是我们 select 的字段名。

所以 columns = [col[0] for col in cursor.description] 这一行代码获取的是指定的所有字段名

使用示例：

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

with connection.cursor() as cursor:
    sql = "select id, name from blog_blog"
    cursor.execute(sql)
    results = dictfetchall(cursor)

print(results)

# 返回的结果：
# [{'id': 20, 'name': 'name_3'}, {'id': 21, 'name': 'name_4'}, {'id': 1, 'name': 'name_5'}]

在我使用的过程中，我们使用的是上下文管理器的方式来获取游标：

with connection.cursor() as cursor:
    cursor.execute()

所以，使用完之后，不需要手动关闭，他与下面的用法效果是一致的：

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

但推荐的还是上下文管理器的方式，更优雅。

3、多数据库操作

如果系统用到了多个数据库，那么在使用 cursor 的时候，就需要使用到 django.db.connections 模块：

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    pass

以上就是本篇笔记全部内容，下一篇笔记将简单介绍一下多数据库的操作。

如果想获取更多相关文章，可扫码关注阅读：