How to Implement CRUD operations in Python on MySQL

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

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

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: https://www.mysql.com/downloads/

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.

import mysql.connector #Importing Connector package   
mysqldb=mysql.connector.connect(host="localhost",user="root",password="")#established connection   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create database dbpython")#Execute SQL Query to create a database    
mysqldb.close()#Connection Close  

Create a table

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

#Create a table into dbpython database  
import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create table student(roll INT,name VARCHAR(255), marks INT)")#Execute SQL Query to create a table into your database  
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.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object    
try:  
   #Execute SQL Query to insert record  
   mycursor.execute("insert into student values(1,'Sarfaraj',80),(2,'Kumar',89),(3,'Sohan',90)")  
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record inserted successfully...')   
except:  
   # rollback used for if any error   
   mysqldb.rollback()  
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.

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
try:  
   mycursor.execute("select * from student")#Execute SQL Query to select all record   
   result=mycursor.fetchall() #fetches all the rows in a result set   
   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()#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.

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

Delete Record

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")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object   
try:   
   mycursor.execute("DELETE FROM student WHERE roll=3")#Execute SQL Query to detete a record   
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record deteted successfully...')  
except:  
   # rollback used for if any error  
   mysqldb.rollback()  
mysqldb.close()#Connection Close  

Summary

In this post, I covered MySQL database operations (create database, create table, insert, display, update and delete) with Python and MySQL Connector driver. Thank you for reading!

#Python #MySQL #database #python