Python Forum
Making a generalised CSV COPY script in Python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Making a generalised CSV COPY script in Python
#1
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()
Reply
#2
Quote: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()
Reply
#3
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.
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#4
Quote: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.

Quote: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?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Is there a *.bat DOS batch script to *.py Python Script converter? pstein 3 3,000 Jun-29-2023, 11:57 AM
Last Post: gologica
  making variables in my columns and rows in python kronhamilton 2 1,575 Oct-31-2021, 10:38 AM
Last Post: snippsat
Question Making a copy list in a function RuyCab 1 1,766 Jul-11-2021, 02:06 PM
Last Post: Yoriz
  Copy documents to Teams using python SallySmith 0 2,339 Mar-23-2021, 04:27 AM
Last Post: SallySmith
  How to kill a bash script running as root from a python script? jc_lafleur 4 5,784 Jun-26-2020, 10:50 PM
Last Post: jc_lafleur
  Looking for help on making a script [no idea where to start] Chietnemese 1 1,704 Jun-26-2020, 03:50 AM
Last Post: Larz60+
  crontab on RHEL7 not calling python script wrapped in shell script benthomson 1 2,247 May-28-2020, 05:27 PM
Last Post: micseydel
  To Copy text values to Excel using Python ksasi2k3 16 15,539 Dec-26-2019, 11:59 AM
Last Post: ashutoshdeodhar
  Copy data from different workbooks into Master sheet with Python Fatman003 0 2,184 Aug-27-2019, 07:36 AM
Last Post: Fatman003
  How do I copy files faster with python? steckinreinhart619 7 18,146 Jul-19-2019, 11:47 AM
Last Post: perfringo

Forum Jump:

User Panel Messages

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