Tutorial Gateway

Connect Python and SQL Server

by

There are many libraries available in internet to establish a connection between SQL and Python. In this article we will show you, How to Connect Python and SQL Server using pyodbc library with example.

Syntax to Connect Python and SQL Server

The basic syntax to establish a connection between the Python and SQL Server using the pyodbc is as shown below

pyodbc.connect("Driver = {SQL Server Native Client 11.0};"               
               "Server = Server_Name;"
               "Database = Database_Name;"
               "username = User_Name;"
               "password = User_Password;"
               "Trusted_Connection = yes;")
  • Driver: Here you have to specify the ODBC Connection, or SQL Server Native Client.
  • Server: You have to specify the server instance name.
  • Database Name: You have to specify the database name from where you want to extract the data.
  • UserName: Please specify the user name who has access to SQL Server. If you are using the Windows authentication then you can omit the Username and password.
  • password: Please specify the password for the above user name

Before we get into the example, let me show you the data that we are going to extract.

Connect Python and SQL Server Example 1

From the above screenshot you can observe that, we are selecting all the records present in the Employ table. The SQL SELECT Statement we used in this example is as shown below:

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ID]
  FROM [Employ]

Connect Python and SQL Server Example

In this example we will show you, how to establish the connection between Python and SQL Server using the pyodbc library with practical example.

PYTHON CODE

#How to Connect Python and SQL Server
import pyodbc
conn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=SURESH;"
                      "Database=SQL Tutorial;"
                      "Trusted_Connection=yes;")

cursor = conn.cursor()
cursor.execute('SELECT * FROM Employ')

for row in cursor:
    print('row = %r' % (row,))

OUTPUT

Connect Python and SQL Server Example 2

ANALYSIS

First, we are importing the pyodbc library, and it has all the required functions to set up connection with SQL Server from Python IDE. If you don’t have the library then open the command prompt as Administrator, then navigate to Python scripts (optional), and type pip install pyodbc. 

import pyodbc

Next, we are using the connect function to establish the connection with SQL Servers. Here, we are using the windows authentication, and that’s why we are not specified the username, and password. But in real-time you have to specify both username and password.

conn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=SURESH;"
                      "Database=SQL Tutorial;"
                      "Trusted_Connection=yes;")

Next, we are importing data from Employ table present in SQL Server

cursor = conn.cursor()
cursor.execute('SELECT * FROM Employ')

Lastly we are using the For loop to iterate each and every row present in the Employ table, and then printing the output

for row in cursor:
    print('row = %r' % (row,))

Thank You for Visiting Our Blog