Блог

У меня есть приведенный ниже запрос для импорта списка файлов CSV в базу данных PostgreSQL.Но при выполнении инструкций insert возникает некоторая ошибка.

Есть идеи или предложения о том, как ее устранить? В папке содержится множество файлов CSV с одинаковым форматом и с разными форматами, я хотел бы импортировать файлы того же формата в базу данных PostgreSQL и передать несоответствующие.

     import csv
    import psycopg2
    import os
    from glob import glob
    
    conn = psycopg2.connect(host="localhost", database="postgres", user="postgres",password="postgres", port="5432")
    cur = conn.cursor()
    
    DIRECTORY_PATH = "/Users/user/downloads/reports/newsletter_1/merged_newsletter_1"
    
    EXT = "*.csv"
    all_csv_files = [file
                     for path, subdir, files in os.walk(DIRECTORY_PATH)
                     for file in glob(os.path.join(path, EXT))]
    for i in all_csv_files: 
        filePath=i 
        print("CSV FILE - "  filePath,"n")
      
        with open('' filePath '', 'r') as f:
            reader = csv.reader(f)
            
            next(reader) # Skip the header row.
            for row in reader:               
                row_0= row[0]
                row_1= row[1] 
                row_2= row[2] 
                row_3= row[3] 
                row_4= row[4] 
                row_5= row[5] 
                row_6= row[6] 
                row_7= row[7] 
                row_8= row[8] 
                row_9= row[9] 
                row_10= row[10] 
                row_11 =  row[11] 
                row_12= row[12] 
                row_13= row[13]
                row_14= row[14]
                row_15= row[15] 
                row_16= row[16] 
                row_17= row[17] 
                row_18= row[18] 
                row_19= row[19] 
                row_20= row[20] 
                row_21= row[21] 
                row_22= row[22] 
                row_23= row[23] 
                row_24= row[24] 
                row_25 =  row[25]
           
                     #insert_table="""INSERT INTO dl_get_response.weekly_news_letter_all_status ( replace_campaign_id, campaign_name, message_id, message_name, message_subject, send_date, total_emails_sent, total_emails_delivered, replace_emails_sent, emails_delivered, delivered_rate, emails_opened, open_rate, unique_opens, unique_open_rate, links_clicked, click_rate, unique_links_clicked, unique_click_rate, bounces, bounce_rate, unsubscribed, unsubscribe_rate, spam_complaints, user_unknown, user_unknown_rate)
                                 #SELECT """  ''  row_0  '' ",'"    row_1   '' "','"   row_2  "','"    row_3  "','"   row_4  "','"  row_5  "','"   row_6  "','"   row_7  "','"   row_8 "','"    row_9  "','"   row_10  "','"    row_11  "','"   row_12  "','"   row_13 "','"   row_14  "','"   row_15 "','"   row_16 "','"   row_17 "','"   row_18 "','"   row_19 "','"   row_20 "','"   row_21 "','"   row_22 "','"   row_23 "','"   row_24 "','"   row_25 "'"
                
insert_table="""INSERT INTO dl_get_response.weekly_news_letter_all_status ( replace_campaign_id, campaign_name, message_id, message_name
                                 , message_subject, send_date, total_emails_sent, total_emails_delivered, replace_emails_sent, emails_delivered
                                 , delivered_rate, emails_opened, open_rate, unique_opens, unique_open_rate, links_clicked, click_rate
                                 , unique_links_clicked, unique_click_rate, bounces, bounce_rate, unsubscribed, unsubscribe_rate
                                 , spam_complaints, user_unknown, user_unknown_rate)
                                 SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s"""
               
    
                print(insert_table)
                cur.execute(insert_table,row)
                conn.commit()
  

Ниже приведена ошибка,

 INSERT INTO  weekly_news_letter_all_status ( replace_campaign_id, campaign_name, message_id, message_name
                             , message_subject, send_date, total_emails_sent, total_emails_delivered, replace_emails_sent, emails_delivered
                             , delivered_rate, emails_opened, open_rate, unique_opens, unique_open_rate, links_clicked, click_rate
                             , unique_links_clicked, unique_click_rate, bounces, bounce_rate, unsubscribed, unsubscribe_rate
                             , spam_complaints, user_unknown, user_unknown_rate)
                             SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s
Traceback (most recent call last):
  File "/Users/user/Documents/news_letter_all_details.py", line 68, in <module>
    cur.execute(insert_table,row)
TypeError: not all arguments converted during string formatting