Using Pandas to Read and Write MySQL Databases

When performing data analysis with Python, pandas provides convenient functions to read from and write to MySQL databases. The tutorial first introduces three pandas functions for reading data: read_sql_query() , read_sql_table() , and read_sql() , explaining their differences.

It then shows how to establish a database connection using pymysql :

import pandas  # import pandas module
import pymysql  # import pymysql module
import sqlalchemy  # import sqlalchemy module

pymysql_db = pymysql.connect(host="localhost", user="root",
    password="root", db="jd_data", port=3306, charset="utf8")

sql = "select book_name from sales_volume_rankings where id<=5"

sql_query_data = pandas.read_sql_query(sql=sql, con=pymysql_db)
print('Data read with read_sql_query():', sql_query_data)

For operations that require SQLAlchemy, the engine is created as follows:

sqlalchemy_db = sqlalchemy.create_engine(
    "mysql+pymysql://root:root@localhost:3306/jd_data")

sql_table_data = pandas.read_sql_table(table_name='sales_volume_rankings', con=sqlalchemy_db)
print('Number of rows read with read_sql_table():', len(sql_table_data))

read_sql_data = pandas.read_sql(sql=sql, con=sqlalchemy_db)
print('Data read with read_sql():', read_sql_data)

The output examples show the book names retrieved from the sales_volume_rankings table and the number of rows returned by read_sql_table .

To write data back to the database, pandas offers the to_sql() method. The tutorial creates a simple DataFrame and inserts it into a new table:

data = {'A': [1, 2, 3, 4, 5],
        'B': [6, 7, 8, 9, 10],
        'C': [11, 12, 13, 14, 15]}

data_frame = pandas.DataFrame(data)

data_frame.to_sql('to_sql_demo', sqlalchemy_db, if_exists='append')

read_sql_data = pandas.read_sql(sql="select * from to_sql_demo", con=sqlalchemy_db)
print('Data after insertion:', read_sql_data)

The final result displays the inserted rows, confirming that the write operation succeeded. The article notes that the pymysql and sqlalchemy packages must be installed and that the MySQL server must be running.