Create Postgresql table from csv file using psycopg2

Question

Hi I looking for solutions that let me create table from csv files. I find solution on another forum. This code looks like this:

import csv
import psycopg2
import os
import glob


conn = psycopg2.connect("host= localhost port=5433  dbname=testDB user= postgres password= ************")
print("Connecting to Database")

cur = conn.cursor()
csvPath = "W:\Maciej.Olech\structure_files"

# Loop through each CSV
for filename in glob.glob(csvPath+"*.csv"):
# Create a table name
    tablename = filename.replace("W:\Maciej.Olech\structure_files", "").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.execute(sqlQueryCreate)
conn.commit()
cur.close()

I try to run this code but i get this error:

C:\Users\MACIEJ~1.OLE\AppData\Local\Temp/ipykernel_5320/1273240169.py in <module>
     40         sqlQueryCreate += ");"
     41 
---> 42 cur.execute(sqlQueryCreate)
     43 conn.commit()
     44 cur.close()

NameError: name 'sqlQueryCreate' is not defined

I don't understand why i have this error becouse sqlQueryCreate is defined. Any one have idea what is wrong? Thanks for any help.

Also, in Windows, you need to use \\ as a path separator. i.e. "W:\\Jan.Bree\\structure_files" Also, if there are no csv files, you will get that error as sqlQueryCreate is defined inside the for loop. If there are more than one file, you're going to process the last file. — ewokx
– ewokx, Commented May 5, 2022 at 7:40

ewokx · Accepted Answer · 2022-05-05 08:19:43Z

1

There are a few issues with your code.

In Windows, paths need to have the \ escaped.
your cur.execute(sqlQueryCreate) and conn.commit() are indented wrong. ditto with sqlQueryCreate = sqlQueryCreate[:-2] and sqlQueryCreate += ");"
Edit: Realized that your glob.glob() parameter isn't correct. What you intend: W:\\Jan.Bree\\structure_files\\*.csv, what you actually had W:\\Jan.Bree\\structure_files*.csv

import csv
import psycopg2
import os
import glob


conn = psycopg2.connect("host= localhost port=5433  dbname=testDB user= postgres password= ************")
print("Connecting to Database")

cur = conn.cursor()
csvPath = "W:\\Jan.Bree\\structure_files"

# Loop through each CSV
for filename in glob.glob(os.path.join(csvPath,"*.csv")):
# Create a table name
    tablename = filename.replace("W:\\Jan.Bree\\structure_files", "").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.execute(sqlQueryCreate)
    conn.commit()

cur.close()

This should cover the issues; but I have no way of testing the code as I don't use psycopg2. I'm assuming that the connect() works.

edited May 5, 2022 at 8:19

answered May 5, 2022 at 7:46

ewokx

2,4413 gold badges19 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

maciej.o Over a year ago

I dont get the error no more but code dont make any table. It dont print any tablename.

ewokx Over a year ago

Then make sure there are files in your path.

maciej.o Over a year ago

I checked everything ten times, path is correct and files are in path. I also copy file to another path but still nothing hapend.

ewokx Over a year ago

My bad for not being careful. Your glob.glob path wasn't right.

Wojciech Moszczyński · Accepted Answer · 2022-08-18 06:13:44Z

1

You do too much work, there is the Alchemy library and things are done quickly and painlessly.

import pandas as pd
from sqlalchemy import create_engine
import psycopg2

df = pd.read_csv('W:\Maciej.Olech\structure_files.csv', sep=',')



##> {dialect}+{driver}://{user}:{password}@{host}:{port}/{database}
# database = bazakot22
# user = foka2
# password = #gg3Ewampkl
# host = 127.0.0.1
# port= 5432


engine = create_engine('postgresql://foka2:#[email protected]:5432/bazakot22')

df.to_sql("table_name4", engine)

answered Aug 18, 2022 at 6:13

Wojciech Moszczyński

3,21525 silver badges28 bronze badges

Collectives™ on Stack Overflow

Create Postgresql table from csv file using psycopg2

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related