In this article, we will be seeing how to perform CRUD (CREATE, READ, UPDATE and DELETE) operations inPython usingMySQL.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 namedemployee_db and a table namedtblemployee 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 MySQLimportmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password")# cursor object cc=db.cursor()# executing the create database statementc.execute("CREATE DATABASE employee_db")# fetching all the databasesc.execute("SHOW DATABASES")# printing all the databasesforiinc:print(i)c=db.cursor()# finally closing the database connectiondb.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 aprimary 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 MySQLimportmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password",database="employee_db")# cursor object cc=db.cursor()# create statement for tblemployeeemployeetbl_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 databasec.execute("desc tblemployee")# print the table detailsforiinc:print(i)# finally closing the database connectiondb.close()Output:
tblemployee details are printedInserting Data
Inserting the data into tablesis 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 MySQLimportmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password",database="employee_db")# cursor object cc=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 variabledata=[("Vani","HR","100000"),("Krish","Accounts","60000"),("Aishwarya","Sales","25000"),("Govind","Marketing","40000")]# execute the insert commands for all rows and commit to the databasec.executemany(employeetbl_insert,data)db.commit()# finally closing the database connectiondb.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. toREAD
Data inserted successfullyReading / 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 MySQLimportmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password",database="employee_db")# cursor object cc=db.cursor()# select statement for tblemployee which returns all columnsemployeetbl_select="""SELECT * FROM tblemployee"""# execute the select query to fetch all rowsc.execute(employeetbl_select)# fetch all the data returned by the databaseemployee_data=c.fetchall()# print all the data returned by the databaseforeinemployee_data:print(e)# finally closing the database connectiondb.close()
Output:
Employee records fetched from tblemployeeUpdating 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 MySQLimportmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password",database="employee_db")# cursor object cc=db.cursor()# update statement for tblemployee# which modifies the salary of Vaniemployeetbl_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 databasec.execute(employeetbl_update)db.commit()# finally closing the database connectiondb.close()
Output:
The salary of employee id 1 has been updatedDeleting 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.
Python3importmysql.connector# connecting to the mysql serverdb=mysql.connector.connect(host="localhost",user="root",passwd="password",database="employee_db")# cursor object cc=db.cursor()# delete statement for tblemployee# which deletes employee Aishwarya having empid 3employeetbl_delete="DELETE FROM tblemployee WHERE empid=3"# execute the delete statement and commit to the databasec.execute(employeetbl_delete)db.commit()# finally closing the database connectiondb.close()
Output:
Employee Aishwarya with empid=3 is deleted