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 create tables in MySQL, as well as query tables from MySQL by running the following lines of code:

    • Create Tables in MySQL

    • Query Tables From MySQL

    # ====== Writing 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)
    # ====== Reading Tables ====== #
    # To read a MySQL table in a pandas DataFrame.
    data = pd.read_sql('SELECT * FROM helloworld', engine)
  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)
    
    # ====== Closing the Connection to MySQL ====== #
    engine.dispose()