Connect Python and SQL Server Example 2

Connect Python and SQL Server

There are many libraries available on the internet to establish a connection between them. This section discusses how to Connect Python and SQL Server using pyodbc library with an example.

Connect Python and SQL Server Syntax

The syntax to establish a connection between the Python and Microsoft SQL Server using the pyodbc connect 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 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 databases. If you are using 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 we will extract.

Employee Table 1

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

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 with the SQL Server using the Python pyodbc library connect function with a practical example.

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,))
Connect Python and SQL Server Example 2

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.

The following are a few of the operations we can do on SQL Server but are not limited to them.

  1. Create Database
  2. Select Records from Table
  3. Select Sorted Table Records
  4. Top 10 records
  5. Where Clause

If you don’t have the Python library, open the command prompt as Administrator, navigate to scripts (optional), and type pip install pyodbc.

import pyodbc

Next, we are using the connect function to connect to SQL Servers. Here, we are using Windows authentication, and that’s why we have not specified the Username and password. However, in real-time, you have to specify both your Username and password for connecting to the DB.

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 print the output.

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