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 I am going to create insert SQL statement for inserting data individually or I 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 – 3.9.1, MySQL 8.0.17 – 8.0.22, mysql-connector-python (pip install mysql-connector-python)

How to connect to MySQL database using Python

MySQL Table

I have created the following table student under roytuts database in MySQL server.

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

Insert or Load CSV Data

Let’s see how you 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 I need to open or read the csv file from current directory. Then I 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 I have the header in the csv file then I 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 I will show you how to load or insert csv data directly into MySQL table.

In the below code snippets I am 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 I am ignoring the first row if I 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 = ""

Hope you got an idea how to insert CSV data in different ways using Python program.

You will see the following output when data get inserted into MySQL database:

Source Code

Download