Executing SQL query with psycopg2

Question

I'm attempting to pass a list into a postgres table using psycopg2. I keep running into an exception:

  File "c:/Python27/Projects/Newsletter/newsletter.py", line 148, in <module>
insert_pg(listString)
  File "c:\Python27\Projects\Newsletter\pg.py", line 23, in insert_pg
    print('pggggg', error)
IOError: [Errno 0] Error

The data is pretty messy (forgive me), but here's a snippet of the code. I'm running it from newsletter.py:

if __name__ == '__main__':
dataList = [today, str(int(round(float(str(spxprice.replace(',', '')))))), str(int(round(float(spxchg)))), str(int(round(float(spxpchg)))), str(int(round(float(str(dowprice.replace(',', '')))))), dowpchg, str(int(round(float(dowchg)))), str(int(round(float(str(ndxprice.replace(',', '')))))), ndxpchg, str(int(round(float(ndxchg)))), ''.join(oilPrice[4]), ''.join(getOilChg), ''.join(getOilPct), dayName]

listString = ', '.join(dataList)

insert_pg(listString)

This is pg.py, where i'm importing insert_pg from:

import psycopg2
from config import config
import sys


def insert_pg(thedata):
    sql = ("""insert into prices values (%s);""" % thedata)

    conn = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.execute(sql)
        conn.commit()
        cur.close()
        print 'Success.'
    except (Exception, psycopg2.DatabaseError) as error:
        print('pggggg', error)
    finally:
        if conn is not None:
            conn.close()

The output of sql when I print:

insert into prices values (02/14/2018, 2675, 12, 0, 24698, 0.23, 58, 7074, 0.86, 60, 59.09, -0.06, -0.10%, Wednesday);

Not sure where i'm going wrong here. The database is connecting fine. Any ideas?

Answer 1

First off, you're not using bound variables which is bad practice as this can lead to SQL injection. What you should be doing is this:

cur.execute('INSERT INTO PRICES(col1, col2, ...) VALUES(%(val1)s, %(val2)s, ...)', kwargs)

where kwargs is a dictionary of key/value pairs corresponding to the column names and values. this is the correct way to do it.