How to add data from CSV file to MySQL using Python
Tags: python, database, mysql, tutorial
Published: 2022-03-05 12:23:11

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cd21yjcg0hok0fgj9k7i.png)

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


![SQL Workbench](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/noi1c5nbh1v109ijzjpp.png)

---


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