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()