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

Question

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.

Answer 1

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()

Answer 2

i tried your code and works fine

import psycopg2

conn = psycopg2.connect("host= 127.0.0.1 dbname=testdb user=postgres password=postgres")
print("Connecting to Database")
cur = conn.cursor()

'''cur.execute("""CREATE TABLE abstable1(
absid varchar(10) PRIMARY KEY,
name integer,
number integer 
)
""")'''

with open('lolo.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'abstable1', sep=',', columns=('absid', 'name', 'number'))

conn.commit()
conn.close()

although i had to make some changes for it to work: i had to name the table abstable1 because using abs.abstable1 postgres assumes that i'm using the schema abs, maybe you created that schema on your database if not check on that, also i'm using python 3.7 i noticed that you are using python 2.7(which i think is no longer supported), this may cause issues, since you say you are learning i would recommend that you use python 3 since it is more used now and you most likely encounter code written on it and you would have to be adapting your code to fit your python 2.7

Answer 3

I post my solution here based on @Rose answer.

I used sqlalchemy, a JSON file as config and glob.

import json
import glob
from sqlalchemy import create_engine, text


def create_tables_from_files(files_folder, engine, config):
    try:
        for filename in glob.glob(files_folder+"\*csv"):
            tablename = filename.replace(files_folder, "").replace('\\', "").replace(".csv", "")
            input_file = open(filename, "r")
            columns = input_file.readline().strip().split(",")
            create_query = 'DROP TABLE IF EXISTS ' + config["staging_schema"] + "." + tablename + "; \n"
            create_query +='CREATE TABLE ' + config["staging_schema"] + "." + tablename + " ( "

            for column in columns:
                create_query += column + " VARCHAR, \n "

            create_query = create_query[:-4]
            create_query += ");"

            engine.execute(text(create_query).execution_options(autocommit=True))
            print(tablename + " table created")
    except:
        print("Error at uploading tables")