Exporting Data from Pandas to MySQL

Exporting Data from Pandas to MySQL

Exporting data from Pandas to MySQL means writing the contents of a pandas DataFrame into a table inside a MySQL database. This is the reverse of importing — instead of pulling data from MySQL into Python, you are pushing data from Python into MySQL.

The function that does this is pandas.DataFrame.to_sql(). Its general syntax is:

DataFrame.to_sql(table, sql_conn, if_exists="fail", index=False/True)

Each parameter has a specific job.

table — This is simply the name you want to give to the MySQL table that will receive the data. It can be a new table name or an existing one, depending on what you want to do.

sql_conn — This is the connection identifier returned by create_engine(). You must already have a working engine object that connects your Python script to a specific MySQL database. Without this, the function cannot reach the database.

if_exists — This parameter controls what happens when the table you named already exists in the database. It takes one of three string values:

• "fail" — This is the default. If the table already exists, the function raises a ValueError and does nothing. It is the safe option that prevents accidental overwrites.
• "replace" — The existing table and all its data are dropped, and the DataFrame's contents are written in their place. The old data is completely lost.
• "append" — The DataFrame's rows are added to the end of the existing table. For this to work, the column names and their order in the DataFrame must match the existing table's structure exactly.

index — By default, this is True, which means the DataFrame's index (the row labels) will be written as a separate column in the MySQL table. If you set it to False, the index is ignored and only the actual data columns are written.

A complete example

The textbook gives a clear step-by-step illustration. First, you import the necessary libraries: pandas, pymysql, and sqlalchemy. Then you create an engine to connect to your database. In the example, the connection string is:

mysql+pymysql://root:smsmb@localhost:3306/CARSHOWROOM

This connects as user root with password smsmb to the database CARSHOWROOM running on the local machine at port 3306.

Once the engine is ready, you can read data from MySQL into a DataFrame using pd.read_sql_query(). The textbook reads the entire INVENTORY table and prints it. The output shows eight rows of car data with columns: CarId, CarName, Price, Model, YearManufacture, and Fueltype.

Then comes the export. A new dictionary data is created with two keys: ShowRoomId and Location. This dictionary is turned into a DataFrame. Finally, the to_sql() method is called:

df.to_sql('showroom_info', engine, if_exists="replace", index=False)

After this line runs, a new MySQL table named showroom_info is created inside the CARSHOWROOM database, containing the two columns from the DataFrame. Because if_exists is set to "replace", if a table named showroom_info already existed, it would be replaced entirely.

Summary of key points from the section

• Exporting means storing a pandas DataFrame into a MySQL table. …