insert dictionaries as rows in sqlite table

Question

I have dictionaries like this:

{'id': 8, 'name': 'xyzzy', 'done': False}

the table is already created with the correct column names (keys of the dictionary). How can I insert the values in the respective columns? I want to create a new row for each dictionary.

Note that for 'done' the type defined is originally Integer since sqlite does not offer bool type.

cur = connection().cursor()

query = "insert .... tablename"

Answer 1

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

Answer 2

You could use .format() method to insert into a query string however this is much more straightforward.

dic = {'id': 8, 'name': 'xyzzy', 'done': False}

cur.execute("INSERT INTO tablename VALUES (:id,:name,:done)",{"id" : dic["id"],"name" : dic["name"],"done" : dic["done"]})