📊 Unit 1 – DataFrame · Lesson 15
Pandas with MySQL
Why move data between pandas and MySQL?
Organisations keep data safely in a database (MySQL) but analyse it with pandas. pandas can read a table or query result straight into a DataFrame, and save a DataFrame back as a table.
Installing the connectors
Command prompt
pip install pymysql sqlalchemy
Use your own MySQL user name and password in the programs below.
Create a database
Python
import pymysql
con = pymysql.connect(host='localhost', user='root', password='root123')
cur = con.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS shop')
print('Database ready')
con.close()Database ready
The engine
Python
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
# driver user password host databaseExport: to_sql()
Python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
stock = pd.DataFrame({'Item': ['Pen', 'Pencil', 'Notebook', 'Eraser', 'Ruler'],
'Category': ['Writing', 'Writing', 'Paper', 'Other', 'Other'],
'Qty': [120, 300, 80, 150, 60],
'Price': [10, 5, 45, 3, 12]})
stock.to_sql('stock', engine, if_exists='replace', index=False)
print('Rows written:', len(stock))Rows written: 5
Import: read_sql()
Python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
df = pd.read_sql('SELECT * FROM stock', engine)
print(df)
print(df.dtypes) Item Category Qty Price
0 Pen Writing 120 10
1 Pencil Writing 300 5
2 Notebook Paper 80 45
3 Eraser Other 150 3
4 Ruler Other 60 12
Item object
Category object
Qty int64
Price int64
dtype: object
Python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
q = 'SELECT Item, Qty * Price AS Value FROM stock WHERE Qty >= 100 ORDER BY Value DESC'
print(pd.read_sql(q, engine)) Item Value
0 Pencil 1500
1 Pen 1200
2 Eraser 450
Appending rows
Python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
new = pd.DataFrame({'Item': ['Sharpener'], 'Category': ['Other'], 'Qty': [90], 'Price': [6]})
new.to_sql('stock', engine, if_exists='append', index=False) # add rows
df = pd.read_sql('SELECT * FROM stock', engine)
print(df.groupby('Category')['Qty'].sum())Category
Other 300
Paper 80
Writing 420
Name: Qty, dtype: int64
CSV → MySQL
Python
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:root123@localhost/shop')
df = pd.DataFrame({'Item': ['Glue', 'Marker'], 'Category': ['Other', 'Writing'],
'Qty': [40, 75], 'Price': [20, 25]})
df.to_csv('stock_new.csv', index=False) # a CSV file, e.g. saved from Excel
pd.read_csv('stock_new.csv').to_sql('stock', engine, if_exists='append', index=False)
print(pd.read_sql('SELECT COUNT(*) AS items FROM stock', engine)) items
0 8
Parameters summary
| Call | Meaning |
|---|---|
| create_engine(url) | Connection to the database |
| df.to_sql(name, engine, if_exists, index) | Write DataFrame to table name |
| pd.read_sql(query_or_table, engine) | Run a SELECT and get a DataFrame |
| if_exists='replace' / 'append' / 'fail' | What to do when the table exists |
| index=False | Do not store the DataFrame index |
Frequently Asked Questions
What does if_exists do in to_sql()?
'fail' (default) raises an error if the table exists, 'replace' drops and recreates it, and 'append' adds the rows to the existing table.
Which libraries are needed to connect pandas with MySQL?
pymysql (the MySQL driver) and sqlalchemy (to create the engine). Install both with pip.
What is the format of the connection string?
'mysql+pymysql://user:password@host/database', e.g. 'mysql+pymysql://root:root123@localhost/shop'.
