在Python中制作广义的CSV复制脚本

Question

我正在使用以下python脚本将CSV文件复制到我的PostgreSQL数据库表中的一个。下面的脚本工作正常，但是我在想将此脚本成为广义的，所以我需要您的建议/建议。

脚本要做什么：

1(脚本将从特定路径中搜索带有名称ufl.csv的CSV文件，并将其内容复制到PostgreSQL数据库中的预定义表。

2(完成副本后，将CSV文件移至新目标。

我想实现的目标：

1(而不是预先定义诸如ufl.csv之类的文件名，而是要在工作文件夹中获取文件(或者如果可能的话(。

2(我现在已经预定了表结构(CSV有75列，也可以以3种不同格式下载CSV文件，每种格式使用不同的列号和名称，我想做它是一个广义的，以便无论列名是多少列或列名，都应将CSV数据移植到动态创建的PostgreSQL表中。

。

请找到以下脚本，

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.
if os.path.isfile(filePath):
 try:
     print('Entered loop')   
     #sql = "COPY %s FROM STDIN WITH DELIMITER AS ';'  csv header"
     sql = "COPY %s FROM PROGRAM 'cat /Users/local/Downloads/*' WITH DELIMITER AS ';'  csv header"
     file = open(filePath, "r" , encoding="latin-1")
     table = 'stage.ufl_details'

     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()

Answer 1

您可以使用FROM PROGRAM的COPY

选项

COPY tablename FROM PROGRAM 
     'cat /Users/local/Downloads/ufl_old_files/*'  WITH DELIMITER AS ';'  csv header