OraFAQ Forum: Programming Interfaces

Home » Open Source » Programming Interfaces » sql query (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query [message #687568]

Thu, 06 April 2023 15:04

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I am trying to pass the arguments to the below query in python and getting quoted extra in the below field.

can you let me know how to remove the quote.

prgname=sys.argv[0]
ctry=sys.argv[1]
city=sys.argv[2]
area=sys.argv[3]
code=sys.argv[4]
date=sys.argv[5]

 
 postgreSQL_select_Query2 = "select distinct(CONCAT('DDD_',a.ctry,'_',a.city,'_',a.area,'_FC_SSS',
 hierarchy_num,'_',file_code,'_%s_%s.csv')) as INPUT_FILE from file_types a  JOIN prod b  ON a.area = b.area 
 and a.city = b.city and a.ctry = b.ctry where a.ctry= %s and a.city= %s and a.area= %s "


cursor.execute(postgreSQL_select_Query2, (code, date, ctry, city, area))

Current Output Received :

DDD_EU_CHENNAI_ALANDUR_FC_SSS01_WEIGHT_'SD'_'2023'.csv

Expected Output Received :

DDD_EU_CHENNAI_ALANDUR_FC_SSS01_WEIGHT_SD_2023.csv

Report message to a moderator

Re: sql query [message #687569 is a reply to message #687568]

Thu, 06 April 2023 19:08

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

I know nothing about python or postgreSQL. However, comparing it to SQL*Plus substitution variables, if the values are passed with quotes, then you get quotes in the output as shown below.

SCOTT@orcl_12.1.0.2.0> select concat ('WEIGHT','_&s1._&s2..csv') as INPUT_FILE
  2  from   dual
  3  /
Enter value for s1: ''SD''
Enter value for s2: ''2023''

INPUT_FILE
----------------------
WEIGHT_'SD'_'2023'.csv

1 row selected.

If you pass the values without quotes, then you don't get quotes.

SCOTT@orcl_12.1.0.2.0> select concat ('WEIGHT','_&s1._&s2..csv') as INPUT_FILE
  2  from   dual
  3  /
Enter value for s1: SD
Enter value for s2: 2023

INPUT_FILE
------------------
WEIGHT_SD_2023.csv

1 row selected.

So, it may have more to do with the values being passed than the query. However, if you have no control over whatever generates the quotes in the values, then another alternative is to remove them by using the replace function to replace each quote with an empty string.

SCOTT@orcl_12.1.0.2.0> select concat ('WEIGHT', REPLACE ('_&s1._&s2..csv', '''', '')) as INPUT_FILE
  2  from   dual
  3  /
Enter value for s1: ''SD''
Enter value for s2: ''2023''

INPUT_FILE
------------------
WEIGHT_SD_2023.csv

1 row selected.

Note: Two single quotes within other singles quotes results in one single quote.

I have moved this post from the SQL and PL/SQL sub-forum to the programming interfaces sub-forum, where it may get the attention of people familiar with python and/or postgresql.

[Updated on: Thu, 06 April 2023 19:15]

Report message to a moderator

Previous Topic:

export the query into csv file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 15:11:22 CDT 2024