In the fast-paced world of data management, efficiency is key. Whether you’re a seasoned developer or someone taking their first steps into the realm of databases, the ability to smoothly transfer data from one format to another is a valuable skill.

One common scenario involves extracting information from CSV (Comma-Separated Values) files and seamlessly integrating it into a database.

Read And Insert CSV File Into Database

If you’re visual learner , below is the full tutorial video:

Create the Database connection

To create database connection , at first, we need to import the database .So, lets see how can we import the database .

Import Database

At first, you need to import the Database into the Python file by : import MySQLdb

To import it , MySQLdb must have been installed in your computer. To install it, go to: Download MySQLdb

Open CSV File And Read

We need to create the CSV file and import it .

Opening CSV File

To have access to CSV file , first, you need to import it. For that: import csv

Now, you need to open the CSV file that you have prepared. To open :

with open('xyz.csv') as csv_file:

Reading CSV File

After opening the CSV File , now need to read and append the csv file into the single variable which stores all the data in database.

with open('xyz.csv') as csv_file:
csvfile = csv.reader(csv_file, delimiter=',')
all_value=[]
for row in csv file:
value=(row[0],row[1],row[2])
all_value.append(value)

The code above reads the csv file and and append reach row in ‘all_value ‘ in the database. The code is for the three different elements in a row in csv file ,that’s why there is 3 index 0,1 & 2 for a row. It can vary according to the number of elements in a row you have in csv file.

Insert Into Table

After we read and append the csv file , now we need to insert the data into the table of our database.

To insert the data into the table, the code below is used:

query= "insert into `table_name`(`name,`company`,`launch_year`)values(%s,%s,%s)"

In the above code, name, company and launch year are the attributes in the database table named as ‘table_name’ and its values are stored in the database.

Execute The Query Using Cursor

After inserting the data into database , we now need to create a cursor to query a database .

Create a cursor To Query A Database

In order to execute the query, we need to create a cursor for our database.

we can create a cursor for our database and use it to query a database. To create the cursor ,

mycursor = mydb.cursor()

Here, mycursor is the cursor name and mydb.cursor creates a cursor named mycursor.

Execute The Query

Now , to execute the query by using the cursor we created earlier:

mycursor.executemany(query,all_values)

Commit The Changes

Finally, after doing all the necessary steps successfully, we need to commit the changes that we made earlier

To make commit :

mydb.commit()

Hey! that’s all you need to do to read and insert the csv file into a database. Hope you enjoyed the tutorial….

After doing all the necessary steps successfully, you’ll get the data into your database as shown in the image below:

you may also like: Read CSV File Using Python

Conclusion-Read And Insert The CSV Data into Database

In this blog post, we have read the data from csv file and we inserted the data into a database using the query. Its necessary to commit the changes that we’ve made in the database. After following all the steps, you must have successfully read and inserted the data from csv file into the database. If you have any problem, let me know in the comments below.

Sharing is caring!