Pandas and MySQL

Pandas can read data from, and write data to, a MySQL database.

First install two libraries: pip install pymysql (the driver that talks to MySQL) and pip install sqlalchemy (which manages the connection).

Then create an engine from a connection string:

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:password@localhost:3306/SCHOOL')

Here mysql+pymysql names the database and driver; root:password is the user and password; localhost is the server; 3306 is MySQL's default port; and SCHOOL is the database.

• Importing data into a DataFrame: read_sql_query(query, engine), read_sql_table(table, engine), or read_sql(sql, engine).
• Exporting a DataFrame: df.to_sql('table', engine, if_exists=..., index=False). The if_exists parameter decides what happens if the table already exists: 'fail' (raise an error, the default), 'replace' (drop and recreate it) or 'append' (add the rows).

Engine first, then read or write.