How to import CSV file data into Database using SQLAlchemy in Python

How to import CSV file data into Database using SQLAlchemy in Python!
 

 

In the field of data analysis, it is crucial to read files and load data into tables. Occasionally, data may be loaded without prior cleaning. Python offers multiple methods for data loading, and today we will focus on the sqlalchemy library, which is widely used in the Python community.

The following program demonstrates a simple and straightforward approach. Depending on your specific needs, you can modify and update the code accordingly.

step 1: import required librearies

import pandas as pd

from sqlalchemy import create_engine

# 1.Read CSV file into Pandas DataFrame:
irisData = pd.read_csv('G:\ETL_Automation\data\dept.csv', header=None, index_col=False)

# 2.create sqlalchemy engine
engine = create_engine('oracle://project:tiger@localhost:1521')

# 3.Insert whole DataFrame into Oracle DB
irisData.to_sql('iris', con=engine, if_exists='append', chunksize=1000, index=False)
print("Record inserted successfully")

This code will read the CSV file 'data.csv' into a Pandas DataFrame, prepare an SQL INSERT statement based on the table structure, and execute the INSERT statements for each row of data in the DataFrame. Finally, it will commit the changes to the Oracle database and close the cursor and connection.