Introduction

AWS Aurora has different write and read endpoints. I have summarized how to support SQLAlchemy in python in such cases.

So, the premise is that the model is the same, only the endpoints are different for writing and reading.

environment

python 3.8.1 SQLAlchemy 1.3.12

query_property cannot be used

Unfortunately, query_property, which is a convenient function of SQLAlchemy, cannot be used because it expects to have one session in the application. In terms of code, it was very convenient to write as follows, but two sessions are absolutely necessary to support multi database.

User.query.all()

Then what to do

You can create two sessions and call them normally. So, it's like writing a query call using session.

session.query(User).all()

Also, be aware that the data acquired in one session will result in an error if you insert or update another session as it is. Specifically, the following code does not work. I think that it will work if you delete the state somewhere in the acquired data, but it is safer to transfer it to another object obediently.

read_session = read_session()
users = read_session.query(User).all()

write_session = write_session()
write_session.add_all()

Whole source code

I will post it on github, so please see that for details.

https://github.com/tasogarei/sqlalchemy_multi_session_sample

in conclusion

query_property It's convenient, but I gave up because I couldn't handle it. If the Model is divided for each database, I feel that if you create multiple declarative_base () well, you can link each one and it will work. However, it has not been verified. When I'm done with what I want to do now, I'll verify it.