Is it possible to pass a dictionary into create_engine function in SQLAlchemy?

Question

I am trying to start a connection to an Azure SQL database. For security reasons I cannot hardcode in the username and password, I need to get it from a dictionary in a config file and put it into the connection URL that way.

When I try to do so and run it in a Jupyter notebook, the {key[value]} is not being replaced by what it refers to in the config file.

If I use a formatted string, I get a KeyError. If I do not use a formatted string, it simply prints the {variablename} literally.

Answer 1

You cannot pass a dictionary straight into create_engine() as a substitute for the url, however you can pass a sqlalchemy.engine.url.URL object which can be instantiated easily with a dict.

From the docs:

This object is suitable to be passed directly to a create_engine() call.

For example:

from sqlalchemy.engine.url import URL

config = dict(
    drivername='driver',
    username='username',
    password='qwerty1',
    host='127.0.0.1',
    port='5000',
    database='mydb',
    query={'encoding': 'utf-8'}
)

url = URL.create(**config)

print(url)  # driver://username:[email protected]:5000/mydb?encoding=utf-8

engine = create_engine(url, echo=True)

However, the issues you are having with passing in a string formatted URL aren't likely to be a SQLAlchemy problem as the string formatting would take place before create_engine() gets it's hands on the string. Unfortunately your question doesn't include an example that reproduces the exact problem you are having, so I cannot say much more than that.