How to Use Python to Connect to SQL Server

Can Python work with SQL Server database? Some may argue that SQL Server is not the best SQL database for Python, but in most cases it actually does not matter. In my corporate experience, SQL Server may be the easiest database to use with Python. Besides, in the workplace often times we just have to use what’s available to solve our business problems.

One of the advantages of Python language is the short development time that makes it very suitable to use as a simple tool to query a database.

The easiest way to access a SQL Server database with Python is by using pyodbc. From its project description, pyodbc is an open source Python module that makes accessing ODBC databases simple.

There is another driver called pymssql, but I have not tested it and it looks like even Microsoft themselves recommend pyodbc over pymssql although try to remain somewhat neutral.

There are several python SQL drivers available. However, Microsoft places its testing efforts and its confidence in pyodbc driver.

Source: Python SQL Driver: Getting Started.

You can install it by running the pip command in the Command Prompt (Windows) or Terminal (macOS):

pip install pyodbc

Once installed, create a new python file (e.g., hrquery.py) and import the pyodbc.

Example

hrquery.py

#!python3
# hr.py - Look for HR record based on last name.
#         If no argument is passed, return total count of records.

import sys, pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DB_SERVER_NAME;'
                      'Database=DATABASE_NAME;'
                      'UID=SQLID_OR_NTID;'
                      'PWD=PASSWORD;'
                      'Trusted_Connection=no;')

cursor = conn.cursor()

# Check if there is a passed argument (last name)
# If yes, search by last name, in parameterized query
# otherwise, give total count of records from HR table.

if len(sys.argv) > 1:
    lastName = sys.argv[1]

    cursor.execute('SELECT * FROM dbo.HR WHERE [Last name] = ?', lastName)
    for i in cursor:
        print(i)
else:
    totalCount = cursor.execute('SELECT COUNT(*) FROM dbo.').fetchval()
    print(f"\nTotal count in HR table: {totalCount} records\n")

Other than Driver and Trusted_Connection, you need to substitute the values of all the parameters with your own (Server, Database, UID, and PWD).

To run your program, you can launch Command Prompt or Terminal and type the following:

C:\> python.exe hrquery.py lastname

OR

C:\> python.exe hrquery.py

Using Windows Authentication

If you use your NTID or a Windows Service Account (Windows Authentication) to login to the database, then there’s a slight difference in how you setup the connection string. This is the preferred way nowadays since you don’t store your password as a clear text in your code.

You just need to remove the PWD and set the Trusted_Connection to yes so it will look like the following:

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DB_SERVER_NAME;'
                      'Database=DATABASE_NAME;'
                      'UID=NTID_OR_SVCACCT;'
                      'Trusted_Connection=yes;')

That’s all there is in using Python to connect to SQL Server and the program will work the same way.

Further Reading

How to Install Python on Windows Server
pyodbc
Python SQL Driver