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:

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'}]

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

Step [2]: Assign file path

Make sure to keep you csv file in current working directory

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

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]})

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

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',

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")

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

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)

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

This will disconnect the connection to MySQL server

mydatabase.close()

---

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

Comments

[calvin mubata]

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