让 Python 的数据库查询返回字典记录 | 隔叶黄莺 Yanbin Blog

2018-12-16 | 阅读(3,286)

在使用 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 数据库操作代码为例

import sqlite3

conn = sqlite3.connect("datafile")
cursor = conn.cursor()
cursor.execute("select * from test")
print(cursor.fetchall())

import sqlite3

conn = sqlite3.connect("datafile")

cursor = conn.cursor()

cursor.execute("select * from test")

print(cursor.fetchall())

打印出来的结果是

[('Bob', 1), ('Joe', 8)]

对结果进行遍历的方式可以是以下几种方式

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

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

或

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

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

或

for name, count in cursor:
    print(name, count)

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

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

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

首先，前面提到的，查询后的字段名称可由 cursor.description 获得，下面的代码会打印出每个字段的名称

for col in cursor.description:
    print(col[0])

1 2	for col in cursor.description: print(col[0])

如果把字段时的列表与每一行结果的 Tuple 以 zip相结合就能得到一个字典行

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)))

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 函数属性的，完整代码如下：

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())

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}]

它是一个字典列表，访问方式就要用字典的方式，如

for row in cursor:  #或  for row in cursor.fetchall():
    print(row['name'], row['count'])

1 2	for row in cursor: #或 for row in cursor.fetchall(): print(row['name'], row['count'])

MySQL 返回字典结果集

设置 conn.row_factory 的方式对 MySQL 是不奏效了。幸好 MySQLdb 库预备好了不同的打开 cursor 的方式

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())

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 安装，尚不知道如何返回字典结果集

import mysql.connector

conn = mysql.connector.connect(host='localhost', user='test', password='xxx', database='test')

import mysql.connector

conn = mysql.connector.connect(host='localhost', user='test', password='xxx', database='test')

另外，PostgreSQL 也能用类似的方式

import psycopg2.extras

cursor = conn.cursor(cursor_factory=psycopg2.extras.DictDursor)

import psycopg2.extras

cursor = conn.cursor(cursor_factory=psycopg2.extras.DictDursor)

SQL Server 返回字典结果集

用 SQL Server 的驱动 pymssql 让结果集返回为字典的方式就更为直截了当，只需要设置 cursor 的 as_dict 属性为 True 就行了。详见下方代码

import pymssql


conn = pymssql.connect('localhost', 'sa', 'your-password', 'testdb')
cursor = conn.cursor(as_dict = True)

cursor.execute('select * from test')
print(cursor.fetchall())

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 中的做法

import psycopg2.extras

cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

import psycopg2.extras

cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)

链接：

pymssql examples

本文链接 https://yanbin.blog/python-database-query-return-dictionary-result/, 来自隔叶黄莺 Yanbin Blog

2 Comments

Inline Feedbacks

View all comments

fffwl

3 years ago

优秀

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

4 years ago

[…] 假如有下面的数据库表与两条记录阅读全文 >> […]

best coffee on SciPy 最优化之最小化I wanted to take a moment to commend you on the outstanding quality of...
seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...

M	T	W	T	F	S	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31