Pattern for a Flask App using (only) SQLAlchemy Core

Question

I have a Flask application with which I'd like to use SQLAlchemy Core (i.e. I explicitly do not want to use an ORM), similarly to this "fourth way" described in the Flask doc:

http://flask.pocoo.org/docs/patterns/sqlalchemy/#sql-abstraction-layer

I'd like to know what would be the recommended pattern in terms of:

How to connect to my database (can I simply store a connection instance in the g.db variable, in before_request?)
How to perform reflection to retrieve the structure of my existing database (if possible, I'd like to avoid having to explicitly create any "model/table classes")

Philipp Faster · Accepted Answer · 2023-06-01 06:31:36Z

10

Correct: You would create a connection once per thread and access it using a threadlocal variable. As usual, SQLAlchemy has thought of this use-case and provided you with a pattern: Using the Threadlocal Execution Strategy
```
 db = create_engine('mysql://localhost/test', strategy='threadlocal')
 db.execute('SELECT * FROM some_table')
```

Note: If I am not mistaken, the example seems to mix up the names db and engine (which should be db as well, I think).

I think you can safely disregard the Note posted in the documentation as this is explicitly what you want. As long as each transaction scope is linked to a thread (as is with the usual flask setup), you are safe to use this. Just don't start messing with threadless stuff (but flask chokes on that anyway).

Reflection is pretty easy as described in Reflecting Database Objects. Since you don't want to create all the tables manually, SQLAlchemy offers a nice way, too: Reflecting All Tables at Once
```
 meta = MetaData()
 meta.reflect(bind=someengine)
 users_table = meta.tables['users']
 addresses_table = meta.tables['addresses']
```

I suggest you check that complete chapter concerning reflection.

edited Jun 1, 2023 at 6:31

Philipp Faster

982 silver badges11 bronze badges

answered Mar 4, 2014 at 22:57

javex

7,6447 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

cjauvin Over a year ago

Thanks, that's useful. Would you know where to put this code? This answer provides a possible way, which seems to work with what I want to do.

javex Over a year ago

Actually, I would just do it during application startup. This way, once your application runs you know everything is up and don't have a first request that takes longer because of reflection. Thus: Define it at the import level. Then either manually assign your tables like above or only access the required one during a request like in the answer your linked.

monostop Over a year ago

I'm a bit confused about step 1. In the OP cjauvin suggested to get a connection from the engine and store it on the g object. i.e. @app.before_request def before_request(): g.db = engine.connect() and use that throughout the request. Is this the same as creating the engine using the 'threadlocal' strategy? Or do you need to do both?

javex Over a year ago

Basically yes, mostly because flask heavily relies on the assumption that you have a thread per request. If you were to alter this and have multiple requests per thread (in whichever way you achieve this) you'd want to prefer something that doesn't rely on threadlocal variables (i.e. events, callbacks, etc.).

Matthew Moisen Over a year ago

I don't think you need to create the engine using threadlocal mode. The g object is already threadlocal. Inside before_request you can create a new connection and add it to g. Inside your route you can execute g.conn.execute(select([Foo])). Then in after_request you can issue g.conn.close(). Do you agree with this?

Collectives™ on Stack Overflow

Pattern for a Flask App using (only) SQLAlchemy Core

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related