Python 如何从sqlite查询中获取dict？

How can I get dict from sqlite query?

提问by Meloun

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

With iteration I get lists coresponding to the rows.

通过迭代，我得到与行对应的列表。

for row in res:
    print row

I can get name of the columns

我可以得到列的名称

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

But is there some function or setting to get dictionaries instead of list?

但是是否有一些功能或设置来获取字典而不是列表？

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

or I have to do myself?

还是我必须自己做？

采纳答案by Alex Martelli

You could use row_factory, as in the example in the docs:

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

or follow the advice that's given right after this example in the docs:

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

If returning a tuple doesn't suffice and you want name-based access to columns, you should consider setting row_factory to the highly-optimized sqlite3.Row type. Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

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

回答by Ignacio Vazquez-Abrams

From PEP 249:

来自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.

So yes, do it yourself.

所以是的，自己做。

回答by bbengfort

Even using the sqlite3.Row class-- you still can't use string formatting in the form of:

即使使用 sqlite3.Row 类——您仍然不能使用以下形式的字符串格式：

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

In order to get past this, I use a helper function that takes the row and converts to a dictionary. I only use this when the dictionary object is preferable to the Row object (e.g. for things like string formatting where the Row object doesn't natively support the dictionary API as well). But use the Row object all other times.

为了解决这个问题，我使用了一个辅助函数来获取行并转换为字典。我只在字典对象比 Row 对象更可取时使用它（例如，对于字符串格式，Row 对象本身也不支持字典 API）。但在所有其他时间使用 Row 对象。

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

回答by andere

Or you could convert the sqlite3.Rows to a dictionary as follows. This will give a dictionary with a list for each row.

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

回答by Macabeus

A generic alternative, using just three lines

通用替代方案，仅使用三行

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

But if your query returns nothing, will result in error. In this case...

但是如果您的查询没有返回任何内容，则会导致错误。在这种情况下...

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

or

或者

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

回答by gandalf

I thought I answer this question even though the answer is partly mentioned in both Adam Schmideg's and Alex Martelli's answers. In order for others like me that have the same question, to find the answer easily.

我以为我回答了这个问题，尽管 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

回答by M. Utku ALTINKAYA

Shorter version:

较短的版本：

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

回答by Emrah Tuncel

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)

The result is definitely true, but I do not know the best.

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

回答by Ran Aroussi

Fastest on my tests:

在我的测试中最快：

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)

You decide :)

你决定 ：）

回答by Falko

Similar like before-mentioned solutions, but most compact:

类似于前面提到的解决方案，但最紧凑：

db.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) }