Native way to insert a dict into SQLite with row factory, if possible with auto-growing number of columns

user_1422096 0
Viewed 376

As detailed in How can I get dict from sqlite query?, row_factory is helpful to get a dict when querying a SQLite database:

import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute('create table test (a text, b text, c int)')
conn.execute('insert into test values (?, ?, ?)', ('hello', 2, 17))
for r in conn.execute('select * from test'):
    print(dict(r))
# {'a': 'hello', 'b': '2', 'c': 17}

The result of the query is directly a dict, which is sometimes very useful.

Question: is it also possible, when using row_factory to easily INSERT directly a dict into the database?

Here is a partial solution:

d = {'a': 1, 'b': 2, 'c': 3}
conn.execute('insert into test ({}) values ({})'.format(','.join(d.keys()), ','.join(['?'] * len(d))), tuple(d.values()))

but it's not really "pythonic".

Is there a cleaner way to insert a dict directly into a SQLite DB, for example, by using row_factory?

Side-remark: it would be great that it would also "auto-grow" the table definition, i.e. if I insert d = {'newkey': 1, 'b': 2, 'c': 3}, it would automatically add a new column to the table.

dictionary sqlite python
1 Answers

For the reasons I mentioned in the comments, it probably isn't possible to do this "natively" without replacing the SQL interface with your own way of writing to the DB file, but it certainly is possible to make this code more "pythonic" as you mention:

def sqlify_list(string_iterable):
    """ ('a', 'b', 'c') -> '(a, b, c)' """
    return f'({",".join(string_iterable)})'

def insert_dict_into_table(conn, table_name, _dict):
    conn.execute(
        'insert into {table_name} {keys} values {replacement_fields}'.format(
            table_name=table_name,
            keys=sqlify_list(d), # dict.__iter__ yields the keys, so .keys() isn't necessary
            replacement_fields=sqlify_list('?'*len(d)) # str.__iter__ yields characters, so ','.join('?'*5) is equivalent to ','.join(['?']*5)
        ),
        d.values()
    )

d = {'a': 1, 'b': 2, 'c': 3}
insert_dict_into_table(conn, 'test', d)

As for

it would be great that it would also "auto-grow" the table definition

To my knowledge, relational databases aren't typically used for this sort of ad-hoc schema changing. Perhaps it would be simpler to store the dictionary in the database as JSON? Or perhaps switch to a schema-less DB like MongoDB?

user_114116860
Related