0

I tried to find the string formats to write down a query for a database in Python from this post.

But when I tried to implement the following code

cursor = session.cursor()

query_1 = ("""Drop table PP_oap_KK_T.shipping_identifier1;\
Create table PP_oap_KK_T.shipping_identifier1 as ( 
sel 
a.tracking_number, 
shipping_company, 
shipping_company_other, 
Case when shipping_company_other is null  then 0 else 1 end as Free_Text_YoNo, 
Case when shipping_company like  any ( '%other%','%None%') then 1 else 0 end as Ship_other_none_YN, 
Case when Ship_other_none_YN = 0 and Free_Text_YoNo =0 then 1 else 0 end as Dropdown_YN, 
Case when tracking_number like any ('%1%', '%2%' ,'%3%','%4%','%5%','%6%','%7%','%8%','%9%','%0%') then 1 else 0 end as Digit_YN, 
Case when tracking_number like any ( '% %', '%#%', '%@%' , '%!%' , '%$%', '%^%', '%&%', '%*%', '%(%', '%)%', '%-%', '%+%', '%=%', '%,%', '%.%' , '%|%'  , '%\%' , '%/%' 
) then 1 else 0 end as SpecialCharacters_YN 
from PP_oap_KK_T.inr_tv6 a 
where Digit_YN = 1 and SpecialCharacters_YN =0 and character_length(tracking_number ) between 8 and 40 
and atkc_open_date between '2017-06-01' and '2017-06-30' 
and atkc_workflow_code in 'C' 
and dispute_reason in 'INR' 
sample 10000 
) with data  primary index(tracking_number); """)

cursor.execute(query_1)

But I am getting following error

Traceback (most recent call last):
  File "C:\Users\pbuddharaju\Desktop\jsonTesting.py", line 34, in <module>
    cursor.execute(query_1)
  File "C:\Users\pbuddharaju\AppData\Local\Programs\Python\Python36\lib\site-packages\teradata\udaexec.py", line 745, in execute
    self._execute(self.cursor.execute, query, params, **kwargs)
  File "C:\Users\pbuddharaju\AppData\Local\Programs\Python\Python36\lib\site-packages\teradata\udaexec.py", line 789, in _execute
    query = self.udaexec.config.resolve(query)
  File "C:\Users\pbuddharaju\AppData\Local\Programs\Python\Python36\lib\site-packages\teradata\udaexec.py", line 577, in resolve
    return self._resolve(value, sections, default, errorMsg)
  File "C:\Users\pbuddharaju\AppData\Local\Programs\Python\Python36\lib\site-packages\teradata\udaexec.py", line 604, in _resolve
    "escape '$' by adding another '$'.".format(value, error))
teradata.api.InterfaceError: ('CONFIG_ERROR', 'Unable to resolve "Drop table PP_oap_KK_T.shipping_identifier1;Create table PP_oap_KK_T.shipping_identifier1 as ( \nsel \na.tracking_number, \nshipping_company, \nshipping_company_other, \nCase when shipping_company_other is null  then 0 else 1 end as Free_Text_YoNo, \nCase when shipping_company like  any ( \'%other%\',\'%None%\') then 1 else 0 end as Ship_other_none_YN, \nCase when Ship_other_none_YN = 0 and Free_Text_YoNo =0 then 1 else 0 end as Dropdown_YN, \nCase when tracking_number like any (\'%1%\', \'%2%\' ,\'%3%\',\'%4%\',\'%5%\',\'%6%\',\'%7%\',\'%8%\',\'%9%\',\'%0%\') then 1 else 0 end as Digit_YN, \nCase when tracking_number like any ( \'% %\', \'%#%\', \'%@%\' , \'%!%\' , \'%$%\', \'%^%\', \'%&%\', \'%*%\', \'%(%\', \'%)%\', \'%-%\', \'%+%\', \'%=%\', \'%,%\', \'%.%\' , \'%|%\'  , \'%\\%\' , \'%/%\' \n) then 1 else 0 end as SpecialCharacters_YN \nfrom PP_oap_KK_T.inr_tv6 a \nwhere Digit_YN = 1 and SpecialCharacters_YN =0 and character_length(tracking_number ) between 8 and 40 \nand atkc_open_date between \'2017-06-01\' and \'2017-06-30\' \nand atkc_workflow_code in \'C\' \nand dispute_reason in \'INR\' \nsample 10000 \n) with data  primary index(tracking_number); ".  Parameter not found: Invalid placeholder in string: line 10, col 70.  If parameter substitution is not intended, escape \'$\' by adding another \'$\'.')

As we can see that we are getting '\n' as well as " \' " characters which should not happen in order to execute properly.

I also figured out that adding '\' to end of each line will eliminate the new line character but it is arduous work to do so for 20-30 queries.

Although previously I followed the format mentioned as an answer in the post, I still end up getting the "\'" error after escaping the single quotes in python code.

So I would like to know the best way to format the string so that errors are eliminated and also I can use the method frequently with ease.

HackChamp
  • 65
  • 10
  • You could chuck a `str.replace` on the end to replace `'\n'` with `' '` only a problem if you actually need `'\n` in your query – Steven Summers Aug 03 '17 at 05:58
  • Thank you. But can you also tell me how to deal with ` \'%x%\' ` . In order to execute in sql the query going to sql through string should not have the escape characters introduced by python. – HackChamp Aug 03 '17 at 06:08
  • I think those are introduced by sql, if you do `print(repr(s))` you'll see the format of the string that is sent. – Steven Summers Aug 03 '17 at 07:33
  • Thank you so much @StevenSummers. So how do I tackle this situation any idea, please. – HackChamp Aug 03 '17 at 07:57
  • Is there any reason that the top voted answer in the [post you linked to](https://stackoverflow.com/questions/5243596/python-sql-query-string-formatting) won't suffice (relying on Pythons automatic string literal concatenation to do multiline strings)? Or is that you want to submit the line breaks to the database in order to avoid submitting multiple DDLs in parallel which will cause Teradata to error out. – JNevill Aug 03 '17 at 17:24
  • @JNevill i have tried the answer in the post. In order to tell python to escape the single quotes in sql, I have to put ' \ ' before each " ' " which though I did so didn't turned out to be successful. For example query = 'sel * from abc where xyz = \'qwe\' ' – HackChamp Aug 04 '17 at 04:45

0 Answers0