如何在类中启动Snowflake连接

Question

我想在Python中创建一个类，建立与SnowFlake的连接。我有一个user.txt文件，它指定了一个帐户、仓库、数据库、模式和我的用户。这是我迄今为止的代码：

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"

在运行完这个类之后，我希望运行这段代码，在pandas数据帧中检索数据库中的所有行：

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

我已经设法创建了一个到Snowflake的连接，但一直试图将其放入一个类中，以便于阅读。

此外，我有一段代码，我不确定如何集成到我的数据库类中

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

Answer 1

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