Obtain data from SQL in Python

sql_example.py

# %%
import configparser
import mysql.connector
from mysql.connector import Error
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

config = configparser.ConfigParser()
config.read("config.ini")
db = config["mysql"]

# %%
try:
    connection = mysql.connector.connect(
        host=db.get("host"),
        database=db.get("database"),
        user=db.get("username"),
        password=db.get("password"),
        auth_plugin="mysql_native_password"
    )

    if connection.is_connected():
        print("Connected to MySQL", connection.get_server_info(), "\n")

except Error as e:
    print("Connection failed", e)

# %%
df = pd.read_sql("select * from iris;", con=connection)
df = df.sample(frac=0.5)

# %%
if connection.is_connected():
    connection.close()
    print("\nConnection closed.")

# %%

# write to csv
df.to_csv(config["output"]["csv"], index=False)

# write to Excel
xlsx = config["output"]["xlsx"]
wb = load_workbook(filename=xlsx)
wb.remove(wb["raw"])
ws = wb.create_sheet("raw", index=2)
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

pv = wb["Pivot"]
pivot = pv._pivots[0] # any will do as they share the same cache
pivot.cache.refreshOnLoad = True

wb.save(xlsx)

# %%