在Python中生成通用CSV拷贝脚本

Question

我正在使用下面的Python脚本将CSV文件复制到我的一个PostgreSQL数据库表中。下面的脚本工作正常，但我正在考虑将此脚本制作成一个通用脚本，因此我需要您关于如何执行此操作的建议

脚本的作用：

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

2）复制完成后，将CSV文件移动到新目标

我想要达到的目标：

1）不要预先定义文件名（如ufl.csv），而要获取工作文件夹中的文件（或al文件，如果可能）

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

您可以使用COPY的FROM PROGRAM选项

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