Connecting to MS SQL using ODBC

ODBC (Open Database Connectivity) represents a common protocol to connect to many different databases (MS SQL, Postgres, Oracle, DB2, Terradata, etc). There are several different components to an ODBC implementation, but we are only focused on one, the actual “driver” used by Python to allow us to connect to a database server. Just like with the other methods, once we have made the connection, we can create a cursor to execute queries using the connected data source. One might choose to use ODBC versus a vendor specific library (like pymssql) in order to limit the code changes if a different backend database is chosen.

The most confusing part of an ODBC connection for many users is defining the connection string. The connection string is a string that formatted with instructions about how the driver should connect to the target database. It sets a variety of parameters which affect the connection and can look very cryptic. The key attributes of the connection string include the particular driver that should be used, the database server, the authentication information, and potentially a default database. Other parameters that can be set are dependent on the driver utilized.

In the example that follows, we see that the connection string uses the ODBC Driver for SQL Server and gathers the other information from environment variables. In order to try this out for yourself, simply replace the variables for svr, uid, pwd and db with the values provided by your administrator.

# One more example, this time using pyodbc
from dotenv import load_dotenv
from os import getenv
import pyodbc

# This library allows the environment variables to be loaded from a file
load_dotenv()
# Set the values for server, user_id, password, and database from environment variables
svr = getenv("MSSQL_SERVER")
uid = getenv("HALLUX_USER")
pwd = getenv("HALLUX_PASSWORD")
db = getenv("HALLUX_DB")

# To get this working quickly, you can just replace these variables with your own values
# svr = 'my-server-name'
# uid = 'my-user-id'
# pwd = 'my-password'
# db = 'my-database-name'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+svr+';DATABASE='+db+';UID='+uid+';PWD='+ pwd+';TrustServerCertificate=yes;')

# Create the cursor required to run the command
cursor = cnxn.cursor()

# Run the SQL command
# Replace this SQL statement with an appropriate query 
cursor.execute('SELECT top 10 * FROM Band')

# print out each of the resultant rows
for row in cursor:
    print(row)

(1000, 'A', 'The A-Bomb Project                                                                                  ', '90001', 110000, datetime.datetime(1999, 1, 4, 0, 0))
(1001, 'A', 'Festive Deepak                                                                                      ', '39577', 110010, datetime.datetime(2011, 4, 24, 0, 0))
(1002, 'A', 'Crash The Drug                                                                                      ', '00924', 110020, datetime.datetime(1999, 8, 19, 0, 0))
(1003, 'A', 'Fight The Shame                                                                                     ', '11694', 200037, datetime.datetime(2013, 10, 6, 0, 0))
(1004, 'A', 'Capricious Revelation                                                                               ', '75681', 200002, datetime.datetime(2011, 8, 17, 0, 0))
(1005, 'A', 'Offbeat Sofia                                                                                       ', '17046', 200040, datetime.datetime(2012, 7, 11, 0, 0))
(1006, 'A', 'Ramakrishna Jain                                                                                    ', '37211', 110060, datetime.datetime(2007, 9, 15, 0, 0))
(1007, 'A', 'The Fernandez Disaster                                                                              ', '92243', 110070, datetime.datetime(2007, 11, 27, 0, 0))
(1008, 'A', 'Gabby Dorothy                                                                                       ', '11375', 110080, datetime.datetime(2009, 8, 13, 0, 0))
(1009, 'A', 'The Rivera Gamble                                                                                   ', '21532', 200016, datetime.datetime(1998, 9, 15, 0, 0))