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()