How to Upload Data From Python to Mysql

Introduction

This instance will show you how to insert CSV data into MySQL database table directly. And then I am going to create insert SQL statement for inserting information individually or I volition also load the csv data directly into the MySQL table.

CSV ways Comma Separated Value, and so information technology'due south a plain text data. Each line of the file is information tape. 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 y'all demand to create database and tabular array in MySQL server.

You need to prepare csv file to take the fields in the aforementioned guild equally the MySQL table fields.

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

Prerequisites

Python 3.eight.3 – 3.ix.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 post-obit table student under roytuts database in MySQL server.

          CREATE Table `student` (   `student_id` int unsigned COLLATE utf8mb4_unicode_ci NOT Nil 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 Zippo,   `student_address` varchar(250) COLLATE utf8mb4_unicode_ci Non NULL,   Primary KEY (`student_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;        

Insert or Load CSV Data

Allow's see how you tin insert csv data row by row using INSERT statement.

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

          import csv import mysql.connector        

So 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.i', database='roytuts')  cur = conn.cursor()        

Adjacent I demand to open or read the csv file from electric 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 afterwards from the source lawmaking section.

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

While iterating if I have the header in the csv file and then I skip the kickoff row data from the csv file. The following code does the task. If your file does non accept headers then you tin can set skipHeader to Imitation.

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

In the post-obit example I will show y'all how to load or insert csv information straight into MySQL tabular array.

In the below lawmaking snippets I am loading csv file in which there is no header.

          query = "LOAD DATA INFILE 'C:/python/python-insert-csv-information-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 get-go row if I accept whatever header row data in the csv file.

          query = "LOAD Information INFILE 'C:/python/python-insert-csv-data-into-mysql/students-header.csv' INTO Tabular array 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.

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

Solution: Cheque what value is there in your database using MySQL client past executing the command SHOW VARIABLES Similar 'local_infile';.

if value for local_infile shows every bit OFF so you tin can set up using the command set global local_infile = ON;.

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

Solution: Cheque what value if there for this variable using the control SHOW VARIABLES Similar "secure_file_priv";.

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

Then open up my.ini file and add the post-obit line. my.ini file generally exists nether your root binder of your MySQL installation path.

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

Hope y'all got an idea how to insert CSV information in different ways using Python program.

Yous volition see the post-obit output when data get inserted into MySQL database:

Source Code

Download

alibuithe.blogspot.com

Source: https://roytuts.com/how-to-insert-csv-data-into-mysql-database-using-python-in-different-ways/