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:

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>

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...

);

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

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.

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>

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.

Output:

Employee Aishwarya with empid=3 is deleted

arorapranay

Follow

5

Improve

Article Tags :

• Python
• Technical Scripter
• Technical Scripter 2022

Practice Tags :

• python