Python MySQL- A Small CRUD Application

Developing applications in Python can involve use of database to store data. If you are looking forward to develop web applications using Python then you have to understand using DB as backend and Python to implement application logic. So, it is important to understand Python MySQL interaction. If you understand this, you can easily learn Flask framework for web application development in Python.

The concept of using Python MySQL is not different from creating Web Applications in PHP MySQL. It is similar to use any RDBMS with other frameworks or web application development language. The key concepts involved are-

Connection- is a facility to make a program and DB communicate to send data to DB for storage and accessing the stored data.
Query/Command- It is the task to be done on database like inserting a record, updating an existing record, deletion of a record or displaying stored data
Cursor- can be understood as a pointer to represent data fetched from database into a form understandable by the program

Making a database application in Python MySQL

The first step is to install Python MySQL connector module in your Python installation. For that you can use the pip install command in command prompt.

python -m pip install mysql-connector

Note: This is for Windows OS. You can find the command for your OS here

After installing MySQL connector you are ready to develop this small CRUD application in Python. Let’s Begin!!!

Create the database in MySQL

Run the WAMP/XAMPP/LAMP control panel
Start Apache and MySQL services.
Go to phpMyAdmin interface
Create a new database and a table where you want to store the data.

We have created a database bikerentdb and a table customer with this structure.

CREATE TABLE `customer` (   `CustID` int(11) NOT NULL,   `CustName` varchar(50) NOT NULL,   `CustAddress` varchar(200) DEFAULT NULL,   `CustPhone` varchar(20) DEFAULT NULL,   `CustEmail` varchar(50) DEFAULT NULL,   `CustCity` varchar(50) DEFAULT NULL )

You can copy this CREATE statement and execute it in the SQL tab in phpMyAdmin Interface

Import the mysql.connector Module

Write the import statement at the top of your code.

#import the module
import mysql.connector

Make Python MySQL connection

We will do this in the main function of our program. Create a variable con and call method mysql.connector.connect.

Its Syntax of this method is

Con-variable= mysql.connector.connect( host=”Host-name”, user=”username”, passwd=”password”, database=”databasename” )

This is the main function code. In this we are using a while loop that displays a menu of all actions that you can do with the customer table. You can type one number from option after the prompt to do the activity. Make sure the main fucntion is at the end of all the functions that we are going to discuss next.

def main():
    #make connection to database using localhost, root as username, no password so "" and database name
    con = mysql.connector.connect( 
            host="localhost",
            user="root",
            passwd="",
            database="bikerentdb"
            )
    #opne cursor
    cur = con.cursor()
    ch=0
    #diaplay menu until user presses 5
    while(ch<=4):
        #menu options
        print("1. INSERT")
        print("2. UPDATE")
        print("3. DELETE")
        print("4. DISPLAY")
        print("5. EXIT")
        # ask user to enter what he wants to do
        ch=int(input("Enter Your choice:"))
        #call relevant fucntions defined above
        if (ch==1):
            insert(con, cur)
        if (ch==2):
            update(con, cur)
        if (ch==3):
            delete(con, cur)
        if  (ch==4):
            display(cur)
        
#call main
main()

Insert Data into Table

For this activity, you need an open connection and a cursor that is ready to accept an SQL command to be executed. An SQL DML statement can be executed by calling execute function on the opened cursor.

Syntax to open a cursor is

Cur-variable = Con-variable.cursor()

To execute a statement defined as a string with the cursor is

Cur-variable.execute(sql-string)

The complete code for Insertion using INSERT statement is defined as a function insert() to be called when required.

def insert(con,cur):
    #read values to be inserted
    cid=input("Enter customer ID:")
    cnm=input("Enter customer Name:")
    cad=input("Enter customer Address:")
    cph=input("Enter customer Phone:")
    cem=input("Enter customer Email:")
    cct=input("Enter customer City:")
    #create the Insert query
    sql = "INSERT INTO customer (CustID, CustName, CustAddress,CustPhone, CustEmail, CustCity) VALUES (%s, %s,%s, %s,%s, %s)"
    #create list of values typed from user to insert in customer table
    val = (cid,cnm,cad,cph,cem,cct)
    #Execute query with values
    cur.execute(sql, val)
    #commit for permanent storage in database
    con.commit()
    #display success message
    print(cur.rowcount, "Record inserted.")

Update Data in Table

An existing record can be updated with UPDATE SQL statement. In this application you will update a record for a given customer ID.

def update(con,cur):
    #read values to be updated
    cid=input("Enter customer ID:")
    cnm=input("Enter customer Name:")
    cad=input("Enter customer Address:")
    cph=input("Enter customer Phone:")
    cem=input("Enter customer Email:")
    cct=input("Enter customer City:")
    #create update query
    sql = "update customer set CustName='"+cnm+"', CustAddress='"+cad+"',CustPhone='"+cph+"', CustEmail='"+cem+"', CustCity='" +cct+"' where CustID="+cid
    #Execute Update query on opened cursor
    cur.execute(sql)
    #commit Changes to DB
    con.commit()
    #display success message
    print(cur.rowcount, "Record updated.")

Delete Record in Table

An existing record can be deleted with DELEET SQL statement. In this application you will delete a record for a given customer ID.

def delete(con,cur):
    #read the customer ID for which record to be deleted
    cid=input("Enter customer ID to delete:")
    #Create Delete Query
    sql = "delete FROM customer where CustID = '"+cid+"'"
    #execute delete query
    cur.execute(sql)
    #commit changes to DB
    con.commit()
    #display success message
    print(cur.rowcount, "Record deleted.")

Display all Records in Table

All the records saved in the table can be displayed by SELECT SQL statement. They are fetched with fetchAll() function and stored in variable that considers each row as a tuple of column values.

def display(cur):
    #Execute SELECT statement 
    cur.execute("SELECT * FROM customer")
    #Fetch all records from table
    res = cur.fetchall()
    #print
    print("------------------------------------------------------------------------")
    print("CustID   CustName    CustAddress     CustCity   CustPhone   CustEmail")
    print("------------------------------------------------------------------------")
          
    for x in res:
        print(str(x[0])+"  "+x[1]+"  "+x[2]+"  "+x[5]+"  "+x[3]+"  "+x[4])
    print("------------------------------------------------------------------------")

Complete Code

#import the module
import mysql.connector

def insert(con,cur):
    #read values to be inserted
    cid=input("Enter customer ID:")
    cnm=input("Enter customer Name:")
    cad=input("Enter customer Address:")
    cph=input("Enter customer Phone:")
    cem=input("Enter customer Email:")
    cct=input("Enter customer City:")
    #create the Insert query
    sql = "INSERT INTO customer (CustID, CustName, CustAddress,CustPhone, CustEmail, CustCity) VALUES (%s, %s,%s, %s,%s, %s)"
    #create list of values typed from user to insert in customer table
    val = (cid,cnm,cad,cph,cem,cct)
    #Execute query with values
    cur.execute(sql, val)
    #commit for permanent storage in database
    con.commit()
    #display success message
    print(cur.rowcount, "Record inserted.")
    
    
def update(con,cur):
    #read values to be updated
    cid=input("Enter customer ID:")
    cnm=input("Enter customer Name:")
    cad=input("Enter customer Address:")
    cph=input("Enter customer Phone:")
    cem=input("Enter customer Email:")
    cct=input("Enter customer City:")
    #create update query
    sql = "update customer set CustName='"+cnm+"', CustAddress='"+cad+"',CustPhone='"+cph+"', CustEmail='"+cem+"', CustCity='" +cct+"' where CustID="+cid
    #Execute Update query on opened cursor
    cur.execute(sql)
    #commit Changes to DB
    con.commit()
    #display success message
    print(cur.rowcount, "Record updated.")
    

def delete(con,cur):
    #read the customer ID for which record to be deleted
    cid=input("Enter customer ID to delete:")
    #Create Delete Query
    sql = "delete FROM customer where CustID = '"+cid+"'"
    #execute delete query
    cur.execute(sql)
    #commit changes to DB
    con.commit()
    #display success message
    print(cur.rowcount, "Record deleted.")
    

def display(cur):
    #Execute SELECT statement 
    cur.execute("SELECT * FROM customer")
    #Fetch all records from table
    res = cur.fetchall()
    #print
    print("------------------------------------------------------------------------")
    print("CustID   CustName    CustAddress     CustCity   CustPhone   CustEmail")
    print("------------------------------------------------------------------------")
          
    for x in res:
        print(str(x[0])+"  "+x[1]+"  "+x[2]+"  "+x[5]+"  "+x[3]+"  "+x[4])
    print("------------------------------------------------------------------------")    
   
   

def main():
    #make connection to database using localhost, root as username, no password so "" and database name
    con = mysql.connector.connect( 
            host="localhost",
            user="root",
            passwd="",
            database="bikerentdb"
            )
    #opne cursor
    cur = con.cursor()
    ch=0
    #diaplay menu until user presses 5
    while(ch<=4):
        #menu options
        print("1. INSERT")
        print("2. UPDATE")
        print("3. DELETE")
        print("4. DISPLAY")
        print("5. EXIT")
        # ask user to enter what he wants to do
        ch=int(input("Enter Your choice:"))
        #call relevant fucntions defined above
        if (ch==1):
            insert(con, cur)
        if (ch==2):
            update(con, cur)
        if (ch==3):
            delete(con, cur)
        if  (ch==4):
            display(cur)
        
#call main
main()

admin