Making a generalised CSV COPY script in Python

[Sandy7771989]

Hi All,

I'm using the below Python script to COPY a CSV file to one of my PostgreSQL database table.The script below is working fine,But i'm thinking of making this script a generalised one,So i need your recommendations/suggestions on how to do this.

What the script do:

1) The script will search for a CSV file with name ufl.csv from a specific path and copy its content to a predefined table in PostgreSQL database.

2) Move the CSV file to a new destination once the COPY is done.

What i want to achieve:

1) instead of predefining a file name such as ufl.csv , want to take the file which is in the working folder (Or al files if possible).

2)I have predefined the table structure now (The CSV has 75 columns, also i can download the CSV files in 3 different formats each formats with different column numbers and names, I want to make it a generalised one so that no matter how many columns or what the column names, it should port the CSV data to a dynamically created PostgreSQL table.

Please find the below script,

import csv
import psycopg2
import time
import os
from datetime import datetime
import shutil
from time import gmtime, strftime

# File path.
filePath='''/Users/local/Downloads/ufl.csv'''
dirName = '/Users/local/Downloads/ufl_old_files/'

try:
  conn = psycopg2.connect(host="localhost", database="postgres", user="postgres",
                         password="postgres", port="5432")
  print('DB connected')

except (Exception, psycopg2.Error) as error:
        # Confirm unsuccessful connection and stop program execution.
        print ("Error while fetching data from PostgreSQL", error)
        print("Database connection unsuccessful.")
        quit()

# Check if the CSV file exists.
if os.path.isfile(filePath):
 try:
     print('Entered loop')   
     sql = "COPY %s FROM STDIN WITH DELIMITER AS ';'  csv header"
     file = open(filePath, "r" , encoding="latin-1")
     table = 'stage.ufl_details'# The table structure is already defined.

     with conn.cursor() as cur:
        cur.execute("truncate " + table + ";")
        print('truncated the table')
        cur.copy_expert(sql=sql % table, file=file)
        print('Data loaded')
        conn.commit()
        cur.close()
        conn.close()

 except (Exception, psycopg2.Error) as error:
        print ("Error while fetching data from PostgreSQL", error)
        print("Error adding  information.")
        quit()
#Move the processed CSV file to the new path after renaming it.    

 os.rename(filePath,dirName + 'ufl_old_'+ strftime("%Y_%m_%d", gmtime())+'.csv')

else:
    # Message stating CSV file could not be located.
    print("Could not locate the CSV file.")
    quit()

[Larz60+]

1) The script will search for a CSV file with name ufl.csv from a specific path and copy its content to a predefined table in PostgreSQL database.

Rather than searching for a specific file, use a generalized search method like:

# Larz60+ FindFile.py
#
from pathlib import Path
import os
import pathlib


class Filesearch():
    def __init__(self):
        # Anchor current filepath
        os.chdir(os.path.abspath(os.path.dirname(__file__)))

    def find_file(self, filename, fpath):
        fileloc = None
        if not isinstance(fpath, pathlib.PosixPath):
            fpath = Path(fpath)
        for file in fpath.glob('**/*'):
            if file.name == filename:
                fileloc = file
                break
        return fileloc


if __name__ == '__main__':
    fs = Filesearch()

[DeaD_EyE]

Changing the current working dir feels wrong.
Instead you can convert all paths into absolute paths.

# Path.home() is already absolute
Path.home() / 'testdir' / 'testfile.py'

If you have a relative path, you can convert it to an absolute path:

p = Path('.')
print(p.absolute())

• Using the method glob on relative paths, results into relative paths of matching files.
  
• Using the method glob on absolute paths, results into absolute paths of matching files.
  

[Larz60+]

Changing the current working dir feels wrong.

I have been anchoring my starting path to 'script resident' directory for years, and then using relative paths
for everything else with most all languages I have used, and never had a problem.
My reasoning is that I can then take a complete project, move it to any type of media, anywhere on the current machine, or anywhere within a network and still have access to to any file within the project without modification of code.
Perhaps you can also do this with absolute paths, but seems like whenever I did that, sooner or later it bit me, as code would have to changed to reflect the new physical environment.

Using the method glob on relative paths, results into relative paths of matching files.

I have to think about this a bit more, but if I always work with relative paths anyway, would this be an issue?