Backend is an important things, when we played with the large and complex system. One of backend component is databases, there is many database choices today. But, in this article, I would like to share about how to import our CSV/EXCL files into our MySQL database Using Python. I promise it can help us for saving time. So keep reading.
As you can see from the picture above, MySQL is still the most popular database that every developer used. But if you ever using it manually, probably it’s hard to follow along with the syntax. Because MySQL is an SQL database. Okay, let’s do it.
Note: For this article I am using MariaDB but you can still use MySQL. Because, MySQL and MariaDB is just the same.
Creating CSV Files
First of all, we need to make an CSV files right? Today, it can be done using many program, the most common way to make this CSV files is by using Microsoft Excel or Google Spreadsheet. Here is the view of my CSV files.
Yes, don’t forget to save it as CSV files, because when it convert into CSV files, it can be read using text editor like this
As you can see every column is separated by comma and every row is separated by new line, so with that concept, we can import our CSV files into database using Python. Because it just the collection of array.
Creating New Databases
The next things we need to do is to make new database, you can use your database exactly. For the example I make new database name ‘Fruit_Shop’. The syntax for MySQL is here
create database Fruit_Shop;
Then, I am using it, so my database pointer will look like this
Creating New Tables
After we make the database, we need to build the table for the database so it can be work. Here is the syntax that I use.
create table `product` [
`id` int[11] NOT NULL,
`name` varchar[50] NOT NULL,
`price` int[11] NOT NULL,
`stock` int[11] NOT NULL
];
If we want to know the structure of table simply use
desc {table name}
For the product, here is the structure
Build the Python Scripts
If we are ready, let’s go into the main part for this tutorial. Here is the script that I make
As you can see, we need to import MySQLdb, csv, and sys libraries. It can be done by using pip install help. For me I just need to install MySQLdb only. Here is the command.
pip install mysql
The installation will takes some times, it works to connect our python program into MySQL databases.
After that in line 4, don’t forget to change the host, user, password, and database into yours. For this tutorial, I just using it for local network. So simply put 127.0.0.1 on it.
The next line is accessing the CSV files. What the header in line 8 is used to bypass the first line, so it will work
from the second line of CSV files. The next one, as you can see I declare cursor.execute
It will converted into MySQL syntax to put our data from CSV into tables of MySQL. Finally, it will be done.
Run the Scripts
Whenever you are ready, let’s run the code
Before
After
Congratulation, now you can using backend easier.
Conclusion
Today, python is growing up and can help a lot for solving our life problem. This article show us, that we can easily import our CSV files into our MySQL database. I hope you enjoy this article, any question just ask me inside the comment column. Here is the source code.
Have a nice code 😇