[SOLVED] Writing a connection string when password contains special characters

Writing a connection string when password contains special characters

I'm using SQLalchemy for a Python project, and I want to have a tidy connection string to access my database. So for example:

engine = create_engine('postgresql://user:pass@host/database')

The problem is my password contains a sequence of special characters that get interpreted as delimiters when I try to connect.

I realize that I could just use engine.URL.create() and then pass my credentials like this:

import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    drivername="postgresql",
    username="user",
    password="p@ss",
    host="host",
    database="database",
)
print(connection_url)
# postgresql://user:p%40ss@host/database

But I'd much rather use a connection string if this is possible.

So to be clear, is it possible to encode my connection string, or the password part of the connection string - so that it can be properly parsed?

Solution

You need to URL-encode the password portion of the connect string:

from urllib.parse import quote_plus
from sqlalchemy.engine import create_engine
engine = create_engine("postgres://user:%s@host/database" % quote_plus("p@ss"))

If you look at the implementation of the class used in SQLAlchemy to represent database connection URLs (in sqlalchemy/engine/url.py), you can see that they use the same method to escape passwords when converting the URL instances into strings.