Skip to content

Instantly share code, notes, and snippets.

@codingforentrepreneurs
Last active February 27, 2024 18:22
Show Gist options
  • Save codingforentrepreneurs/6b822e768245102f53462dc1d2a0d5f6 to your computer and use it in GitHub Desktop.
Save codingforentrepreneurs/6b822e768245102f53462dc1d2a0d5f6 to your computer and use it in GitHub Desktop.
Export Pandas Dataframe to a PostgreSQL Database Table

Export Pandas Dataframe to a PostgreSQL Database Table

Export your Pandas analysis really easily to a PostgresSQL database table with this tutorial. We used Docker Compose to create the postgres database with docker compose up and the related compose.yaml file.

Step 1 - Install Requirements

Add requirements.txt from below.

python3 -m pip install -r requirements.txt

Step 2 - Create dotenv

Make a file named .env with the PostgresSQL connection string like:

DATABASE_URL=postgres://myuser:mypass@localhost:5430/mydb

Step 3 - Run df_to_sql.py

Run python3 df_to_sql.py

version: '3.9'
services:
db:
image: postgres
restart: always
ports:
- 5430:5432
volumes:
- my_pg_v:/var/lib/postgresql/data/
environment:
- POSTGRES_DB=mydb
- POSTGRES_PASSWORD=mypass
- POSTGRES_USER=myuser
volumes:
my_pg_v:
networks:
default:
name: my_custom_network
# `docker compose up` yields:
# postgres://myuser:mypass@localhost:5430/mydb
import pandas as pd
import random
import string
from datetime import datetime
from decouple import config
from sqlalchemy import create_engine, select, Table, MetaData
# Configure Postgres
# Connection String for
# SQLalchemy
DATABASE_URL=config('DATABASE_URL', default=None)
if DATABASE_URL.startswith('postgres://'):
DATABASE_URL = DATABASE_URL.replace("postgres://", 'postgresql+psycopg://')
elif DATABASE_URL.startswith('postgresql://'):
DATABASE_URL = DATABASE_URL.replace("postgresql://", 'postgresql+psycopg://')
# Generate Randmo Data
domains = ["example.com", "jgmail.com", "downtime.com"]
def get_random_email():
un_length = random.randint(5, 15)
domain = random.choice(domains)
username = ''.join(random.choices(string.ascii_lowercase, k=un_length))
return f"{username}@{domain}"
# generate dataset and
# Pandas DataFrame
dataset = []
dataset_size = 10
for i in range(dataset_size):
dataset.append({
'id': i,
"email": get_random_email(),
'timestamp': datetime.now()
})
df = pd.DataFrame(dataset)
# Connect to DB and output data frame
engine = create_engine(DATABASE_URL)
output_table = 'daily_results'
df.to_sql(output_table, engine, index=False, if_exists='replace')
# Verify data in database
# via SQLAlchemy
metadata = MetaData()
metadata.reflect(bind=engine)
random_emails_table = Table(output_table, metadata, autoload_with=engine)
select_statement = select(random_emails_table)
with engine.connect() as connection:
result = connection.execute(select_statement)
for row in result:
print(row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment