Insert Python list of dictionaries into PSQL database

Question

I have a list of dictionaries with the same keys (built in Python) which I would like to insert into a PSQL database. Key names coincide with the columns of the PSQL database. Could someone please suggest something to do it efficiently?

score 1 · Answer 1 · answered Jul 02 '18 at 07:23

1

You can use .executemany method. Example using psycopg2:

cursor.executemany(
    'INSERT INTO mytable (field_a, field_b, filed_c) '
    'VALUES (%(field_a)s, %(field_b)s, %(field_c)s)',
    data
)

data can be your list of dicts.

answered Jul 02 '18 at 07:23

nosklo

217,122
57
293
297

exactly what I was looking for, thank you very much ;) – Sonia Olaechea Lázaro Jul 02 '18 at 07:40

Sijan Bhandari · Answer 2 · 2018-07-02T07:54:23.730

1

You can use executemany in PSQL to insert multiple records at once as follows:

conn=psycopg2.connect("dbname='db_name' user='db_user' password='db_pass'")

data = [{"col1":"data11", "col2":"data21"},
            {"col1":"data12", "col2":"data22"},
            {"col1":"data13", "col2":"data23"}]


cur = conn.cursor()
cur.executemany("""INSERT INTO bar(col1,col2) VALUES (%(col1)s, %(col2)s)""", data)

If you do not have dict structure, you need to make sure that data is in correct sequences of your columns in your table as :

cur.executemany(
    """INSERT INTO bar(col1,col2) 
       VALUES (%s,%s)""", data)

and data should be format :

data = [['data11', 'data21'], ['data12', 'data22']]

Is that what you are trying to do? Let me know, it works.

edited Jul 02 '18 at 07:54

answered Jul 02 '18 at 07:24

Sijan Bhandari

2,941
3
23
36

Please mark the answer as correct and close the thread. I am glad it helps you out. – Sijan Bhandari Jul 02 '18 at 07:42
And what would be the syntaxis for PSQL if I just wanted to add values from a list (not a list of dictionaries)? – Sonia Olaechea Lázaro Jul 02 '18 at 07:42
Yes, I've marked it, but I don't have a 15 in reputation so it won't save my opinion :( – Sonia Olaechea Lázaro Jul 02 '18 at 07:45
I have updated my answer above. Just make sure your list of data has correct column sequences : `[[col1, col2], [col1, col2]]` – Sijan Bhandari Jul 02 '18 at 07:49
I have not executed the code above. Let me know if it raises any error. – Sijan Bhandari Jul 02 '18 at 07:55

aydow · Answer 3 · 2023-03-16T09:13:23.703

From the docs:

The current implementation of executemany() is (using an extremely charitable understatement) not particularly performing.

As per this thread, You should be using cursor.copy_from or extras.execute_values which perform single, bulk inserts.

copy_from

data = [{'col1': 1, 'col2': 2}, {'col1': 3, 'col2': 4}, {'col1': 5, 'col2':6}]
f = IteratorFile(("{0}\t{1}".format(k['col1'], k['col2']) for k in data.keys()))
cursor.copy_from(f, 'table_name', columns=('col1', 'col2'))

Please see here for a benchmark test compared to cursor.execute. The IteratorFile class can be found here. Alternatively, a more classic implementation can be done with StringIO.

data = [{'col1': 1, 'col2': 2}, {'col1': 3, 'col2': 4}, {'col1': 5, 'col2':6}]
f = io.StringIO([f"{row['col1']}\t{row['col2']}\n" for row in data])
f.seek(0)
cursor.copy_from(f, 'table_name', columns=('col1', 'col2'))

execute_values

data = [{'col1': 1, 'col2': 2}, {'col1': 3, 'col2': 4}, {'col1': 5, 'col2':6}]
insert_query = 'insert into table_name (a, b) values %s'
psycopg2.extras.execute_values(
    cursor, insert_query, [tuple(d) for d in data]
)

@baxx, it's here https://gist.github.com/jsheedy/ed81cdf18190183b3b7d. I've also added it to the answer. — aydow, Mar 16 '23 at 09:14
sequence in execute_values section should be [tuple(d.values()) for d in data] — Juan Rojas, Jun 21 '23 at 17:18

Insert Python list of dictionaries into PSQL database

3 Answers3