Skip to content

WebNinjaDeveloper.com

Programming Tutorials




Menu
  • Home
  • Youtube Channel
  • Official Blog
  • Nearby Places Finder
  • Direction Route Finder
  • Distance & Time Calculator
Menu

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

Posted on December 27, 2022

 

 

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

 

 

Python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

 

 

1
2
3
4
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.

 

Recent Posts

  • React.js Twitter API Tutorial to Embed Profile & Timeline, Hashtags of User in Browser Using Javascript
  • Android Java Tutorial to Change Styles & Visibility of System Bars (Top, Action & Status) Full Example
  • Android Java Project to Display & Play Audio Files From Storage inside ListView Using MediaPlayer Class
  • Android Java Project to Build MP4 Video to MP3 Audio Converter Using MediaMuxer Class
  • Android Java Project to Merge Multiple PDF Documents From Gallery Using iTextPDF Library
  • Angular
  • Bunjs
  • C#
  • Deno
  • django
  • Electronjs
  • java
  • javascript
  • Koajs
  • kotlin
  • Laravel
  • meteorjs
  • Nestjs
  • Nextjs
  • Nodejs
  • PHP
  • Python
  • React
  • ReactNative
  • Svelte
  • Tutorials
  • Vuejs




©2023 WebNinjaDeveloper.com | Design: Newspaperly WordPress Theme