[sqlalchemy] Re: Dynamic where-statement with OR
On 11/7/06, DNeumann [EMAIL PROTECTED] wrote: Hello list, To build a dynamic where-statement in my script, i looked here for more info: http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_building statement = my_table.select() if id: statement.append_whereclause(my_table.c.id==id) if name: statement.append_whereclause(my_table.c.name==name) ... The where-statement is now generated with the AND-operator. now my question: Is it possible to get OR instead of AND? Or alternative Solutions? Another option that I use quite a lot is to use: query_args = [] if id: query_args.append(my_table.c.id==id) if name: query_args.append(my_table.c.name==name) statement = my_table.select(or_(*query_args)) You can build quite complex queries this way by nesting and_() and or_() in the list. Lee -- Lee McFadden blog: http://www.splee.co.uk work: http://fireflisystems.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Design question: option mechanism
Hello, this might be a little off topic and is more related to database design. I have 2 tables, clients and sites with a one-to-many relation (1 client has many sites). I'd like to design an option mechanism where I could create options (bool or multivalues strings, ie. checkbox or dropdown list) to be assigned for a client, and where every new site hinerits from it's client's option values as default. Then the site can save it's own custum values if default values are not satisfying. Any idea how I could design this ? Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] readonly attributes
I have an ActiveMapper class which looks like this: class OraJob(ActiveMapper): class mapping: id = column(Integer, primary_key = True) job_type = column(Unicode(30)) source_type = column(Unicode(30)) ... Attributes are used / should be used in readonly manner. All updates to database are happening via specialized Oracle procedures (that in turn use AUTONOMOUS_TRANSACTION). This is so because changes to this object must be committed to database no mater what is the outcome of main transaction and must be written immediately so I can track the progress of job from outside. One of update functions looks like this: def start_job(self, status_message, expected_steps = None): self.status_message = status_message if expected_steps is not None: self.expected_steps = expected_steps orapro.tool_job.start_job( # This calls my Oracle function start_job() i_job_id = self.id ,i_status_message = status_message ,i_expected_steps = expected_steps ).execute(connection()) self.status = uRUNNING My problem is in: self.status = something because this causes the object to get dirty and it gets updated when I flush the session. This sql update in turn locks the row, and my next call to specialized update function (ie. finish_job) fails with a deadlock (remember, specialized update is inside autonomous transaction). My current solution is to expunge(self) when calling any of specialized update functions: def finish_job(self, status_message, warnings, errors, extra = None): self._expunge() if self.status != u'RUNNING': raise RuntimeError(You can only finish a job if it is in RUNNING state.) self.status_message = status_message self.warnings = warnings self.errors = errors self.extra = extra orapro.tool_job.finish_job( i_job_id = self.id ,i_status_message = status_message ,i_warnings = warnings ,i_errors = errors ,i_extra = extra ).execute(connection()) self.status = uFINISHED def _expunge(self): try: self.__expunged except AttributeError: session.expunge(self) self.__expunged = True This barely works because currently I'm calling flush only *after* one of my specialized update functions had a chance to expunge itself. My next try was to create object with _sa_session=None... How can I go about solving this problem: 1. somehow make attributes readonly for outside world but let me change attributes from inside customized update function without affecting dirty state 2. expunge object from session inside __init__ (if it is possible at all) 3. expunge object from session on each call to update function 4. create object somehow so it doesn't end up inside session (is it enough to say _sa_session=None when creating object) 5. something else It seems that readonly attribute would be the best solution, because the only interface for changing job state is through several well defined functions (start/update/finish/fail) Thanks, Tvrtko --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Constructing where-clauses dynamically
On Nov 9, 2006, at 10:20 PM, Daniel Miller wrote:
Can you give an example of the SQL (including the joins) that would
be generated by your statement above?
(user.user_id=orders.user_id AND orders.order_id=items.order_id AND
items.item_name='item #4') OR
(user.user_id=orders.user_id AND orders.order_id=items.order_id AND
items.item_name='item #5')
Right. It should be possible (although I'm not sure how simple) to
combine conditions using the rules for combining logical
expressions (i.e. commutative, transitive, etc.). For example:
(A == B AND C == D) OR (A == B AND C == E)
can be reduced to
(A == B) AND (C == D OR C == E)
So what we need is a way to take a group of expressions and reduce
them to their simplest form before generating the SQL. However,
don't most main-stream databases do this kind of optimization
anyway? MySQL does (http://dev.mysql.com/doc/refman/5.0/en/where-
optimizations.html).
this is what i was getting at; an innocent feature is already
spawning complicated expression parsing before we've even written any
code. Its possible the databases optimize a clause like the
above...then again i dont know if this approach is always going to
come up with a valid clause. by adding our own syntaxes, as opposed
to using syntaxes that always map directly to SQL, we just dont know
(since im not a language syntax expert).
Having said all that, it may be simpler to use join_to/join_via in
some cases and maybe that's a good reason to keep those functions
around. However, I think this new syntax would still be very
valuable in many cases.
Being able to say user.c.orders==someorder is not a big deal.
user.c.orders.items compiling into a Join (or into the join clause ?
im not sure) im still not sure how that would be used (i.e. do you
stick it in the WHERE criterion or in the from_obj)? The syntax I
like best is the one provided by the selectresults extension, where
you can just say (well, it has just join_to right now, but this is
the idea):
query.select().join_via('orders', 'items').select
(Item.c.item_name=='item #4')
this is also very clear if you want to outerjoin:
query.select().outerjoin_via('orders', 'items').select()
its quick and its explicit. users.c.orders.items seems more ambiguous.
ive been concerned that SelectResults is not easy enough to use (i.e.
you need a mapper extension, or a separate SelectResults
construction).im thinking it should be built-in to Query somehow,
i.e. just say query.selectresults() and start building (but even
that's too verbose). Im still wondering if Query should have just
been built that way from the start - using a strictly transformative
interface instead of keyword-args-based.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Constructing where-clauses dynamically
On Nov 10, 2006, at 12:13 AM, Daniel Miller wrote:
This query:
q.select(or_(
c.orders.items.item_name == 'item#4',
c.orders.items.item_name == 'item #5'
))
Should generate something similar to this SQL:
SELECT ...
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id
INNER JOIN items i
ON o.order_id = i.order_id
WHERE i.item_name = 'item#4'
OR i.item_name = 'item #5'
For the given clause, each occurrence of User.c.orders.items is
combined into a single join path from users to items, and every
occurrence of 'item_name' uses the same table alias 'i'. Is that
too simplistic? Here are a few more examples:
its kind of what SQL construction does now, i.e. extracts unique
FROM objects from the where criterion and adds them to a list, so
the approach would probably work (might possibly have other issues we
arent thinking of just yet)
Notice how the join path for Users.c.orders is superimposed on the
join path for Users.c.orders.items. It should be possible to use
the alias function to force separate joins like this:
q.select(and_(
c.orders.alias('ox').order_total 50,
or_(
c.orders.alias('o').items.item_name == 'item #4',
c.orders.alias('o').items.item_name == 'item #5',
)
))
ASIDE: One thing we might want to change is the 'alias' function.
Since the 'orders' table may have a column named 'alias', it might
be better to make it a stand-alone function like this:
alias(c.orders, 'ox')
i think this is the problem when you start munging namespaces
together, as opposed to grouping them under explicit prefixes (i.e.
'c'). so far I think this new syntax is going to be confusing to
people since its not explicitly clear what it means underneath (which
leaves people to guess, get it wrong, and then report it as bugs/
confusion), and also presents a completely different way to create
joins when we already have more explicit ways to do it (inlcuding the
selectresults transformative style which i think is very fast and
clear). when you start throwing aliases into the mix i think it
becomes more overwhelming.
compare to the current method:
ox = ordertable.alias('ox')
q.select(and_(ox.c.order_total 50, or_(itemtable.c.item_name=='item
#4', itemtable.c.item_name=='item #5')), from_obj=[users.join
(ordertable).join(itemstable).join(ox, users.c.user_id==ox.c.user_id)])
more explicit...but I think clearer. the user definitely knows what
they are doing when they do this (note that from_obj hasnt really
worked in query() up until version 0.3.0 which is why it looks less
familiar, but i think also aids in creating more complex queries). i
think people should be expressing exactly the joins they want if they
want to be joining at all.
I think your original proposal, i.e. User.c.orders == someorder is
reasonable since its hard to misunderstand what that does.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
