For this tutorial my agenda is:
- Fetch data from CSV file
- Add data to a list
- 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()
Top comments (1)
csv_reader = csv.reader(csv_reader)Open an interactive python shell in this frame
AttributeError: 'function' object has no attribute 'reader'