Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pattern for a Flask App using (only) SQLAlchemy Core

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:

  1. How to connect to my database (can I simply store a connection instance in the g.db variable, in before_request?)

  2. 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")

like image 643
cjauvin Avatar asked Mar 03 '14 20:03

cjauvin


People also ask

Should I use SQLAlchemy core or ORM?

If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.

How do I connect my Flask app to SQLAlchemy?

Step 1 - Install the Flask-SQLAlchemy extension. Step 2 - You need to import the SQLAlchemy class from this module. Step 3 - Now create a Flask application object and set the URI for the database to use. Step 4 - then use the application object as a parameter to create an object of class SQLAlchemy.

Can I use Flask-SQLAlchemy without Flask?

All you need is to create a db connection and import your model to either flask, or non-flask app. I'd simply put the model in 'models.py' so (really just move the above from the flask main . py file and import/instantiate IPentry when needed).

Is Flask-SQLAlchemy the same as SQLAlchemy?

What is Flask-SQLAlchemy? Flask-SQLAlchemy is an extension for Flask that aims to simplify using SQLAlchemy with Flask by providing defaults and helpers to accomplish common tasks. One of the most sought after helpers being the handling of a database connection across the app.


1 Answers

  1. 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).

  2. 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.

like image 171
javex Avatar answered Oct 11 '22 01:10

javex