如何从sqlite查询获得字典?
db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")
通过迭代,我得到了与这些行相关的列表。如何从sqlite查询获得字典?
for row in res:
print row
我可以列
col_name_list = [tuple[0] for tuple in res.description]
的名字,但有一些功能或设置为获取字典而不是列表?
{'col1': 'value', 'col2': 'value'}
还是我必须自己做?
你可以在示例中的文档使用row_factory,如:
import sqlite3
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]
或同样遵循此例中的文档之后给出的建议是:
如果返回一个元组没有按” t足够 并且您希望基于名称访问 列,则应考虑将row_factory设置为 sqlite3.Row类型。行同时提供 基于索引和不区分大小写的 基于名称的访问列与 几乎没有内存开销。它可能会比你自己的 基于自定义字典的方法或 更好,即使是基于db_row的解决方案。
只需引用文档,'con.row_factory = sqlite3.Row' – 2010-12-30 01:19:30
如果列名中包含特殊字符例如'SELECT 1 AS'dog [cat]“'那么'cursor'将不会有正确的描述来创建一个字典。 – Crazometer 2016-06-16 06:23:49
我已经设置了'connection.row_factory = sqlite3.Row',我尝试了'connection.row_factory = dict_factory'如图所示,但'cur.fetchall()'仍然给我一个元组列表 - 任何想法为什么这不起作用? – displayname 2016-12-07 10:51:45
从PEP 249:
Question:
How can I construct a dictionary out of the tuples returned by
.fetch*():
Answer:
There are several existing tools available which provide
helpers for this task. Most of them use the approach of using
the column names defined in the cursor attribute .description
as basis for the keys in the row dictionary.
Note that the reason for not extending the DB API specification
to also support dictionary return values for the .fetch*()
methods is that this approach has several drawbacks:
* Some databases don't support case-sensitive column names or
auto-convert them to all lowercase or all uppercase
characters.
* Columns in the result set which are generated by the query
(e.g. using SQL functions) don't map to table column names
and databases usually generate names for these columns in a
very database specific way.
As a result, accessing the columns through dictionary keys
varies between databases and makes writing portable code
impossible.
所以,是的,自己做。
>在数据库之间有所不同 - 比如说,sqlite 3.7和3.8? – Nucular 2014-03-07 17:23:18
@ user1123466:...像SQLite,MySQL,Postgres,Oracle,MS SQL Server,Firebird ... – 2014-03-07 17:52:41
即使使用sqlite3.Row分类 - 你仍然无法使用字符串形式格式化:
print "%(id)i - %(name)s: %(value)s" % row
为了突破这个,我使用一个辅助函数,它的行并转换成字典。当字典对象比Row对象更受欢迎时(例如,对于像Row对象本身不支持字典API的字符串格式)的情况,我只使用它。但是其他时间使用Row对象。
def dict_from_row(row):
return dict(zip(row.keys(), row))
sqlite3.Row实现了映射协议。你可以做'print'%(id)i - %(name)s:%(value)s“%dict(row)' – Mzzzzzz 2015-06-11 14:07:28
或者您可以将sqlite3.Rows转换为字典,如下所示。这将为字典提供每行的列表。
def from_sqlite_Row_to_dict(list_with_rows):
''' Turn a list with sqlite3.Row objects into a dictionary'''
d ={} # the dictionary to be filled with the row data and to be returned
for i, row in enumerate(list_with_rows): # iterate throw the sqlite3.Row objects
l = [] # for each Row use a separate list
for col in range(0, len(row)): # copy over the row date (ie. column data) to a list
l.append(row[col])
d[i] = l # add the list to the dictionary
return d
一个普通的选择,只用三行
def select_column_and_value(db, sql, parameters=()):
execute = db.execute(sql, parameters)
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
con = sqlite3.connect('/mydatabase.db')
c = con.cursor()
print(select_column_and_value(c, 'SELECT * FROM things WHERE id=?', (id,)))
但是,如果您的查询返回任何内容,将导致错误。在这种情况下...
def select_column_and_value(self, sql, parameters=()):
execute = self.execute(sql, parameters)
fetch = execute.fetchone()
if fetch is None:
return {k[0]: None for k in execute.description}
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
或
def select_column_and_value(self, sql, parameters=()):
execute = self.execute(sql, parameters)
fetch = execute.fetchone()
if fetch is None:
return {}
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
我想我回答,即使答案是亚当Schmideg的和Alex马尔泰利的答案部分提到了这个问题。为了让像我这样有相同问题的人能够轻松找到答案。
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row#This is the important part, here we are setting row_factory property of connection object to sqlite3.Row(sqlite3.Row is an implementation of row_factory)
c = conn.cursor()
c.execute('select * from stocks')
result = c.fetchall()#returns a list of dictionaries, each item in list(each dictionary) represents a row of the table
较短的版本:
db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
这对我很好。谢谢! – 2017-12-19 20:22:52
import sqlite3
db = sqlite3.connect('mydatabase.db')
cursor = db.execute('SELECT * FROM students ORDER BY CREATE_AT')
studentList = cursor.fetchall()
columnNames = list(map(lambda x: x[0], cursor.description)) #students table column names list
studentsAssoc = {} #Assoc format is dictionary similarly
#THIS IS ASSOC PROCESS
for lineNumber, student in enumerate(studentList):
studentsAssoc[lineNumber] = {}
for columnNumber, value in enumerate(student):
studentsAssoc[lineNumber][columnNames[columnNumber]] = value
print(studentsAssoc)
结果肯定是真的,但我不知道最好。
在我的测试中最快:
conn.row_factory = lambda c, r: dict(zip([col[0] for col in c.description], r))
c = conn.cursor()
%timeit c.execute('SELECT * FROM table').fetchall()
19.8 µs ± 1.05 µs per loop (mean ± std. dev. of 7 runs, 100000 loops each)
VS:
conn.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
c = conn.cursor()
%timeit c.execute('SELECT * FROM table').fetchall()
19.4 µs ± 75.6 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)
你决定:)
的可能重复[Python的 - MySQLdb的,SQLite的结果作为字典(HTTP:// *.com/questions/4147707/python-mysqldb-sqlite-result-as-dictionary) – vy32 2015-08-22 22:50:09
@ vy32:这个问题是从2010年7月,你链接到2010年11月。所以,这是一个愚蠢的。正如人们所期望的那样,反向评论已经被提上了那个:-) – aneroid 2016-06-07 20:49:16