Retrieve Records From MSSQLServer in Python


The below Python code will create a connection to a MSSQLServer instance, and retrieve data from it back into a variable called tblResults.

# use pyodbc for database connection
import pyodbc

# keep our database credentials in a store
secrets = {
    'host': '<db_host>',
    'Name': '<db_name>',
    'username': '<db_username>',
    'password': '<db_password>',
}

# create a connection string
conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER=tcp:{secrets['host']};DATABASE={secrets['Name']};UID={secrets['username']};PWD={secrets['password']}"

# create a connection to the database
conn = pyodbc.connect(conn_str)

# a reusable function to get data from
def sql_get(sql, conn):
    cursor = conn.cursor()
    cursor.execute(sql)
    columns = [column[0] for column in cursor.description]
    results = []
    for row in cursor.fetchall():
        results.append(dict(zip(columns, row)))
    return results

# some variable to use in the SQL statement below
something = 13

# make a database connection and return the results into a variable
tblResults = sql_get(
    f"""SELECT * FROM tblName WHERE something='{something}'"""
, conn)