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
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.