Connect Pandas Dataframes to MySql Tables

A convenient way to integrate data-analyses with a web application is to connect pandas dataframes to MySql tables. The general idea is to import data from a MySql-table, manipulate data in pandas and than restore the dataframe to MySql for use on a website.

The modules mysql-connector and sqlalchemy combine to create a connector-engine:

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

engine = create_engine('mysql+mysqlconnector://username:password@localhost/databasename')

The engine serves to write and read pandas dataframes to and from MySql-tables:

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

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

A disclaimer is that this may work fine with tables that were initially created in this way, but not necessarily with tables that were created in MySql and than imported into pandas. Information about specific data-types will get lost, as the engine will create its own data-types. It may happen that data can be read into pandas all right from an existing table, but that subsequent storage results in a data-type error.