SQLAlchemy and MySQL

 

Install dependencies for SQLalchemy and MySQL:

pip install sqlalchemy pymysql python-dotenv

Set up a .env file containing your DB creds:

SQL_HOST=localhost
SQL_PASSWORD=blahblah
SQL_USER=readonly
SQL_DB=my_database_name

 

Once you have set up your .env file you can load it into your python script 

import os
import sqlalchemy
import dotenv

dotenv.load_dotenv()

engine = sqlalchemy.create_engine(f"mysql+pymysql://{env['SQL_USER']}:{env['SQL_PASSWORD']}@{env['SQL_HOST']}/{env['SQL_DB']}")

query = """SELECT * FROM table_name WHERE col1="blah" LIMIT 1000"""

with engine.connect() as conn:
  df = pd.read_sql(query, conn)


df.head()