Error when Inserting CSV file values into a postgreSQL using psycopg2

Question

#!/usr/bin/python3.5


import psycopg2, csv

#create a connection object
conn = psycopg2.connect("dbname='testdb' user='postgres' host='localhost' password='password'")

#use cursor object to execute commands 
cur= conn.cursor()

reader = csv.reader(open('E:\\data\\sample.csv', 'r'))

for row in reader:
    print (row[1])
    
    cur.execute('INSERT INTO "data" ("VehicleNo", "DepartureDate", "DepartureCity", "SeatNumber", "UCI", "PAXSurname", "FirstName", "PNRNumber", "Ticket", "FQTVNumber", "PassportNo", "PassportExpDate")' +
                'VALUES %s', [(val1,val2,val3,val4,val5,val6,val7,val8,val9,val10,val11,val12)])
    
    conn.commit()
    cur.close()

Sample.csv
VehicleNo,DepartureDate,DepartureCity,SeatNumber,UCI,PAXSurname,FirstName,PNRNumber,Ticket,FQTVNumber,PassportNo,PassportExpDate
100,2010-11-25,ATB,,1087100000858D99,RAMAN,CHETTI,6WIL66,6034202799543,,KP4523,20201220
230,2011-10-28,KLI,025G,001035477423095B,MEHMOOD,HIDDY,63IC4Y,6035448301629,,YL0152441,20190609
270,2012-10-13,KWI,002K,20632703000E3281,ALMARRI,GALI,2UITWH,6039659907963,,K302216,20161020
502,2015-12-03,ADB,026B,200235B3000C4633,HONGI,XYIUE,4S63HA,6035853329241,,DL0007453,20171020

I'm newbie, & I'm using python 3.5 I want to insert the data in a CSV file into a PostgreSQL table using psycopg2.

Traceback (most recent call last): File "E:\Shared Folder Home\Python\Python3\postgressqlCSV.py", line 23, in 'VALUES %s', [(val1,val2,val3,val4,val5,val6,val7,val8,val9,val10,val11,val12)]) NameError: name 'val1' is not defined

I'm getting above error when I used the python code shown below

Can someone help me to solve this issue. I have inserted a sample of my CSV file. Thanks you very much for your time & effort.

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2016-10-02T18:46:44

The message is clear. You do not define val1. Turn the list of strings in each row into a tuple. Use the Psycopg2's execute method parameter passing. You do not want the first headers row.

for i, row in enumerate(reader): print (i,row) if i == 0: continue

cursor.execute('''
    INSERT INTO data (
        VehicleNo, DepartureDate, DepartureCity, SeatNumber, UCI,
        PAXSurname, FirstName, PNRNumber, Ticket, FQTVNumber,
        PassportNo, PassportExpDate
    ) values %s''', [tuple(row)]
)

BTW double quoting identifiers is a bad idea.

Error when Inserting CSV file values into a postgreSQL using psycopg2

1 Answers