How Can I Efficiently Insert a Pandas DataFrame into a MySQL Database?

Inserting Pandas Dataframe into Database via MySQLdb

Connecting to MySQL using Python is straightforward, and operations like row insertion are well-supported. However, when it comes to inserting an entire Pandas dataframe into an existing table, the question arises: is direct insertion possible or does it require iterating over rows?

Direct Insertion using to_sql

The preferred method for inserting a dataframe into MySQL is using the to_sql method:

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

Connection Setup with MySQLdb

To establish a connection to MySQL using MySQLdb, execute the following:

from pandas.io import sql
import MySQLdb

con = MySQLdb.connect()  # may need additional connection parameters

Flavor and if_exists

Setting the flavor of write_frame to 'mysql' enables writing to MySQL. The if_exists parameter controls the behavior when the table already exists, with options being 'fail', 'replace', and 'append'.

Example

Consider a simple table with columns ID, data1, and data2, and a matching Pandas dataframe:

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

Inserting this dataframe into the database using the to_sql method would look like this:

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

In this example, 'example_table' is the table name in the database, and 'replace' means that the existing table will be replaced with the data from the dataframe.

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