Sorry for the delay. My DAL code would ideally be something like this:
mems=[135123123,135123154,150012323] rows=odb(odb.player_master.link_id.belongs(mems)).select() This works, but I know if I scale up to a large list, as I eventually must, it will bring complaints & lockout from our oracle dba. The *lastsql* of DAL is like this: SELECT player_master.link_id, player_master.last_name, player_master.first_name FROM logismos.player_master WHERE (player_master.link_id IN (135123123, 135123154)) Browsing oracles website<http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html>, I came across some info on using cx_oracle with bind queries. They have some examples: >>> named_params = {'dept_id':50, 'sal':1000} > >>> query1 = cursor.execute('SELECT * FROM employees WHERE > department_id=:dept_id AND salary>:sal', named_params) > >>> query2 = cursor.execute('SELECT * FROM employees WHERE > department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000) When using > named bind variables you can check the currently assigned ones using the > bindnames() method of the cursor: > > > >>> print cursor.bindnames() > ['DEPT_ID', 'SAL'] > Passing by position is similar but you need to be careful about naming. > Variable names are arbitrary so it's easy to mess up queries this way. In > the example below, all three queries r1, r2, and r3 are equivalent. The > parameters variable must be given as a sequence. > > > >>> r1 = cursor.execute('SELECT * FROM locations WHERE country_id=:1 AND > city=:2', ('US', 'Seattle')) > >>> r2 = cursor.execute('SELECT * FROM locations WHERE country_id=:9 AND > city=:4', ('US', 'Seattle')) > >>> r3 = cursor.execute('SELECT * FROM locations WHERE country_id=:m AND > city=:0', ('US', 'Seattle')) > When binding, you can first prepare the statement and then execute None > with changed parameters. Oracle will handle it as in the above case, > governed by the rule that one prepare is enough when variables are bound. > Any number of executions can be involved for prepared statements. > > > >>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min') > >>> r = cursor.execute(None, {'min':1000}) > >>> print len(cursor.fetchall()) > Any suggestions would be much appreciated. Martin Barnard. On Wednesday, April 3, 2013 6:22:28 AM UTC+3, Massimo Di Pierro wrote: > > What does the SQL look like? > > On Tuesday, 2 April 2013 20:44:40 UTC-5, Martin Barnard wrote: >> >> I have a list of ids [1,2,3,...,n] and I want to run a query on an oracle >> table (using cx_oracle), but I have to use bind variables. Anybody know the >> correct format before I start experimentation? >> >> Will I be forced to use db.executesql? >> >> Thanks, >> >> Martin Barnard >> > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
