How do I initiate a Snowflake connection within a class?

Question

I want to create a class in Python that establishes a connection to SnowFlake. I have a user.txt file that specifies an account, warehouse, database, schema, and my user. Here is the code I have so far:

import pandas as pd
import snowflake.connector
import os
from getpass import getpass
import sfcommon.sfdb as sf

class Database:
    def __init__(self):
        self.environ = os.environ['SFPASSWORD'] = getpass(prompt='Password: ', stream=None)
        self._conn = sf.get_connect()
        self._cursor = self._conn.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    @property
    def connection(self):
        return self._conn

    @property
    def cursor(self):
        return self._cursor

    def commit(self):
        self.connection.commit()

    def close(self, commit=True):
        if commit:
            self.commit()
        self.connection.close()

    def execute(self, sql, params=None):
        self.cursor.execute(sql, params or ())

    def fetchall(self):
        return self.cursor.fetchall()

    def fetchone(self):
        return self.cursor.fetchone()

    def query(self, sql, params=None):
        self.cursor.execute(sql, params or ())
        return self.fetchall()

sql = "select * from test_database"

After running this class, I would hope to run this block of code to retrieve all the rows in my database in a pandas dataframe:

with Database() as test:
    resultSet = pd.read_sql(sql, conn)

I have managed to create a connection to Snowflake but have been trying to get it into a class for easier readability.

Also, I have this block of code that I'm not sure how to integrate into my Database class

%reload_ext sql_magic
%config SQL.conn_name = 'conn'

Ankur Srivastava Ankur Srivastava · Accepted Answer · 2020-05-12T18:54:40

You can use the below code for dataframe .

---------------------------------------------------------------------------------

import snowflake.connector

import pandas as pd

# creates a connection
def openConn():
  ctx = snowflake.connector.connect(
      account='',
      user='',
      password='',
      database='',
      schema='public',
      warehouse='',
      role='',
  )
  return ctx

def main():
    conn = openConn()
    cs = conn.cursor()
    cs.execute("""<query>""")

    # Put it all to a data frame
    sql_data = pd.DataFrame(cs.fetchall())

    print(sql_data)

if __name__ == "__main__":
   main()

------------------------------------------------------------------------------

How do I initiate a Snowflake connection within a class?

1 Answers