Home » Open Source » Programming Interfaces » sql query (oracle 10g)
sql query [message #687568] Thu, 06 April 2023 15:04 Go to next message
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
Re: sql query [message #687569 is a reply to message #687568] Thu, 06 April 2023 19:08 Go to previous message
Barbara Boehmer
Messages: 9095
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:
  


Current Time: Sun May 19 09:16:07 CDT 2024