Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Connect Python and SQL Server

by suresh

There are many libraries available on the internet to establish a connection between SQL Server and Python. In this section, we discuss how to Connect Python and SQL Server using pyodbc library with an example.

Syntax to Connect Python and SQL Server

The 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 Username 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 Username.

Before we get into the python connect to the sql server 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 python connect to sql server 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 show you how to establish the connection between Python and SQL Server using the pyodbc library with a practical example.

#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

Within this python connect to the SQL server program, first, we are importing the pyodbc library. And it has all the required functions to set up a connection with SQL Server from Python IDE.

If you don’t have the Python 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 connect Python to SQL Servers. Here, we are using the windows authentication, and that’s why we have not specified the Username and password. However, in real-time, you have to specify both Username and password for connecting to the SQL server.

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

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

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

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

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

Placed Under: Python

  • Download and Install Python
  • Python Arithmetic Operators
  • Python Assignment Operators
  • Python Bitwise Operators
  • Python Comparison Operators
  • Python Logical Operators
  • Python If Statement
  • Python If Else
  • Python Elif Statement
  • Python Nested If
  • Python For Loop
  • Python While Loop
  • Python Break
  • Python Continue
  • Python Dictionary
  • Python datetime
  • Python String
  • Python Set
  • Python Tuple
  • Python List
  • Python List Comprehensions
  • Python Lambda Function
  • Python Functions
  • Python Types of Functions
  • Python Iterator
  • Python File
  • Python Directory
  • Python Class
  • Python classmethod
  • Python Inheritance
  • Python Method Overriding
  • Python Static Method
  • Connect Python and SQL Server
  • Python SQL Create DB
  • Python SQL Select Top
  • Python SQL Where Clause
  • Python SQL Order By
  • Python SQL Select Statement
  • Python len Function
  • Python max Function
  • Python map Function
  • Python print Function
  • Python sort Function
  • Python range Function
  • Python zip Function
  • Python Math Functions
  • Python String Functions
  • Python List Functions
  • Python NumPy Array
  • NumPy Aggregate Functions
  • NumPy Arithmetic Operations
  • Python Numpy Bitwise operators
  • Numpy Comparison Operators
  • Numpy Exponential Functions
  • Python Numpy logical operators
  • Python Numpy String Functions
  • NumPy Trigonometric Functions
  • Python random Array
  • Python Numpy concatenate
  • Python Numpy Array shape
  • Python pandas DataFrame
  • Pandas DataFrame plot
  • Python Series
  • Python matplotlib Histogram
  • Python matplotlib Scatter Plot
  • Python matplotlib Pie Chart
  • Python matplotlib Bar Chart
  • Python List Length
  • Python sort List Function
  • Python String Concatenation
  • Python String Length
  • Python Substring
  • Python Programming Examples
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy