[FIXED] how to automatically create table based on CSV into postgres using python

Issue

I am a new Python programmer and trying to import a sample CSV file into my Postgres database using python script.
I have CSV file with name abstable1 it has 3 headers:

absid, name, number I have many such files in a folder I want to create a table into PostgreSQL with the same name as the CSV file for all.

Here is the code which I tried to just create a table for one file to test:

import psycopg2
import csv
import os

#filePath = 'c:\\Python27\\Scripts\\abstable1.csv'
conn = psycopg2.connect("host= hostnamexx dbname=dbnamexx user= usernamexx password= pwdxx")
print("Connecting to Database")
cur = conn.cursor()

#Uncomment to execute the code below to create a table
cur.execute("""CREATE TABLE abs.abstable1(
absid varchar(10) PRIMARY KEY,
name integer,
number integer 
)
 """)
#to copy the csv data into created table
with open('abstable1.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'abs.abstable1', sep=',')
conn.commit()
conn.close()

This is the error that I am getting:

File "c:\Python27\Scripts\testabs.py", line 26, in <module>
    cur.copy_from(f, 'abs.abstable1', sep=',')
psycopg2.errors.QueryCanceled: COPY from stdin failed: error in .read() call: exceptions.ValueError Mixing iteration and read methods would lose data
CONTEXT:  COPY abstable1, line 1

Any recommendation or alternate solution to resolve this issue is highly appreciated.

Solution

Here's what worked for me by: import glob

This code automatically reads all CSV files in a folder and Creates a table with Same name as of the file. Although I'm still trying to figure out how to extract specific datatypes according to the data in CSV. But as far as table creation is concerned, this works like a charm for all CSV files in a folder.

import csv
import psycopg2
import os
import glob


conn = psycopg2.connect("host= hostnamexx dbname=dbnamexx user= usernamexx password= 
pwdxx")
print("Connecting to Database")

csvPath = "./TestDataLGA/"

# Loop through each CSV
for filename in glob.glob(csvPath+"*.csv"):
# Create a table name
tablename = filename.replace("./TestDataLGA\\", "").replace(".csv", "")
print tablename

# Open file
fileInput = open(filename, "r")

# Extract first line of file
firstLine = fileInput.readline().strip()


# Split columns into an array [...]
columns = firstLine.split(",")
     

# Build SQL code to drop table if exists and create table
sqlQueryCreate = 'DROP TABLE IF EXISTS '+ tablename + ";\n"
sqlQueryCreate += 'CREATE TABLE'+ tablename + "("

#some loop or function according to your requiremennt
# Define columns for table
for column in columns:
    sqlQueryCreate += column + " VARCHAR(64),\n"

sqlQueryCreate = sqlQueryCreate[:-2]
sqlQueryCreate += ");"

cur = conn.cursor()
cur.execute(sqlQueryCreate)
conn.commit()
cur.close()

Answered By - Rose

Answer Checked By - David Goodson (PHPFixing Volunteer)