Setup

macOS

⚠️ Always review any code before running it on your system.

Installing Homebrew

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

---

Installing the Microsoft ODBC driver for SQL Server

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

---

Python

Connect to a SQL Server database. Requires an environment file.

import os
import pyodbc

def load_env(filename):
    """Load environment variables from a file."""
    try:
        with open(filename, 'r', encoding='utf-8') as file:
            for line in file.readlines():
                line = line.rstrip('\n')
                if line and '=' in line:
                    key, value = line.split('=', 1)
                    os.environ[key] = value
                    print(f"Setting {key}: {repr(value)}")
    except FileNotFoundError:
        print(f"Error: Environment file '{filename}' not found")
        exit(1)
    except Exception as e:
        print(f"Error reading environment file: {e}")
        exit(1)

def main():
    # Load environment variables
    load_env('.env')
    
    # Construct connection string
    conn_str = (
        f"DRIVER={{ODBC Driver 18 for SQL Server}};"
        f"SERVER={os.getenv('DB_SERVER')};"
        f"DATABASE={os.getenv('DB_NAME')};"
        fr"UID={os.getenv('DB_USERNAME')};"
        f"PWD={os.getenv('DB_PASSWORD')};"
        "TrustServerCertificate=yes"
    )
    
    try:
        # Connect to the database
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()
        
        # Execute query
        query = f"SELECT TOP 1 * FROM [{os.getenv('DB_SCHEMA')}].[{os.getenv('DB_TABLE')}]"
        cursor.execute(query)
        
        # Get column names
        columns = [column[0] for column in cursor.description]
        
        # Fetch and print results
        for row in cursor.fetchall():
            for col_name, value in zip(columns, row):
                print(f"{col_name}: {value}")
            print("-" * 50)
            
    except pyodbc.Error as e:
        print(f"Database error: {e}")
    except Exception as e:
        print(f"Error: {e}")
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    main()

R

Connect to a SQL Server database. Requires an environment file.

library(DBI)
library(odbc)
library(dotenv)

# Function to load environment variables
load_env <- function(filename) {
  if (!file.exists(filename)) {
    stop("Environment file not found")
  }
  
  env_lines <- readLines(filename)
  for (line in env_lines) {
    if (nchar(trimws(line)) > 0 && !startsWith(trimws(line), "#")) {
      parts <- strsplit(line, "=", fixed = TRUE)[[1]]
      if (length(parts) == 2) {
        var_name <- trimws(parts[1])
        var_value <- trimws(parts[2])
        do.call(Sys.setenv, structure(list(var_value), names = var_name))
      }
    }
  }
}

main <- function() {
  # Load environment variables
  load_env("environment.txt")
  
  # Create connection string
  conn <- dbConnect(odbc::odbc(),
    Driver = "/opt/homebrew/lib/libmsodbcsql.18.dylib",
    Server = Sys.getenv("DB_SERVER"),
    Database = Sys.getenv("DB_NAME"),
    UID = Sys.getenv("DB_USERNAME"),
    PWD = Sys.getenv("DB_PASSWORD"),
    TrustServerCertificate = "yes"
  )
  
  # Ensure connection is closed when function exits
  on.exit(dbDisconnect(conn))
  
  # Construct and execute query
  query <- sprintf("SELECT TOP 1 * FROM [%s].[%s]",
                  Sys.getenv("DB_SCHEMA"),
                  Sys.getenv("DB_TABLE"))
  
  # Execute query
  tryCatch({
    result <- dbGetQuery(conn, query)
    
    # Print results
    for (col in names(result)) {
      cat(sprintf("%s: %s\n", col, result[[col]][1]))
    }
  }, error = function(e) {
    stop(sprintf("Query execution failed: %s", e$message))
  })
}

main()

Windows

⚠️ Always review any code before running it on your system.

Installing the Microsoft ODBC driver for SQL Server

1. Download the ODBC for your system driver here (most likely the ‘x64’ version)
2. Install it

---

Python

Connecting to the BIGFI server

R

Connecting to the BIGFI server