up vote 0 down vote favorite

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?

share
up vote 0 down vote accepted

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')
share
    
Pros/cons of this versus the proposed sql? The second solution is basically the same as the proposed one (except string format vs compounding)? Afaik cur.execute calls cur.mogrify if needed? – Paamand Aug 30 '16 at 20:58
up vote 0 down vote

[Suggested answer/workaround - better answers are appreciated!]

After some trial/error I got the following to work:

sql = "INSERT INTO table (" + ", ".join(dict_data.keys()) + ") VALUES (" + ", ".join(["%("+k+")s" for k in dict_data]) + ");"

This gives the sql string

"INSERT INTO table (k1, k2, ... , kn) VALUES (%(k1)s, %(k2)s, ... , %(kn)s);"

which may be executed by

with psycopg2.connect(database='deepenergy') as con:
    with con.cursor() as cur:
        cur.execute(sql, dict_data)

Post/cons?

share

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.