How do I use SqlAlchemy?

[will-kuk-self]

What is the proper way to use SqlAlchemy as a driver?

I notice a file called SqlAlchemyPooledConnectionProvider, but I am not sure if I need to call this class explicitly.

[karenc-bq]

Hi @will-kuk-self, documentation and the sample codes for SqlAlchemyPooledConnectionProvider can be found here. Please let us know if you have any other questions.

[samuelstuetz]

I don't understand how one should use SqlAlchemyPooledConnectionProvider from the examples.
I'd expect to create_engine(..., creator = ....) -> to get an Engine.
e.g. in fast api I want Session = sessionmaker(engine) then I'd know what to do.
But the lifecycle of the provide looks like I still have to wrap this in any sort of Engine object.
Also I tried various setups with pymysql and aiomysql and always ran into some form of compatibility issues.

How would one wire up pymysql or an async driver with this driver in a way that works for fastapi or similar frameworks using sqlalchemy?

[karenc-bq]

Hi @samuelstuetz, the wrapper currently only supports Psycopg and MySQL Connector/Python. Support for SqlAlchemy is in the backlog. We will provide an update here once it is released.

[will-kuk-self]

Thank you for the documentation, it is useful to learn how to use SqlAlchemyPooledConnectionProvider.

My remaining question is about if SqlAlchemy is supported as a driver like psycopg. When I first read https://github.com/aws/aws-advanced-python-wrapper/blob/main/docs/using-the-python-driver/DriverDialects.md#list-of-available-driver-codes, I thought this was a case. Yet, I think I may have misunderstanding after digging more in the code.

[karenc-bq]

Hi @will-kuk-self, unfortunately this wrapper currently doesn't support SqlAlchemy as a driver. Support for SqlAlchemy is in our backlog, but we cannot comment on a timeline right now. We understand this may be blocking for you, thank you for your patience.

[will-kuk-self]

Thank you @karenc-bq . I have another question related.
Does SqlAlchemyPooledConnectionProvider work with the IAM Authentication plugin?

[will-kuk-self]

We figured it out. IAM Authentication plugin works with SqlAlchemyPooledConnectionProvider.
We also discovered without setting iam_region, the IAMAuthPlugin takes additional 80 ms to look up "available regions" for each request.
https://github.com/aws/aws-advanced-python-wrapper/blob/main/aws_advanced_python_wrapper/utils/region_utils.py#L53