Python Forum - Error when Inserting CSV file values into a postgreSQL using psycopg2

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

The error msg is;

Error:
Table data created successfully 0 ['100','2010-11-25','ATB','','1087100000858D99','RAMAN','CHE‌‌TTI','6WIL66','603‌42‌02799543','','KP‌4523‌,20201220'] 1 ['230','2011-10-28','KLI','025G','001035477423095B','MEHMOOD‌‌','HIDDY','63IC4Y'‌,'‌6035448301629','‌','Y‌L0152441','201‌90609'‌] Traceback (most recent call last): File "E:\Shared Folder Home\Python\Python3\postgressqlCSV.py", line 49, in <module> ) values %s''', [tuple(row)] psycopg2.ProgrammingError: column "VehicleNo" of relation "data" does not exist LINE 3: "VehicleNo", "DepartureDate", "DepartureCity"...

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.

#!/usr/bin/python3.5

import psycopg2, csv

#create a connection object
try:
    conn = psycopg2.connect("dbname='testdb' user='postgres' host='localhost' password='password'")
    print("connected")
except:
    print ("I am unable to connect to the database")

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

#using the cursor execute sql commands

cur.execute('''DROP TABLE IF EXISTS data''') 


cur.execute('''CREATE TABLE data
        (ID INT PRIMARY KEY        NOT NULL,
        VehiNo   INT       NOT NULL,
        DepartureDate  CHAR(50)   ,
        DepartureCity CHAR(50)   ,
        SeatNumber  CHAR(50)  ,
        UCI   CHAR(50) ,
        PAXSurname  CHAR(50)  ,
        FirstName   CHAR(50) ,
        PNRNumber   CHAR(50) ,
        Ticket   INT ,
        FQTVNumber   CHAR(50) ,
        PassportNo  CHAR(50)  ,
        PassportExpDate CHAR(50)
        );''')


print ("Table data created successfully")

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

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

    cur.execute('''
        INSERT INTO "data" (
            "VehiNo", "DepartureDate", "DepartureCity", "SeatNumber", "UCI", "PAXSurname", "FirstName", "PNRNumber", "Ticket", "FQTVNumber", "PassportNo", "PassportExpDate"
        ) values %s''', [tuple(row)]
    )
    conn.commit()
    cur.close()

sample.csv
VehiNo,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

(Oct-04-2016, 12:14 PM)klllmmm Wrote: [ -> ]

cur= conn.cursor()

for i, row in enumerate(reader):
    # ...
    conn.commit()
    cur.close()

You close the cursor inside the for loop. The second time through the loop, the cursor isn't available anymore. That might not actually be your issue (the error looks like the Vehicle ID isn't available or something), but that is definitely AN issue.

I know you can have multi-insert statements in sql server, mysql, and sqlite, but I don't know about oracle. That might be an easier way to do it (and also faster, since there'd only be a single insert/commit instead of one for each row). That would end up looking like this:

insert into Table_name (column1, column2, column3) values
(value1, value2, value3),
(another_value, another_value2, another_value3),
(once_more, unto_the, breach)

But all that said, the error you get doesn't really match what it looks like your code is doing, based off of your csv. Are there any rows in the file that don't have a vehicleno? You print each row in the for loop, at which point in the csv file, specifically, are you getting the error?

(Oct-04-2016, 03:05 PM)nilamo Wrote: [ -> ]
(Oct-04-2016, 12:14 PM)klllmmm Wrote: [ -> ]
cur= conn.cursor()

for i, row in enumerate(reader):
    # ...
    conn.commit()
    cur.close()
You close the cursor inside the for loop. The second time through the loop, the cursor isn't available anymore. That might not actually be your issue (the error looks like the Vehicle ID isn't available or something), but that is definitely AN issue.

I know you can have multi-insert statements in sql server, mysql, and sqlite, but I don't know about oracle. That might be an easier way to do it (and also faster, since there'd only be a single insert/commit instead of one for each row). That would end up looking like this:
insert into Table_name (column1, column2, column3) values
(value1, value2, value3),
(another_value, another_value2, another_value3),
(once_more, unto_the, breach)
But all that said, the error you get doesn't really match what it looks like your code is doing, based off of your csv. Are there any rows in the file that don't have a vehicleno? You print each row in the for loop, at which point in the csv file, specifically, are you getting the error?

@nilamo Thank you very much, As u point out i should take out conn.commit & cur.close from the for loop.
Also for loop has to be change as :

if i >= 0: continue

continue So now all the lines CSV file printed no error pops up, but it seems nothing is inserted into PostgreSQL data table, as nothing is printed when i run a select command.

(Oct-04-2016, 05:43 PM)klllmmm Wrote: [ -> ]@nilamo Thank you very much, As u point out i should take out conn.commit & cur.close from the for loop.
Also for loop has to be change as :
if i >= 0: continue
So now all the lines CSV file printed no error pops up, but it seems nothing is inserted into PostgreSQL data table, as nothing is printed when i run a select command.

Are you sure you want to skip the loop if the index is greater than 0? That means you're skipping every row except the first... which sounds like the opposite of what you want, which would explain why nothing is getting inserted.

(Oct-04-2016, 05:50 PM)nilamo Wrote: [ -> ]
(Oct-04-2016, 05:43 PM)klllmmm Wrote: [ -> ]@nilamo Thank you very much, As u point out i should take out conn.commit & cur.close from the for loop.
Also for loop has to be change as :
if i >= 0: continue
So now all the lines CSV file printed no error pops up, but it seems nothing is inserted into PostgreSQL data table, as nothing is printed when i run a select command.
Are you sure you want to skip the loop if the index is greater than 0? That means you're skipping every row except the first... which sounds like the opposite of what you want, which would explain why nothing is getting inserted.

Thanks, To skip the loop the index has to be lower/equal to 0. Then i have to find out what is the appropriate data types for Variables as to set up proper data table.
Currently i'm getting an error stating that Values relating to ticket no are out of range for type of 'Integer'. Ticket no consists of 13 numerical characters. What would be the appropriate data type for this?

https://www.postgresql.org/docs/9.5/stat...meric.html

Looks like you might want "bigint".

(Oct-04-2016, 07:17 PM)nilamo Wrote: [ -> ]https://www.postgresql.org/docs/9.5/stat...meric.html

Looks like you might want "bigint".

@nilamo Thank you very much for your so valuable comments. 'bigint' is what appropriate data type.
1.but how to handle empty or blank rows in ticket no field? What is the bestway. Do i need to handle at time reading the CSV by assigning some specific character if the field value is blank?
2. I would like make that the PostgreSQL table recognizes the departure date & passportExpDate as the "DATE" format. How to recognize '20180422' or similar values in passportExpDate as date format?

1. That depends, what do you want to happen when they're blank? Do you want to skip the row? Have a blank value in the db? Invent a random value?

2. Use a date or datetime type for the column in the db, instead of char. The db *might* be able to figure out how to format what you're giving it, but if it can't you'll have to parse it and format it in a way the db would understand (maybe dd/mm/yyyy, for example).

(Oct-05-2016, 02:38 PM)nilamo Wrote: [ -> ]1. That depends, what do you want to happen when they're blank? Do you want to skip the row? Have a blank value in the db? Invent a random value?

2. Use a date or datetime type for the column in the db, instead of char. The db *might* be able to figure out how to format what you're giving it, but if it can't you'll have to parse it and format it in a way the db would understand (maybe dd/mm/yyyy, for example).

1. Yes i want to skip the blank values. eg- if one field in a variable is blank i can't make that variable to a bigint data type, How to overcome this? I think i can assign null, n/a or something similar to blank fields at the time of loading the CSV. How can i do that?

2. If the value in the field is similar to 20201220, can i make it to a date format 2020-12-20 in the db.
Thanks