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

Learn more about here: The Most Popular Databases In The World

 

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:

 

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 blog, I covered MySQL database operations (create database, create table, insert, display, update and delete) with Python and MySQL Connector driver.