How to insert CSV data into MySQL database using Python in different ways

Introduction

This example will show you how to insert CSV data into MySQL database table directly. So we are going to create insert SQL statement for inserting data individually or we will also load the csv data directly into the MySQL table.

CSV means Comma Separated Value, so it’s a plain text data. Each line of the file is data record. Each record consists of one or more fields separated by commas. Each line of data record is terminated by new line.

To insert csv data into MySQL database table you need to create database and table in MySQL server.

You need to prepare csv file to have the fields in the same order as the MySQL table fields.

You need to remove the header row from the csv file so that it contains only data. If you are really using header for your csv data then you can use IGNORE 1 ROWS in the MySQL command while loading your file for inserting data to ignore the first record data (header) from your csv file.

Prerequisites

Python 3.8.3, MySQL 8.0.17, mysql-connector-python

How to connect to MySQL database using Python

MySQL Table

We have the following table under roytuts database in MySQL server.

CREATE TABLE `student` (
  `student_id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) NOT NULL,
  `student_dob` varchar(10) NOT NULL,
  `student_email` varchar(80) NOT NULL,
  `student_address` varchar(250) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Insert or Load CSV Data

Let’s see how we can insert csv data row by row using INSERT statement.

The first things would be to import the required modules for working with csv file and connecting with MySQL from Python.

import csv
import mysql.connector

Then you need to establish the database connection. Make sure you change the database details according to your settings.

conn = mysql.connector.connect(user='root', password='root', host='127.0.0.1', database='roytuts')

cur = conn.cursor()

Next we open or read the csv file from current directory. Then we iterate over each row and build the MySQL insert query to insert csv data into table. The sample files can be downloaded later from the source code section.

file = open('students.csv')
csv_data = csv.reader(file)

While iterating if we have the header in the csv file then we skip the first row data from the csv file. The following code does the job. If your file does not have headers then you can set skipHeader to False.

skipHeader = True

for row in csv_data:
	if skipHeader:
		skipHeader = False
		continue
	cur.execute('INSERT INTO student(student_id, student_name, student_dob, student_email, student_address)' 'VALUES(%s, %s, %s, %s, %s)', row)

In the following example we will see how to load or insert csv data directly into MySQL table.

In the below code snippets we are loading csv file in which there is no header.

query = "LOAD DATA INFILE 'C:/python/python-insert-csv-data-into-mysql/students.csv' INTO TABLE student FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (student_id, student_name, student_dob, student_email, student_address)"

cur.execute(query)

In this example also we are ignoring the first row if we have any header row data in the csv file.

query = "LOAD DATA INFILE 'C:/python/python-insert-csv-data-into-mysql/students-header.csv' INTO TABLE student FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (student_id, student_name, student_dob, student_email, student_address)"

cur.execute(query)

Issues

You may face the following issues during inserting or loading csv file data directly into the table without creating any insert statement.

Problem: mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version

Solution: Check what value is there in your database using MySQL client by executing the command SHOW VARIABLES LIKE 'local_infile';.

if value for local_infile shows as OFF then you can set using the command set global local_infile = ON;.

Problem: The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Solution: Check what value if there for this variable using the command SHOW VARIABLES LIKE "secure_file_priv";.

If it shows NULL then you can disable security for file in my.ini file.

So open my.ini file and add the following line. my.ini file generally exists under your root folder of your MySQL installation path.

[mysqld]
...
secure-file-priv = ""

Source Code

Download

Thanks for reading.

Related posts

Leave a Comment