DEV Community

Rahul Kumar
Rahul Kumar

Posted on

How to add data from CSV file to MySQL using Python

For this tutorial my agenda is:

  1. Fetch data from CSV file
  2. Add data to a list
  3. Insert list data into a MySQL database

This is my CSV file:

data.csv

Now, I will write a code to first add this data into a list which has dictionaries like this,

dict_list = [{'empid': '1', 'name': 'john', 'phone': '5123'}, {'empid': '2', 'name': 'jane', 'phone': '5124'}, {'empid': '3', 'name': 'doe', 'phone': '5678'}, {'empid': '4', 'name': 'adam', 'phone': '7427'}]
Enter fullscreen mode Exit fullscreen mode

So, let's get started with today's tutorial. Make sure to have data.csv file into the same folder as your python file.
I assume that you have mysql up and running.


Step [1]: Import Required Modules

In this step I will import important modules

import pathlib
import csv
import mysql.connector
Enter fullscreen mode Exit fullscreen mode

Step [2]: Assign file path

Make sure to keep you csv file in current working directory

csv_path = pathlib.Path.cwd() / "data.csv"
Enter fullscreen mode Exit fullscreen mode

Step [3]: Fetch data from the csv file and add to dictionary

This will create a list with dictionaries as shown above

dict_list = list()
with csv_path.open(mode="r") as csv_reader:
    csv_reader = csv.reader(csv_reader)
    for rows in csv_reader:
        dict_list.append({'empid':rows[0], 'name':rows[1], 'phone':rows[2]})
Enter fullscreen mode Exit fullscreen mode

Step [4]: Add a MySQL connection and cursor

Using connection we connect to database and cursor will help performing operations on it

mydatabase = mysql.connector.connect(
    username = 'root',
    password = 'adminuser',
    host = 'localhost',
)

mycursor = mydatabase.cursor()
Enter fullscreen mode Exit fullscreen mode

Step [5]: Create a database and assign it to connection

We're making a database name "myempdb"

mycursor.execute("CREATE DATABASE myempdb")
# add below line inside your connect method 
# database = 'myempdb',
Enter fullscreen mode Exit fullscreen mode

Step [6]: Create table 'emptable' in myempdb

The table will contains 3 rows "empid", "name", "phone"

mycursor.execute("CREATE TABLE emptable (empid INT PRIMARY KEY, name VARCHAR(255), phone INT")
Enter fullscreen mode Exit fullscreen mode

Step [7]: Adding data to database

Add list_dict data to database using loop and closing database

for item in dict_list:
    sql = "INSERT INTO emptable(empid, name, phone) VALUES (%s, %s, %s)"
    val = item['empid'], item['name'], item['phone']
    mycursor.execute(sql, val)
mydatabase.commit()
Enter fullscreen mode Exit fullscreen mode

Step [8]: Fetch the data and display it on terminal

This will show all the data stored in "emptable" into terminal

mycursor.execute('SELECT * FROM emptable')
myresult = mycursor.fetchall()
for x in myresult:
    print(x)
Enter fullscreen mode Exit fullscreen mode

Step [9]: Fetch the data and display it on terminal

This will disconnect the connection to MySQL server

mydatabase.close()
Enter fullscreen mode Exit fullscreen mode

SQL Workbench


Final code

# fetch data from csv file

import pathlib
import csv
import mysql.connector

csv_path = pathlib.Path.cwd() / "data.csv"

dict_list = list()
with csv_path.open(mode="r") as csv_reader:
    csv_reader = csv.reader(csv_reader)
    for rows in csv_reader:
        dict_list.append({'empid':rows[0], 'name':rows[1], 'phone':rows[2]})


mydatabase = mysql.connector.connect(
    username = 'root',
    password = 'adminuser',
    host = 'localhost',
    database = 'myempdb',
)

mycursor = mydatabase.cursor()

mycursor.execute("CREATE DATABASE myempdb")

mycursor.execute("CREATE TABLE emptable (empid INT PRIMARY KEY, name VARCHAR(255), phone INT")

for item in dict_list:
    sql = "INSERT INTO emptable(empid, name, phone) VALUES (%s, %s, %s)"
    val = item['empid'], item['name'], item['phone']
    mycursor.execute(sql, val)
mydatabase.commit()


mycursor.execute('SELECT * FROM emptable')
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

mydatabase.close()
Enter fullscreen mode Exit fullscreen mode

Top comments (0)