Connection String when password contains special characters

Question

I am using SQLALchemy to make connection to my DB string.

There are some special characters in My Password. which causes sqlALchemy to form a wrong connection string.

db_credentails = {
    'username': 'my_uname',
    'password': '@SaNU(wXFPtm^7',
    'engine': 'postgresql',
    'host': 'db-code-gx.dev.my_host.io',
    'port': 5432,
    'dbname': 'xms'
}

def _get_db_conn_str(creds)
    return 'postgresql://{}:{}@{}:{}/{}'.format(
           db_credentials['username'],
           db_credentials['password'],
           db_credentials['host'],
           db_credentials['port'],
           db_credentials['dbname']
   )

this creates the following url as per sqlAlchmey format

{engine}://{username}:{password}@{host}:{port}/{db}

postgresql://my_uname:@SaNU(wXFPtm^7@db-code-gx.dev.my_host.io:5432/xms

As you can see the problem here is that the first @ here in password is treated as a breakpoint by sqlAlchemy between password and host.

To fix this i tried the solution suggested in this answer to wrap my password with urllib.parse.quote_plus before returning.

from urllib.parse import quote_plus

def _get_db_conn_str(creds)
     return 'postgresql://{}:{}@{}:{}/{}'.format(
           db_credentials['username'],
           quote_plus(db_credentials['password']),
           db_credentials['host'],
           db_credentials['port'],
           db_credentials['dbname']
     )

URL: postgresql://my_uname:%40SaNU(wXFPtm^7@db-code-gx.dev.my_host.io:5432/xms

This replaces the @ character in password with '%40' however after this i am getting a wrong password message on authentication.

I have also tried using the sqlalchemy.engine.URL.create to generate the URL but its also failing trying to connect to the database.

import sqlalchemy as sa

connection_url = sa.engine.URL.create(drivername="mysql+pymysql",
                                   username=db_credentials['username'],
                                   password=db_credentials['password'],
                                   host=db_credentials['host'],
                                   port=db_credentials['port'],
                                   database=db_credentials['dbname'],
                                   )
print(connection_url)
`postgresql://my_uname:%40SaNU(wXFPtm^7@db-code-gx.dev.my_host.io:5432/xms`

this raises an error:

(psycopg2.OperationalError) connection to server at "db-code-gx.dev.my_host.io", port 5432 failed: FATAL: password authentication failed for user "my_uname" connection to server at "db-code-gx.dev.my_host.io" , port 5432 failed: FATAL: password authentication failed for user "my_uname"

I am not sure what is wrong here.

Connection String when password contains special characters

0 Answers0