example-python-read-and-write-from-mysql

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

# ====== Connection ======
# Connecting to mysql by providing a sqlachemy engine
engine = create_engine('mysql+mysqlconnector://os.environ['MYSQL_USER']:os.environ['MYSQL_PASSWORD']@os.environ['MYSQL_HOST_IP']:os.environ['MYSQL_PORT']/sandbox', echo=False)

# ====== Writing table ======
# Creating a simple pandas DataFrame with two columns
liste_hello = ['hello1','hello2']
liste_world = ['world1','world2']
df = pd.DataFrame(data = {'hello' : liste_hello, 'world': liste_world})

# Writing Dataframe to mysql and replacing table if it already exists
df.to_sql(name='helloworld', con=engine, if_exists = 'replace', index=False)

# ====== Reading table ======
# Reading Mysql table into a pandas DataFrame
data = pd.read_sql('SELECT * FROM helloworld', engine)