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 (1)

Collapse
 
calvin_mubata_a48a38c0f8a profile image
calvin mubata

csv_reader = csv.reader(csv_reader)Open an interactive python shell in this frame
AttributeError: 'function' object has no attribute 'reader'