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)
