Read and Write Tables From MySQL With Python

How to read and write tables from MySQL with Python.

  1. Install PyMySQL to connect to a MySQL database server from Python:

    pip install PyMySQL

  2. Install the following packages:

    import pandas as pd
import pymysql
import os
from sqlalchemy import create_engine

  3. Connect to MySQL by running the following lines of code:

    # ====== Connection to MySQL ====== #
# To connect to MySQL by providing an SQLAlchemy engine.
engine = create_engine('mysql+pymysql://'+os.environ['MYSQL_USER'] + ':' + os.environ['MYSQL_PASSWORD'] + '@' + os.environ['MYSQL_HOST'] + ':' + os.environ['MYSQL_PORT']+ '/sandbox', echo=False)
    The MySQL connection URL format must be mysql+pymysql://user:password@host_ip:port/database.

  4. You can now read and write tables from MySQL by running the following lines of code:

    • Read Tables

    • Write Tables

    # ====== To Read Tables ====== #
# To read a MySQL table in a pandas DataFrame.
data = pd.read_sql('SELECT * FROM helloworld', engine)
    # ====== To Write Tables ====== #
# To create 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})

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

  5. Close your connection when you no longer need it by running the following lines of code:

    # ====== Connection to MySQL ====== #
# To connect to MySQL by providing an SQLAlchemy engine.
engine = create_engine('mysql+pymysql://'+os.environ['MYSQL_USER'] + ':' + os.environ['MYSQL_PASSWORD'] + '@' + os.environ['MYSQL_HOST'] + ':' + os.environ['MYSQL_PORT']+ '/sandbox', echo=False)
data = pd.read_sql('SHOW DATABASES;', engine)

# ====== To Close the Connection to MySQL ====== #
engine.dispose()
See also