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?

Clodoaldo Neto · Accepted Answer · 2016-08-29 19:17:31Z

13

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')

edited Aug 29, 2016 at 19:17

answered Aug 29, 2016 at 14:19

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paamand Over a year ago

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?

yoni keren Over a year ago

It has been a while, but I think that this code is vulnerable to an SQL injection attack.

rosey99 · Accepted Answer · 2019-06-14 00:06:50Z

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.

Paamand · Accepted Answer · 2016-08-29 09:39:53Z

0

[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?

answered Aug 29, 2016 at 9:39

Paamand

7361 gold badge6 silver badges18 bronze badges

1 Comment

JarochoEngineer Over a year ago

Not sure why I am getting the following error:(psycopg2.errors.SyntaxError) syntax error at or near "%" LINE 1: ...1, response, duration_time) VALUES (%(id)s, %(... The sql insert statement is correctly formed: INSERT INTO table (id, name, response, duration_time) VALUES (%(id)s, %(name)s, %(response)s, %(duration_time)s); However, when I execute the SQL statement cursor.execute(sql_insert, data_dict) By any chance do you know what is the problem?

lily.z · Accepted Answer · 2020-05-04 14:35:37Z

0

using %(name)s placeholders may solve the problem:

dict_data = {'key1':val1, 'key2':val2}

cur.execute("""INSERT INTO table (field1, field2) 
VALUES (%(key1)s, %(key2)s);""", 
dict_data)

you can find the usage in psycopg2 doc Passing parameters to SQL queries

answered May 4, 2020 at 14:35

lily.z

1

Comments

Ryan Aquino · Accepted Answer · 2020-12-30 03:40:11Z

Here is another solution inserting a dictionary directly

Product Model (has the following database columns)

name
description
price
image
digital - (defaults to False)
quantity
created_at - (defaults to current date)

Solution:

    data = {
        "name": "product_name",
        "description": "product_description",
        "price": 1,
        "image": "https",
        "quantity": 2,
    }
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO products (name,description,price,image,quantity) "
        "VALUES(%(name)s, %(description)s, %(price)s, %(image)s, %(quantity)s)", data
    )

    conn.commit()
    conn.close()

Note: The columns to be inserted is specified on the execute statement .. INTO products (column names to be filled) VALUES ..., data <- the dictionary (should be the same **ORDER** of keys)

Collectives™ on Stack Overflow

Psycopg2 insert python dictionary in postgres database

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related