2

I'm currently working on a project with Flask and SQLAlchemy, basically forum software like discourse but in Python 3.

There are Two models at the moment, Forum and Threads

I've got a Jinja template that should be generating table records of forum threads, please see below.

{% for thread in Forum.ForumThreads.filter_by(TagID.in_(TagsToShow)): %}
    <TR Class="ThreadRecord">
        <TD><a href="{{thread.ForumID}}/Thread/{{thread.ThreadID}}">{{thread.Title}}</a></TD>
        <TD class="ThreadTag{{thread.TagID}}">{{thread.Tag.Name}}</TD>
        <TD>{{thread.PostList.count()}}</TD>
        <TD>{{thread.User.Name}}</TD>
        <TD>{{thread.CreationDate}}</TD>
    </TR>
{% endfor %}

This is called by the following view (slightly simplified).

@app.route('/Forum/<int:URLForumID>/ForumThreads')
    def ThreadsTable(URLForumID):
    TagsToShow = (1,2,3,4)
    Forum = models.Forum.query.filter_by(ForumID=URLForumID).first()
    return flask.render_template('ForumThreads.html', Forum=Forum, TagsToShow=TagsToShow)

However, every time I try to run it I get the error "jinja2.exceptions.UndefinedError: 'TagID' is undefined".

I've tried running it with {% for thread in Forum.ForumThreads.filter_by(TagID=1): %} and it seems to run fine, so my problem appears to be in how I am calling the .in_() method. I've searched through the documentation for SQLalchemy but have been unable to find the answer, would anyone be able to point me in the right direction?

I don't know if it helps, but below are the two stripped down versions of the SQLalchemy models used.

class Forum(db.Model):
    __tablename__ = "Forum"
    ForumID = db.Column(db.Integer, primary_key=True)
    ForumName = db.Column(db.Unicode(20), unique=True, nullable=False)
    CreationDate = db.Column(db.DateTime, default=datetime.datetime.utcnow(), nullable=False)
    ForumThreads = db.relationship("Thread", backref="Forum", lazy='dynamic')
    __table_args__ = {'mysql_engine': 'InnoDB'}

class Thread(db.Model):
    __tablename__ = "Thread"
    ThreadID = db.Column(db.Integer, primary_key=True)
    ForumID = db.Column(db.Integer, db.ForeignKey("Forum.ForumID"), nullable=False)
    Title = db.Column(db.Unicode(100), nullable=False)
    UserID = db.Column(db.Integer, db.ForeignKey("User.UserID"), nullable=False)
    TagID = db.Column(db.Integer, db.ForeignKey("TagTypes.TagID"), nullable=False)
    CreationDate = db.Column(db.DateTime, default=datetime.datetime.utcnow(), nullable=False)
    __table_args__ = (db.ForeignKeyConstraint(['ForumID'], ['Forum.ForumID']),
                  db.ForeignKeyConstraint(['UserID'], ['User.UserID']),
                  db.ForeignKeyConstraint(['TagID'], ['TagTypes.TagID']),
                  {'mysql_engine': 'InnoDB'})
Devasta
  • 1,489
  • 2
  • 17
  • 28

1 Answers1

6

TagID is undefined because it isn't directly accessible. It is part of your Thread model. You need to go through Thread in order to reference it: Thread.TagID.

Once you correct this, however, you will most likely encounter a TypeError. filter_by accepts keyword arguments, not positional ones. In order to filter using in_, you need to use the filter method. filter_by accepts keyword arguments and builds the filters based on that.

SomeModel.query.filter_by(a=1, b=2)

will roughly translate to

SELECT * FROM somemodel WHERE a = 1 AND b = 2

filter, on the other hand, accepts expressions (type BinaryExpression) as arguments. The above query would be expressed as

SomeModel.query.filter(SomeModel.a == 1, SomeModel.b == 2)

Here, SomeModel.a is an InstrumentedAttribute. InstrumentedAttribute objects possess methods that allow you to perform comparisons that are more complex that equality.

SomeModel.query.filter(SomeModel.a.in_((1, 2)))

will roughly translate to

SELECT * FROM somemodel WHERE a IN (1, 2)
dirn
  • 19,454
  • 5
  • 69
  • 74
  • Hi Dirn, Many thanks for your response. A quick question (with possibly a not-so quick answer) if you don't mind: Why is the TagID with my current setup seemingly not accessible? I'm able to filter just fine if I set it with a single value, just not with the in_() method. I don't doubt it, just want to know! I've tried {% for thread in Forum.ForumThreads.filter(Forum.ForumThreads.TagID.in_(TagsToShow)): %} now and I am getting getting the below: jinja2.exceptions.UndefinedError: 'sqlalchemy.orm.dynamic.AppenderBaseQuery object' has no attribute 'TagID' Have I misunderstood the answer? – Devasta Sep 08 '14 at 23:06
  • 1
    Because `TagID` is being used as the name of a [keyword argument](https://docs.python.org/3/tutorial/controlflow.html#keyword-arguments). – dirn Sep 08 '14 at 23:19
  • how to get a column value in the query SomeModel.query(SomeModel.Id).filter(SomeModel.a.in_((1, 2))) is this possible, I am getting BaseQuery exception – newuser Jun 24 '20 at 07:34
  • @newuser please ask a new question with more details. – dirn Jun 24 '20 at 13:15
  • please find the link [link]https://stackoverflow.com/questions/62568985/get-all-employees-whose-job-code-in-a-list – newuser Jun 25 '20 at 06:19