CRUD Operations in Python with SQL Database

Introduction

This practical demonstration will help you to understand how to implement CRUD (CREATE, READ, UPDATE and DELETE) operations functionality in Python with SQL database. For any application which is associated with a database, we perform some operations for inserting the record [C= Create], reading the records [R=Read], updating the existing record with new value [U=Update] or deleting the record from the database [D=Delete]. So, These four operations are essentials when talking about any application where we have to use a database.

Day 1: Set and test python environment in Visual Studio 2017

Day 2: Getting started with first python project in Visual Studio 2017

Background

Today, we will try to implement this CRUD operations functionality with a simple Python application. We will use SQL Server for this demo and see how we make connectivity with the SQL server. We will follow step by step practical demonstration and cover each step in detail. The steps will be as follows:

1. Create a Python project in Visual Studio 2017
2. Create a database and a table in SQL
3. Create a config file for the database
4. Install Python Package as "Pypyodbc"
5. Create a connection file
6. Create new record
7. Read Data
8. Update existing record
9. Delete data

I have defined the objective for this article above. So, without wasting much time, let's move to the actual implementation.

Create a Python Project in Visual Studio

Open Visual Studio 2017 and go to File > New > Project. It will open the New Project window, now just select the Python from the installed section in the left panel and from the middle section, just click to "Python Application" and provide the suitable name "PythonCRUD". You can also change the location for this application, if you wish and click to OK.

It will take some time and your project will be ready. You can also read this article for step by step information on how to create your first Python application in Visual Studio.

Create a Database and a Table in SQL

We have a Python project ready and we are ready to implement CRUD operations, but before that, let's create the database and a table in SQL Server. So that we will perform CRUD operations with a live database. Here, we will create a simple database as "Test" and will create a simple table as "Employee" as follows. Following is the SQL script for creating database and table. You can directly execute it. Once it will execute successfully, you can find a Test database along with the Employee table in SQL Server.

CREATE DATABASE Test
GO

USE Test
GO

CREATE TABLE Employee(Id int PRIMARY KEY IDENTITY(1,1),	Name varchar(255) NULL,	Age int NULL)

Create a Config File for the Database

Now let's add a configuration file in this project where we will define all the properties and their values. These properties further will use while creating the connection with SQL database. So, open the Solution Explorer and right-click on Project and choose Add > New Item. It will open the "Add New Item" window similar to as follows. From this window, we have to select "Empty Python File" and provide the valuable name as "config.py" and click to Add. After clicking to Add, it will add a new Python file in the project as config.py.

Now, let's modify the "config.py" and add the required properties and its values which are required for SQL database connection as follows. Here, we need driver name, server name, database name, and credentials as user name and password. These properties are inside an object so that we can use it later.

# SQL Server Database Connection Properties
DATABASE_CONFIG ={
    'Driver': 'SQL Server',
    'Server': 'DESKTOP-QQ95JAD',
    'Database': 'Test',
    'UID': 'sa',
    'Password': 'xxxxxxx'
    }

Install Python Package as "Pypyodbc"

Next, we will install a Python package which is required for making the database connection and executing the command on the database. It is Pypyodbc which is a pure Python package for database operations.

As per PyPi.Org: Pypyodbc is One pure Python script, runs on CPython / IronPython / PyPy , Version 2.4 / 2.5 / 2.6 / 2.7 / 3.2 / 3.3, Win / Linux , 32 / 64 bit. For more..https://pypi.org/project/pypyodbc/

For installing a new package in a project environment, right click to Python environment and choose "Install Python Package...".

It will open Python Environments windows where you can find all install environments and just below it, we have a search box from where we can search any specific package and install it. So, just search pypyodbc as shown in the below image and click to "Install pypyodbc (1.3.5)".

Once you will install the pypyodbc, you can see the successfully installed message in the Output window and find that package in Python environment as well.

Create a Connection File

Any application which works with database and performs database operations, it has a mechanism to create and manage the connection with the database. Here in this application, we will do the same and create a database connection file, which generates the connection with a defined configuration in a configuration file. Let's create a new python file as "db_connection.py" and create a method in it. This method (getConnection()) will return the database connection based on database configuration defined in config.py.

First, let import the pypyodbc, so that we can use it. For accessing the configuration properties defined in config.py, first we have to import that file using "import filename" and then can access the object directly.

import pypyodbc
import config

# Return the sql connection 
def getConnection():
     connection = pypyodbc.connect("Driver= {"+config.DATABASE_CONFIG["Driver"]+"} ;
     Server=" + config.DATABASE_CONFIG["Server"] + ";
     Database=" + config.DATABASE_CONFIG["Database"] + ";
     uid=" + config.DATABASE_CONFIG["UID"] + ";pwd=" + config.DATABASE_CONFIG["Password"])
     return connection 

Now, let's decorate the main function from where execution will start. As you can see the below code, this is the PythonCRUD.py file which is created automatically when we created the project in Visual Studio with the similar name of the project. Here, first of all, we are importing all required modules as pypyodbc for database connection and operations with SQL Server, db_connection for getting the connection based on configuration provided above. And four other files as read, create, update and delete for performing CRUD operations respectively.

The first screen will give the user to input for his/her choice among available options as C=Create, R=Read, U=Update and D=Delete.

import pypyodbc
import db_connection as dbConn
from read import Read
from create import Create
from update import Update
from delete import Delete

def main():
    print('Available Options: C=Create, R=Read, U=Update, D=Delete ')
    choice = input('Choose your option = ')

    if choice == 'C':
        createObj=Create()
        createObj.func_CreateData()
    elif choice == 'R':
        readObj =  Read()
        readObj.func_ReadData()
    elif choice == 'U':
        updateObj = Update()
        updateObj.func_UpdateData()
    elif choice == 'D':
        deleteObj = Delete()
        deleteObj.func_DeleteData()
    else:
        print('Wrong choice, You are going exist.')

# Call the main function
main()

Create New Record

As we have done lots of things as Project Creation, Database and Table Creation, Creating the Configuration File for the database, Creating the Connection File, etc. Now, let's move and create a new record from python file and save it into the database.

So, first create a new empty python file as "create.py" as same we have created in the above step. In this file, create a class as "Create" and a method as "func_CreateData(self)", this method will be responsible for adding a new record in the database. Here, we are doing the following things.

1. First, getting the database connection from db_connection file.
2. Making two input for entering the values for name and age respectively.
3. Create the insert query using input values (name, age) and execute it.
4. Commit the connection, so that value commit in a database table.
5. In finally block, close the connection.
6. Maintaining the execution in try-except-finally block.
7. Print the valuable message.

import db_connection as dbConn

class Create:
    def func_CreateData(self):

        # Get the sql connection
        connection = dbConn.getConnection()
                
        name = input('Enter Name = ')
        age = input('Enter Age = ')

        try:
           query = "Insert Into Employee(Name, Age) Values(?,?)" 
           cursor = connection.cursor()

           # Execute the sql query
           cursor.execute(query, [name, age])

           # Commit the data
           connection.commit()
           print('Data Saved Successfully')

        except:
             print('Something wrong, please check')

        finally:
           # Close the connection
           connection.close()

Let's run the project and you will get the windows similar to below. Here, you will get the options for your choice [Code will be available at the bottom of this article]. So, first choose your option as C. It means, you are going to create a new record.

It will ask to enter the name and age respectively and data will save with appropriate message that "Data Saved Successfully".

Read Data

Let's create one more file as "read.py" for reading the data and printing it. Here, we will also do the same operation as we have done in creating.

1. First, getting the database connection from db_connection file.
2. Create the select query and execute it.
3. Get the value in some variable, here we are getting in a cursor.
4. Loop on the cursor and print the value.

import db_connection as dbConn

class Read:
    def func_ReadData(self):   
        # Get the sql connection
        connection = dbConn.getConnection()
        cursor = connection.cursor()

        # Execute the sql query
        cursor.execute('Select * from Employee')

        # Print the data
        for row in cursor:
            print('row = %r' % (row,))

While running the project, just choose R as an option for reading the data and press the enter. You will get the output as follows:

Update Existing Record

Now, it's time to update the existing record. So, for that, first let's create one more file as "update.py" and make the code as follows. Here, we have to first get the record which needs to be updated and then we will provide the new values for that record and update it.

import db_connection as dbConn;

class Update:
    def func_UpdateData(self):
        # Get the SQL connection
        connection = dbConn.getConnection()

        id = input('Enter Employee Id = ')
    
        try:
           # Fetch the data which needs to be updated
           sql = "Select * From Employee Where Id = ?" 
           cursor = connection.cursor()
           cursor.execute(sql, [id])
           item = cursor.fetchone()
           print('Data Fetched for Id = ', id)
           print('ID\t\t Name\t\t\t Age')
           print('-------------------------------------------')       
           print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2]))
           print('-------------------------------------------')
           print('Enter New Data To Update Employee Record ')

           name = input('Enter New Name = ')
           age = input('Enter New Age = ')
           query = "Update Employee Set Name = ?, Age =? Where Id =?" 
       
           # Execute the update query
           cursor.execute(query, [name, age, id])
           connection.commit()
           print('Data Updated Successfully')

        except:
             print('Something wrong, please check')

        finally:
           # Close the connection
           connection.close()

This time while running the project, just choose U as an option for updating the record. First, it will ask to enter the employee id which is going to be updated and show the data for that employee. Next onwards this, it will ask to enter updated data for name and age for that employee and update it. Once it will update successfully, it will show the confirmation message.

Delete Data

Here, we will try to delete the existing record. So, let's create one more file for delete operation as "delete.py". Here, we will first ask a user to enter the employee id which needs to be deleted and based on that, it shows the data for that employee.

Next message will ask to confirm to you that you are willing to delete or not. We have two options as "Y" and "N". Just press Y, if you would delete the record.

import db_connection as dbConn;

class Delete:
    def func_DeleteData(self):
        # Get the SQL connection
        connection = dbConn.getConnection()

        id = input('Enter Employee Id = ')
    
        try:
           # Get record which needs to be deleted
           sql = "Select * From Employee Where Id = ?" 
           cursor = connection.cursor()
           cursor.execute(sql, [id])
           item = cursor.fetchone()
           print('Data Fetched for Id = ', id)
           print('ID\t\t Name\t\t\t Age')
           print('-------------------------------------------')       
           print(' {}\t\t {} \t\t\t{} '.format(item[0], item[1], item[2]))
           print('-------------------------------------------')
           confirm = input('Are you sure to delete this record (Y/N)?')

           # Delete after confirmation
           if confirm == 'Y':
               deleteQuery = "Delete From Employee Where Id = ?"
               cursor.execute(deleteQuery,[id])
               connection.commit()
               print('Data deleted successfully!')
           else:
                print('Wrong Entry')
        except:
            print('Something wrong, please check')
        finally:
            connection.close()

Run the project and choose the last option as D for deleting the record and provide the employee id which needs to be deleted. After providing the employee id, first, it will show the record data and ask for confirmation. If you provide Y as confirmation, then it will delete the record and show the confirmation message that data has deleted successfully.

Conclusion

So, today, we have learned how to perform CRUD operations in Python with SQL database.

I hope this post will help you. Please put your feedback using comment which will help me to improve myself for the next post. If you have any doubts, please ask in the comments section below and if you like this post, please share it with your friends. Thanks!