Python 3 Script to Import CSV File Data into MySQL Table in Command Line

Welcome folks today in this blog post we will be importing csv into mysql table using python in terminal. All the full source code of the application is shown below.

Get Started

In order to get started you need to install the below library using the pip command as shown below

pip install mysqlclient

After you install this library you need to start the xammp control panel and start the apache server and mysql server as well

Now you need to create the table inside the phpmyadmin as shown below

As you can see we have the four fields inside the user table. ID is the primary key of the table and then we are storing the name,age and country of the user.

Now just make an app.py file inside the root directory and copy paste the following code

app.py

import csv
import MySQLdb

# Open the CSV file
with open('file.csv', 'r') as file:
    # Create a CSV reader object
    reader = csv.reader(file)

    # Connect to the MySQL database
    connection = MySQLdb.connect(host='localhost', user='root', password='', db='exceldb')

    # Create a cursor object
    cursor = connection.cursor()

    # Iterate over the rows of the CSV file
    for row in reader:
        # Insert the data into the MySQL table
        cursor.execute('INSERT INTO users (Name,Age,Country) VALUES (%s, %s, %s)', row)

    # Commit the transaction
    connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()

import csv

import MySQLdb

# Open the CSV file

with open('file.csv', 'r') as file:

# Create a CSV reader object

reader = csv.reader(file)

# Connect to the MySQL database

connection = MySQLdb.connect(host='localhost', user='root', password='', db='exceldb')

# Create a cursor object

cursor = connection.cursor()

# Iterate over the rows of the CSV file

for row in reader:

# Insert the data into the MySQL table

cursor.execute('INSERT INTO users (Name,Age,Country) VALUES (%s, %s, %s)', row)

# Commit the transaction

connection.commit()

# Close the cursor and connection

cursor.close()

connection.close()

As you can see we have imported the library of mysql at the very top. And then we are reading the csv file which will be stored inside the same directory and for reading it we are using the reader() method of the csv module. And then we are making the connection to the mysql database. And then we are using the cursor object to insert the data inside the table using the INSERT statement of the mysql database.

Now you need to create the file.csv which will hold sample data records as shown below

file.csv

Gautam,34,India
John,24,New Zealand
Harris,25,USA
Harry,56,England

Gautam,34,India

John,24,New Zealand

Harris,25,USA

Harry,56,England

Now execute the app.py file in terminal as shown below

python app.py

As you can see the data from the csv file is successfully imported into the mysql table as shown above.