Support multi session with SQLAlchemy

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.

Recommended Posts

Support multi session with SQLAlchemy
Use Enums with SQLAlchemy
Get table dynamically with sqlalchemy
Use DATE_FORMAT with SQLAlchemy filter
Introduction to RDB with sqlalchemy Ⅰ
How to update with SQLAlchemy?
Browse columns encrypted with sqlalchemy
How to Alter with SQLAlchemy?
Support yourself with Twitter API
Presentation Support System with Python3
How to Delete with SQLAlchemy?
How to handle session in SQLAlchemy
Use Azure SQL Database with SQLAlchemy
Connect to multiple databases with SQLAlchemy
Accelerate query generation with SQLAlchemy ORM
Combining polymorphic inheritance with many-to-many with SQLAlchemy
Introduction to RDB with sqlalchemy II
Using Sessions and Reflections with SQLAlchemy
How to INNER JOIN with SQLAlchemy