Post
Ask Question

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 an 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 that 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 the 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 the 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 the 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 a particular database.
  • Server: Which database server we are using.
  • Database: Name of the database which we will use.
  • uid: the 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 a 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 symbols. 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 in 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
 
 


Similar Articles