how to use .query attribute in sqlacodegen models

Question

When I use sqlacodegen to create models.py I can't use User.query, getting the warning "[AttributeError: type object 'User' has no attribute 'query']". I think db.query is a very usable attribute, it's used in SQLAlchemy():

db = SQLAlchemy()

But I need to use sqlacodegen to create models for existing table in our system. Creation code below:

models.py

# coding: utf-8
from sqlalchemy import Column, Date, Float, Integer, String, Table, text
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata

class User(Base):
    __tablename__ = u'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(64))
    username = Column(String(64))
    role_id = Column(Integer)
    password_hash = Column(String(128))

Now I import models.py:

from models import *
@main.route('/', methods=['GET', 'POST'])
def index():
    form = NameForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.name.data).first()
...

When I run this, the warning given is:

AttributeError: type object 'User' has no attribute 'query'

I want to know how I can trans [class User(Base)] to a type like [class User(db.Model)] so it can use .query attribute? Or is there some other usable method to do that with [class User(Base) ]type?

Answer 1

It sounds like you're used to using Flask-SQLAlchemy, which includes the secret sauce to allow the functionality you mention.

The Flask Docs on SQLAlchemy indicate how to get that same functionality:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                     autoflush=False,
                                     bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

It'll enable the shorthand of User.query.... that you expect. The important line is the last one. You could alternatively use the db.Model class from Flask-SQLAlchemy instead of the 'pure' SQLAlchemy Base class.