Using python to connect to Snowflake

Based on this documentation.

This topic provides instructions for installing the Snowflake Connector for Python. The connector can currently be installed in Linux, macOS, and Windows environments.

Install the connector:

$ pip3 install --upgrade snowflake-connector-python

Create a file (e.g. validate.py) containing the following Python sample code, which connects to Snowflake and displays the Snowflake version:

#!/usr/bin/env python
import snowflake.connector

# Gets the version
ctx = snowflake.connector.connect(
    user='<your_user_name>',
    password='<your_password>',
    account='<your_account_name>'
    )
cs = ctx.cursor()
try:
    cs.execute("SELECT current_version()")
    one_row = cs.fetchone()
    print(one_row[0])
finally:
    cs.close()
ctx.close()

Make sure to replace <your_user_name>, <your_password>, and <your_account_name> with the appropriate values for your Snowflake account.

The Snowflake Connector for Python uses a temporary directory to store data for loading and unloading (PUT, GET), as well as other types of temporary data.

The temporary directory can be explicitly specified by setting the TMPDIR, TEMP or TMP environment variables, otherwise the operating system’s default temporary directory (i.e. /tmp, C:\temp) is used.

 $ export TMPDIR=/large_tmp_volume

Multiple SQL

This example shows executing multiple commands in a single string and then using the sequence of cursors that is returned:

#!/usr/bin/env python
import snowflake.connector
# Gets the version
ctx = snowflake.connector.connect(
    user='jsly',
    password='!Visitor3',
    account='bamboohr.us-east-2.aws'
    )
sql_cmd = "select CALLEE_NAME from DB_RAW.JIVE.CALL_DETAILS where CALLEE_NAME LIKE 'Jeff%' LIMIT 3;"   \
          "select CALLEE_NAME from DB_RAW.JIVE.CALL_DETAILS where CALLEE_NAME LIKE 'Joe%' LIMIT 3;"
try:
   cursor_list = ctx.execute_string(sql_cmd)

   for cursor in cursor_list:
      for row in cursor:
         print(row[0], row[1])
      print()
    
finally:
   ctx.close()