models module for flask application

Question

I'm building a flask application foo, up until this point this is how files are organized

foo
|-- app
|   |-- __init__.py
|   |-- models.py
|
|-- foo.py

in __init__.py I define a function to create the app, which it is then called from foo.py. This is how __init__.py looks like:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

def create_app(config_name):
    app = Flask(__name__)
    ...
    db.init_app(app)

The module models.py imports db and access an existing database. So far I've been using db.engine.execute(<...>) to make queries, but it seems kind of inefficient. For example, I have a table called var which I constantly access in my app, so I define a class in models.py

class Var(object):       
   def query ...

but I'd like to use alchemy instead:

class Var(db.Model):
    ...

I tried to follow the answers here to build my application, but these are the problems I've found so far

If I insert base.metadata.reflect(db.engine) inside the create_app function I get an error saying I need to create a context, which doesn't make a lot of sense, since this is the the place where I'm creating it. Of course, if I do with app.app_context(): base.metadata.reflect(db.engine) I can get around, but then the problem is still there when I create the class Var:

class Var(db.Model): table = db.Model.metadata.tables['var']

The dictionary is empty, so I get a KeyError.

When I try to create a context inside models to create my class Var it also fails, because it cannot import it.

I've tried all the suggestions I've found, but none of them seems to work. Thanks for your help

You don't need to set `table` inside the model, `db.Model` already sets the table for you. — Luis Orduz, Aug 27 '18 at 20:58
@LuisOrduz I see, so how do I link the the class `Var` to the table `var`? — caverac, Aug 28 '18 at 11:11
The class `Var` is the table. To create it you just need to import the class to where you're running the `create_all` method. — Luis Orduz, Aug 28 '18 at 11:32
This is what I get `ArgumentError: Mapper Mapper|Var|var could not assemble any primary key columns for mapped table 'var'`, which I assume implies I need to define the structure of the table (columns, primary keys, ...), is that the solution? — caverac, Aug 28 '18 at 12:26
You aren't adding a primary key to your table. `class Var(db.Model): ... id = db.Column(db.Integer, primary_key=True) ...` — Luis Orduz, Aug 28 '18 at 13:43
@LuisOrduz Thanks, how about the other columns? Do I need to declare them as well? Say the table `var` has columns `c1`, `c2`, `c3`, `id`, do I need write `c1 = db.Column(db.Integer), ...`. This would imply to pretty copy the structure of the db into my code, which will turn out into a problem if the db is migrated — caverac, Aug 28 '18 at 13:48
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/178939/discussion-between-luis-orduz-and-caverac). — Luis Orduz, Aug 28 '18 at 13:50

score 2 · Accepted Answer · answered Aug 28 '18 at 14:12

The problem is that you're using Flask-SQLAlchemy that, like most flask extensions, is tied with flask and, for running certain tasks, it needs a flask context but, to reflect an already existing database, you need an engine independent of any request.

You could probably still use db by using with app.app_context() wherever you need, but it's probably better to not use Flask-SQLAlchemy and instead use SQLAlchemy directly and use create_engine, like in the question you linked.

models module for flask application

1 Answers1