Parameterized SQL query

[purnima1]

Hi Experts,

I have one sql file(workphone.sql) which has one query written in that:

query >>


SELECT * FROM <TABLE_NAME> WHERE <COLUMN_NAME>=? AND <COLUMN_NAME2> IS NOT NULL

Now I am calling this in separate script

import cx_Oracle from datetime import datetime #reading query from file #f= open('C:/Users/pubhatia/Documents/learning/python/query/test.sql') f = open('C:/Users/pubhatia/Documents/learning/python/query/work_phone.sql') #print(f.read()) filer = f.read() print(filer) print(repr(filer)) f.close() #filer3=filer #source_name ='PWS' source_name= ('PWS') #filer=filer.replace("\'","")	#print( repr(filer3 ) filer2="select * from dual" if filer==filer2: print('same value') else: print ('no same ') #create connection string conn_str = u'user/pwd@db'	#setting up connection conn = cx_Oracle.connect(conn_str) c = conn.cursor()	#c.execute("select WORK_PHONE,source_sys_cd,source_sys_id from mdm_people_standard where source_sys_cd ='%s' AND WORK_PHONE IS NOT NULL"%(source_name)) c.execute(filer,'PWS') start_time = datetime.now() for row in c:	print(row) conn.close() end_time = datetime.now() print('Duration: {}'.format(end_time - start_time))

Now When I am using 3 formats I am getting error for all format

source_name= ('PWS',) c.execute(filer,source_name)

source_name= ('PWS') c.execute(filer,source_name)

source_name= ('PWS') c.execute(filer,'PWS')

error I am getting for all scenario

Error:
Traceback (most recent call last): File "oracle_test.py", line 35, in <module> c.execute(filer,source_name) cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Please confirm how can we pass value in parameterised sql

---

Hi All,
I did some changes mentioned in below given link and its working fine

http://www.oracle.com/technetwork/articl...91105.html

If anyone has better resolution do let me now

[nilamo]

From your own link: http://www.oracle.com/technetwork/articl...05.html#t7

import cx_Oracle con = cx_Oracle.connect('pythonhol/[email protected]/orcl') cur = con.cursor() cur.prepare('select * from departments where department_id = :id') cur.execute(None, {'id': 210}) res = cur.fetchall() print res cur.execute(None, {'id': 110}) res = cur.fetchall() print res cur.close() con.close()

Using :1 might work for a tuple of params, but it looks like the recommended way is to use named params with a dict.