Insert Python Dictionary using Psycopg2

Question

What is the best way to insert a Python dictionary with many keys into a Postgres database without having to enumerate all keys?

I would like to do something like...

song = dict() song['title'] = 'song 1' song['artist'] = 'artist 1' ...  cursor.execute('INSERT INTO song_table (song.keys()) VALUES (song)') 

Answer 1

from psycopg2.extensions import AsIs  song = {     'title': 'song 1',     'artist': 'artist 1' }  columns = song.keys() values = [song[column] for column in columns]  insert_statement = 'insert into song_table (%s) values %s'      # cursor.execute(insert_statement, (AsIs(','.join(columns)), tuple(values))) print cursor.mogrify(insert_statement, (AsIs(','.join(columns)), tuple(values))) 

Prints:

insert into song_table (artist,title) values ('artist 1', 'song 1') 

Psycopg adapts a tuple to a record and AsIs does what would be done by Python's string substitution.

Answer 2

You can also insert multiple rows using a dictionary. If you had the following:

namedict = ({"first_name":"Joshua", "last_name":"Drake"},             {"first_name":"Steven", "last_name":"Foo"},             {"first_name":"David", "last_name":"Bar"}) 

You could insert all three rows within the dictionary by using:

cur = conn.cursor() cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict) 

The cur.executemany statement will automatically iterate through the dictionary and execute the INSERT query for each row.

PS: This example is taken from here