How do i import excel data into mysql using python?

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 😇

How do you insert data from Excel to MySQL using Python?

How do I transfer data from Excel to MySQL?

How do I add data to a MySQL database using Python?