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.
python 3.8.1 SQLAlchemy 1.3.12
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()
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()
I will post it on github, so please see that for details.
https://github.com/tasogarei/sqlalchemy_multi_session_sample
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.
Recommended Posts