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.
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
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 = 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)
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: