Advance Python – CRUD Operation in Python with SQL Server Database

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?

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

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 –

• MVC Blogs
• Web API Blogs
• Angular Blogs
• Learn TypeScript

Interview Questions and Answers Series –

• MVC Interview Questions and Answers
• Web API interview questions and answers