How can I get dict from sqlite query?

Question

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

With iteration, I get lists matching strings.

 for row in res: print row 

I can get the column name

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

But is there any function or setting for getting dictionaries instead of a list?

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

or should I do it myself?

Answer 1

You can 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 recommendations given immediately after this example in the docs:

If returning a tuple is not enough and you want access based on the name of the columns, you should consider setting row_factory for the highly optimized sqlite3.Row type. The row provides both index-based and case-insensitive access to columns with names with almost no memory overhead. It will probably be better than your own dictionary based user approach or even db_row based solution.

Answer 2

I thought I was answering this question, although the answer is partially mentioned in the answers of Adam Schmideg and Alex Martelli. For others like me to have the same question, it's easy to find the answer.

 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 

Answer 3

Even using the sqlite3.Row class, you still cannot use line formatting in the form:

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

To get past this, I use a helper function that takes a string and converts it to a dictionary. I use this only when the dictionary object is preferable to the Row object (for example, for things like formatting strings where the Row object does not support the dictionary API). But use the Row object all other times.

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

Answer 4

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 (eg 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.

Answer 5

Shorter version:

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

Answer 6

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

against:

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

Answer 7

Or you can convert sqlite3.Rows to a dictionary as follows. This will give a list dictionary for each line.

  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 8

Generic alternative using only 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 request returns nothing, it will lead to an 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))} 

Answer 9

 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.

Answer 10

Like the previous solutions, but the most compact:

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

Answer 11

Dictionaries in python provide random access to their elements. Thus, any dictionary with "names", although it can be informative on the one hand (in other words, what are the names of the fields), "undoes" the fields, which may be undesirable.

The best approach is to collect the names in a separate list, and then combine them with the results yourself, if necessary.

 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 that you specified in the query, not the names in the database. The exception is SELECT * FROM

If your only task is to get the results using the dictionary, then be sure to use conn.row_factory = sqlite3.Row (already indicated in another answer).

Answer 12

After connecting to SQLite: con = sqlite3.connect(.....) quite simple to execute:

 con.row_factory = sqlite3.Row 

Voila!