CRUD Operations in Python on MySQL

This blog demonstrates how to implement CRUD operations in Python language on a MySQL database.

Introduction

 
Python supports most of the popular databases to work with and implement CRUD operations. Some of the popular databases include:
  • MySQL
  • Oracle
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Sybase
 
Today, in this blog, we will see how to implement CRUD operations on MySQL.
 

MySQL Database

 
If you want to work with CRUD operations in Python, first of all, download and install MySQL Database. If you already have a database, skip this step.
 
Go to below link and download MySQL Database:
 
 

Install MySQL Connector Driver

 
Python needs MySQL Connector Driver to access a MySQL Database. Follow the below steps to install the drive
 
STEP 1:
 
Open Command Prompt and navigate your pip.exe folder path.
 
By default pip folder path is: C:\Users\Acer\AppData\Local\Programs\Python\Python37-32\Scripts
 
STEP 2:
 
Type the below command and press Enter:
 
C:\Users\Acer\AppData\Local\Programs\Python\Python37-32\Scripts>pip install mysql-connector-python
 
After successful installation, exit from the command prompt.
 
 

Create Database

 
The following code snippet creates a new database. If you already have a database, you may skip this step. The database is created on the local server. You may want to use your server name, user id, and password. 
 
To create a new database, we use CREATE Database SQL query.
 
The cursor() method creates a cursor object that is used to execute a SQL query by using the execute method. Once the database object is used, we need to close it.  
  1. import mysql.connector #Importing Connector package   
  2. mysqldb=mysql.connector.connect(host="localhost",user="root",password="")#established connection   
  3. mycursor=mysqldb.cursor()#cursor() method create a cursor object  
  4. mycursor.execute("create database dbpython")#Execute SQL Query to create a database    
  5. mysqldb.close()#Connection Close  

Create a table

 
The following code snippet creates a new database table using CREATE TABLE SQL query. 
  1. #Create a table into dbpython database  
  2. import mysql.connector  
  3. mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database   
  4. mycursor=mysqldb.cursor()#cursor() method create a cursor object  
  5. mycursor.execute("create table student(roll INT,name VARCHAR(255), marks INT)")#Execute SQL Query to create a table into your database  
  6. mysqldb.close()#Connection Close  

Insert Record

 
The INSERT INTO SQL query adds new records to the table. 
 
mysqldb.commit() method commits the changes to the database.  
  1. import mysql.connector  
  2. mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
  3. mycursor=mysqldb.cursor()#cursor() method create a cursor object    
  4. try:  
  5.    #Execute SQL Query to insert record  
  6.    mycursor.execute("insert into student values(1,'Sarfaraj',80),(2,'Kumar',89),(3,'Sohan',90)")  
  7.    mysqldb.commit() # Commit is used for your changes in the database  
  8.    print('Record inserted successfully...')   
  9. except:  
  10.    # rollback used for if any error   
  11.    mysqldb.rollback()  
  12. mysqldb.close()#Connection Close  

Display Record

 
The following code uses a SELECT * SQL query to select data from a database table. The resultset is stored using cursor.fetchall() method.  
  1. import mysql.connector  
  2. mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
  3. mycursor=mysqldb.cursor()#cursor() method create a cursor object  
  4. try:  
  5.    mycursor.execute("select * from student")#Execute SQL Query to select all record   
  6.    result=mycursor.fetchall() #fetches all the rows in a result set   
  7.    for i in result:    
  8.       roll=i[0]  
  9.       name=i[1]  
  10.       marks=i[2]  
  11.       print(roll,name,marks)  
  12. except:   
  13.    print('Error:Unable to fetch data.')  
  14. mysqldb.close()#Connection Close  
 
Note: If you want to fetch a single record then use fetchone() method
 

Update Record

 
The following code uses an UPDATE SQL query to update an existing record.  
  1. import mysql.connector  
  2. mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
  3. mycursor=mysqldb.cursor()#cursor() method create a cursor object  
  4. try:  
  5.    mycursor.execute("UPDATE student SET name='Ramu', marks=100 WHERE roll=1")#Execute SQL Query to update record
  6.    mysqldb.commit() # Commit is used for your changes in the database  
  7.    print('Record updated successfully...')   
  8. except:   
  9.    # rollback used for if any error  
  10.    mysqldb.rollback()  
  11. mysqldb.close()#Connection Close  

Delete Record

 
The following code uses a DELETE SQL query to delete a record from the table.  
  1. import mysql.connector   
  2. mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
  3. mycursor=mysqldb.cursor()#cursor() method create a cursor object   
  4. try:   
  5.    mycursor.execute("DELETE FROM student WHERE roll=3")#Execute SQL Query to detete a record   
  6.    mysqldb.commit() # Commit is used for your changes in the database  
  7.    print('Record deteted successfully...')  
  8. except:  
  9.    # rollback used for if any error  
  10.    mysqldb.rollback()  
  11. mysqldb.close()#Connection Close  

Summary

 
In this blog, I covered MySQL database operations (create database, create table, insert, display, update and delete) with Python and MySQL Connector driver.