Open In App

CRUD Operation in Python using MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we will be seeing how to perform CRUD (CREATE, READ, UPDATE and DELETE) operations in Python using MySQL. For this, we will be using the Python MySQL connector. For MySQL, we have used Visual Studio Code for python.

Before beginning we need to install the MySQL connector with the command: 

pip install mysql-connector-python-rf

In order to perform CRUD operations we need to have a database and a table. We shall first create the database.

IWe are going to create an employee database named employee_db and a table named tblemployee which consists of the following columns:

Column name

Data type

Description

empid

INT

Stores the employee id and has auto-increment i.e. increments every time a record is added

empname

VARCHAR(45)

Stores the employee’s name

department

VARCHAR(45)

Stores the department to which the employee belongs i.e. accounts, HR.

salary

INT

Stores the salary of the employees.

Creating Database

After we have connected the MySQL server using the connector, we will create a cursor object and then pass the SQL command using the execute function. 

Syntax for creating the Database:

CREATE DATABASE <DATABASE_NAME>

Python




# Python implementation to create a Database in MySQL
import mysql.connector
 
# connecting to the mysql server
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password"
)
 
# cursor object c
c = db.cursor()
 
# executing the create database statement
c.execute("CREATE DATABASE employee_db")
 
# fetching all the databases
c.execute("SHOW DATABASES")
 
# printing all the databases
for i in c:
    print(i)
c = db.cursor()
 
# finally closing the database connection
db.close()


Output:

Note the employee_db on the 2nd row.

Creating Table

Now in order to create the table, we use the create table command. It is recommended to always keep a primary key which in this case is empid and helps to uniquely identify the employees. 

The general syntax to create a table is:

CREATE TABLE
(
    column1 column1_data_type,
    column2 column2_data_type,
    column3 column3_data_type...

);

Python3




# Python implementation to create a table in MySQL
import mysql.connector
 
# connecting to the mysql server
 
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password",
    database="employee_db"
)
 
# cursor object c
c = db.cursor()
 
# create statement for tblemployee
employeetbl_create = """CREATE TABLE `employee_db`.`tblemployee` (
  `empid` INT NOT NULL AUTO_INCREMENT,
  `empname` VARCHAR(45) NULL,
  `department` VARCHAR(45) NULL,
  `salary` INT NULL,
   PRIMARY KEY (`empid`))"""
 
c.execute(employeetbl_create)
 
c = db.cursor()
 
# fetch tblemployee details in the database
c.execute("desc tblemployee")
 
# print the table details
for i in c:
    print(i)
 
 
# finally closing the database connection
db.close()


Output:

tblemployee details are printed

Inserting Data

Inserting the data into tables is a crucial part, it is required to make sure there is no mismatch of data i.e. the data type being sent should match the data type of the particular column. 

The general syntax for insert statements:

INSERT INTO <TABLE_NAME> (column1,column2,column3...) VALUES (data1,data2,data3...);

We will be inserting multiple rows at one type, however, you can even insert one row at a time. After writing the insert statement, we will be creating a list or collections of row data to be passed. This is to be created right before the executed query. 

Since multiple rows will be sent together, we need to use the executemany() function instead of execute().

Python3




# Python implementation to insert data into a table in MySQL
import mysql.connector
 
# connecting to the mysql server
 
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password",
    database="employee_db"
)
 
# cursor object c
c = db.cursor()
 
# insert statement for tblemployee
# this statement will enable us to insert multiple rows at once.
employeetbl_insert = """INSERT INTO tblemployee (
   empname,
   department,
   salary)
   VALUES  (%s, %s, %s)"""
 
# we save all the row data to be inserted in a data variable
data = [("Vani", "HR", "100000"),
        ("Krish", "Accounts", "60000"),
        ("Aishwarya", "Sales", "25000"),
        ("Govind", "Marketing", "40000")]
 
# execute the insert commands for all rows and commit to the database
c.executemany(employeetbl_insert, data)
db.commit()
 
# finally closing the database connection
db.close()


NOTE: To verify the data has been inserted successfully we will have a look at the table in Microsoft workbench and later use the select statement via python itself i.e. to READ

Data inserted successfully

Reading / Selecting Data

Reading/Selecting or fetching data from the database follows the command:

SELECT * FROM <TABLE_NAME>

This command fetches all columns/attributes from the table. 

However at times, one may need to fetch only certain columns. For that we run the following command:

SELECT COLUMN1,COLUMN2... FROM <TABLE_NAME>

Let us look at the implementation for selecting all columns.

Python3




# Python implementation to fetch data from a table in MySQL
import mysql.connector
 
# connecting to the mysql server
 
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password",
    database="employee_db"
)
 
# cursor object c
c = db.cursor()
 
# select statement for tblemployee which returns all columns
employeetbl_select = """SELECT * FROM tblemployee"""
 
# execute the select query to fetch all rows
c.execute(employeetbl_select)
 
# fetch all the data returned by the database
employee_data = c.fetchall()
 
# print all the data returned by the database
for e in employee_data:
    print(e)
 
# finally closing the database connection
db.close()


Output:

Employee records fetched from tblemployee

Updating Data

Updating data is changing the existing values in the database. Let’s say in our employee records, the salary of an employee named “Vani” has been increased by 15% hence the salary in the records should become 115000. Hence we will run the update query whose general syntax is:

UPDATE <TABLE_NAME> SET <COLUMN_NAME> = <VALUE> WHERE <PRIMARY KEY NAME> =<PRIMARY KEY VALUE>

Python3




# Python implementation to update data of a table in MySQL
import mysql.connector
 
# connecting to the mysql server
 
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password",
    database="employee_db"
)
 
# cursor object c
c = db.cursor()
 
# update statement for tblemployee
# which modifies the salary of Vani
employeetbl_update = "UPDATE tblemployee\
SET salary = 115000 WHERE empid = 1"
 
# execute the update query to modify
# the salary of employee with
# employee id = 1 and commit to the database
c.execute(employeetbl_update)
db.commit()
 
# finally closing the database connection
db.close()


Output:

The salary of employee id 1 has been updated

Deleting Data

Deleting data from tables has to be done with utmost care as it can lead to the loss of important data at times. Often a soft delete is performed where there is an extra column named “active” whose values are 1 or 0 only. 1 means present in the table and 0 means deleted from being displayed i.e. it is still recoverable but not shown or acted upon.

 However, in this tutorial, we will be performing a regular or hard delete whose general syntax is:

DELETE FROM <TABLE_NAME> WHERE <PRIMARY KEY NAME> = <PRIMARY KEY VALUE>

The where clause doesn’t necessarily have to be the primary key.

We will be deleting Aishwarya(empid=3) from our records.

Python3




import mysql.connector
 
# connecting to the mysql server
 
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="password",
    database="employee_db"
)
 
# cursor object c
c = db.cursor()
 
# delete statement for tblemployee
# which deletes employee Aishwarya having empid 3
employeetbl_delete = "DELETE FROM tblemployee WHERE empid=3"
 
# execute the delete statement and commit to the database
c.execute(employeetbl_delete)
db.commit()
 
# finally closing the database connection
db.close()


Output:

Employee Aishwarya with empid=3 is deleted



Last Updated : 09 Dec, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads