another oracle question :(

[Martin Barnard]

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

[Massimo Di Pierro]

What does the SQL look like?

[Martin Barnard]

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, 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.

[Niphlod]

I really don't understand.
The query "shipped" to your oracle instance will be exactly the same using DAL as the one using "bind variables".

The DAL just does binding for you (in the 90% of the cases, better)

If you want to retrieve from a table all records in one query, and you have a "large" list of "key values" to filter it with, you can do it only with two syntaxes....
either

select * from table where column in (1,2,3,4,5,6,....)

or

select * from table
where
column = 1
or
column = 2
or
column = 3
or
column = 4

Now, every database has its own query planning, but as far as I know Oracle responds better to the first one than the latter.
For the sake of completeness,  MSSQL is more fun .... MSSQL 2000 to 2005 without any SP performed better on the first, while 2005 sp2 to 2008 R2 performs best with the latter ... Postgresql instead has similar times but from 8.4 to 9.1 there has been a steep improvement on it, etc etc etc

Binding with "in" won't get you where you want to go.... you have to prepare the "in" list as a "pre-quoted" string and pass that as a parameter, however, as I was saying, there will not be any difference on the query shipped to your db instance.

Same thing goes for the "or" method .....

For the sake of completeness #2, in web2py should resemble similarly to

mems = [1,2,3,4,5, ...]
q = [] # list of conditions
for c in mems:
    q.append[db.table1.field == c]
all_queries_in_or = reduce(lambda a,b: (a | b ), q)
result = db(all_queries_in_or).select()

[Martin Barnard]

Thanks for the info, Niphlod. 

I will look into the efficiency of the IN clause for my needs, as it appears to offer a solution which may mollify the IT  DBA, and his demands for bind vars (they are concerned that a looped select will bring the db to it's knees).

Martin Barnard.

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/E2pVWl_71t4/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Niphlod]

On Friday, April 5, 2013 11:58:37 PM UTC+2, Martin Barnard wrote:

Thanks for the info, Niphlod. 

I will look into the efficiency of the IN clause for my needs, as it appears to offer a solution which may mollify the IT  DBA, and his demands for bind vars (they are concerned that a looped select will bring the db to it's knees).

.... a db(whatever.belongs(a_set)) issues ONE query only.

If you want to "force" a looping query, you should do explicitely with

mems = [1,2,3,4,5,6,...]
for c in mems:
     one_result = db(db.table.field == c).select()
     ......

of course, for zillions values into mems, it's not a smart move.

the smartest move with a huge set (i.e. the technique with most of the "balance") would be "paginating" through your "mems" .

You'd loop a few times but if you have thousands of values into "mems", a single IN () (or thousands ORs) will take some time ....

Try to "draw a limit" with your DBA and if he says that you're "allowed" to do an IN() with 500 values at a time, you have it covered ^_^

On the other end, you have a requirement..... fetch a zillions rows..... either you do it in one shot or in zillions/times of 500 each.

[Martin Barnard]

Thanks again for the help - it's invaluable to the oracle noob! :D

I'm actually building an analysis system so that my boss can view some statistical subset of the information based on a few months or weeks. One part of the data is stored in our MySQL database (which our department control), whilst the rest of it is stored in the IT's oracle system. I have to pull a bunch of data from the MySQL database (usually with a date-based search parameter), then search the oracle system for the rest of the data for each return in the MySQL rows (by membership).

My current solution (for those on similar paths):

rows=db(db.my_table.created_on > date_one).select()

ml=[]

for row in rows:

  if row.my_field==my_criteria:

    ml.append(row.membership)

rows=odb(odb.other_table.membership.belongs(ml)).select()

Of course, I have to worry about the length of the list that I'm passing, as I read that it cannot exceed 1000 items, but this shouldn't be an issue in normal use, and I'll wrap it in a test first.
More efficient methods welcome!!!

Martin

On 6 April 2013 01:11, Niphlod <nip...@gmail.com> wrote:

On Friday, April 5, 2013 11:58:37 PM UTC+2, Martin Barnard wrote:

Thanks for the info, Niphlod. 

I will look into the efficiency of the IN clause for my needs, as it appears to offer a solution which may mollify the IT  DBA, and his demands for bind vars (they are concerned that a looped select will bring the db to it's knees).

.... a db(whatever.belongs(a_set)) issues ONE query only.

If you want to "force" a looping query, you should do explicitely with

mems = [1,2,3,4,5,6,...]
for c in mems:
     one_result = db(db.table.field == c).select()
     ......

of course, for zillions values into mems, it's not a smart move.

the smartest move with a huge set (i.e. the technique with most of the "balance") would be "paginating" through your "mems" .

You'd loop a few times but if you have thousands of values into "mems", a single IN () (or thousands ORs) will take some time ....

Try to "draw a limit" with your DBA and if he says that you're "allowed" to do an IN() with 500 values at a times, you have it covered ^_^

On the other end, you have a requirement..... fetch a zillions rows..... either you do it in one shot or in zillions/500 each.

[Niphlod]

I'd go for
rows=db(
       (db.my_table.created_on > date_one) &
       (db.mytable..my_field==my_criteria)
       ).select(db.mytable.membership)
.....

if your "my criteria" is not a function, but a fixed value ....

[Martin Barnard]

Good point!
:)

Martin