Advance Python - CRUD Operation in Python with SQL Server Database

In the previous blog of Python, I wrote about the basic syntax and basics of Python Programming. In this blog, I will write about CRUD Operation in Python using SQL server as back-end database.
As you all know that we can connect a Python application with various data sources. By end of this blog you will be able to understand How to connect Python application to SQL Server using pyodbc?

Table of Contents

How to Connect Python to SQL Server?

There are many libraries using which we can connect python application with the back-end database.
Some of the Python SQL libraries are SQLite, pymssql, pyodbc. Each of these Python SQL libraries have their pros and cons. In this blog I will use pyodbc to connect with MS-SQL Server.

What is Pyodbc?

To perform CRUD Operation in Python Pyodbc package is very important.

pyodbc package is used to help to connect Python application to SQL Server.

First, we will install it.

I am using Visual Studio Code to demonstrate this Python blog. You can use any IDE on which you are comfortable.

How to Connect Python to SQL Server using pyodbc?

Below steps will help you to understand how to implement CRUD operation in Python application with SQL Server database.

To do this first install pyodbc, please follow below steps.

1. Open Visual Studio Code

2. From Terminal menu please select New Terminal

3. Type this command and press enter – pip install pyodbc

Once pyodbc is installed successfully. we will start writing code.

Believe me it is very easy to write SQL server connectivity code in Python.

Let’s start

Create a new file in your project folder. Give a name to your file, say – PythonWithSQL.py.

import pyodbc at the top of your page using below line –

import pyodbc

Python Code to Connect SQL Server Database –

 
sqlDbConn = pyodbc.connect(
"Driver= {SQL Server Native Client 11.0};"
"Server=localhostsqlexpress;"
"Database=PilotDB;"
"Trusted_Connection=yes;"
)

Python Code to read or fetch data from SQL server table –

def getData(sqlDbConn):
    print("Read")
    cursor = sqlDbConn.cursor();
    cursor.execute("select * from MyTable")
    for row in cursor:
        print(f'{row}')

Python Code to insert data into SQL Server table –

def insertData(sqlDbConn):
    print("Insert")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'insert into MyTable (name,city) values(?,?)',
        ('Ram','Delhi'))
    sqlDbConn.commit() 


Note: Without calling commit data will not saved in database. 
Commit transactions to make changes persistent in the database.

Python Code to update data in SQL server table –

  def updateData(sqlDbConn):
    print("Update")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'update MyTable set city = ? where id = ?',
        ('Patna',20))
    sqlDbConn.commit()

Python Code to delete a record from table –

def deleteData(sqlDbConn):
    print("Delete")    
    cursor = sqlDbConn.cursor();   
     
    cursor.execute(        
        'delete from MyTable where id = ?',        
        (17))    
    sqlDbConn.commit()

Finally, below is the complete code block in Python to perform CRUD operation in
SQL Server using pyodbc.

Below code will help you to build application in Python which require database functionality such as inserting new record, updating or deleting a record,
showing record which are saved in database.

import pyodbc

sqlDbConn = pyodbc.connect(
"Driver= {SQL Server Native Client 11.0};"
"Server=localhostsqlexpress;"
"Database=PilotDB;"
"Trusted_Connection=yes;"
)

def getData(sqlDbConn):
    print("Read")
    cursor = sqlDbConn.cursor();
    cursor.execute("select * from MyTable")
    for row in cursor:
        print(f'{row}')
        

def insertData(sqlDbConn):
    print("Insert")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'insert into MyTable (name,city) values(?,?)',
        ('Ram','Delhi'))

    sqlDbConn.commit() 
    # Without calling commit data will not saved in database. 
        

def updateData(sqlDbConn):
    print("Update")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'update MyTable set city = ? where id = ?',
        ('Motihari',17))
    sqlDbConn.commit() 
    
        

def deleteData(sqlDbConn):
    print("Delete")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'delete from MyTable where id = ?',
        (17))
    sqlDbConn.commit() 
    
     

#Call the functions one by one
insertData(sqlDbConn)
updateData(sqlDbConn)
deleteData(sqlDbConn)
getData(sqlDbConn)

To execute this code, type python Filename.py and you can see the result.

You can execute this either from windows command prompt window or in Visual Studio Code terminal.

Summary

This blog will help us to connect Python application with SQL Server database to perform CRUD operation in Python.

Watch this Video to know CRUD Operation in Python with SQL Server Database

Subscribe to YouTube channel – https://www.youtube.com/channel/UC4ZoMYeuCp9M2aZpBpr9q0w?sub_confirmation=1

Previous Blog – Exception Handling in Python With Example

Hope you like this blog. Keep following this blog

You may like other blogs –

Interview Questions and Answers Series –

Advance Python – CRUD Operation in Python with SQL Server Database