How Can I Insert a Pandas DataFrame into a MySQL Database using MySQLdb?

Inserting Pandas DataFrame into Database using MySQLdb

Question:

Is it possible to directly insert an entire Pandas DataFrame into an existing MySQL table using MySQLdb?

Answer:

Yes, it is possible using the sql.write_frame function provided by Pandas.

Updated Syntax for Pandas >= 0.14

Code:

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

For Pandas < 0.14

Code:

import MySQLdb

con = MySQLdb.connect()  # Establish connection
sql.write_frame(df, con=con, name='table_name_for_df', if_exists='replace', flavor='mysql')

Parameters:

• con: MySQLdb connection object
• name: Name of the table in the database to insert the DataFrame into
• if_exists: Specifies how to handle if the table already exists. Can be 'fail', 'replace', or 'append' (default: 'fail')
• flavor: Specifies the SQL flavor to use, in this case 'mysql'

Example:

import pandas as pd
import MySQLdb

df = pd.DataFrame({
    'ID': [1, 2, 3],
    'Column1': ['Value1', 'Value2', 'Value3'],
    'Column2': [10, 20, 30]
})

con = MySQLdb.connect(...)

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

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