Connecting To SQL Server using Python 3

mssql_connect.py

#!/usr/bin/env python3

# an example of connecting to a SQL Server database using Python 3
# automatically closes the db connection after use

import pyodbc
from contextlib import contextmanager

# pyodbc wiki: https://github.com/mkleehammer/pyodbc/wiki
# example based on https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

# 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' 

# use line below for Windows Active Directory Authentication
# CONN_STRING = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes'

# use line below for database authentication. There is probably a safer way to store credentials.
username = 'myusername' 
password = 'mypassword' 
CONN_STRING = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

@contextmanager
def db_cursor(*args, **kwgs):
  """ Provides access to the database for queries.
  
  Arguments same as those for pyodbc.connect
  """
  conn = pyodbc.connect(*args, **kwgs)
  cursor = conn.cursor()
  try:
    yield cursor
  finally:
    cursor.close()
    conn.close()

def test():
  """ An example / test to see if the SQL Server version can be returned.
  """
  with db_cursor(CONN_STRING) as cursor:
    cursor.execute("SELECT @@version;") 
    row = cursor.fetchone() 
    while row: 
      print(row[0])
      row = cursor.fetchone()

if __name__ == '__main__':
  test()