SQLAlchemy should accept a URL object in the constructor

[hornste]

On some databases on SQL Server, you need to pass an instance name in the form of "Server\Instance". SQLAlchemy does not handle this well, and you need to enclose the server name in three brackets {{{server}}}.

For example, to create an engine, you'd need to do something like this:

connection_url = URL.create(
    "mssql+pyodbc",
    query={
        "odbc_connect": f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={{{server}}};DATABASE={{{db}}};UID={{{user}}};PWD={{{password}}}"
    }
)
engine = create_engine(connection_url)

Simple solutions like url encoding the server doesn't work.

There's no way to get around this using the current ploomber implementation of db.py. What I propose is to overload the constructor so that it will accept a sqlalchemy.engine.URL object, which you can then use to call the sqlalchemy.create_engine function that you are using anyway.

I tried to work around this by first instantiating the ploomber SQLAlchemyClient, and then overwriting it with an engine, but that didn't seem to work:
client = SQLAlchemyClient(uri_goes_here)
client._engine = engine

[edublancas]

thanks for the feedback! We've gotten similar feedback in the past (passing the URI is too restrictive), so I think accepting a URL object makes sense.

In the meantime, would using the other DB client work?