<jupyter_start><jupyter_text>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<jupyter_code>import pandas as pd
import sqlalchemy<jupyter_output><empty_output><jupyter_text>Read some data from the web into a dataframe<jupyter_code>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]<jupyter_output><empty_output><jupyter_text>Define the PostgreSQL connection string<jupyter_code>MY_DB = "teaching_bucket"
UN = "aaron"
PW = "<PASSWORD>"
HOST = "localhost"

db_uri = f"postgresql://{UN}:{PW}@{HOST}:5432/{MY_DB}"<jupyter_output><empty_output><jupyter_text>Connect to the database with `sqlalchemy` and then pass that connection into a the `pandas` `.to_sql()` functionMake sure to `.dispose()` the connection afterwards!<jupyter_code>connection = sqlalchemy.create_engine(db_uri)

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

connection.dispose()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11244979919678715, 'fraction_numerical': 0.010441767068273093, 'mean_word_length': 7.090909090909091, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '428600', 'license': 'MIT', 'n_tokens_mistral': 454, 'n_tokens_neox': 434, 'n_words': 107, 'path': 'examples/SQL data/sql_02_save_dataframe.ipynb', 'repo_name': 'bgruswitz/teaching-colleagues', 'size': '1246'}