exporting database from python to postgresql

Roy W 0
Viewed 19

I've done cleaning my data in python using pandas and now I need to feed the data into PostgreSQL, however, my data has 9m+ rows which is impossible for me to output as a single CSV file. What options do I have to export the data from python to PostgreSQL? appreciate any help. Thanks

dataframe pandas postgresql
1 Answers

You can use psycopg2 to connect to postgres in your python script. Import the psycopg2 module to your script. Create a variable with your SQL script and then call the query. You will need to put in the appropriate values for host, database, user and password. See psycop documentation for more details https://www.psycopg.org/

import psycopg2

your_data = df.to_dict(orient='records')

insert_query= """
   INSERT INTO...
   VALUES (%s)
"""
with psycopg2.connect(host=host, database=database, user=user, password=password) as conn:
    with conn.cursor() as cur:
        extras.execute_batch(cur, insert_query,your_data, page_size=100)
DLH0
Related