This blog goes through the steps to connect to Snowflake from your Python applications securely using environment variables. We all know that hardcoding login credentials is very bad and it should be avoided at all costs. Hardcoded credentials give cyber attackers and hackers an easy way in.Let us see how we can use the environment variables to securely pass the login credentials in this blog in detail.
What is an Environment variable ?
An environment variable is a variable set outside of the program usually through the functionality built into the operating system. Basically an environment variable is a key-value pair (outside of your program) that resides in your local system. A .env file basically contains all the environment variables.
Create a .env file at the root of the project.
Pop your sensitive snowflake credentials into this file as shown below.
NOTE : .env file should be ignored using .gitignore file when you are using git for version control. More information on how to do this is given below.
SF_ACCOUNT = <'snowflake_account_name'>
SF_USER = <'snowflake_username'>
SF_WAREHOUSE = <'warehouse_name'>
SF_DATABASE = <'database_name'>
SF_SCHEMA = <'schema_name'>
SF_PASSWORD = <'snoflake_password'>
Using dotenv in our python program:
Reading environment variables is very easy using the package — python-dotenv. To install, run the command — pip install python-dotenv
To load the environment variables, open a file name snowflake_connector.py and type the following commands as shown in the image.
from os.path import join, dirname
from dotenv import load_dotenv
dotenv_path = join(dirname(file), '.env')
SF_ACCOUNT = os.getenv('SF_ACCOUNT')
SF_USER = os.getenv('SF_USER')
SF_WAREHOUSE = os.getenv('SF_WAREHOUSE')
SF_DATABASE = os.getenv('SF_DATABASE')
SF_SCHEMA = os.getenv('SF_SCHEMA')
SF_PASSWORD = os.getenv('SF_PASSWORD')
Snowflake connector for python:
Snowflake connector for python is available in PyPI. To install the connector, execute the following command:
pip install — upgrade snowflake-connector-python
(The Snowflake connector for Python requires Python 2.7.9 (or higher) or 3.5.0 (or higher))
connection = snowflake.connector.connect (
account = SF_ACCOUNT,
user = SF_USER,
password = SF_PASSWORD,
warehouse = SF_WAREHOUSE,
database = SF_DATABASE,
schema = SF_SCHEMA
Connecting to Snowflake:
Import the snowflake.connector module using the command — import snowflake.connector. You can connect to Snowflake with the required login parameters using the the Default Authenticator. We have securely passed our credentials using .env file. We are ready to Create table in our database. The sample code creates a table test_table with two columns — col1 integer and col2 string.
cs = connection.cursor()
"CREATE OR REPLACE TABLE "
"test_table_1(col1 integer, col2 string)")
except Exception as e:
We have established a connection securely with our Snowflake account using the connector for python.
.gitignore for git
If you are using git for version control, you should ignore this .env file in .gitignore file . Create a .gitignore file at the root of the project.
Add the following lines in .gitignore to avoid the venv, .env files in your git repository.
Please refer https://github.com/bhuvanakundumani/snowflake_connector for code.