Configuration files are widely used in software projects, as they allow developers to define parameters and settings. Python is a versatile programming language and it offers several approaches for writing configuration files.

For this article we are going to use these configuration files to hide credentials that we are going to use for connecting Python to Snowflake.

Method 1. Using Config Parser

Configparser library is include in Python3 by default so in this case we would not need to install this library.

In this case we are going to import configparser and os. Os library is going to help us to define the path on which we are going to have the file from which we are going to read the credentials.

First we are going to create a configuration file “secrets.config” with the following text.

[SNOWFLAKE]
USERNAME = user
PASSWORD = password
ACCOUNT = zk85032.south-central-us.azure
ROLE = ACCOUNTADMIN
DATABASE = SNOWFLAKE_SAMPLE_DATA
SCHEMA = TPCH_SF1
WAREHOUSE = COMPUTE_WH

Essentially, this file consists of a section called “SNOWFLAKE”, that contains keys with values.

Then we are going to import two libraries, the first one will be “configparser”, and the other one would be “os”, this last one is going to help us to get the path to the secrets.config file that we created.

Then we are going to call this parameters on the snowflake connection string.

import snowflake.connector as sf
import pandas as pd
import configparser as cp
import os

config = cp.ConfigParser()
currentdir = os.path.dirname(os.path.realpath(__file__))
config_file = os.path.join(currentdir, "secrets.config")
config.read(config_file)

conn = sf.connect(user=config.get('SNOWFLAKE', 'USERNAME'),
                    password=config.get('SNOWFLAKE', 'PASSWORD'),
                    account=config.get('SNOWFLAKE', 'ACCOUNT'),
                    database=config.get('SNOWFLAKE', 'DATABASE'),
                    role=config.get('SNOWFLAKE', 'ROLE'),
                    warehouse=config.get('SNOWFLAKE', 'WAREHOUSE'),
                    schema=config.get('SNOWFLAKE', 'SCHEMA')
                    )

cursor = conn.cursor()
str_query = f""" 
            SELECT *
            FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
"""
cursor.execute(str_query)

df = cursor.fetch_pandas_all()
print(df)
cursor.close()

Line 6-9 are setting the configparser, getting the location of the file and reading it by using the config.read statement.

Then we are reading the connection string, but instead of calling the values directly, we are invoking them from the file.

Method 2. Using dotenv

Dotenv library is not included into python default installation, In this case you will have to install it by using pip

pip install python-dotenv

Dotenv usage is similar to Config Parser, after install we will have to create a configuration file called “.env”, we are going to get the credentials from this file.

USER_NAME = user
PASSWORD = password
ACCOUNT = tx50920.west-us-2.azure
DATABASE = ALPHA_QC_CODE_UPDATES_LOCAL_DB
ROLE = ALPHA_DEV_DEVELOPER_ROLE
WAREHOUSE = ALPHA_DEV_WORKLOAD_WH
WAREHOUSE_KPI = ALPHA_DEV_WORKLOAD_WH
WAREHOUSE_QC = ALPHA_DEV_WORKLOAD_WH

After creating the env file, we will need to import and use the library that we installed, then we are going to load the file by using load_dotenv.

Finally we are going to use the variables that we set on the .env file.

import snowflake.connector as sf
import pandas as pd
import os
from dotenv import load_dotenv

dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
load_dotenv(dotenv_path)

conn = sf.connect(user=os.getenv("USER_NAME"),
                    password=os.getenv("PASSWORD"),
                    account=os.getenv("ACCOUNT"),
                    database=os.getenv("DATABASE"),
                    role=os.getenv("ROLE"),
                    warehouse=os.getenv("WAREHOUSE"),
                    schema=os.getenv("SCHEMA"),
                    )

cursor = conn.cursor()
str_query = f""" 
            SELECT *
            FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
"""
cursor.execute(str_query)

df = cursor.fetch_pandas_all()
print(df)
cursor.close()

Conclusion

By using a configuration file, we are going to avoid having our credentials shared when we push our code into git by adding the secrets or .env files to the .gitignore file.

Also, the credentials are going to be secured in a separate file, avoiding having them on your main script.