Python

Python With SQL Server Database

Introduction

In this  article you will learn:
  • How to connect through SQL Server Database using Python?
  • How can we store some information into the SQL Server database using Python ?
  • How can we perform update and delete operations ?
  • How we will retrieve stored information from the SQL Server database ?
Prerequisites

Before performing all the above operations you have to install the following tools.
  1. Download and install Visual Studio 2013 or higher version. You can install this from the following link Visual Studio Community Website.
  2. After installing visual studio you need to install Python Tool for Visual Studio (PTVS).
  3. After Installing PTVS You need to Install Python Interpreter: Click Here to Download Python Interpreter.
  4. And for communicate with the database you need to install SQL Server express 2008 or higher version: Click Here to Install SQL Sever Express.
Steps

Firstly, we will create a console based Python project.
  1. Open Visual Studio.

  2. Select File, New, then Project.

    new project

  3. Now select Install template, Python, then Python Application. and give the name to the application and choose location where you want to save your application and then press OK.

    /python application

  4. After performing above operation you need to Install Python package for SQL Server Connectivity. 
Python Package Installing For SQL Server Connectivity

If you want to connect your python code to SQL Server there is no direct method. You need to install packages for connecting SQL Server. To install package perform the following steps:
  1. Open Python Environment in Visual Studio by clicking VIEW, Other Windows, then Python Environment.



  2. Now click on Python Interpreter which you have installed in your system (Note: A Computer can contain more than 1 Python interpreter), select pip from the drop down and search form pypyodbc.



    Note: "pypyodbc"     is a module which is used to connect to the database and perform operations on that. pypyodbc can be used with any type of database: Oracle, SQL Server, MySQL, Microsoft Access or Excel, etc.
After successfully installing pypyodbc. Now open SQL Server and create a database and create table.
 
I am creating a database Payroll and inside that database I am creating a table named EmployeeMaster which contains the following fields.



Here EmployeeID is primary key and Auto Incremented.
 
You can use the following code to create this EmployeeMaster table.
  1. CREATE TABLE EmployeeMaster(    
  2.     EmployeeID int IDENTITY(1,1) NOT NULL,    
  3.     Name nvarchar(100) NOT NULL,    
  4.     Salary decimal(18, 2) NOT NULL,    
  5.     Mobile varchar(15) NOT NULL,    
  6.     Designation varchar(50) NOT NULL,    
  7.  CONSTRAINT [PK_EmployeeMaster1] PRIMARY KEY CLUSTERED     
  8. (    
  9.     [EmployeeID] ASC    
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
  11. ) ON [PRIMARY]   
Now switch to visual studio again and now we will see database connection insert, update, delete and select operations with Python step by step.
 
Connect to SQL Server Database: The following Python code shows you how you can connect to SQL Server database.
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5.   
  6. print("Connection Successfully Established")  
  7.   
  8. #closing connection  
  9. connection.close()  
Connect in the function which contains connection string.
  • Driver for connection to particular database.
  • Server: Which database server we are using.
  • Database : Name of the database which we will use.
  • uid: user ID of the database server.
  • pwd: Password for connection to the database.
Insert Data To Database

The following command is used to Insert data into the database.
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7. #SQL Query  
  8. SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES ('Sourabh','200000','9928486447','Computer Programmer')")  
  9. #Processing Query  
  10. cursor.execute(SQLCommand)   
  11. #Commiting any pending transaction to the database.  
  12. connection.commit()  
  13. #closing connection  
  14. connection.close()  
  15. print("Data Successfully Inserted")  
Here,
  • cursor() : This is a method which returns a new cursor object using the connection.
  • execute() : Prepares and executes SQL.
  • commit() : Commits any pending transaction to the database. 
Output

 

And check the database, it will contain data which you have inserted.

 
 
Using Question Mark (?)
 

We can also execute our query with question mark sign. Sometimes we need to insert our data at run time and we want to form our query with the + symbol and sometimes we forget to open/close double quotes or single quotes or plus symbol. So we can also execute our query with Question Mark Sign ? like the following:
  1. SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
And we will execute our query like the following:
  1. Values = [Name,Salary,Mobile,Designation]  
  2.   
  3. cursor.execute(SQLCommand,Values)  
Example
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7. #############Database Parameters##########  
  8. Name= "DJ"  
  9. Salary=50000  
  10. Mobile="9876543210"  
  11. Designation="Computer Programmer"  
  12. ##########################################  
  13.   
  14. #SQL Query  
  15. SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
  16. Values = [Name,Salary,Mobile,Designation]  
  17.   
  18. #Processing Query  
  19. cursor.execute(SQLCommand,Values)   
  20. #Commiting any pending transaction to the database.  
  21. connection.commit()  
  22. #closing connection  
  23. connection.close()  
  24. print("Data Successfully Inserted")  
Output

  

Inside Database

 
 
Data read from user and inserted into the database
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7. #############Database Parameters##########  
  8. Name= input("Please Enter Name:")  
  9. Salary=input("Please Enter Salary:")  
  10. Mobile=input("Please Enter Mobile Number:")  
  11. Designation=input("Please Enter Designation:")  
  12. ##########################################  
  13.   
  14. #SQL Query  
  15. SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
  16. Values = [Name,Salary,Mobile,Designation]  
  17.   
  18. #Processing Query  
  19. cursor.execute(SQLCommand,Values)   
  20. #Commiting any pending transaction to the database.  
  21. connection.commit()  
  22. #closing connection  
  23. connection.close()  
  24. print("Data Successfully Inserted")  
Output

 
 
Inside Database

 

Update In Database
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7. #SQL Query  
  8. SQLCommand = ("Update EmployeeMaster set Name='Dhananjay' where EmployeeID=2")  
  9. #Processing Query  
  10. cursor.execute(SQLCommand)   
  11. #Commiting any pending transaction to the database.  
  12. connection.commit()  
  13. #closing connection  
  14. connection.close()  
  15. print("Updated Successfully")  
Output

 

Inside Database

 

Delete From the Database
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7. #SQL Query  
  8. SQLCommand = ("Delete from EmployeeMaster where EmployeeID=3")  
  9. #Processing Query  
  10. cursor.execute(SQLCommand)   
  11. #Commiting any pending transaction to the database.  
  12. connection.commit()  
  13. #closing connection  
  14. connection.close()  
  15. print("Deleted Successfully")  
Output

 

Inside Database

 
 
Retrieve Data from the Database

We can retrieve data from the database by two ways.
  • Fetch one row at a time: We call fetchone() method.
  • Fetch all rows: We call fetchall() method.
fetchone method example
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7.   
  8. #SQL Query  
  9. SQLCommand = ("select * from EmployeeMaster")  
  10. #Processing Query  
  11. cursor.execute(SQLCommand)   
  12. results = cursor.fetchone()   
  13. while results:  
  14.     print ("Name:" +  str(results[0]))  
  15.     print ("Salary:" +  str(results[1]))  
  16.     print ("Mobile:" +  str(results[2]))  
  17.     print ("Designation:" +  str(results[3]))  
  18.     print()  
  19.     results = cursor.fetchone()  
  20.   
  21. #closing connection  
  22. connection.close()  
Output

 
 
Fetch All Rows

The following example shows how can we fetch all the rows at a time from the database using fetchall() method.
  1. #importing module  
  2. import pypyodbc  
  3. #creating connection Object which will contain SQL Server Connection  
  4. connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
  5. #Creating Cursor  
  6. cursor = connection.cursor()   
  7.   
  8. #SQL Query  
  9. SQLCommand = ("select * from EmployeeMaster")  
  10. #Processing Query  
  11. cursor.execute(SQLCommand)   
  12. i=1  
  13. for rows in cursor.fetchall():  
  14.     print("------------Employee %d-----------------"%i)  
  15.     for field in rows:  
  16.         print(str(field))  
  17.     print("---------------------------------------")  
  18.     print('')  
  19.     i=i+1  
  20.   
  21. #closing connection  
  22. connection.close()  
Output

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    sourabhsomani.com
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs