Bulk Insert Pandas DataFrame with psycopg2

If you want to bulk insert Pandas DataFrame into PostgreSQL, you can insert SQLAlchemy and use .to_sql (), but when you are using psycopg2 as a PostgreSQL client, you feel like "I don't want to insert SQLAlchemy just for that". Become.

So I thought, "If you do.to_csv ()of DataFrame and read it with.copy_from ()of psycopg2, you can do Bulk Insert."

from io import StringIO
import pandas as pd
import psycopg2

def df2db(conn: psycopg2.extensions.connection, df: pd.DataFrame, table: str):
    buf = StringIO()
    df.to_csv(buf, sep='\t', na_rep=r'\N', index=False, header=False)
    buf.seek(0)
    with conn.cursor() as cur:
        cur.copy_from(buf, table, columns=df.columns)

Create a function like this

image.png

For example, if you want to Bulk Insert such a DataFrame into a table called logs in PostgreSQL,

with psycopg2.connect('postgresql://...') as conn:
    df2db(conn, df.reset_index(), 'logs')

You can do this.

Since index is not output, if you want to put index in the table, you need to do .reset_index ().

Recommended Posts

Bulk Insert Pandas DataFrame with psycopg2
Replace column names / values with pandas dataframe
Delete rows with arbitrary values in pandas DataFrame
Remove rows with duplicate indexes in pandas DataFrame
3D plot Pandas DataFrame
Quickly visualize with Pandas
Processing datasets with pandas (1)
Bootstrap sampling with Pandas
Convert 202003 to 2020-03 with pandas
Processing datasets with pandas (2)
Extract N samples for each group with Pandas DataFrame
Learn Pandas with Cheminformatics
Data visualization with pandas
Data manipulation with Pandas!
Shuffle data with pandas
Python application: Pandas # 3: Dataframe
Bulk indent with Xcode
Check what line caused the error with apply () (dataframe, Pandas)
Calculate the time difference between two columns with Pandas DataFrame
Read csv with python pandas
Load nested json with pandas
Formatted display of pandas DataFrame
Standardize by group with pandas
Python hand play (CSV is applied with Pandas DataFrame, but only full-column Insert from CSV to DB?)
Export pandas dataframe to excel
Prevent omissions with pandas print
Data processing tips with Pandas
7rep --Insert Dataframe To Elasitcsearch