Why is my this flask-sqlalchemy query failing

Question

I am trying to write a query which emits the stock data for a given ticker for the current day. My underlying model records the date and time in a datetime field. Currently, I am trying to query by ticker and then time_stamp.

@socketio.on("get initial data")
def on_initial_data(ticker):
    """
    Emits the initial stock data for given ticker.
    :param str ticker: ticker of stock to query.
    """

    socketio.emit("initial data",
                  [i.serialize for i
                   in Stock.query.filter_by(
                      ticker=ticker,
                      time_stamp=db.func.date(Stock.time_stamp) == date.today()).order_by(Stock.time_stamp)],
                  room=request.sid)

When this is executed however, I get the error:

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I am not sure why I am getting this because isn't the db.func.date() casting?

score 0 · Accepted Answer · answered Mar 23 '18 at 12:22

You've mixed filter_by() and filter(). See What's the difference between filter and filter_by in SQLAlchemy? and Flask-SQLAlchemy - Greater than or equal to. In short, you're comparing time_stamp for equality with an expression producing a boolean and the resulting query might look something like:

... WHERE ticker = :ticker AND time_stamp = (DATE(time_stamp) = :date) ...

Instead:

Stock.query.\
    filter_by(ticker=ticker).\
    filter(db.func.date(Stock.time_stamp) == date.today()).\
    order_by(Stock.time_stamp)

Why is my this flask-sqlalchemy query failing

1 Answers1