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