Welcome folks today in this blog post we will be using the sqlalchemy and pandas library to add the data inside the mysql table using csv file in python. All the full source code of the application is shown below.
Get Started
In order to get started you need to install the below library using the pip command as shown below
pip install sqlalchemy
pip install pandas
Now we need to make an app.py file and copy paste the following code
app.py
|
1 2 3 4 5 6 7 8 9 10 11 |
import pandas as pd from sqlalchemy import create_engine # Read the CSV file df = pd.read_csv('output.csv') # Connect to the database engine = create_engine('mysql+pymysql://root:@localhost/exceldb') # Convert the CSV file to a table in the database df.to_sql('table_name', engine, if_exists='replace') |
As you can see we are first of all importing the pandas library and reading the contents of the output.csv file using the read_csv() method and then we are creating the connection to the mysql database using the username and password and also the database name. And then we are using the to_sql() method to insert the csv file data to the mysql table. Now first of all you need to start the mysql database as shown below
And now you need to make the output.csv file before you execute the python script.
output.csv
|
1 2 3 4 5 |
Name,Age,Country Alice,25,USA Bob,30,Canada Claire,35,UK David,40,Australia |
python app.py
