# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Save a `pandas` dataframe to SQL with `sqlalchemy`
#
# We will: 
# - make a dataframe from a hosted Excel file
# - set all column names to lowercase
# - write to the database

import pandas as pd
import sqlalchemy

# ### Read some data from the web into a dataframe

# +
url = 'https://www1.nyc.gov/assets/nypd/downloads/excel/analysis_and_planning/stop-question-frisk/sqf-2018.xlsx'

df = pd.read_excel(url)

# +
# Clean up the dataframe before writing to SQL
# For example, SQL column names may not use capital letter

df.columns = [x.lower() for x in df.columns]
# -

# ### Define the PostgreSQL connection string

# +
MY_DB = "teaching_bucket"
UN = "aaron"
PW = "<PASSWORD>"
HOST = "localhost"

db_uri = f"postgresql://{UN}:{PW}@{HOST}:5432/{MY_DB}"
# -

# ### Connect to the database with `sqlalchemy` and then pass that connection into a the `pandas` `.to_sql()` function
#
# Make sure to `.dispose()` the connection afterwards!

# +
connection = sqlalchemy.create_engine(db_uri)

df.to_sql("nyc_stop_frisk_2018", connection, if_exists="replace")

connection.dispose()

StarcoderdataJupyterScriptsDedupFiltered