让 Python 的数据库查询返回字典记录

calendar Dec 16, 2018   ---   · 2 min read · database Python  ·
Share on: linkedin copy
在使用  Python 进行数据库查询，通常情况下 cursor 的 fetchall, fetchmany 返回的是元组(Tuple) 的列表，所以对查询到的结果只能用索引下标来访问，而无法通过字段名来获取值。对 Java JDBC 的 ResultSet 操作，我们有两种获取值的方式，resultSet.getString(1) 和 resultSet.getString('name')。

其实只要能用数字索引访问到字段值也就足够了，查询后字段名可以由 cursor.description 获得。通过字段名来访问值唯一的好处估计是出错的概率小些罢了，比如 result['firstname'], result['lastname'] 总是比 result[1], result[2] 更不容易搞混，错误定位也会更轻松。

假如有下面的数据库表与两条记录
create table test(name varchar(10), count integer);
insert into test(name, count) values ('Bob', 1), ('Joe', 8);
以 SQLite 数据库操作代码为例
1import sqlite3
2
3conn = sqlite3.connect("datafile")
4cursor = conn.cursor()
5cursor.execute("select * from test")
6print(cursor.fetchall())

打印出来的结果是
[('Bob', 1), ('Joe', 8)]
对结果进行遍历的方式可以是以下几种方式
1for row in cursor:fetchall():
2    print(row[0], row[1])

或
1for row in cursor:
2    print(row[0], row[1])

或
1for name, count in cursor:
2    print(name, count)

如果希望查询得到的结果集是一个带有字段名与值的字典的话，也就是说可能像 JDBC resultSet 那样通过字段名来访问值该如何定制呢？对于不同的数据库实现方式略有不同

通用方式(适用于所有数据库)

首先，前面提到的，查询后的字段名称可由 cursor.description 获得，下面的代码会打印出每个字段的名称
1for col in cursor.description:
2    print(col[0])

如果把字段时的列表与每一行结果的 Tuple 以 zip相结合就能得到一个字典行
1import sqlite3
2
3conn = sqlite3.connect('datafile')
4cursor = conn.cursor()
5cursor.execute('select * from test')
6
7columns = [column[0] for column in cursor.description]
8for row in cursor.fetchall():
9    print(dict(zip(columns, row)))

上面输出的结果是
{'name': 'Bob', 'count': 1}
{'name': 'Joe', 'count': 8}

SQLite 结果集返回字典

SQLite 就是基于这一通用的方式来设定连接的 row_factory 函数属性的，完整代码如下：
 1import sqlite3
 2
 3
 4def dict_factory(cursor, row):
 5    d = {}
 6    for idx, col in enumerate(cursor.description):
 7        d[col[0]] = row[idx]
 8    return d
 9    # 以上四行代码可用一行 Comprehension 代码实现
10    # return {col[0]:row[idx] for idx, col in enumerate(cur.description)}
11
12
13conn = sqlite3.connect("datafile")
14conn.row_factory = dict_factory
15cursor = conn.cursor()
16cursor.execute("select * from test")
17
18print(cursor.fetchall())

cursor.fetchall() 获得的结果输出为
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
它是一个字典列表，访问方式就要用字典的方式，如
1for row in cursor:  #或  for row in cursor.fetchall():
2    print(row['name'], row['count'])

MySQL 返回字典结果集

设置 conn.row_factory 的方式对 MySQL 是不奏效了。幸好 MySQLdb 库预备好了不同的打开 cursor 的方式
1import MySQLdb
2
3conn = MySQLdb.connect(host='localhost', user='test', password='xxx', database='test')
4cursor = conn.cursor(MySQLdb.cursors.DictCursor)
5cursor.execute("select * from test")
6
7print(cursor.fetchall())

此时 cursor.fetchall() 的结果也是
({'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8})
如果没有设置 cursor  类型为  MySQLdb.cursors.DictCursor, 得到的结果也是 [('Bob', 1), ('Joe', 8)]。

注：关于  MySQLdb 的安装方法，可参考 https://pypi.org/project/mysqlclient/，在  Ubuntu 平台下是
$ sudo apt-get install default-libmysqlclient-dev
$ pip3 install mysqlclient
MySQL 还有一个 Python 驱动，可用  pip3 install mysql-connector  安装，尚不知道如何返回字典结果集
1import mysql.connector
2
3conn = mysql.connector.connect(host='localhost', user='test', password='xxx', database='test')

另外，PostgreSQL 也能用类似的方式
1import psycopg2.extras
2
3cursor = conn.cursor(cursor_factory=psycopg2.extras.DictDursor)

SQL Server 返回字典结果集

用 SQL Server 的驱动 pymssql 让结果集返回为字典的方式就更为直截了当，只需要设置 cursor 的 as_dict 属性为 True 就行了。详见下方代码
1import pymssql
2
3
4conn = pymssql.connect('localhost', 'sa', 'your-password', 'testdb')
5cursor = conn.cursor(as_dict = True)
6
7cursor.execute('select * from test')
8print(cursor.fetchall())

打印的结果为
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
上面基本涵盖了我们常用的数据库类型，MySQL, PostgreSQL 和 SQL Server。Python 默认查询只是返回一个不含有字段名的结果集(List of Tuple)，这个需求基本上就满足了。可能很少情况下真正需要 Python 查询数据库后返回一个字典结果集，如果真有如此需求的话，以上内容可供参考。

补充(2021-09-17): PostgreSQL 中的做法
1import psycopg2.extras
2
3cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

链接：

  1. pymssql examples
永久链接 https://yanbin.blog/python-database-query-return-dictionary-result/, 来自 隔叶黄莺 Yanbin's Blog
[版权声明] 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。