Parameterized SQL query - Printable Version

Parameterized SQL query - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Parameterized SQL query (/thread-5546.html)

Parameterized SQL query - purnima1 - Oct-10-2017

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/articles/dsl/python-091105.html

If anyone has better resolution do let me now

RE: Parameterized SQL query - nilamo - Oct-10-2017

From your own link: http://www.oracle.com/technetwork/articles/dsl/python-091105.html#t7

Quote:

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.