Using Snowflake with Python and Copilot

Photo by David Clode on Unsplash

I like to play around with Snowflake. Also with Python. And then also combining the 2, for cases such as generating SQL using Python and/or Jinja and executing this code in Snowflake. Or do some provisioning of a trial based on a fixed set of SQL files that I want to execute.

I use a config file to store the Snowflake credentials. The format of this json file is:

{
  "account":"pqfc-gt555",
  "user":"HDYGBBD",
  "password":"&YBF(_NNC",
  "warehouse": "",
  "database": "",
  "schema": ""
}

This config is somewhere in my laptop folder structure. In my case, I have a folder called ‘git’ with a lot of github repo’s. The config file can be in the git folder or in one of the folders below.

The code to grab the contents of this config file:

def get_conn():
    with open('sfconfig.json', 'r') as file:
        data = json.load(file)
        user = data['user']
        password = data['password']
        account = data['account']

    conn = snowflake.connector.connect(
        user=user,
        password=password,
        account=account
    )
    return conn

I have an issue to fix here: run my code without bothering where my config file resides. And as I am a very slow Python developer (don’t use it often enough I guess) I decided to use Microsoft Copilot for this challenge:

Prompt 1: “i use python with a config file with credentials. the config file will always be named sfconfig.json and there will only be one file with that name on my computer. can you give me a python script that i can call from other scripts, that will find the sfconfig.json in the current folder plus one folder higher, and reads the content of the file ? the file content will be this: { “account”:””, “user”:””, “password”:”” }”

Here we go:

import os
import json

def read_sfconfig():
    filenames = ['sfconfig.json']
    directories = [os.getcwd(), os.path.dirname(os.getcwd())]
    
    config_data = None
    
    for directory in directories:
        for filename in filenames:
            file_path = os.path.join(directory, filename)
            if os.path.isfile(file_path):
                with open(file_path, 'r') as file:
                    config_data = json.load(file)
                break
        if config_data is not None:
            break
    
    if config_data is None:
        raise FileNotFoundError("sfconfig.json not found in the current or parent directory.")
    
    return config_data

# Example usage:
if __name__ == "__main__":
    try:
        config = read_sfconfig()
        print("Configuration data:", config)
    except Exception as e:
        print(e)

This is not really useful yet, so we need another prompt:

Prompt 2: how can i call this script from another python script without copying the code

# config_reader.py

import os
import json

def read_sfconfig():
    filenames = ['sfconfig.json']
    directories = [os.getcwd(), os.path.dirname(os.getcwd())]
    
    config_data = None
    
    for directory in directories:
        for filename in filenames:
            file_path = os.path.join(directory, filename)
            if os.path.isfile(file_path):
                with open(file_path, 'r') as file:
                    config_data = json.load(file)
                break
        if config_data is not None:
            break
    
    if config_data is None:
        raise FileNotFoundError("sfconfig.json not found in the current or parent directory.")
    
    return config_data

# main_script.py

from config_reader import read_sfconfig

try:
    config = read_sfconfig()
    print("Configuration data:", config)
    # You can now use the configuration data in your script
except Exception as e:
    print(e) 

It gets better with each prompt. But, the first code snippet was the calling of the config and putting it in the proper variables. We need another prompt to achieve this.

Prompt 3: can you now integrate the code from main_Script.py in this code snippet: def get_conn(): with open(‘sf-config.json’, ‘r’) as file: data = json.load(file) user = data[‘user’] password = data[‘password’] account = data[‘account’] conn = snowflake.connector.connect( user=user, password=password, account=account ) return conn

import snowflake.connector
from config_reader import read_sfconfig

def get_conn():
    try:
        config = read_sfconfig()
        user = config['user']
        password = config['password']
        account = config['account']
    except Exception as e:
        print(f"Error reading configuration: {e}")
        return None

    try:
        conn = snowflake.connector.connect(
            user=user,
            password=password,
            account=account
        )
        return conn
    except Exception as e:
        print(f"Error establishing connection: {e}")
        return None

# Example usage:
if __name__ == "__main__":
    connection = get_conn()
    if connection:
        print("Connection established successfully")
    else:
        print("Failed to establish connection")

No connection, which makes sense, since no real credentials are in the file. Let me fix that:

So, this was my challenge: The config file is somewhere on disk, could not be bothered where.

The solution was to give 3 prompts to Copilot, get some snippets and get this working.

Disclaimer: feel free to copy/use this code, but since it is Copilot produced, only trust the code unless tested thoroughly.