Jul-04-2019, 08:18 AM
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,
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()