Keeping credentials out of the repo

24 June 2021

You use credentials in your (jupyter) notebook or python script and you are about to check it in to a (git) repository. What’s the quickest way to get the credentials into a separate file and load them?

Firstly, create a secrets.env file with key value pairs in it in a similar way to a shell script

username="bob"
password="topsecret"
server="server.location"
database="database"

Secondly, add this to your .gitignore

echo secrets.env >> .gitignore

Note that this assumes you are in the root of the repo so if not you will need to adjust the path to the .gitignore file

Thirdly, this is the minimal code to get a dict set up in python with the creds from your secrets.env.

from pathlib import Path

# Note that this just takes the first match of *.env in the current directory
p = Path("secrets.env")

# Need the first line as f-strings may not contain slashes
key_val_pairs = p.read_text().replace("\n", ",\n")
exec(f"creds = dict({key_val_pairs})")
# creds is now a dict with the key value pairs in

Clearly one should be careful about using exec in a production/professional environment because it facilitates arbitrary code execution but if we’re aiming for efficiency five lines is not bad.

By way of example one can then use this dictionary to construct e.g. a connection string to a database.

import pandas as pd
from sqlalchemy import create_engine

def js(s):
    return "".join(s.split())

conn_str = js(
    f"""
mssql+pyodbc://
{creds['username']}:{creds['password']}@{creds['server']}/{creds['database']}
?driver=SQL+Server
"""
)

query = "SELECT * FROM information_schema.tables;"

engine = create_engine(conn_str)
with engine.connect() as c:
    df = pd.read_sql(query, c)