How to perform MySQL database CRUD in Python

Hello everyone, welcome back to programminginpython.com. Here I am going to show you how to access MySQL database and perform all database operations on the database in Python. In most of the applications, you need a database to store, update, and manage your data. So here I will show you all the CRUD(Create, Read, Update, Delete) operations which can be performed on a database so basically we are doing a MySQL database CRUD operations in python. So let’s get started.

You can also watch this video on YouTube here

First, you need some module to connect to MySQL, So here I will install a module called `PyMySQL`, which is an interface to connect to a database from python. It implements the Python database API version 2.0. As I cover most of the tutorials in the site using Python 3, am using PyMySQL, for Python 2, there is another module called MySQLdb, which is not supported in Python 3.

Program on Github

You can install pyMySQL using either pip or easy_install for windows.

pip install PyMySQL 
# OR 
easy_install PyMySQL

Video Explanation

Check this video on YouTube here.

Ad:
Learn Python Programming Masterclass – Enroll Now.
Udemy

So before performing these database operations, I will first create a database named ‘python_tutorials’ in phpMyAdmin locally.

Create a MySQL database table in Python

First I will import the pyMySQL package, which I installed before.

import pymysql

Now I need to connect to the database, this pymysql has a function called connect('host', 'username', 'password', 'database_name') which takes 4 arguments.

db = pymysql.connect("localhost", "root", "", "python_tutorials")

Next, I will prepare a cursor to execute the SQL queries.

cursor = db.cursor()

So, now I can write queries like cursor.execute("SQL QUERY")

I will create a new database table by executing the following query,

sql = """CREATE TABLE PERSON (
   ID INT NOT NULL,
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   SEX CHAR(1),
   PRIMARY KEY (ID) )"""

cursor.execute(sql)

The above code creates a new table.

Finally, I will close the connection.

db.close()

Program on Github

Code:

__author__ = 'Avinash'

import pymysql

# Open database connection
db = pymysql.connect("localhost", "root", "", "python_tutorials")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS PERSON")

# Create table as per requirement
sql = """CREATE TABLE PERSON (
   ID INT NOT NULL,
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   SEX CHAR(1),
   PRIMARY KEY (ID) )"""

cursor.execute(sql)

# disconnect from server
db.close()

Inserting data into a table

Similar to creating a table, here also I will use a SQL query to insert data, but here  I also use try and except, so if any error or problem occurs while inserting the data, the whole operation can be rolled back.

sql = """INSERT INTO PERSON(ID, FIRST_NAME,
   LAST_NAME, AGE, SEX )
   VALUES (1, 'ABC', 'ABCD', 20, 'M')"""
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
except pymysql.Error:
    # Rollback in case there is any error
    db.rollback()

Update data in the table

This update operation is also similar to insert operation, I just will change my SQL query here. So I will change the age column value to 30 if the person is male(M)

sql = "UPDATE PERSON SET AGE = 30  WHERE SEX = '%c'" % ('M')
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

Program on Github

Read data from the table

For reading the data, I will use a simple query to get all data, but for showing the data, I will loop through all the data and fetch single rows data and print them.

sql = "SELECT * FROM PERSON"
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Fetch all the rows in a list of lists.
    results = cursor.fetchall()
    for row in results:
        fname = row[1]
        lname = row[2]
        age = row[3]
        sex = row[4]
        # Now print fetched result
        print("fname = %s,lname = %s,age = %d,sex = %s" % \
        (fname, lname, age, sex, ))
except:
   print("Error: unable to fetch data")

Delete data from the table

For deleting I will use a simple query with some condition to delete elements/rows from the table.

sql = "DELETE FROM PERSON WHERE AGE > '%d'" % (29)
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

So after running the above code, the only row in the table gets deleted as it satisfies the condition I gave i.e age > 29.

That’s it for the post and we performed MySQL database CRUD in Python. I hope now you learned how to perform CRUD operations on a DB and its tables.

Ad:
Learn Python Programming Masterclass – Enroll Now.
Udemy

Program on GitHub

Feel free to look at my other Python posts on GUI Programs, Math Programs and Basic Programs in Python.