CRUD operations in Python with MySQL

How to perform CRUD operations in python using MYSQL.

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.

What are CRUD Operations in Python using MySQL

In a MySQL database, there are four essential operations you can do utilizing MySQL

  • Create operation
  • Read operation
  • Update operation
  • Delete operation

We consider the above tasks the CRUD Operations. In this Python MySQL exercise, we will figure out how to do these four essential operations, i.e CRUD operation in MySQL with Python programming.

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.

Click here and download MySQL Database.

Install MySQL connector driver

Python needs MySQL Connector Driver to access a MySQL Database. Follow the steps to install the drive

  • Open Command Prompt and navigate your pip.exe folder path.

By default pip folder path is:

C:\Users\Dell\AppData\Local\Programs\Python\Python37-32\Scripts
  • Type the below command and press Enter:
C:\Users\Dell\AppData\Local\Programs\Python\Python37-32\Scripts>pip introduce mysql-connector-python

After successful installation, exit from the command prompt.

Implement CRUD Operations in Python using MySQL

So as to execute any of these four operations referenced above, you need the accompanying:

  • A module or connector to associate MySQL database with your Python program.
  • MySQL server username and password.
  • Database name

If you are as of now have a database and a table in it, at that point you are prepared to learn CRUD operation.

Here is the code to establish the connection in between Python program and MySQL server:

import mysql.connector 
mysqldb=mysql.connector.connect(host="localhost",user="root",password="")    
mycursor=mysqldb.cursor()  
mycursor.execute("create database dbpython")   
mysqldb.close()

The four basic CRUD Operations in MySQL using Python:

We will do the following operations in our table with the Python program:

  • Create data or insert data in MySQL table
  • Read or retrieve data from MySQL table
  • Update data in MySQL table

CRUD – Create, Read, Update, Delete

Create a table

By this operation, we can create data in our MySQL database table.

The following code snippet creates a new database table using CREATE TABLE SQL query.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")   
mycursor=mysqldb.cursor()
mycursor.execute("create table student(roll INT,name VARCHAR(255), marks INT)")  
mysqldb.close()

Insert data

The INSERT INTO SQL query adds new records to the table. .In simple words, we can insert data into our database table. This type of operation is known as create operation.

The MySQL Query for inserting data is: INSERT INTO table_name ( column1, column2, …. ) values ( val1, val2, …)

mysqldb.commit() method commits the changes to the database.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")  
mycursor=mysqldb.cursor()#cursor() method create a cursor object    
try:    
   mycursor.execute("insert into student values(1,'Sorit',80),(2,'Kumar',89),(3,'Susmita',90)")  
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record inserted successfully...')   
except:     
   mysqldb.rollback()  
mysqldb.close()

Read data

This operation is as simple as the name of the operation. By using this operation we can read data from a MySQL table. Or you can say we can fetch or retrieve data from MySQL table. We call this operation a Read operation.

The MySQL Query to read data is: SELECT * FROM table_name

The following code uses a SELECT * SQL query to select data from a database table. The resultset is stored using cursor.fetchall() method.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")  
mycursor=mysqldb.cursor() 
try:  
   mycursor.execute("select * from student")  
   result=mycursor.fetchall()   
   for i in result:    
      roll=i[0]  
      name=i[1]  
      marks=i[2]  
      print(roll,name,marks)  
except:   
   print('Error:Unable to fetch data.')  
mysqldb.close()

#output
#1 Sorit 80
#2 Kumar 89
#3 Susmita 90

If you want to fetch a single record then use fetchone() method.

Update data

Using this operation, we can update the values in a MySQL database table. With this operation, we modify our database table data. 

MySQL query for updating the data in a table is: UPDATE table_name SET column_name=’new value’ where column_name=’value’

The following code uses an UPDATE SQL query to update an existing record.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython") 
mycursor=mysqldb.cursor()
try:  
   mycursor.execute("UPDATE student SET name='Ramu', marks=100 WHERE roll=1")
   mysqldb.commit()   
   print('Record updated successfully...')   
except:     
   mysqldb.rollback()  
mysqldb.close()#Connecti

Delete data

To delete any data from MySQL table you can use this operation. So this is known as a delete operation. Delete operation can be easily done with Python.

Query for delete operation in MySQL: DELETE FROM table_name

The following code uses a DELETE SQL query to delete a record from the table.

import mysql.connector   
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython") 
mycursor=mysqldb.cursor()   
try:   
   mycursor.execute("DELETE FROM student WHERE roll=2")   
   mysqldb.commit()  
   print('Record deteted successfully...')  
except:    
   mysqldb.rollback()  
mysqldb.close()