Retrieve records from MSSQLServer in Python

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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