Python Converting a CSV File to a MySQL Table

In this article, you will learn how to convert a CSV file to a MySQL table using the Python programming language.

The CSV "Comma-separated-values" file is a very common way to share information across several platforms. This is widely used in business and data based applications for data exchange. Most organisations are web-based, so there may also be a common need to transfer data from a spreadsheet or a CSV file to a database.

Suppose we have an 'employee' table in our MySQL database. You can either copy and paste this into your database OR use your existing data.

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_enabled` int(11) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `created_date`, `is_enabled`) VALUES
(1, 'John', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2323234543', '2019-06-05 15:36:07', 1),
(2, 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9898577442', '2019-05-14 15:36:07', 1),
(3, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9393452387', '2019-05-28 15:36:07', 1),
(4, 'Gaga', This email address is being protected from spambots. You need JavaScript enabled to view it.', '8482764537', '2019-06-29 15:36:07', 1);

Install PyMySQL module

PyMySQL is a Python MySQL client library. PyMySQL is an interface for connecting to a MySQL database server from Python. First, we need to install this library using the following command-

pip install pymysql

Once PyMySQL is installed, import this at the top. Next, we have written a function 'csv_to_mysql' to load a CSV file into a MySQL table. The variable 'load_query' contains the query statement to load the data.

Here is the complete code for converting a CSV file to a MySQL table using Python. Make sure to replace the 'hostname', 'username', 'password', and 'databasename' with your database credentials and name.

import pymysql
import sys

def csv_to_mysql(load_query, host, user, password):

    try:
        con = pymysql.connect(host=host,
                                user=user,
                                password=password,
                                autocommit=True,
                                local_infile=1)
        print('Connected to DB: {}'.format(host))
        # Create cursor and execute Load SQL
        cursor = con.cursor()
        cursor.execute(load_query)
        print('Succuessfully loaded the table from csv.')
        con.close()
        
    except Exception as e:
        print('Error: {}'.format(str(e)))
        sys.exit(1)


load_query = """LOAD DATA LOCAL INFILE 'employee.csv' INTO TABLE company.employee\
 FIELDS TERMINATED BY ',' ENCLOSED BY  '"' IGNORE 1 ROWS;"""
host = 'localhost'
user = 'root'
password = ''
csv_to_mysql(load_query, host, user, password)

In the above code, we have loaded the 'employee.csv' file, which has the following data.

When, you will run the above code, it loads the data in MySQL as follows-

Related Articles