postgresql - Psycopg2 insert python dictionary in postgres database -

in python 3+, want insert values dictionary (or pandas dataframe) database. have opted psycopg2 postgres database.

the problems cannot figure out proper way this. can concatenate sql string execute, psycopg2 documentation explicitly warns against this. ideally wanted this:

cur.execute("insert table values (%s);", dict_data) 

and hoped execute figure out keys of dict matches columns in table. did not work. examples of psycopg2 documentation got approach

cur.execute("insert table (" + ", ".join(dict_data.keys()) + ") values (" + ", ".join(["%s" pair in dict_data]) + ");", dict_data) 

from a

typeerror: 'dict' object not support indexing 

what phytonic way of inserting dictionary table matching column names?

two solutions:

d = {'k1': 'v1', 'k2': 'v2'}  insert = 'insert table (%s) values %s' l = [(c, v) c, v in d.items()] columns = ','.join([t[0] t in l]) values = tuple([t[1] t in l]) cursor = conn.cursor() print cursor.mogrify(insert, ([asis(columns)] + [values]))  keys = d.keys() columns = ','.join(keys) values = ','.join(['%({})s'.format(k) k in keys]) insert = 'insert table ({0}) values ({1})'.format(columns, values) print cursor.mogrify(insert, d) 

output:

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