How to import multiple CSV files into a PostgreSQL table using python?

user_15708627 0
Viewed 853

I have a number of daily csv files to be imported into the Postgres. Below python codes work for importing a single csv file. How can I import batch cvs files? Thanks!

import psycopg2

con = psycopg2.connect(
    database="xxxx", 
    user="xxxx", 
    password="xxxx", 
    host="xxxx")

cur = con.cursor()
file = open('path/to/directory/client_record_2020-01-01.csv', 'r')
next(file)
cur.copy_from(file, "table_name", columns=('col1', 'col2', 'col3', 'col4', 'col5'), sep=",")

con.commit()
con.close()
python-3.x postgresql
1 Answers

Let's try putting the names of our files in a list and then iterate over that list doing one copy_from() at a time. Maybe:

import psycopg2

file_names = [
    'path/to/directory/client_record_2020-01-01.csv'
]

con = psycopg2.connect(database="xxxx", user="xxxx", password="xxxx", host="xxxx")

for file_name in file_names:
    with open(file_name, 'r') as file_in:
        next(file_in)
        with con.cursor() as cur:
            cur.copy_from(file_in, "table_name", columns=('col1', 'col2', 'col3', 'col4', 'col5'), sep=",")
        con.commit()

con.close()
user_2186630
Related