Using a helper class with Snowflake causes connection issues

Question

I did a bit of follow-along with the Snowflake documentation to get used to connecting to Snowflake via the python API, but I'm finding my use-cases increasing in number and I need to build out a little more flexibility. I started on a helper class to assist me, but the code that runs fine is leading to connection issues.

Here's the code that returns fine:

import snowflake.connector
from snowflake.connector import errors as sce
import os
from dotenv import load_dotenv

load_dotenv()

ctx = snowflake.connector.connect(
    account=os.getenv('ACCOUNT'),
    user=os.getenv('USER'),
    password=os.getenv('SNOWSQL_PASSWORD'),
    warehouse=os.getenv('WAREHOUSE'),
    role=os.getenv('ROLE'),
    database=os.getenv('DATABASE')
)

cur = ctx.cursor()
cur.execute(task_history)
query_id = cur.sfqid
results = cur.fetchall()
for result in results:
    print(result)

With that functioning properly, I moved on to building a helper class that looks like this:

import snowflake.connector
from snowflake.connector import errors as sce
import os
from dotenv import load_dotenv

load_dotenv()


class TaskMonitor():

    def __init__(self):
        print('initializing task monitor')
        self.account = os.getenv('ACCOUNT'),
        self.user = os.getenv('USER'),
        self.password = os.getenv('SNOWSQL_PASSWORD'),
        self.warehouse = os.getenv('WAREHOUSE'),
        self.role = os.getenv('ROLE'),
        self.database = os.getenv('DATABASE')

    def __connect__(self):
        print('connecting to snowflake account')
        self.ctx = snowflake.connector.connect(
            account=self.account,
            user=self.user,
            password=self.password,
            warehouse=self.warehouse,
            role=self.role,
            database=self.database
        )

    def fetch(self, sql):
        self.__connect__()
        self.cur = self.ctx.cursor()

        print('sending select statement')

        try:
            self.cur.execute(sql)
            query_id = self.cur.sfqid
            print(query_id)

            results = self.cur.fetchall()

            for rec in results:
                print(rec)

        except sce.ProgrammingError as e:
            print(e)
            print('Error {0} ({1}): {2} ({3})'.format(e.errno, e.sqlstate, e.msg, e.sfqid))

        finally:
            self.ctx.close()

    def execute(self, sql):
        self.__connect__()
        self.cur.execute(sql)

    def test_connection(self):
        self.__connect__()


tm = TaskMonitor()
tm.fetch('show tasks;')

The problem is that when I run the code, I get a connection error.

snowflake.connector.errors.OperationalError: 250003: Failed to execute request: HTTPSConnectionPool(host="('xxxxxxxxxxxxxxxxxxxxxxxxx',).snowflakecomputing.com", port=443): Max retries exceeded with url:xxxxxxxxxxxxxxxxxxxxxxxxxxxxx (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x000001842623E850>: Failed to establish a new connection: [Errno 11001] getaddrinfo failed'))

Thanks to anyone who can point out what I'm doing wrong here!

Answer 1

I think your problem is in this code:

def __init__(self):
   print('initializing task monitor')
   self.account = os.getenv('ACCOUNT'),
   self.user = os.getenv('USER'),
   self.password = os.getenv('SNOWSQL_PASSWORD'),
   self.warehouse = os.getenv('WAREHOUSE'),
   self.role = os.getenv('ROLE'),

You have left the commas at the end of each line, probably from when you copy-pasted from the snowflake.connector.connect part of your previous version of the code.

Remove the commas and make the code look like this:

def __init__(self):
   print('initializing task monitor')
   self.account = os.getenv('ACCOUNT')
   self.user = os.getenv('USER')
   self.password = os.getenv('SNOWSQL_PASSWORD')
   self.warehouse = os.getenv('WAREHOUSE')
   self.role = os.getenv('ROLE')
   self.database = os.getenv('DATABASE')