Connecting Python To SQL Server, For Inserting Record

Introduction

 

Before starting my article about connecting SQL Server to Python, I would suggest that new learners check the fundamental concept of Python from Python web site or any other websites suggested on Google. We can connect Python with various kinds of databases, including MySQL, SQL Server, Oracle, and Sybase, etc.

 

So, in this tutorial, I will explain how we are able to connect with SQL Server. To connect with any database, we mainly follow 4 steps.

1. Importing the API module (similar to Namespace used in .Net).
2. Establishing Connection with the database using connect constructor.
    
   connect ( parameters... )
    
   It takes a number of parameters like-Driver, Server, Database, Uid, Pwd
    
   Driver- It mainly depends on the database you are using. According to the database, this driver will change.
   Server- This is the name of the server where the database exists.
   Database- Name of the database. 
   Uid- This is the userId for your database. 
   Pwd- This is the password for your database.
    
3. Passing your Query in a command object.
4. Creating a Cursor for executing the Query. 
5. Closing the Connection (optional).

Now, open Visual Studio. Create a New Project, and then go to Python Template as follows.

 

 

Now, go to Python Environment.

 

 

Check for Pipiodbc in Python Environment, like the following:

 

 

Install it or update it.

 

 

Now, open the Python application and write the following code.

 

#importing module Like Namespace in .Net
import pypyodbc
#creating connection Object which will contain SQL Server Connection
connection = pypyodbc.connect('Driver={SQL Server};Server=Debendra;Database=CodeX;uid=sa;pwd=123')

print("Connected.")

#closing connection
connection.close()

Now, I have the codex database.Here, I have tbl_employee.

 

 

Now, I will write the code to input the data from the user and save the data in the database, as follows.

#importing module

import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};Server=Debendra;Database=CodeX;uid=sa;pwd=123')
print("Enter a Unique Id")
Id=int(input())
print("Enter first Name")
firstname=input()
print("Enter last Name")
LastName=input()
print("Enter Regno")
RegisterNo=int(input())
cursor = connection.cursor()
SQLCommand = ("INSERT INTO tbl_employee(id,firstName, LastName, EmployeeId) VALUES (?,?,?,?)")
Values = [Id,firstname,LastName,RegisterNo]
#Processing Query
cursor.execute(SQLCommand,Values)
#Commiting any pending transaction to the database.
connection.commit()
#closing connection
print("Data Successfully Inserted")
connection.close()

Now, Run the application and input the data.

 

 

Press Enter to save the data in the database.

 

 

Go to the database and check for the Record.

 

 

Thus, in this way, we can connect Python to the SQL Server. Hope this tutorial will help all those who are new to Python and trying to learn it.