CODEWITHSUNDEEP
pythonsqlsqlalchemypyramid
Jonathan Ong
Jonathan Ong

Reputation: 20325

Understanding intermediary SQL statements in SQLAlchemy

So I have table, and I want to have users be able to view the table in whichever order they'd like. Here's an example:

class Thing(Base):
    __tablename__ = 'thing'
    id = Column(Integer, primary_key=True)
    amount = Column(Integer)
    date = Column(DateTime, default=datetime.now())

    def __init__(self, amount):
        self.amount = amount

So I'd like to make the following query:

 if sort == 'amount':
     orderby = Thing.amount.desc()
 elif sort == 'date':
     orderby = Thing.date.desc()

 things = DBSession.query(Thing).order_by(orderby).all()

Main questions:

  1. When I set the orderby statement, am I actually calling anything or is the statement just being set?
  2. Am I doing this correctly and efficiently?

  3. Can I do this similarly with filters?

    filterby = Thing.amount == 5

    things = DBSession.query(Thing).filter_by(filterby).all()

Thanks!

Upvotes: 1

Views: 212

Answers (1)

Neel
Neel

Reputation: 21297

Ans 1: When you are giving any syntax what exactly it will return you can check with type of that syntax like

When you write

In [5]: Thing.amount.desc()
Out[5]: <sqlalchemy.sql.expression._UnaryExpression object at 0x199a5d0>

It will create one object of type expression._UnaryExpression. So this expression you can give to sqlalchemy when we want unary operation. Now when you print it In [6]: a = Thing.amount.desc()

In [7]: print a
thing.amount DESC

So when query run it will be converted in thing.amount DESC. So when you create expression it will not call any thing, it will just create structure of query.

Ans: 2 When you are creating query it will create object which is needed. For filter and order by it need expression so if you pass directly to the order_by or filter then it will create object for the same internally. So you can use both way.

Ans: 3

In [8]: Thing.amount == 5
Out[8]: <sqlalchemy.sql.expression._BinaryExpression object at 0x1a1dbd0>

This is also a expression so you can give expression to filter_by.

You will get more idea about expression from sqlalchemy site.

Upvotes: 3

Related Questions