Python Script to Load Data in MySQL

Python programming language provides modules to connect to different databases, and run SQL queries on them. Often you may need to load data into your database via your python script or application. In this article, we will learn how to create a python script to load data in MySQL.

Python Script to Load Data in MySQL

There are several python libraries to work with MySQL database. We will MySQLdb which is the most popular one.

1. Install MySQLdb

Open terminal and run the following command to install MySQLdb package on your system.

#ubuntu/debian
$ sudo apt-get install python-pip python-dev libmysqlclient-dev

#centos/fedora/suse
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

Once you have installed the above pre-requisite libraries, run the following command to install MySQLdb.

$ pip install mysqlclient

If you are using python<2.7, run the following command.

$ pip install MySQL-python

2. Create MySQL database & table

Log into MySQL to create a database mydb and a table csv_data in this database.

$ sudo mysql -uroot -p

Once you have logged in, run the following queries to create your database and table.

mysql> create database mydb;
mysql> create table csv_data(id int, name varchar(255);

When you define the MySQL table above, please make sure that it has the same columns as your csv data.

3. Import Required Libraries

For our example, we will load a simple csv file data.csv into MySQL table csv_data. Create an empty python script.

$ sudo vi load_csv.py

Add the following lines to it to import newly installed MySQLdb for making database connection, and inbuilt csv module to read csv files.

import csv
import MySQLdb

4. Connect to MySQL database

Next, add the following lines to connect to your MySQL database. Replace <hostname>, <username>, <password>, <database> with your database’s hostname, username, password and database name respectively.

mydb = MySQLdb.connect(host='<hostname>',
    user='<username>',
    passwd='<password>',
    db='<database>')
cursor = mydb.cursor()

We will obtain a connection object after successful connection. We will use its cursor object for running SQL queries.

5. Insert data into MySQL

Next, we will open data.csv file, loop through it line by line and insert it into our MySQL table.

csv_data = csv.reader(file('data.csv'))
for row in csv_data:

    cursor.execute('INSERT INTO data_csv(id, name )' \
          'VALUES("%s", "%s")', 
          row)
#close the connection to the database.
mydb.commit()
cursor.close()
print "Done"

In the above code, please make sure that you have correctly mentioned the table name, column names and csv filename highlighted above.

We use csv.reader() to get a file pointer to our csv data. Then we use for loop to go through its line, one by one. In each iteration, we use cursor.execute() function to run an INSERT query to insert 1 row of csv file’s data into our MySQL table. After all lines have been added, we call commit() function to commit the transactions. Finally, we close the database connection using cursor.close() function.

Here is the complete code for your reference.

import csv
import MySQLdb

mydb = MySQLdb.connect(host='<hostname>',
    user='<username>',
    passwd='<password>',
    db='<database>')
cursor = mydb.cursor()

csv_data = csv.reader(file('data.csv'))
for row in csv_data:

    cursor.execute('INSERT INTO data_csv(id, name )' \
          'VALUES("%s", "%s")', 
          row)
#close the connection to the database.
mydb.commit()
cursor.close()
print "Done"

In this article, we have learnt how to load csv data into MySQL database. You can modify it or embed it in your application/website, as per your requirements.

Python Script to Load Data in MySQL