Confusing behavior when using % in password in sqlalchemy.url inside alembic.ini

[heavenyoung1]

Summary

I'm having issues connecting to my PostgreSQL database using Alembic, specifically due to a @ character in the database password. When using a URL like this in alembic.ini:

sqlalchemy.url = postgresql+psycopg2://postgreSS:P%40ssw0rd@10.163.1.64:5432/auth_db

I get the following error:
configparser.InterpolationSyntaxError: '%' must be followed by '%' or '(', found: '%40ssw0rd@...'
When I try to escape % like this:
sqlalchemy.url = postgresql+psycopg2://postgreSS:P%%ssw0rd@10.163.1.64:5432/auth_db

I then get a database connection error:
sqlalchemy.exc.OperationalError: ... password authentication failed for user "postgreSS"

What works
The same URL does work perfectly when passed directly to create_engine() from sqlalchemy in my FastAPI application.
create_engine("postgresql+psycopg2://postgreSS:P%40ssw0rd@10.163.1.64:5432/auth_db")

Questions

1. How exactly should special characters like %, @, etc. be escaped in alembic.ini?
2. Why does Alembic/ConfigParser treat @ differently than SQLAlchemy does when using create_engine()?
3. Is there an official recommendation for placing the DB URL with special characters in a .env or settings.py file and setting it in env.py instead of alembic.ini?

[heavenyoung1]

By the way this way works:

_url = URL.create(
    drivername="postgresql+psycopg2",
    username="postgres",
    password="P@ssw0rd",  
    host="10.165.1.63",     
    port=5432,
    database="auth_test_db"
)

There are no unshielded characters used here, why there are such conflicts and SQLAlchemy and Alembic can't read the same data in the same way, it's strange, I could be wrong and I'm waiting for your answer.

[zzzeek]

Alembic uses Python's ConfigParser.

This configparser interpolates percent signs, which Alembic uses to support the token %(here)s:

All the '%' interpolations are expanded in the return values, unless the raw argument is true. Values for interpolation keys are looked up in the same manner as the option.

So this is just the percent sign. From there it looks like you're already familiar with escaping the @ signs in the URL string itself

Here's a program that will run any URL through the same steps as alembic + SQLAlchemy's unescaping of percent signs, illustrating your URL including an @ sign by first URL escaping it as %40, then escaping the percent sign as %%40:

from configparser import ConfigParser
import urllib.parse


MY_URL = "postgresql+psycopg2://postgreSS:P%%40ssw0rd@10.163.1.64:5432/auth_db"

config = f"""
[section]
sqlalchemy.url = {MY_URL}
"""


pp = ConfigParser()
pp.read_string(config)

url_from_configparser = pp.get("section", "sqlalchemy.url")

print(f"Retreived from configparser: {url_from_configparser}")


unescaped_url = urllib.parse.unquote(url_from_configparser)

print(f"Will be unescaped by SQLAlchemy as: {unescaped_url}")

from sqlalchemy import make_url

assert make_url(url_from_configparser).password == "P@ssw0rd"

print(
    f"SQLAlchemy parsed the password as: {make_url(url_from_configparser).password}"
)

By the way this way works:

_url = URL.create(
    drivername="postgresql+psycopg2",
    username="postgres",
    password="P@ssw0rd",  
    host="10.165.1.63",     
    port=5432,
    database="auth_test_db"
)

There are no unshielded characters used here, why there are such conflicts and SQLAlchemy and Alembic can't read the same data in the same way, it's strange, I could be wrong and I'm waiting for your answer.

That's Python code that is bypassing the use of Configparser, which parses a configurational string that supports interpolation variables with percent signs (which means a percent sign that's not part of an interpolation variable must be escaped), and is also bypassing SQLAlchemy's make_url function, which parses individual components from a single-string URL where the @ sign is significant to the syntax (which means an @ sign that is not part of the syntax must be escaped).

Here's another program that uses the same functions to go the other way. Put your actual password into it and it will run these two escaping sequences:

import urllib.parse

MY_PASSWORD = "P@ssw0rd"

escaped_password = urllib.parse.quote(MY_PASSWORD)

print(f"Password to send to make_url: {escaped_password}")

percent_escaped_password = escaped_password.replace("%", "%%")
print(f"Password to apply in interpolated config file: {percent_escaped_password}")

[zzzeek]

I've added a detailed section to Alembic's documentation with the above details at https://alembic.sqlalchemy.org/en/latest/tutorial.html#escaping-characters-in-ini-files

[heavenyoung1]

thanks, buddy, I'll watch it.