add dataframe to a mysql database in python

To add a Pandas DataFrame to a MySQL database in Python, you can use the to_sql method of the Pandas DataFrame.

The following steps are required:

  1. Connect to the MySQL database using SQLAlchemy library
  2. Create a table in the database using pandas DataFrame, specifying the column names and data types
  3. Use to_sql method, passing the table name and connection object, to add data to the MySQL database

Here's the sample code for adding a Pandas DataFrame to a MySQL database:

main.py
# import required libraries
import pandas as pd
from sqlalchemy import create_engine

# create a Pandas DataFrame
data = {'name': ['John', 'Mike', 'Sara', 'Steve'],
        'age': ['25', '30', '28', '32'],
        'gender': ['Male', 'Male', 'Female', 'Male']}
df = pd.DataFrame(data)

# create a database connection using SQLAlchemy
engine = create_engine('mysql://user:password@host:port/database')

# create a table in the database using pandas DataFrame
df.to_sql('table_name', con=engine, if_exists='replace', index=False, 
               dtype={'name': sqlalchemy.types.VARCHAR(length=100), 
                      'age': sqlalchemy.types.Integer(), 
                      'gender': sqlalchemy.types.VARCHAR(length=10)})

# add data to the MySQL database using to_sql method
df.to_sql('table_name', con=engine, if_exists='append', index=False)
848 chars
22 lines

In the above code, replace the MySQL database credentials with your own. The if_exists parameter in to_sql method specifies what action should be taken if the table already exists in the database. If set to replace, it will overwrite the existing table, otherwise, it will append the data to the table.

gistlibby LogSnag