</>
pythonintermediate

Bulk Load CSV into PostgreSQL with COPY

Use psycopg2's copy_expert for the fastest possible bulk CSV load into a PostgreSQL table.

python
import psycopg2
import io
import pandas as pd

df = pd.DataFrame({'id':range(1000),'name':[f'user_{i}' for i in range(1000)],'score':range(1000)})

buffer = io.StringIO()
df.to_csv(buffer, index=False, header=False)
buffer.seek(0)

conn = psycopg2.connect('dbname=mydb user=postgres')
with conn.cursor() as cur:
    cur.execute('TRUNCATE staging_users')
    cur.copy_expert('COPY staging_users(id, name, score) FROM STDIN WITH CSV', buffer)
conn.commit()
print(f'Loaded {len(df):,} rows via COPY')

Use Cases

  • high-speed bulk loads
  • staging tables
  • ETL data ingestion

Tags

#psycopg2#postgres#bulk-load#etl

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlbeginner

PostgreSQL COPY — Fast CSV Import

Use PostgreSQL COPY command for high-speed bulk data loading from CSV files with error handling.

Best for: High-speed bulk data loading into PostgreSQL

#postgres#copy
pythonadvanced

Python ETL Pipeline Example

Complete extract-transform-load pipeline with error handling, logging, and incremental processing.

Best for: Automating data ingestion from CSV to warehouse

#etl#pipeline
pythonintermediate

Python Batch Processing Script

Process large files in configurable batches with progress tracking, error handling, and resume support.

Best for: Processing large CSV files that don't fit in memory

#batch-processing#python
pythonadvanced

Database Sync Script in Python

Sync data between two databases with upsert logic, batch processing, and change detection.

Best for: Replicating data between databases

#database#sync