How to insert a dictionary into Postgresql Table with Pscycopg2

Question

How do I insert a python dictionary into a Postgresql2 table? I keep getting the following error, so my query is not formatted correctly:

Error syntax error at or near "To" LINE 1: INSERT INTO bill_summary VALUES(To designate the facility of...

import psycopg2
import json
import psycopg2.extras
import sys
with open('data.json', 'r') as f:
        data = json.load(f)
con = None
try:
    con = psycopg2.connect(database='sanctionsdb', user='dbuser') 
    cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("CREATE TABLE bill_summary(title VARCHAR PRIMARY KEY, summary_text VARCHAR, action_date VARCHAR, action_desc VARCHAR)")
    for d in data:
        action_date = d['action-date']
        title = d['title']
        summary_text = d['summary-text']
        action_date = d['action-date']
        action_desc = d['action-desc']
        q = "INSERT INTO bill_summary VALUES(" +str(title)+str(summary_text)+str(action_date)+str(action_desc)+")"
        cur.execute(q)
    con.commit()

except psycopg2.DatabaseError, e:

    if con:
        con.rollback()

    print 'Error %s' % e    
    sys.exit(1)
finally:

    if con:
        con.close()

Answer 1

You should use the dictionary as the second parameter to cursor.execute(). See the example code after this statement in the documentation:

Named arguments are supported too using %(name)s placeholders in the query and specifying the values into a mapping.

So your code may be as simple as this:

with open('data.json', 'r') as f:
    data = json.load(f)
    
print(data)

""" above prints something like this:

{'title': 'the first action', 'summary-text': 'some summary', 'action-date': '2018-08-08', 'action-desc': 'action description'}

use the json keys as named parameters:
"""

cur = con.cursor()
q = "INSERT INTO bill_summary VALUES(%(title)s, %(summary-text)s, %(action-date)s, %(action-desc)s)"
cur.execute(q, data)
con.commit()

Note also this warning (from the same page of the documentation):

Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.