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()
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.