Skip to content

Instantly share code, notes, and snippets.

@tmasjc
Last active October 20, 2020 13:31
Show Gist options
  • Save tmasjc/1a9b2a8dd36b984b548eaf9806ac5834 to your computer and use it in GitHub Desktop.
Save tmasjc/1a9b2a8dd36b984b548eaf9806ac5834 to your computer and use it in GitHub Desktop.
Obtain data from SQL in Python
# %%
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)
# %%
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment