在使用 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 数据库操作代码为例
|
1 2 3 4 5 6 |
import sqlite3 conn = sqlite3.connect("datafile") cursor = conn.cursor() cursor.execute("select * from test") print(cursor.fetchall()) |
打印出来的结果是
[('Bob', 1), ('Joe', 8)]
对结果进行遍历的方式可以是以下几种方式
|
1 2 |
for row in cursor:fetchall(): print(row[0], row[1]) |
或
|
1 2 |
for row in cursor: print(row[0], row[1]) |
或
|
1 2 |
for name, count in cursor: print(name, count) |
如果希望查询得到的结果集是一个带有字段名与值的字典的话，也就是说可能像 JDBC resultSet 那样通过字段名来访问值该如何定制呢？对于不同的数据库实现方式略有不同
通用方式(适用于所有数据库)
首先，前面提到的，查询后的字段名称可由 cursor.description 获得，下面的代码会打印出每个字段的名称
|
1 2 |
for col in cursor.description: print(col[0]) |
如果把字段时的列表与每一行结果的 Tuple 以 zip相结合就能得到一个字典行
|
1 2 3 4 5 6 7 8 9 |
import sqlite3 conn = sqlite3.connect('datafile') cursor = conn.cursor() cursor.execute('select * from test') columns = [column[0] for column in cursor.description] for row in cursor.fetchall(): print(dict(zip(columns, row))) |
上面输出的结果是
{'name': 'Bob', 'count': 1}
{'name': 'Joe', 'count': 8}
SQLite 结果集返回字典
SQLite 就是基于这一通用的方式来设定连接的 row_factory 函数属性的，完整代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d # 以上四行代码可用一行 Comprehension 代码实现 # return {col[0]:row[idx] for idx, col in enumerate(cur.description)} conn = sqlite3.connect("datafile") conn.row_factory = dict_factory cursor = conn.cursor() cursor.execute("select * from test") print(cursor.fetchall()) |
cursor.fetchall() 获得的结果输出为
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
它是一个字典列表，访问方式就要用字典的方式，如
|
1 2 |
for row in cursor: #或 for row in cursor.fetchall(): print(row['name'], row['count']) |
MySQL 返回字典结果集
设置 conn.row_factory 的方式对 MySQL 是不奏效了。幸好 MySQLdb 库预备好了不同的打开 cursor 的方式
|
1 2 3 4 5 6 7 |
import MySQLdb conn = MySQLdb.connect(host='localhost', user='test', password='xxx', database='test') cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("select * from test") print(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 安装，尚不知道如何返回字典结果集
|
1 2 3 |
import mysql.connector conn = mysql.connector.connect(host='localhost', user='test', password='xxx', database='test') |
另外，PostgreSQL 也能用类似的方式
|
1 2 3 |
import psycopg2.extras cursor = conn.cursor(cursor_factory=psycopg2.extras.DictDursor) |
SQL Server 返回字典结果集
用 SQL Server 的驱动 pymssql 让结果集返回为字典的方式就更为直截了当，只需要设置 cursor 的 as_dict 属性为 True 就行了。详见下方代码
|
1 2 3 4 5 6 7 8 |
import pymssql conn = pymssql.connect('localhost', 'sa', 'your-password', 'testdb') cursor = conn.cursor(as_dict = True) cursor.execute('select * from test') print(cursor.fetchall()) |
打印的结果为
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
上面基本涵盖了我们常用的数据库类型，MySQL, PostgreSQL 和 SQL Server。Python 默认查询只是返回一个不含有字段名的结果集(List of Tuple)，这个需求基本上就满足了。可能很少情况下真正需要 Python 查询数据库后返回一个字典结果集，如果真有如此需求的话，以上内容可供参考。
补充(2021-09-17): PostgreSQL 中的做法
|
1 2 3 |
import psycopg2.extras cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor) |
链接：
优秀
[…] 假如有下面的数据库表与两条记录 阅读全文 >> […]