How to Insert a Pandas DataFrame into a MySQL Database Using MySQLdb?

Inserting Pandas Dataframe into Database via MySQLDB

Question:

How can we directly insert an entire pandas dataframe into an existing MySQL database table using MySQLdb?

Answer:

Using the write_frame method from the pandas.io.sql module, you can insert a dataframe into MySQL as follows:

sql.write_frame(df, con=con, name='table_name', if_exists='replace', flavor='mysql')

where:

• df is the pandas dataframe to be inserted.
• con is a MySQLdb connection object.
• name is the name of the table to insert into.

• if_exists specifies how to handle an existing table:

  • 'fail': Do nothing if the table exists.
  • 'replace': Drop the table and recreate it before inserting data.
  • 'append': Insert data into the existing table if it exists, otherwise create it.

Using to_sql Method:

The preferred method for inserting dataframes into SQL databases is now to_sql:

df.to_sql(con=con, name='table_name', if_exists='replace', flavor='mysql')

Setting up MySQLdb Connection:

Establish a connection to MySQL using MySQLdb:

from pandas.io import sql
import MySQLdb

con = MySQLdb.connect()

Sample Code:

For a table with ID, data1, and data2 columns, the Python script to insert the corresponding dataframe would be:

import pandas as pd
import MySQLdb

df = pd.DataFrame({'ID': [1, 2, 3], 'data1': ['a', 'b', 'c'], 'data2': [10, 20, 30]})

con = MySQLdb.connect()
sql.write_frame(df, con=con, name='table_name', if_exists='replace', flavor='mysql')

The above is the detailed content of How to Insert a Pandas DataFrame into a MySQL Database Using MySQLdb?. For more information, please follow other related articles on the PHP Chinese website!