Associating text queries with a specific bind_key

[anzwhitney]

We've got a codebase with several different databases and a bunch of old text SQL queries executed via Session.execute (as well as plenty of actual ORM queries, but not all the text queries are easily convertible to ORM nor do we have the time right now to convert them all), and previously with Flask-SQLAlchemy 2.1 we would specify which bind to use for a given query with the bind kwarg like session.execute(text(<query>), bind=db.get_engine(db.app, <bindkey>).

Now with Flask-SQLAlchemy 3.x/SQLAlchemy 2.x it appears there's no longer a bind kwarg on Session.execute. I see in the SQLAlchemy docs that you can pass a bind_arguments dict to Session.execute, but at that point the execute statement is getting pretty ugly: session.execute(text(<query>), bind_arguments={'bind': db.engines[<bindkey>]}) feels awful verbose for what seems like a pretty simple use case.

When we upgraded to Flask-SQLAlchemy 2.5.1 as an intermediate step, we switched to creating scoped sessions for each bind key via a get_session method on our SQLAlchemy subclass (which we already had set up for other reasons):

    def get_session(self, bindkey):                                                                                                                                                                                                                                                                                                                              
         engine = self.get_engine(self.app, bindkey)                                                                                                                                     
         Session = self.create_scoped_session(options={"bind": engine})                                                                                                                  
         return Session()

...and then use db.get_session(<bindkey>) as a context manager where we actually execute the queries. We could of course keep doing this and just update to Flask-SQLAlchemy 3.x like so:

    def get_session(self, bindkey):                                                                                                                                                                                                                                                                                                                              
         engine = self.engines[bindkey]                                                                                                                                   
         Session = self._create_scoped_session(options={"bind": engine})                                                                                                                  
         return Session()

...but the fact that _create_scoped_session is now explicitly intended to be an internal method gives me pause - it seems we've gotten into the weeds without really meaning to do anything terribly complex. What is the intended mechanism in Flask-SQLAlchemy for running text queries against a specific database by bind key, given that the correct bind is typically chosen silently via the __bind_key__ set on a db.Model subclass, but there's no such thing if you're just executing some SQL text? I see that there's been some related discussion in #107 and #1017, but no real resolution in either case.