如何从sqlite查询获得字典？

Question

db = sqlite.connect("test.sqlite") 
res = db.execute("select * from table") 

通过迭代，我得到了与这些行相关的列表。

for row in res: 
    print row 

我可以列

col_name_list = [tuple[0] for tuple in res.description] 

的名字，但有一些功能或设置为获取字典而不是列表？

{'col1': 'value', 'col2': 'value'} 

还是我必须自己做？

Answer 1

你可以在示例中的文档使用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的解决方案。

Answer 2

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

所以，是的，自己做。

Answer 3

即使使用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))  

Answer 4

或者您可以将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 

Answer 5

一个普通的选择，只用三行

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

Answer 6

我想我回答，即使答案是亚当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 

Answer 7

较短的版本：

db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)]) 

Answer 8

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) 

结果肯定是真的，但我不知道最好。

Answer 9

在我的测试中最快：

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) 

你决定:)