Python with SQL Server Database

Python with SQL Server Database

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?

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?

Steps

Firstly, we will create a console based Python project.

  1. Open Visual Studio.

  2. Select FileNew,then Project.

This is image title

  1. 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.

This is image title

  1. 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.

This is image title

  1. 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.

This is image title

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.

This is image title

Here EmployeeID is the primary key and Auto Incremented.

You can use the following code to create this EmployeeMaster table.

CREATE TABLE EmployeeMaster(    
    EmployeeID int IDENTITY(1,1) NOT NULL,    
    Name nvarchar(100) NOT NULL,    
    Salary decimal(18, 2) NOT NULL,    
    Mobile varchar(15) NOT NULL,    
    Designation varchar(50) NOT NULL,    
 CONSTRAINT [PK_EmployeeMaster1] PRIMARY KEY CLUSTERED     
(    
    [EmployeeID] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) 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.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  

print("Connection Successfully Established")  

#closing connection  
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.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES ('Sourabh','200000','9928486447','Computer Programmer')")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
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 

This is image title

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

This is image title

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:

SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")

And we will execute our query like the following:

Values = [Name,Salary,Mobile,Designation]  

cursor.execute(SQLCommand,Values) 

Example

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#############Database Parameters##########  
Name= "DJ"  
Salary=50000  
Mobile="9876543210"  
Designation="Computer Programmer"  
##########################################  

#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
Values = [Name,Salary,Mobile,Designation]  

#Processing Query  
cursor.execute(SQLCommand,Values)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Data Successfully Inserted")  

Output  

This is image title

Inside Database 

This is image title

Data read from user and inserted into the database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#############Database Parameters##########  
Name= input("Please Enter Name:")  
Salary=input("Please Enter Salary:")  
Mobile=input("Please Enter Mobile Number:")  
Designation=input("Please Enter Designation:")  
##########################################  

#SQL Query  
SQLCommand = ("INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)")  
Values = [Name,Salary,Mobile,Designation]  

#Processing Query  
cursor.execute(SQLCommand,Values)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Data Successfully Inserted")  

Output 

This is image title

Inside Database

This is image title

Update In Database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("Update EmployeeMaster set Name='Dhananjay' where EmployeeID=2")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Updated Successfully")  

Output

This is image title

Inside Database

This is image title

Delete From the Database

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   
#SQL Query  
SQLCommand = ("Delete from EmployeeMaster where EmployeeID=3")  
#Processing Query  
cursor.execute(SQLCommand)   
#Commiting any pending transaction to the database.  
connection.commit()  
#closing connection  
connection.close()  
print("Deleted Successfully")  

Output

This is image title

Inside Database 

This is image title

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

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   

#SQL Query  
SQLCommand = ("select * from EmployeeMaster")  
#Processing Query  
cursor.execute(SQLCommand)   
results = cursor.fetchone()   
while results:  
    print ("Name:" +  str(results[0]))  
    print ("Salary:" +  str(results[1]))  
    print ("Mobile:" +  str(results[2]))  
    print ("Designation:" +  str(results[3]))  
    print()  
    results = cursor.fetchone()  

#closing connection  
connection.close()  

Output 

This is image title

Fetch All Rows

The following example shows how can we fetch all the rows at a time from the database using fetchall() method.

#importing module  
import pypyodbc  
#creating connection Object which will contain SQL Server Connection  
connection = pypyodbc.connect('Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa')  
#Creating Cursor  
cursor = connection.cursor()   

#SQL Query  
SQLCommand = ("select * from EmployeeMaster")  
#Processing Query  
cursor.execute(SQLCommand)   
i=1  
for rows in cursor.fetchall():  
    print("------------Employee %d-----------------"%i)  
    for field in rows:  
        print(str(field))  
    print("---------------------------------------")  
    print('')  
    i=i+1  

#closing connection  
connection.close()  

Output 

This is image title

Thank you for reading!

python sql-sever sql

What's new in Bootstrap 5 and when Bootstrap 5 release date?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Basic Data Types in Python | Python Web Development For Beginners

In the programming world, Data types play an important role. Each Variable is stored in different data types and responsible for various functions. Python had two different objects, and They are mutable and immutable objects.

Top Python Development Companies | Hire Python Developers

After analyzing clients and market requirements, TopDevelopers has come up with the list of the best Python service providers. These top-rated Python developers are widely appreciated for their professionalism in handling diverse projects. When...

Python Hello World Program - Create & Run Your First Python Program in PyCharm

Python Hello World Program - Your first step towards Python world. Learn how to create the Hello World Python program in PyCharm.

Standard Data Types in Python - Python Tutorial - Python Training

This video on 'Standard Data Types in Python' will help you establish a foothold on Python by helping you learn basic concepts.