Posted by In this tutorial, we will learn to import CSV file data into a MySQL table using Python. We will read the CSV file, connect with the database and load the CSV data in the MySQL table. You will be required below package for connecting the database.
| 1 | pip install mysql-connector-python |
Steps to Import CSV file data into MySQL table using Python:
- Import the
mysql.connectandcsvmodule. - Connect with the database and select the database using
mysql.connectmethod. Please do not forget to change the database user, password, host, and database name. - To execute queries on the database table, we create a cursor object.
- Open the CSV file in read mode using the
open()function. - Read the CSV file data using
csv.readermethod. - Skip header next method.
- Run loop to read CSV data row-wise.
- Run and execute the insert query to insert data in the MySQL table. Please change the table name and field name. This will be changed by CSV headers and table fields name.
- Commit the query and close the cursor.
- Log in to your database, select table, and verify your inserted data from CSV file data.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | import mysql.connector as mysql import csv #Connect DB server and database conn = mysql.connect(user='root', password='root', host='localhost', database='test_db') cursor = conn.cursor() #open the csv file with open('users.csv', mode='r') as csv_file: #read csv using reader class csv_reader = csv.reader(csv_file) #skip header header = next(csv_reader) #Read csv row wise and insert into table for row in csv_reader: sql = "INSERT INTO users (name, mobile, email) VALUES (%s,%s,%s)" cursor.execute(sql, tuple(row)) print("Record inserted") conn.commit() cursor.close() |
Also, the code is committed on GitHub. You can check it out there.