How to call snowsql client from python

Question

I'm calling snowsql client from shell script. I'm importing properties file by doing source. And invoking snowsql client. How can I do the same in Python? Any help would be highly appreciated.

Shell script to call snowsql client:

source /opt/data/airflow/config/cyrus_de/snowflake_config.txt


sudo /home/user/snowsql -c $connection --config=/home/user/.snowsql/config -w $warehouse  --variable database_name=$dbname --variable stage=$stagename  --variable env=$env  -o exit_on_error=true -o variable_substitution=True -q /data/snowsql/queries.sql

Would you really want to shell into SnowSQL, which is a Python app, from Python -- rather than using the Python connector for Snowflake? If you have all the prerequisites in Python you can install it using "pip install --upgrade snowflake-connector-python" — Greg Pavlik, Jan 24 '20 at 12:07
As Greg has already mentioned - you should really be using the python connector for Snowflake rather than using the SnowSQL client from within Python. — Simon D, Jan 24 '20 at 16:15
through python i cant even assign variables in sql, it keeps throwing multi statement error and cant find an easy way around. But cli does it — bohontw, Sep 23 '22 at 17:26

score 1 · Accepted Answer · answered Jan 26 '20 at 11:34

Assuming you're switching to using Python purely for improved control flow and would still like to continue using shell features, a straight-forward translation would require writing a function that acts as the source command to import environment variables, then using them in a subprocess call that executes with a shell to allow environment variable substitution:

import os, shlex, subprocess

def source_file_into_env():
  command = shlex.split("env -i bash -c 'source /opt/data/airflow/config/cyrus_de/snowflake_config.txt && env'")
  proc = subprocess.Popen(command, stdout = subprocess.PIPE)
  for line in proc.stdout:
    (key, _, value) = line.partition("=")
    os.environ[key] = value
  proc.communicate()

def run():
  source_file_into_env()

  subprocess.run("""sudo /home/user/snowsql \
      -c $connection \
      --config=/home/user/.snowsql/config \
      -w $warehouse \
      --variable database_name=$dbname \
      --variable stage=$stagename \
      --variable env=$env \
      -o exit_on_error=true \
      -o variable_substitution=True \
      -q /data/snowsql/queries.sql""", \
    shell=True, \
    env=os.environ)

if __name__ == '__main__':
  run()

If you are instead looking to go purely Python without any shell calls, then the more native connector offered by Snowflake can be used instead of snowsql. This would be a far more invasive change but the connection examples will help you get started.

How to call snowsql client from python

1 Answers1

Linked