如何从sqlite查询中获取dict？

您可以使用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"]

或遵循文档中此示例后给出的建议:

如果返回一个元组是不够的,并且您希望对列进行基于名称的访问,则应考虑将row_factory设置为高度优化的sqlite3.Row类型.Row提供基于索引和不区分大小写的基于名称的访问,几乎没有内存开销.它可能比您自己的基于字典的自定义方法甚至基于db_row的解决方案更好.

只是引用文档,`con.row_factory = sqlite3.Row` (68认同)

gandalf.. 22

尽管在Adam Schmideg和Alex Martelli的回答中都部分提到了答案,但我还是回答了这个问题.为了让像我这样有其他问题的人能够轻松找到答案.

conn = sqlite3.connect(":memory:")

#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)
conn.row_factory = sqlite3.Row
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

目前`fetchall()`似乎返回`sqlite3.Row`对象.然而,只需使用`dict()`:`result = [dict(row)for c.fetchall()]中的行就可以将它们转换为字典. (10认同)

bbengfort.. 19

即使使用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)` (9认同)

Ignacio Vazq.. 8

从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.

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

conn = sqlite3.connect(":memory:")

#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)
conn.row_factory = sqlite3.Row
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

print "%(id)i - %(name)s: %(value)s" % row

def dict_from_row(row):
    return dict(zip(row.keys(), row))       

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.