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

User · Answer

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

User · Answer

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

User · Answer

Shorter version   db row factory   lambda c  r  dict   col 0   r idx   for idx  col in enumerate c description

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

After you connect to SQLite  con   sqlite3 connect        it is sufficient to just run   con row factory   sqlite3 Row   Voila

[python] How can I get dict from sqlite query?

Examples related to python

Examples related to sql

Examples related to sqlite

Examples related to dictionary

Examples related to dataformat