How to use %s with SQL in Python when there's another SQL field that uses a %

Question

I am trying to use a separate python file to obtain the first date of the current month along with the current date and to use those dates in SQL. It works with other SQL statements included in my python code, but it doesn't work in one of the SQL statements due to there being a field labeled 'CO%'.

The code runs when the % is taken out, but then it doesn't obtain any data like it should. Because of this, I know the error is due to the % in the 'CO%'. The %s allows me to use the RP.get_rep_date_ora() & RP.get_first_of_cur_month_ora() statements within the SQL. In other words, the %s's are supposed to be replaced by those dates from the python statement at the end.

SQL5 = '''
select count(*) as CNT, sum(A.USER_SHIPPED_QTY) as QTY, sum(( 
    A.USER_SHIPPED_QTY) * A.UNIT_PRICE) as VALUE
    from SHIPPER_LINE A, SHIPPER B
    where  B.PACKLIST_ID = A.PACKLIST_ID
    and A.CUST_ORDER_ID like ('CO%')
    and B.SHIPPED_DATE between ('%s') and ('%s')
    ''' % (RP.get_first_of_cur_month_ora(), RP.get_rep_date_ora())

The "% (RP.get_first_of_cur_month_ora(), RP.get_rep_date_ora())" are the statements that use another python file to acquire the two dates, and they work fine in my other SQL statements.

This is the error message I receive when running the code:

Traceback (most recent call last):
  File "C:\Python27\Lib\site-packages\Pythonwin\pywin\framework\scriptutils.py", line 323, in RunScript
    debugger.run(codeObject, __main__.__dict__, start_stepping=0)
  File "C:\Python27\Lib\site-packages\Pythonwin\pywin\debugger\__init__.py", line 60, in run
    _GetCurrentDebugger().run(cmd, globals,locals, start_stepping)
  File "C:\Python27\Lib\site-packages\Pythonwin\pywin\debugger\debugger.py", line 654, in run
    exec cmd in globals, locals
  File "C:\Project\CSReport\CSReport.py", line 3, in <module>
    """
  File "C:\Project\CSReport\CSReport.py", line 140, in DoSomething
    ''' % (RP.get_first_of_cur_month_ora(), RP.get_rep_date_ora())
ValueError: unsupported format character ''' (0x27) at index 276

Can you get the resultant SQL as a string? I'm not to familiar with Python, but you may need to use and A.CUST_ORDER_ID like (''CO%'') in order to get the single quotes in the resultant string. — unleashed
– unleashed, Commented May 15, 2017 at 18:34

Cory Kramer · Accepted Answer · 2017-05-15 18:13:13Z

2

You can use str.format

SQL5 = '''
select count(*) as CNT, sum(A.USER_SHIPPED_QTY) as QTY, sum(( 
    A.USER_SHIPPED_QTY) * A.UNIT_PRICE) as VALUE
    from SHIPPER_LINE A, SHIPPER B
    where  B.PACKLIST_ID = A.PACKLIST_ID
    and A.CUST_ORDER_ID like ('CO%')
    and B.SHIPPED_DATE between ('{}') and ('{}')
    '''.format(RP.get_first_of_cur_month_ora(), RP.get_rep_date_ora())

answered May 15, 2017 at 18:13

Cory Kramer

119k19 gold badges176 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use %s with SQL in Python when there's another SQL field that uses a %

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