CRUD Operations in Python on MySQL

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

5 Comments

Join the conversation! Your thoughts help the community grow.

Ri Ch6y ago
Why dont' you use SQLAlchemy, https://pythongrid.com also does CRUD. It can work both MySQL and Postgres and others leveraging SQLAlchemy.
- Md Sarfaraj5y ago
  Thank you for sharing your suggestion, I will check it.
Md Sarfaraj7y ago
I want to thanks c# corner editor that change and add some content and make it a beautiful blog. Thanks C# Corner team
Elavarasan R7y ago
Nice article...
- Md Sarfaraj7y ago
  Thanks Elavarasan R