讓 Python 的資料庫查詢返回字典記錄
在使用 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())
打印出來的結果是
[('Bob', 1), ('Joe', 8)]
對結果進行遍歷的方式可以是以下幾種方式
for row in cursor:fetchall(): print(row[0], row[1])
或
for row in cursor: print(row[0], row[1])
或
for name, count in cursor: print(name, count)
如果希望查詢得到的結果集是一個帶有欄位名與值的字典的話,也就是說可能像 JDBC resultSet 那樣通過欄位名來訪問值該如何定製呢?對於不同的資料庫實現方式略有不同
通用方式(適用於所有資料庫)
首先,前面提到的,查詢後的欄位名稱可由cursor.description
獲得,下面的程式碼會打印出每個欄位的名稱
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)))
上面輸出的結果是
{'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())
cursor.fetchall()
獲得的結果輸出為
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
它是一個字典列表,訪問方式就要用字典的方式,如
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())
此時cursor.fetchall()
的結果也是
({'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8})
如果沒有設定cursor
型別為 MySQLdb.cursors.DictCursor
, 得到的結果也是[('Bob', 1), ('Joe', 8)]
。
注:關於MySQLdb
的安裝方法,可參考ofollow,noindex" target="_blank">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')
另外,PostgreSQL 也能用類似的方式
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())
列印的結果為
[{'name': 'Bob', 'count': 1}, {'name': 'Joe', 'count': 8}]
上面基本涵蓋了我們常用的資料庫型別,MySQL, PostgreSQL 和 SQL Server。Python 預設查詢只是返回一個不含有欄位名的結果集(List of Tuple),這個需求基本上就滿足了。可能很少情況下真正需要 Python 查詢資料庫後返回一個字典結果集,如果真有如此需求的話,以上內容可供參考。
連結: