How to Get Dict from SQLite Query

How can I get dict from sqlite query?

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

Sqlite and Python -- return a dictionary using fetchone()?

The way I've done this in the past:

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

Then you set it up in your connection:

from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect(...)
conn.row_factory = dict_factory

This works under pysqlite-2.4.1 and python 2.5.4.

SQLite Database to Python Dictionary using SQL SELECT statement

For this you need to group by WorkList only and use conditional aggregation:

SELECT WorkList, 
       SUM(RecordStatus = 'Open') AS Open,
       SUM(RecordStatus = 'Closed') AS Closed,
       SUM(RecordStatus = 'Hold') AS Hold
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList;

Get dictionary from sqlite query when using python databases module

import databases

pathtodb = '/path/to/your/database.db'
tablename = 'table_name_in_your_database'

database = databases.Database('sqlite:///{}'.format(pathtodb))
query1 = "PRAGMA table_info('{}')".format(tablename)
pragma = await database.fetch_all(query=query1)
query2 = "SELECT * FROM {}".format(tablename)
data = await database.fetch_all(query=query2)

dictionary_keys = list(zip(*pragma))[1]
dictionary_values = list(zip(*data))

dictionary = dict(zip(dictionary_keys,dictionary_values))

insert dictionaries as rows in sqlite table

In Python, database cursors accept two parameters:

1. an SQL statement as a string: the statement may contain placeholders instead of some values to handle cases where the values are not known until runtime.
2. a collection of values to be inserted into the SQL statement. These values replace the placeholders in the SQL statement when it is executed.

Placeholders may be positional or named:

# Positional placeholders: the order of values should match the order of 
# placeholders in the statement.  Values should be contained with
# a tuple or list, even if there is only one.
cur.execute("""SELECT * FROM tbl WHERE name = ? AND age = ?""", ('Alice', 42))

# Named placeholders: values and placeholders are matched by name, order 
# is irrelevant.  Values must be contained within a mapping (dict) of
# placeholders to values.
cur.execute(
    """SELECT * FROM tbl WHERE name = :name AND age = :age""", 
    {'age': 42, 'name': 'Alice'}
)

You can dictionary to cursor execute and it will do the right thing as long as the values placeholders in the SQL statement used the :named format (that is, the dict key prefixed by a colon ":").

conn = sqlite3.connect()
cur = conn.cursor()
stmt = """INSERT INTO mytable (id, name, done) VALUES (:id, :name, :done)"""
cur.execute(stmt, {'id': 8, 'name': 'xyzzy', 'done': False})
# Call commit() on the connection to "save" the data.
conn.commit()

This method ensures that values are correctly quoted before being inserted into the database and protects against SQL injection attacks.

See also the docs

Sqlite query to python dictionary

You are creating your dictionary incorrectly. Use:

for row in r:
    summary = dict(zip(columns, row))
    results.append(summary)

instead.

Your code sets the whole row sequence as the value for each key in Summary, instead of the individual column value, then appending that same dictionary to the results list for each column key..

How to build a list of dicts from a SQLite3 database query in Python?

Try this:

import sqlite3
cur = sqlite3.connect('filename.db').cursor()
query = cur.execute('SELECT * FROM A_TABLE')
colname = [ d[0] for d in query.description ]
result_list = [ dict(zip(colname, r)) for r in query.fetchall() ]
cur.close()
cur.connection.close()
print result_list

how to get multiple dictionaries from sqlite query with python

The problem I see is that you are running row.fetchone(), witch will get only one record of the query. The query will return two rows with the same data, EXCEPT that each line will have a different tag info:

1|Python is the language i love|do you love python?|1||1|Python|eslam|Eslam|Mostafa||||1|1
1|Python is the language i love|do you love python?|1||2|Linux|eslam|Eslam|Mostafa||||1|2

You will need to parse them manually or use two queries, one to get all tags of the question and the other to get the info.

---

Related Topics