问题：如何从sqlite查询中获取字典？
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'}
还是我必须自己做？
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?
回答 0
您可以使用row_factory，如docs中的示例所示：
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的解决方案都要好。
You could use row_factory, as in the example in the docs:
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.
回答 1
我以为我已经回答了这个问题，即使亚当·施密德（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
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.
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
回答 2
即使使用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))
Even using the sqlite3.Row class– you still can’t use string formatting in the form of:
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.
def dict_from_row(row):
return dict(zip(row.keys(), row))
回答 3
连接到SQLite之后：
con = sqlite3.connect(.....)只需运行即可：
con.row_factory = sqlite3.Row
瞧！
After you connect to SQLite:
con = sqlite3.connect(.....) it is sufficient to just run:
con.row_factory = sqlite3.Row
Voila!
回答 4
从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.
所以是的，你自己做。
From 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.
回答 5
较短的版本：
db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
Shorter version:
db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
回答 6
在我的测试中最快：
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)
你决定 ：）
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 :)
回答 7
与上述解决方案类似，但最紧凑：
db.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) }
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) }
回答 8
正如@gandalf的答案所提到的，必须使用conn.row_factory = sqlite3.Row，但是结果不是直接的字典。必须dict在上一个循环中添加一个附加的“ cast” ：
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute('create table t (a text, b text, c text)')
conn.execute('insert into t values ("aaa", "bbb", "ccc")')
conn.execute('insert into t values ("AAA", "BBB", "CCC")')
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from t')
for r in c.fetchall():
print(dict(r))
# {'a': 'aaa', 'b': 'bbb', 'c': 'ccc'}
# {'a': 'AAA', 'b': 'BBB', 'c': 'CCC'}
As mentioned by @gandalf’s answer, one has to use conn.row_factory = sqlite3.Row, but the results are not directly dictionaries. One has to add an additional “cast” to dict in the last loop:
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute('create table t (a text, b text, c text)')
conn.execute('insert into t values ("aaa", "bbb", "ccc")')
conn.execute('insert into t values ("AAA", "BBB", "CCC")')
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from t')
for r in c.fetchall():
print(dict(r))
# {'a': 'aaa', 'b': 'bbb', 'c': 'ccc'}
# {'a': 'AAA', 'b': 'BBB', 'c': 'CCC'}
回答 9
我认为您在正确的轨道上。让我们保持非常简单并完成您要执行的操作：
import sqlite3
db = sqlite3.connect("test.sqlite3")
cur = db.cursor()
res = cur.execute("select * from table").fetchall()
data = dict(zip([c[0] for c in cur.description], res[0]))
print(data)
缺点是.fetchall()，这是您消耗内存的谋杀手段，如果表很大。但是对于仅处理数千行文本和数字列的琐碎应用程序而言，这种简单的方法就足够了。
对于严重的问题，您应该按照其他许多答案中的建议研究行工厂。
I think you were on the right track. Let’s keep this very simple and complete what you were trying to do:
import sqlite3
db = sqlite3.connect("test.sqlite3")
cur = db.cursor()
res = cur.execute("select * from table").fetchall()
data = dict(zip([c[0] for c in cur.description], res[0]))
print(data)
The downside is that .fetchall(), which is murder on your memory consumption, if your table is very large. But for trivial applications dealing with mere few thousands of rows of text and numeric columns, this simple approach is good enough.
For serious stuff, you should look into row factories, as proposed in many other answers.
回答 10
或者，您可以按以下方式将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
Or you could convert the sqlite3.Rows to a dictionary as follows. This will give a dictionary with a list for each row.
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
回答 11
通用替代方案，仅使用三行
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))}
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))}
回答 12
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)
结果肯定是正确的，但我不知道最好的。
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.
回答 13
python中的字典提供对元素的任意访问。因此，任何带有“名称”的词典，尽管一方面可能会提供更多信息（又称字段名称），却会使字段“无序”，这可能是不必要的。
最好的方法是将名称放在单独的列表中，然后根据需要自己将其与结果组合。
try:
mycursor = self.memconn.cursor()
mycursor.execute('''SELECT * FROM maintbl;''')
#first get the names, because they will be lost after retrieval of rows
names = list(map(lambda x: x[0], mycursor.description))
manyrows = mycursor.fetchall()
return manyrows, names
还请记住，在所有方法中，名称都是您在查询中提供的名称，而不是数据库中的名称。exceptions是SELECT * FROM
如果您唯一关心的是使用字典来获得结果，则一定要使用conn.row_factory = sqlite3.Row（已经在另一个答案中说明了）。
Dictionaries in python provide arbitrary access to their elements.
So any dictionary with “names” although it might be informative on one hand (a.k.a. what are the field names) “un-orders” the fields, which might be unwanted.
Best approach is to get the names in a separate list and then combine them with the results by yourself, if needed.
try:
mycursor = self.memconn.cursor()
mycursor.execute('''SELECT * FROM maintbl;''')
#first get the names, because they will be lost after retrieval of rows
names = list(map(lambda x: x[0], mycursor.description))
manyrows = mycursor.fetchall()
return manyrows, names
Also remember that the names, in all approaches, are the names you provided in the query, not the names in database. Exception is the SELECT * FROM
If your only concern is to get the results using a dictionary, then definitely use the conn.row_factory = sqlite3.Row (already stated in another answer).
