Gist Page : example-python-read-and-write-from-mysql
Install mysql package, in bash :
pip install mysql-connector==2.1.4 |
import pandas as pd import mysql.connector from sqlalchemy import create_engine |
SQLAlchemy URI looks like this : 'mysql+mysqlconnector://user:password@host_ip:port/database'
# ====== 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) |