Shorthand for querying a table in Flask-SQLAlchemy

Question

Say I have a model named User, and a tabled called followers:

followers = db.Table( 'followers', db.Column('follower_id', db.Integer, db.ForeignKey('user.id')), db.Column('followed_id', db.Integer, db.ForeignKey('user.id')) ) class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) followed = db.relationship( 'User', secondary=followers, primaryjoin=(followers.c.follower_id==id), secondaryjoin=(followers.c.followed_id==id), backref=db.backref('followers', lazy='dynamic'), lazy='dynamic' )

There are two ways to query the User model:

db.session.query(User).filter(...).all()
User.query.filter(...).all()

The latter is considered a shorthand for the former, because they are functionally identical but it's more compact. However, when comes to the table,followers.query.filter(...).all() gives me an error:

AttributeError: 'Table' object has no attribute 'query'

Is there a shorthand for db.session.query(followers).filter(...).all()? or, how can I get a query object from a table object?

univerio · Accepted Answer · 2017-07-27 16:35:54Z

flask-sqlalchemy defines query only on db.Model, so while you can do Foo.query on anything that subclasses db.Model, you cannot do the same on Table instances.

You could monkeypatch that in, I suppose, but I would not recommend doing that.

krassowski · Accepted Answer · 2017-07-28 00:17:09Z

Adding to @univerio answer: Instead monkey-patching you could wrap the flask_sqlalchemy.Table into your custom class, which would include the query property as shorthand.

class QueryableTable: def __init__(self, *args, **kwargs): table = db.Table(*args, **kwargs) super().__setattr__('_flask_sa_table', table) @property def query(self): return db.session.query(self._flask_sa_table) def __getattr__(self, name): return getattr(self.__dict__['_flask_sa_table'], name) def __setattr__(self, name, value): return setattr(self._flask_sa_table, name, value)

Use it as followers = QueryableTable('followers', db.Column(...), ...)

ionheart · Accepted Answer · 2017-07-28 05:53:51Z

There might be an issue with your Follower object you might wanna check it out again.

Follower.query.all() should work

If you happen to have have a relationship in your User with followers such as.

class User(): followers = db.relationship("Followers", lazy='dynamic')

You can execute via:

user = User.query.get(id=1) user.followers.all()

*Updated

The declaration of your followers table is not equivalent to how you declared the User table. User table is base on the db.Model and db.Model has a query class attached to it: http://flask-sqlalchemy.pocoo.org/2.1/api/#flask.ext.sqlalchemy.Model

Also you can check out the road to enlightenment part at the bottom of the page.

http://flask-sqlalchemy.pocoo.org/2.1/quickstart/#quickstart

In my question, followers isn't a field of the model User, it's a separate table.
Kindly post your follower and user schema or table definition
Ok, after posting it has something to do with how you declared the followers table. I have updated the answer for you. The followers table isn't a class of db.Model that's why you can't execute a query class.

panchamk · Accepted Answer · 2017-07-27 07:28:34Z

To use session.query() method to retrieve the rows from the table, you have to put the table name inside the query().

For example: For table name "fellowers", put session.query(fellowers).filter_by(id = fellow_id).one()

yes that's true, but my question is about a shorthand for session.query(fellowers).filter_by(id = fellow_id).one()

Bogdan Korecki · Accepted Answer · 2020-01-23 08:49:12Z

I am not sure how conventional is the solution that I used, but I took pandas.read_sql_query (link) so it works more or less that way:

pandas.read_sql_query('SELECT your_table_column from followers WHERE follower_id = your_id', your_app.config['SQLALCHEMY_DATABASE_URI'])

That gives me all records which are related to required follower_id as a pandas dataframe, which is possible to process further.

Collectives™ on Stack Overflow

Shorthand for querying a table in Flask-SQLAlchemy

5 Answers 5

Comments

Comments

4 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

1 Comment

Comments

Related