Speaking of Data

Using sqlalchemy to upload csv files into MySQL Database

Data Sensei's photo
Data Sensei
·

2 min read

After activating your python environment where you want to code, Copy the following and press enter.

pip install sqlalchemy
pip install mysqlclient

Then go ahead and download a csv file. Assuming you have pandas already installed, copy and paste the following code.

from sqlalchemy import create_engine as ce
mysqlengine= ce("mysql://root:root@127.0.0.1:3306/nhs_trust")

import pandas as pd

df = pd.read_csv("E:\python\datacamp_basics\lalaland.csv")

df.to_sql('thulaland', mysqlengine, if_exists='replace', index= False)

Code Explanation

  1. Importing the necessary modules:

     from sqlalchemy import create_engine as ce
 import pandas as pd

    This code imports the create_engine function from SQLAlchemy as ce and the pandas library as pd. The create_engine function is used to create a SQLAlchemy engine, and pandas is used to work with data frames, including reading and writing CSV files.

  2. Creating a SQLAlchemy engine:

     mysqlengine = ce("mysql://root:root@127.0.0.1:3306/nhs_trust")

    This code creates a SQLAlchemy engine called mysqlengine using the create_engine function. The engine is configured to connect to a MySQL database with the following connection URL:

     mysql://root:root@127.0.0.1:3306/nhs_trust

    • mysql specifies the dialect for MySQL.

    • root:root represents the username and password for the database connection. In my case root was username and root was password.

    • 127.0.0.1:3306 specifies the host and port for the MySQL server.

    • nhs_trust is the name of the database.

  3. Reading a CSV file into a pandas DataFrame:

     df = pd.read_csv("E:\python\datacamp_basics\lalaland.csv")

    This code uses the read_csv function from pandas to read the CSV file located at "E:\python\datacamp_basics\lalaland.csv". The contents of the CSV file are loaded into a pandas DataFrame named df.

  4. Uploading the DataFrame to a MySQL table:

     df.to_sql('thulaland', mysqlengine, if_exists='replace', index=False)

    This code uploads the data from the pandas DataFrame df to a MySQL table named 'thulaland' using the to_sql method. The parameters passed to to_sql are:

    • 'thulaland' specifies the name of the destination table.

    • mysqlengine is the SQLAlchemy engine to be used for the database connection.

    • if_exists='replace' indicates that if the table already exists, it should be replaced.

    • index=False specifies that the DataFrame's index should not be included in the table.

The to_sql method converts the DataFrame into a SQL insert statement and executes it to upload the data to the specified table in the MySQL database.

Make sure to adjust the file path and database connection details according to your specific setup.