Avoid 'using before assignment' issue when defining 'case' in SQLAlchemy

Question

I have a Post model defined like this in flask-sqlalchemy:

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Post(db.Model):
    __tablename__ = 'post'
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.UnicodeText, nullable=False)
    post_time = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    update_time = db.Column(db.DateTime)

Therefore, each post will have a default post_time, but the update_time will be NULL until it's been updated.
What I want is to make the posts order by update_time primarily if it's update_time field value is not NULL, else order by post_time, like this:

POST_ORDER = case([(Post.update_time != None, Post.update_time)],
                  else_=Post.post_time)

But since I want to declare this POST_ORDER as a constant, there will be a Using variable before assignment issue.

from datetime import datetime
from sqlalchemy import case
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Using variable 'Post' before assignment here
POST_ORDER = case([(Post.update_time != None, Post.update_time)],
                  else_=Post.post_time)

class Post(db.Model):
    __tablename__ = 'post'
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.UnicodeText, nullable=False)
    post_time = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    update_time = db.Column(db.DateTime)

I know I can put this variable declaration after the Post class, but it's just a work around. How to avoid this issue? Which function in flask-sqlalchemy can I use?

EDIT:
Why I want to define the POST_ORDER as constant is that otherwise it will violate the DRY principle. A minimal reproducible example here:

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(120),
                     unique=True,
                     nullable=False,
                     index=True)


post_tag = db.Table(
    'post_tag',
    db.Column('post_id',
              db.Integer,
              db.ForeignKey('post.id'),
              primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True))


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.UnicodeText, nullable=False)
    post_time = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    update_time = db.Column(db.DateTime)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    # Repeat here
    post_order = case([(update_time != None, update_time)], else_=post_time)

    tags = db.relationship('Tag',
                           secondary=post_tag,
                           backref=db.backref('posts',
                                              lazy='dynamic',
                                              order_by=post_order.desc()),
                           lazy='dynamic',
                           order_by=Tag.name)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(120),
                     unique=True,
                     nullable=False,
                     index=True)

    # Repeat here
    # have to consider the class definition order
    post_order = case([(Post.update_time != None, Post.update_time)],
                      else_=Post.post_time)

    posts = db.relationship('Post',
                            backref='category',
                            lazy='dynamic',
                            order_by=post_order.desc())

Avoid 'using before assignment' issue when defining 'case' in SQLAlchemy

0 Answers0