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.