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

Question

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

JonSG · Accepted Answer · 2021-04-20 18:00:44Z

2

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

edited Apr 20, 2021 at 18:00

answered Apr 20, 2021 at 17:50

JonSG

13.6k2 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

geopark Over a year ago

hmm it doesn't loop through though.

geopark Over a year ago

I know what it is. "file_names" lists one file only. So it doesn't loop through. I used glob to grab all files. Done. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related