using .env variables when configuring env.py file in alembic

Question

Based on the posts I've seen this is the most common way to point the alembic.ini sqlalchemy.url to a desired db path in the alembic env.py file

import os

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.

connection_string = f'postgresql+psycopg2://<username>:<password>@localhost/test_server'

config = context.config
config.set_main_option('sqlalchemy.url', connection_string)

this works for me when the value is just one hardcoded string. However, When I try to replace the username and password values with hidden variables coming from my .env file I am always receiving an operational error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  role "None" does not exist

username = os.environ.get("USERNAME")
password = os.environ.get("PASSWORD")

connection_string = f'postgresql+psycopg2://{username}:{password}@localhost/test_server'

config = context.config
config.set_main_option('sqlalchemy.url', connection_string)

What am I doing wrong that is not allowing me to use my environment variables?

Answer 1

I would suggest using dotenv (pip install python-dotenv) like so:

import os
from dotenv import load_dotenv

load_dotenv()

username = os.environ.get("USERNAME")
password = os.environ.get("PASSWORD")

(dotenv doesn't have any external library dependencies which is nice)