Snowflake connection helper functions
Project description
Snowflake connection helper functions
A Python library to simplify connecting to Snowflake databases by leveraging connection options specified in SnowSQL configuration file (~/.snowsql/config
).
Notes
- Recent snowflake-connector-python package supports
private_key_file
as a parameter to theconnect()
function, and the ability to use named connections. Lack of these features were some of the major reasons for this package to exist. If you don't need any other functionality offered by this package then you should rely on the latestsnowflake-connector-python
package. - Following are some minor differences between the way
SnowSQL
interprets connection options v/ssfconn
library:sfconn
library supports settingSFCONN
environment variable to be used as a default connection name before searching for a default connection entry in the config file.- SnowSQL doesn't (yet) allow
private_key_path
to contain home-anchored paths (e.g.~/keys/key.p8
), butsfconn
library does - SnowSQL treats relative paths as relative to working directory of the running process, whereas,
sfconn
library by default evaluates relative paths as relative to the config file location. If SnowSQL-like behavior is needed, do either of the following:- before any other calls to
sfconn
library, include following codeimport sfconn sfconn.conn.relpath_anchor_is_cwd = True
- set
SFCONN_RELPATH_ANCHOR_CWD=1
environment variable
- before any other calls to
Installation
Use Python's standard pip
utility for installation:
pip install --upgrade sfconn
Usage
getconn()
Usage:
def getconn(name: str | None, **overrides: dict[str, Any]) -> Connection
getconn
accepts a connection name that is defined in ~/.snowsql/config
and returns a connection object. If connection name is None
, value of SFCONN
environment variable will be used as the default connection name; if SFCONN
is unset then the default connection entry defined in the config file will be used before returning an error. Any additional parameters, which are not set to None
, will override the values loaded from the configuration file.
Example:
from sfconn import getconn
# assuming 'dev' is a connection defined in ~/.snowflake/config
with getconn('dev', schema='PUBLIC') as cnx:
with cnx.cursor() as csr:
csr.execute('SELECT CURRENT_USER()')
print("Hello " + csr.fetchone()[0])
conn_opts()
Usage:
def conn_opts(name: str | None , **overrides: dict[str, Any]) -> dict[str, Any]
conn_opts
, returns a Python dict
object populated with options and values. This can be useful passing as an argument to snowflake.snowpark.Session.builder.configs()
method.
Example:
from sfconn import conn_opts
from snowflake.snowpark import Session
# assuming 'dev' is a connection defined in ~/.snowflake/config
session = Session.builder.configs(conn_opts('dev')).create()
Decorator functions
Python scripts that accept command-line parameters and use argparse
library, can use decorator functions to further reduce boilerplate code needed for setting up common Snowflake connection options as command-line arguments
def args(doc: str | None) -> Callable[[argparse.ArgumentParser], None]:
def entry() -> Callable[[Connection, ...], None]:
def entry_opts() => Callable([dict[str, Any, ...]])
args()
decorator function:
- builds an
ArgumentParser
object - adds common Snowflake connection options as arguments that allow overriding values specified in
~/.snowsql/config
- calls the decorated function with the parser object to allow adding any script specific options
entry()
decorator function:
- consumes standard Snowflake connection options (specified with
args()
) - creates a connection object
- calls the decorated function with a connection object as first parameter and any other script specific options that were specified on command line
entry_opts()
decorator function:
- Similar to
entry()
but passes adict
of options as the first parameter. This is useful for passing options to thesnowflake.snowpark.Session.builder.configs()
method
Example:
from sfconn import args, entry
@entry
def main(con, show_account: bool):
with con.cursor() as csr:
csr.execute('SELECT CURRENT_USER()')
print("Hello " + csr.fetchone()[0])
if show_account:
csr.execute("SELECT CURRENT_ACCOUNT()")
print("You are connected to account: " + csr.fetchone()[0])
@args("Sample application that greets the current Snowflake user")
def getargs(parser):
parser.add_argument("-a", "--show-account", action='store_true', help="show snowflake account name")
if __name__ == '__main__':
main(**vars(getargs()))
get_token()
Function sfconn.get_token()
returns a JWT token for connections that use private_key_path
option. An optional lifetime value can be specified (default 54 minutes)
Example:
from sfconn import get_token
# assuming 'dev' is a connection defined in ~/.snowflake/config and uses key-pair authentication
jwt_token = get_token('dev', 120) # get a token valid for 120 minutes
Utilities
The sfconn
module provides a couple of handy utilities.
list connections
To list all defined connections, use
python -m sfconn [list]
connection options as JSON
Convert connection options to a JSON object.
python -m sfconn [-c <conn>] json
test a connection
To test a particular connection, use
python -m sfconn [-c <conn>] test [--save]
--save
option applies to connections that require password to be supplied. When specified, it saves the supplied password in OS specific secure local storage.
Notes:
--save
option is supported only if the optional python packagekeyring
is installed.keyring
can also be installed indirectly by installingsnowflake-connector-python
withsecure-local-storage
extra dependency as described here
get a JWT
Get a JWT for connections that use key-pair authentication
python -m sfconn [-c <conn>] jwt [--lifetime <minutes>]
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.