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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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.
Here is the code for this second solution:
con = sqlite3.connect(…) con.row_factory = sqlite3.Row # add this row cursor = con.cursor()
Method 2
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
Method 3
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))
Method 4
After you connect to SQLite:
con = sqlite3.connect(.....) it is sufficient to just run:
con.row_factory = sqlite3.Row
Voila!
Method 5
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.
Method 6
Shorter version:
db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
Method 7
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 🙂
Method 8
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) }
Method 9
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'}
Method 10
Get the results of the query
output_obj = con.execute(query) results = output_obj.fetchall()
Option 1) Explicit Loop w/ Zip
for row in results:
col_names = [tup[0] for tup in output_obj.description]
row_values = [i for i in row]
row_as_dict = dict(zip(col_names,row_values))
Option 2) Faster Loop w/ Dict Comp
for row in results:
row_as_dict = {output_obj.description[i][0]:row[i] for i in range(len(row))}
Method 11
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.
Method 12
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
Method 13
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))}
Method 14
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.
Method 15
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).
Method 16
def getUsers(self,assoc=False):
result = self.cursor.execute("SELECT * FROM users").fetchall()
result_len = len(result)
if(result_len == False): return
if(assoc != True):
return result
else:
result_formated = []
columns = <div class="su-column su-column-size-1-2"><div class="su-column-inner su-u-clearfix su-u-trim"></div></div> for column in self.cursor.description]
for row in result:
row_dict = {}
i = 0
# print(result_len)
while(i <= result_len):
row_dict[columns[i]] = row[i]
i += 1
result_formated.append(row_dict)
return result_formated
i’ll just leave my bad code here
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0