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.
  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.