Re: [web2py] Re: another oracle question :(
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 On Saturday, April 6, 2013 4:27:10 AM UTC+2, Martin Barnard wrote: 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 javascript: 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. -- --- 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 javascript:. For more options, visit https://groups.google.com/groups/opt_out. -- --- 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.
Re: [web2py] Re: another oracle question :(
Good point! :) Martin On 6 April 2013 14:53, Niphlod niph...@gmail.com wrote: 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 On Saturday, April 6, 2013 4:27:10 AM UTC+2, Martin Barnard wrote: 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. -- --- 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=enhttps://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_outhttps://groups.google.com/groups/opt_out . -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- --- 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.
[web2py] Re: another oracle question :(
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
websitehttp://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.
[web2py] Re: another oracle question :(
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() -- --- 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.
Re: [web2py] Re: another oracle question :(
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. On 6 April 2013 00:07, Niphlod niph...@gmail.com wrote: 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() -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- --- 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.
Re: [web2py] Re: another oracle question :(
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. -- --- 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.
Re: [web2py] Re: another oracle question :(
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 niph...@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. -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- --- 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.
[web2py] Re: another oracle question :(
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.
