Psycopg2 insert python dictionary in postgres database

Question

In python 3+, I want to insert values from a dictionary (or pandas dataframe) into a database. I have opted for psycopg2 with a postgres database.

The problems is that I cannot figure out the proper way to do this. I can easily concatenate a SQL string to execute, but the psycopg2 documentation explicitly warns against this. Ideally I wanted to do something like this:

cur.execute("INSERT INTO table VALUES (%s);", dict_data)

and hoped that the execute could figure out that the keys of the dict matches the columns in the table. This did not work. From the examples of the psycopg2 documentation I got to this approach

cur.execute("INSERT INTO table (" + ", ".join(dict_data.keys()) + ") VALUES (" + ", ".join(["%s" for pair in dict_data]) + ");", dict_data)

from which I get a

TypeError: 'dict' object does not support indexing

What is the most phytonic way of inserting a dictionary into a table with matching column names?

Answer 1

Two solutions:

d = {'k1': 'v1', 'k2': 'v2'}

insert = 'insert into table (%s) values %s'
l = [(c, v) for c, v in d.items()]
columns = ','.join([t[0] for t in l])
values = tuple([t[1] for t in l])
cursor = conn.cursor()
print cursor.mogrify(insert, ([AsIs(columns)] + [values]))

keys = d.keys()
columns = ','.join(keys)
values = ','.join(['%({})s'.format(k) for k in keys])
insert = 'insert into table ({0}) values ({1})'.format(columns, values)
print cursor.mogrify(insert, d)

Output:

insert into table (k2,k1) values ('v2', 'v1')
insert into table (k2,k1) values ('v2','v1')

Answer 2

I sometimes run into this issue, especially with respect to JSON data, which I naturally want to deal with as a dict. Very similar. . .But maybe a little more readable?

def do_insert(rec: dict):
    cols = rec.keys()
    cols_str = ','.join(cols)
    vals = [ rec[k] for k in cols ]
    vals_str = ','.join( ['%s' for i in range(len(vals))] ) 
    sql_str = """INSERT INTO some_table ({}) VALUES ({})""".format(cols_str, vals_str)
    cur.execute(sql_str, vals)

I typically call this type of thing from inside an iterator, and usually wrapped in a try/except. Either the cursor (cur) is already defined in an outer scope or one can amend the function signature and pass a cursor instance in. I rarely insert just a single row. . .And like the other solutions, this allows for missing cols/values provided the underlying schema allows for it too. As long as the dict underlying the keys view is not modified as the insert is taking place, there's no need to specify keys by name as the values will be ordered as they are in the keys view.