Python throws valueError when I try to run the following code while forming string

Question

import datetime
from datetime import date

Start_Date = date(2010, 01, 01)
market = 1
Query_PNL = """SELECT rptday,extract(year from rptday),extract(month from rptday),a.botid,closetoclosepnl,
                case when closetoclosepnl > 0 then 1
                    when closetoclosepnl < 0 then 0
                END AS PNL_score
                FROM RESEARCH.ADMIN.BOTSDAILYPNL a
                   --order by botid desc, rptday asc;
            right join

                (SELECT marketid,botid,modelid
                FROM RESEARCH.ADMIN.BOTS
                where modelid = 2018 and botname like '%BackTesting')b
                on a.MARKETID = b.marketid and a.BOTID = b.botid
                where a.rptday >='2010-01-01' and a.rptday <='2010-01-31' and a.MARKETID = %s and PNL_score is not null
                order by rptday asc""" %(market)
print Query_PNL

@alfasin: that could be true, just realised it. I will try re-arranging the query and will run again. Thank you — A.S
– A.S, Commented Nov 29, 2017 at 22:24

Mureinik · Accepted Answer · 2017-11-29 22:25:38Z

You need to escape the % character you're using in the like operator (by doubling it), so python doesn't attempt to evaluate it:

Query_PNL = """SELECT rptday,extract(year from rptday),extract(month from rptday),a.botid,closetoclosepnl,
                case when closetoclosepnl > 0 then 1
                    when closetoclosepnl < 0 then 0
                END AS PNL_score
                FROM RESEARCH.ADMIN.BOTSDAILYPNL a
                   --order by botid desc, rptday asc;
            right join

                (SELECT marketid,botid,modelid
                FROM RESEARCH.ADMIN.BOTS
                where modelid = 2018 and botname like '%%BackTesting')
                on a.MARKETID = b.marketid and a.BOTID = b.botid
                where a.rptday >='2010-01-01' and a.rptday <='2010-01-31' and a.MARKETID = %s and PNL_score is not null
                order by rptday asc""" %(market)

Collectives™ on Stack Overflow

Python throws valueError when I try to run the following code while forming string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related