SQLAlchemy order_by formula result
Question: I am a novice in Python. Based on
[this](http://stackoverflow.com/questions/592209/find-closest-numeric-value-
in-database/) SO post, I created a SQL query using PYODBC to search a MSSQL
table of historic option prices and select the option symbol with a strike
value closest to the desired value I specified. However, I am now trying to
teach myself OOP by re-factoring this program, and to that end I am trying to
implement the ORM in SQLAlchemy.

I cannot figure out how to implement a calculated Order_By statement. I don't
think a calculated column would work because desired_strike is an argument
that that is specified by the user(me) at each method call.

Here is the (simplified) original code:

    
    
    import pyodbc
    
    def get_option_symbol(stock, entry_date, exp_date, desired_strike):
        entry_date = entry_date.strftime('%Y-%m-%d %H:%M:%S')
        exp_date = exp_date.strftime('%Y-%m-%d %H:%M:%S')
    
        cursor.execute("""select top(1) optionsymbol 
                        from dbo.options_pricestore 
                        where underlying=? 
                        and quotedate=? 
                        and expiration=? 
                        and exchange='*' 
                        and option_type=?
                        order by abs(strike - ?)""",
                        stock, 
                        entry_date,
                        exp_date,
                        desired_strike,
                        )
        row = cursor.fetchone()  
        return row
    

Maybe not the most Pythonic, but it worked. I am now encapsulating my formerly
procedural code into classes, and to use SQLAlchemy's ORM, except that in this
one case I cannot figure out how to represent abs(strike - desired_strike) in
the Order_By clause. I have not used lambda functions much in the past, but
here is what I came up with:

    
    
    import sqlalchemy
    
    class Option(Base):
    __tablename__= 'options_pricestore'
    <column definitions go here>
    
    def get_option_symbol(stock, entry_date, exp_date, desired_strike):
        entry_date = entry_date.strftime('%Y-%m-%d %H:%M:%S')
        exp_date = exp_date.strftime('%Y-%m-%d %H:%M:%S')
    
        qry = session.query(Option.optionsymbol).filter(and_
                (Option.underlying == stock, 
                    Option.quotedate == entry_date,
                    Option.expiration == exp_date,
                    Option.option_type== "put",
                    Option.exchange == "*")
                ).order_by(lambda:abs(Option.strike - desired_strike))
    
        return qry
    

I get "ArgumentError: SQL expression object or string expected" - Any help
would be greatly appreciated.


Answer: `order_by` wants a string - give it to it:

    
    
    qry = session.query(Option.optionsymbol).filter(and_
                (Option.underlying == stock, 
                    Option.quotedate == entry_date,
                    Option.expiration == exp_date,
                    Option.option_type== "put",
                    Option.exchange == "*")
                ).order_by('abs(strike - %d)' % desired_strike)
    

