Python Connect to SQL Database

Kailash Chandra Behera | Monday, June 29, 2020

Introduction

This blog Python Connect to SQL Database will guide to connect Microsoft SQL Server database in Python using pyodbc package. Here we will connect the local SQL server with windows authentication and remote SQL server with user credentials. We will also discuss the connection string example to connect Azure Active Directory.

Python Connect to SQL Database

Getting Started

Python library provides pyodbc package to connect Microsoft SQL Server database. The pyodbc database is open source and is available in python portal.If your machine does not have pyodbc, efer my previous blog How to install pyodbc window to install pyodbc package.

The pyodbc introduced a connection method to connect to the Microsoft SQL Server database and it accepts string variable as a parameter. The string variable should contain like below value.

connect to sql server in python

Python Connect to SQL Database 

SQL Windows Authentication

 'DRIVER={SQL Server};SERVER=Database Server Name;DATABASE=Database Name;Trusted_Connection=yes;'

SQL Server Authentication

 'DRIVER={SQL Server};SERVER=Database Server Name;DATABASE=Database Name;UID=sa;PWD=XXXXXX'

Above both are the connection strings to establish connection to Microsoft SQL Server. In below we will see the example of connection strings.

Pyodbc ConnectionString Examples

SQL Windows Authentication


'Driver={SQL Server};Server=DESKTOP-SQQV4ED;Database=Pythondb;Trusted_Connection=yes;'

SQL Server Authentication

 'DRIVER={SQL Server};SERVER=DESKTOP-65KMK07\SQLEXPRESS;PORT=1433;DATABASE=Test;UID=sa;PWD=******;'

pyodbc uses the Microsoft ODBC driver for SQL Server. If your version of the ODBC driver is 17.1 or later, you can use the Azure Active Directory interactive mode of the ODBC driver through pyODBC. This interactive option works if Python and pyODBC permit the ODBC driver to display the dialog. The option is only available on Windows operating systems.

The following example provides an ODBC connection string that specifies Azure Active Directory interactive authentication:

 server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive;

The Microsoft ODBC Driver for SQL Server with version 13.1 or above allows ODBC applications to connect to an instance of Azure SQL Database using a federated identity in Azure Active Directory with a username/password, an Azure Active Directory access token, an Azure Active Directory managed service identity, or Windows Integrated Authentication (Windows driver only).

For the ODBC Driver version 13.1, the Azure Active Directory access token authentication is Windows only. The ODBC Driver version 17 and above support this authentication across all platforms (Windows, Linux, and macOS).

Python Connect to SQL Database

A new Azure Active Directory interactive authentication with Login ID is introduced in ODBC Driver version 17.1 for Windows. A new Azure Active Directory managed service identity authentication method was added in ODBC Driver version 17.3.1.1 for both system-assigned and user-assigned identities.

All of these are accomplished through the use of new DSN and connection string keywords, and connection attributes.

Note that the ODBC Driver on Linux and macOS only supports Azure Active Directory authentication directly against Azure Active Directory. If you are using Azure Active Directory username/password authentication from a Linux or macOS client and your Active Directory configuration requires the client to authenticate against an Active Directory Federation Services endpoint, authentication may fail.

Python Connect to SQL Database

Steps to Connect SQL Server and Fetch data

  1. Import pyodbc package.
  2. Create object of connection method with SQL server configuration details as parameter.
  3. Create object of cursor with help of connection object.
  4. Execute cursor by passing SQL Query as parameter.
  5. Read each row from cursor.

Syntax of Python Connect to SQL Database

 import pyodbc   
 # Some other example server values are  
 # server = 'localhost\sqlexpress' # for a named instance  
 # server = 'myserver,port' # to specify an alternate port  
 server = 'tcp:myserver.database.windows.net'   
 database = 'mydb'   
 username = 'myusername'   
 password = 'mypassword'   
 cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)  
 cursor = cnxn.cursor()

Select Query for Python Database

The cursor.execute function can be used to retrieve a result set from a query against SQL Database. This function accepts a query and returns a result set, which can be iterated over with the use of cursor.fetchone()

 import pyodbc   
 conn = pyodbc.connect('Driver={SQL Server};'  
                          'Server=DESKTOP-SQQV4ED;'  
            'Database=Pythondb;'  
            'Trusted_Connection=yes;')  
 cursor = conn.cursor()  
 cursor.execute('SELECT * FROM dbo.Student')  
 for row in cursor:  
   print(row)
python connect to sql server windows authentication

Python Connect to SQL Database 

Insert Query for Python Connect to SQL Database

 cursor.execute("""  
 INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate)   
 VALUES (?,?,?,?,?)""",  
 'SQL Server Express New 20', 'SQLEXPRESS New 20', 0, 0, CURRENT_TIMESTAMP)   
 cnxn.commit()  
 row = cursor.fetchone()  
 while row:   
   print('Inserted Product key is ' + str(row[0]))  
   row = cursor.fetchone()

In above example, you saw how to run an INSERT statement safely, and pass parameters. The parameters protect your application from SQL injection.

Summary

You have seen Python Connect to SQL Database. Once you established such a connection between Python and SQL Server, you can start using SQL in Python to manage your data. I hope you have enjoyed it a lot.

Thanks

By at