Python Forum
Error when Inserting CSV file values into a postgreSQL using psycopg2
Thread Rating:
  • 3 Vote(s) - 3.67 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error when Inserting CSV file values into a postgreSQL using psycopg2
#1
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
Reply


Messages In This Thread
Error when Inserting CSV file values into a postgreSQL using psycopg2 - by klllmmm - Oct-04-2016, 12:14 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  How to Connect to PostgreSQL Through Jump Server and SSH Tunnel using Python? nishans 1 1,319 Jan-02-2024, 10:37 AM
Last Post: khanzain
  Trying to use 2 values from excel in my script but getting error.. cubangt 3 1,776 May-11-2022, 07:12 AM
Last Post: normanwolf
  Modify values in XML file by data from text file (without parsing) Paqqno 2 1,858 Apr-13-2022, 06:02 AM
Last Post: Paqqno
  Overwrite values in XML file with values from another XML file Paqqno 5 3,572 Apr-01-2022, 11:33 PM
Last Post: Larz60+
  How to split file by same values from column from imported CSV file? Paqqno 5 3,002 Mar-24-2022, 05:25 PM
Last Post: Paqqno
  How psycopg2 autocommit works ? johntay 3 10,994 Oct-08-2021, 11:22 AM
Last Post: Larz60+
  Psycopg2 doesn't work with python2 MedianykEugene 3 3,102 Aug-10-2021, 07:00 AM
Last Post: ndc85430
  Problems with inserting images into an Excel File FightingFarmer 2 3,523 May-12-2021, 10:03 PM
Last Post: FightingFarmer
  Printing x values from an csv file hobbyist 7 4,210 Mar-10-2021, 02:00 PM
Last Post: hobbyist
  Argparse error when inputting values tqader 2 3,002 Sep-11-2020, 07:42 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020