Making a generalised CSV COPY script in Python - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Making a generalised CSV COPY script in Python (/thread-19554.html) |
Making a generalised CSV COPY script in Python - Sandy7771989 - Jul-04-2019 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() RE: Making a generalised CSV COPY script in Python - Larz60+ - Jul-04-2019 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() RE: Making a generalised CSV COPY script in Python - DeaD_EyE - Jul-05-2019 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())
RE: Making a generalised CSV COPY script in Python - Larz60+ - Jul-05-2019 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? |