[sqlalchemy] Re: on delete restrict (bis)
Another solution could be to inverse the order: - first delete the parent (so the rule RESTRICT is immediately fired) - second set null the FKs. On 8 sep, 19:52, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 8, 2007, at 12:54 PM, Jean-Philippe Dutreve wrote: My need is related to Postgresql ON DELETE RESTRICT/NO ACTION : I'd want a sql exception as soon as a parent having any existing child is deleted. I don't want cascade delete on children, just the parent but only if it has no child. I've remarked that SA (0.4) first SET NULL all FKs in child table, and second delete the parent. Doing this in that order, the PG rule is not called and the parent is deleted even if there are children (now orphaned)!!! The only solution I have found is to define the FK as NOT NULL. It would be handy to be able to let this PG rule be fired. Perhaps with an option cascade=delete-donothing on the child relation. the ORM is hardwired to a referential integrity model right now that assumes foreign keys are to be maintained as valid. therefore as long as theres a relation() present, its going to want to either null out the foreign key or to delete the child items. There are some options that can affect this, such as viewonly=True gives you a relation that is only for loading, and passive-deletes=True will give you a relation that doesnt load in unloaded objects in order to update foreign keys (relying instaed upon ON DELETE CASCADE), but still acts upon objects already loaded. you could just use viewonly=True but that means you have to populate foreign key attributes manually. we can look into adding an option to not act on FKS at all during a delete operation but it might be a little involved. (adding trac tickets would be the route for this) --~--~-~--~~~---~--~~ 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] reviving migrate
I read the discussion regarding the status of the migrate tool and would like to help with this project. I built the Debian package for migrate (http://packages.qa.debian.org/ m/migrate.html) which is now available in Debian testing and I use migrate with the SA version contained in etch (0.3.1). Maybe I could find some time to look at the existing code and create a version that works with current SA. Is someone here with a better insight on what is broken in migrate now, to get me started? Regards Jan Dittberner --~--~-~--~~~---~--~~ 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] reviving migrate
I read the discussion regarding the status of the migrate tool and would like to help with this project. I built the Debian package for migrate (http://packages.qa.debian.org/ m/migrate.html) which is now available in Debian testing and I use migrate with the SA version contained in etch (0.3.1). Maybe I could find some time to look at the existing code and create a version that works with current SA. Is someone here with a better insight on what is broken in migrate now, to get me started? Regards Jan Dittberner --~--~-~--~~~---~--~~ 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: reviving migrate
I have no info on this, but I have seen the current owner on this and the migrate group. It would be awesome if migrate would work again. Koen On Sep 9, 2:53 pm, Jan Dittberner [EMAIL PROTECTED] wrote: I read the discussion regarding the status of the migrate tool and would like to help with this project. I built the Debian package for migrate (http://packages.qa.debian.org/ m/migrate.html) which is now available in Debian testing and I use migrate with the SA version contained in etch (0.3.1). Maybe I could find some time to look at the existing code and create a version that works with current SA. Is someone here with a better insight on what is broken in migrate now, to get me started? Regards Jan Dittberner --~--~-~--~~~---~--~~ 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: on delete restrict (bis)
On Sep 9, 2007, at 5:09 AM, Jean-Philippe Dutreve wrote: Another solution could be to inverse the order: - first delete the parent (so the rule RESTRICT is immediately fired) - second set null the FKs. that would *really* add complexity to the core code just to support this feature. FTR i had never really heard of anyone using ON DELETE RESTRICT before this. --~--~-~--~~~---~--~~ 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: on delete restrict (bis)
On Sep 9, 2007, at 5:09 AM, Jean-Philippe Dutreve wrote: Another solution could be to inverse the order: - first delete the parent (so the rule RESTRICT is immediately fired) - second set null the FKs. hm, except that RESTRICT is the same as the default of NO ACTION. The feature is added in r3486, checkout from the trunk. Specify passive_deletes='all' to your relation(), do not set delete or delete-orphan cascade. No nulling out of the child columns will occur when the parent is deleted. --~--~-~--~~~---~--~~ 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] alias table names with a self-ref join
I'm newish to SQLAlchemy. I've been trying to do something for too
long now so I'm posting here. I'm trying to implement a tree in sql.
I've seen the example here[1] but I prefer to implement trees using
this method[2] (it's cleaner and more elegant).
I'm stuck doing the following join (to get the children in a tree).
How do I reference the table names in a query involving a self-
referential join on a non-primary key. More simply, how do I specify
the bar in 'join foo as bar'?
SELECT node.* (COUNT(parent.name) - 1) as depth
FROM treenodes as node
JOIN treenodes as parent on node.lft BETWEEN parent.lft and
parent.rgt
WHERE node.id = 2
GROUP BY node.id
ORDER BY node.lft
Here is the table definition:
prefix='myprefix_'
schema='mydb'
engine='InnoDB'
treenodes = Table(prefix+'treenodes', meta,
Column('id', Integer, primary_key=True),
Column('lft', Integer, nullable=False),
Column('rgt', Integer, nullable=False),
Column('name', String(50), nullable=False),
mysql_engine=engine,
schema=schema
)
I've tried using the same names (even though I didn't expect it to
work):
mapper(TreeNode, treenodes, properties={
'children' : relation(
TreeNode,
primaryjoin=treenodes.c.lft.between(treenodes.c.lft, treenodes.c.rgt)
) })
But I get the following exception:
sqlalchemy.exceptions.ArgumentError: Can't locate any foreign key
columns in primary join condition 'treenodes.lft BETWEEN treenodes.lft
AND treenodes.rgt' for relationship 'TreeNode.children (TreeNode)'.
Specify 'foreign_keys' argument to indicate which columns in the join
condition are foreign
I added a foreign_keys argument:
foreign_keys=[treenodes.c.lft,treenodes.c.rgt]
And I get a different exception:
sqlalchemy.exceptions.ArgumentError: No syncrules generated for join
criterion treenodes.lft BETWEEN treenodes.lft AND treenodes.rgt
How do I reference the joined table?
Scott
---
[1]
http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_selfreferential
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
--~--~-~--~~~---~--~~
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: on delete restrict (bis)
On Sep 9, 2007, at 12:21 PM, Jean-Philippe Dutreve wrote: I prefer put constraints in database rather than in application/ framework because several applications can access the same database and applications can gone quicker than DB. The ORM doesnt get in the way of your placing appropriate constraints on the DB. The reason SA very much wants to maintain referential integrity itself only applies to those objects which are currently present in the session; and its that it wants the session to mirror what's in the database without having to issue extra queries to see what the constraints have fired off. With passive_deletes=True, it wont add the overhead of loading anything additional (i.e. things that are not already in the session) during a delete operation. The passive_deletes=all option I just added will, depending on usage, produce a session that is not in sync with what the database actually has. Other people have asked for the dont set NULL behavior as well, since its just a small conditional it wasnt a big deal to add (but i bet nobody will use it unless they have some triggering scenario in place). in your case, yes a NOT NULL FK is probably the more straightforward way to go but feel free to try out the flag. --~--~-~--~~~---~--~~ 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: alias table names with a self-ref join
On Sep 9, 2007, at 1:09 PM, sc0ttbeardsley wrote:
I'm newish to SQLAlchemy. I've been trying to do something for too
long now so I'm posting here. I'm trying to implement a tree in sql.
I've seen the example here[1] but I prefer to implement trees using
this method[2] (it's cleaner and more elegant).
I'm stuck doing the following join (to get the children in a tree).
How do I reference the table names in a query involving a self-
referential join on a non-primary key. More simply, how do I specify
the bar in 'join foo as bar'?
youre trying to do nested sets. This model is not currently
supported by the relation() function, since it does not use foreign
keys to indicate the relationship between parent and child ('lft' and
'rgt' are not foreign keys here). Im also not familiar with any ORM
in any language that supports nested set object-relational
mappings. The ORM is unlikely to support nested sets directly since
this model relies upon expensive full-table UPDATE statements which
are outside the realm of row-based mappings, so you'd be better off
rolling this yourself using SQL expressions which you can possibly
embed into the ORM using a MapperExtension (i.e. before_insert(),
before_update(), before_delete() fire off the appropriate table-
spanning UPDATE statements; the inefficiency of this for a large set
of new objects should be apparent and would be better off batched
beforehand).
I would add that its very likely that you dont really want to use
nested sets, while it gets a lot of attention from purely
relational sources, its not very usable in the real world, has more
disadvantages than advantages, and doesn't present too many
advantages that arent workable in some other way. For some
background, see:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/
9d61479133ffd6ad/334cb8a0cb5a9de0?#334cb8a0cb5a9de0
--~--~-~--~~~---~--~~
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: alias table names with a self-ref join
On 9/9/07, Michael Bayer [EMAIL PROTECTED] wrote: youre trying to do nested sets. Ya, I'm not up to date on my DB terminology. Im also not familiar with any ORM in any language that supports nested set object-relational mappings. OK. I'll likely implement a tree using the example in the docs then (luckily I have that discretion and am not working with a legacy system). It makes sense in the ORM context. http://groups.google.com/group/sqlalchemy/browse_thread/thread/ 9d61479133ffd6ad/334cb8a0cb5a9de0?#334cb8a0cb5a9de0 Thanks for the link and comments. You've helped me a ton. Scott --~--~-~--~~~---~--~~ 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] avoid a subselect yielding null
g'day. i have a subselect that may yield null (nothing found), and i want to treat that as value of 0. i've read about coalesce() that would return first non-null of its args. plain query looks like: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance) this gives something like: $print expr trans.account LIKE balance.account+'%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce((SELECT max(b.finaldate) FROM balance AS b WHERE b.finaldate balance.finaldate) adding coalesce breaks the subselect: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date func.coalesce( select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance), 0 ) $print expr trans.account LIKE balance.account + '%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce(NULL,0) removing the correlate() restores proper subselect, but now its not correlated, and has 'FROM balance as b, balance' in it... or should i use CASE instead? i'm sure i've misunderstood all this sql thing... (The subselect is just trying to find the date of previous row in table balance before current balance.finaldate, OR 0.) svilen --~--~-~--~~~---~--~~ 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: avoid a subselect yielding null
try calling scalar() on that subquery, it needs to be treated as such. On Sep 9, 2007, at 2:37 PM, [EMAIL PROTECTED] wrote: g'day. i have a subselect that may yield null (nothing found), and i want to treat that as value of 0. i've read about coalesce() that would return first non-null of its args. plain query looks like: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance) this gives something like: $print expr trans.account LIKE balance.account+'%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce((SELECT max(b.finaldate) FROM balance AS b WHERE b.finaldate balance.finaldate) adding coalesce breaks the subselect: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date func.coalesce( select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance), 0 ) $print expr trans.account LIKE balance.account + '%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce(NULL,0) removing the correlate() restores proper subselect, but now its not correlated, and has 'FROM balance as b, balance' in it... or should i use CASE instead? i'm sure i've misunderstood all this sql thing... (The subselect is just trying to find the date of previous row in table balance before current balance.finaldate, OR 0.) svilen --~--~-~--~~~---~--~~ 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: avoid a subselect yielding null
On Sunday 09 September 2007 22:51:32 Michael Bayer wrote: try calling scalar() on that subquery, it needs to be treated as such. oops, forgot to mention: this is 0.3.xx. in 0.4 all is okay without scalars. so, 0.3.latest, adding .scalar() after .correlate() complains about None having no .scalar attribute; adding .scalar() before the .correlate() gives: Traceback (most recent call last): File tests/convertertest.py, line 144, in test4_balance_trans_via_prev_balance_date_subselect b.c.finaldate balance.c.finaldate File /home/az/src/dbcook/sqlalchemy/sql.py, line 1215, in scalar return self.execute(*multiparams, **params).scalar() File /home/az/src/dbcook/sqlalchemy/sql.py, line 1208, in execute return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params) File /home/az/src/dbcook/sqlalchemy/sql.py, line 1097, in execute raise exceptions.InvalidRequestError(This Compiled object is not bound to any Engine or Connection.) InvalidRequestError: This Compiled object is not bound to any Engine or Connection. which is true, its all unbound. btw .scalar() behaves same in 0.4.. so i guess its not that. if its too much of a hassle, forget it, one testcase less when 0.3 (-;). svilen On Sep 9, 2007, at 2:37 PM, [EMAIL PROTECTED] wrote: g'day. i have a subselect that may yield null (nothing found), and i want to treat that as value of 0. i've read about coalesce() that would return first non-null of its args. plain query looks like: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance) this gives something like: $print expr trans.account LIKE balance.account+'%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce((SELECT max(b.finaldate) FROM balance AS b WHERE b.finaldate balance.finaldate) adding coalesce breaks the subselect: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date func.coalesce( select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance), 0 ) $print expr trans.account LIKE balance.account + '%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce(NULL,0) removing the correlate() restores proper subselect, but now its not correlated, and has 'FROM balance as b, balance' in it... or should i use CASE instead? i'm sure i've misunderstood all this sql thing... (The subselect is just trying to find the date of previous row in table balance before current balance.finaldate, OR 0.) svilen --~--~-~--~~~---~--~~ 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: avoid a subselect yielding null
sorry, as_scalar() in 0.4. in 0.3, correlate() is not generative (i.e. modifies the parent select(), returns None), so thats your problem (call correlate() beforehand). On Sep 9, 2007, at 4:03 PM, [EMAIL PROTECTED] wrote: On Sunday 09 September 2007 22:51:32 Michael Bayer wrote: try calling scalar() on that subquery, it needs to be treated as such. oops, forgot to mention: this is 0.3.xx. in 0.4 all is okay without scalars. so, 0.3.latest, adding .scalar() after .correlate() complains about None having no .scalar attribute; adding .scalar() before the .correlate() gives: Traceback (most recent call last): File tests/convertertest.py, line 144, in test4_balance_trans_via_prev_balance_date_subselect b.c.finaldate balance.c.finaldate File /home/az/src/dbcook/sqlalchemy/sql.py, line 1215, in scalar return self.execute(*multiparams, **params).scalar() File /home/az/src/dbcook/sqlalchemy/sql.py, line 1208, in execute return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params) File /home/az/src/dbcook/sqlalchemy/sql.py, line 1097, in execute raise exceptions.InvalidRequestError(This Compiled object is not bound to any Engine or Connection.) InvalidRequestError: This Compiled object is not bound to any Engine or Connection. which is true, its all unbound. btw .scalar() behaves same in 0.4.. so i guess its not that. if its too much of a hassle, forget it, one testcase less when 0.3 (-;). svilen On Sep 9, 2007, at 2:37 PM, [EMAIL PROTECTED] wrote: g'day. i have a subselect that may yield null (nothing found), and i want to treat that as value of 0. i've read about coalesce() that would return first non-null of its args. plain query looks like: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance) this gives something like: $print expr trans.account LIKE balance.account+'%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce((SELECT max(b.finaldate) FROM balance AS b WHERE b.finaldate balance.finaldate) adding coalesce breaks the subselect: expr = and_( trans.c.account.startswith( balance.c.account), trans.c.date) = balance.c.finaldate, trans.c.date func.coalesce( select( [ func.max( b.c.finaldate)], b.c.finaldate balance.c.finaldate ).correlate( balance), 0 ) $print expr trans.account LIKE balance.account + '%' \ AND trans.date = balance.finaldate \ AND trans.date coalesce(NULL,0) removing the correlate() restores proper subselect, but now its not correlated, and has 'FROM balance as b, balance' in it... or should i use CASE instead? i'm sure i've misunderstood all this sql thing... (The subselect is just trying to find the date of previous row in table balance before current balance.finaldate, OR 0.) svilen --~--~-~--~~~---~--~~ 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] Outerjoin on Subselect?
Hello all; I am trying to do a subselect on an Outer join. I have a table of votes that can apply to multiple objects in my system so their is no direct foreign key. A Voteable type implementation. When i load the list of Projects, i want to return the current users votes eagerly if possible, but if they haven't voted still return the project. It is similar to the Association examples, or this example: http://techspot.zzzeek.org/?cat=3 With the difference being that its possible that the associated table won't have any, in which case it still needs to return the parent table, I can't get this accomplished. Currently i have written the SQL by hand, and am loading it into a secondary mapper, but it breaks with Could not find any Tables in the mapped object. I have tried a variety of things before that with less success. Any suggestions would be appreciated. Thank You. Aaron project_table = Table(project, metadata, Column(id, Integer, primary_key=True), Column(title, String(255), nullable=False), Column(createdby_id, Integer), Column(rating_ct, Integer, default=0), ) # ratings rating_table = Table(vote, metadata, Column(id, Integer, primary_key=True), Column(person_id, Integer), Column(entry, Integer), Column(obj_id, Integer), ) # This is the SQL that works by hand SELECT project.id AS project_id, project.title AS project_title, project.createdby_id AS project_createdby_id, project.vote_ct AS project_vote_ct, vote_der.vote_id, vote_der.vote_person_id, vote_der.vote_entry, vote_der.vote_obj_id FROM project LEFT OUTER JOIN ( SELECT vote.id AS vote_id, vote.person_id AS vote_person_id, vote.entry AS vote_entry, vote.obj_id AS vote_obj_id FROM vote where person_id = 24 ) AS vote_der ON project.id = vote_der.vote_obj_id --~--~-~--~~~---~--~~ 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: avoid a subselect yielding null
On Sunday 09 September 2007 23:30:20 Michael Bayer wrote: sorry, as_scalar() in 0.4. in 0.3, correlate() is not generative (i.e. modifies the parent select(), returns None), so thats your problem (call correlate() beforehand). yeah that's it. thanks. now back to that argument, months ago: - how to make same code work with generative and non-generative api? now i need to do: sel = select(...) xx = sel.correlate(..) if not _v03: sel = xx ...use-the-sel... this now is just one place, big deal. But in some wider usage... Anyway, food for thought... and/or documentation. svilen --~--~-~--~~~---~--~~ 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: avoid a subselect yielding null
On Sep 9, 2007, at 4:49 PM, [EMAIL PROTECTED] wrote: now back to that argument, months ago: - how to make same code work with generative and non-generative api? now i need to do: sel = select(...) xx = sel.correlate(..) if not _v03: sel = xx ...use-the-sel... this now is just one place, big deal. But in some wider usage... Anyway, food for thought... and/or documentation. i dunno, make a function correlate() ? def correlate(select, values): s = select.correlate(values) return s or select --~--~-~--~~~---~--~~ 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: Outerjoin on Subselect?
im assuming youre talking about ORM access (since you mentioned a
'secondary mapper') and want to get Project and Vote objects at the
same time (since you are putting both sets of columns into the SELECT
clause):
subselect = select([rating_table],
rating_table.c.person_id==24).alias('votes')
result = session.query(Project).add_entity(Vote,
alias=subselect).select_from(project_table.outerjoin(subselect,
project_table.c.id==subselect.c.obj_id))
however, theres no real reason here to use a subselect for votes.
easier would be to just outerjoin directly (this one will work in 0.3
also if thats an issue):
result = session.query(Project).add_entity(Vote).select_from
(project_table.outerjoin(rating_table,
project_table.c.id==rating_table.c.obj_id)).filter(or_
(rating_table.c.person_id==24, rating_table.c.person_id==None))
good luck !
On Sep 9, 2007, at 4:36 PM, Aaron R wrote:
Hello all;
I am trying to do a subselect on an Outer join. I have a table of
votes that can apply to multiple objects in my system so their is no
direct foreign key. A Voteable type implementation. When i load the
list of Projects, i want to return the current users votes eagerly if
possible, but if they haven't voted still return the project.
It is similar to the Association examples, or this example:
http://techspot.zzzeek.org/?cat=3
With the difference being that its possible that the associated table
won't have any, in which case it still needs to return the parent
table, I can't get this accomplished. Currently i have written the
SQL by hand, and am loading it into a secondary mapper, but it breaks
with Could not find any Tables in the mapped object. I have tried a
variety of things before that with less success.
Any suggestions would be appreciated.
Thank You.
Aaron
project_table = Table(project, metadata,
Column(id, Integer, primary_key=True),
Column(title, String(255), nullable=False),
Column(createdby_id, Integer),
Column(rating_ct, Integer, default=0),
)
# ratings
rating_table = Table(vote, metadata,
Column(id, Integer, primary_key=True),
Column(person_id, Integer),
Column(entry, Integer),
Column(obj_id, Integer),
)
# This is the SQL that works by hand
SELECT project.id AS project_id, project.title AS project_title,
project.createdby_id AS project_createdby_id,
project.vote_ct AS project_vote_ct,
vote_der.vote_id, vote_der.vote_person_id,
vote_der.vote_entry, vote_der.vote_obj_id
FROM project LEFT OUTER JOIN (
SELECT vote.id AS vote_id, vote.person_id AS
vote_person_id,
vote.entry AS vote_entry, vote.obj_id AS vote_obj_id FROM vote
where person_id = 24
) AS vote_der
ON project.id = vote_der.vote_obj_id
--~--~-~--~~~---~--~~
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] Aggregate function of connected items as a property?
Hello;
I am writing a web application implementing online forums and such. I
have defined the data structures that all elements (forums, threads,
comments) are instances of a basic node type, with auxilliary data
linked to the node ID in separate tables like so:
tnode_table = Table('tnode', meta,
Column('tn_id', Integer, primary_key=True),
Column('tn_parent', Integer, ForeignKey('tnode.tn_id'),
nullable=True),
Column('tn_ctime', Integer),
Column('tn_type', Unicode(8)))
forums_table = Table(forum, meta,
Column(tn_id, Integer, ForeignKey('tnode_title.tn_id'),
primary_key=True))
threads_table = Table(thread, meta,
Column(tn_id, Integer, ForeignKey('tnode_title.tn_id'),
primary_key=True),
Column('poster_uid', Integer, ForeignKey('user.u_id')),
Column(text, Unicode),
comments_table = Table(comment, meta,
Column('tn_id', Integer, ForeignKey('tnode.tn_id'),
primary_key=True),
Column('poster_uid', Integer, ForeignKey('user.u_id')),
Column('text', Unicode())
)
These are all mapped to objects, like so:
tnode_mapper = assign_mapper(ctx,TNode, tnode_table)
assign_mapper(ctx,Forum,forums_table, inherits=tnode_mapper)
assign_mapper(ctx, Thread,
threads_table,
inherits=tnode_mapper,
properties = {
'poster' : relation(User, lazy=True)
})
assign_mapper(ctx, Comment, comments_table, inherits=tnode_mapper,
properties = {
'poster' : relation(User, lazy=False)
})
My problem is: I want to be able to select from Thread, ordering it by
descending order of the maximum tn_ctime for each thread, to find the
most recently referenced threads. Which is to say, I want to do
something like
select
t.*,
coalesce(c.most_recent_child, t.tn_ctime) as last_upd
from tnode t
left join (select tn_parent as node_id, max(tn_ctime) as
most_recent_child from tnode group by tn_parent) c on
c.node_id==t.tn_id group by t.tn_id
order by last_upd desc;
Is it possible to add a property to Thread holding the maximum child
node timestamp if any (or the thread node's timestamp, if none), so
that I can do something like
Thread.select(Thread.c.tn_parent==forum_id,
order_by=desc(Thread.c.last_upd))
?
Thanks,
-- acb
--~--~-~--~~~---~--~~
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: backref relation is None instead of list?
On Sep 9, 2007, at 7:00 PM, Dan Eloff wrote:
Hi All,
Table Categories:
categories_table = sa.Table('categories', connection.metadata)
[categories_table.append_column(c) for c in (
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('name', sa.String(40)),
sa.Column('flags', sa.SmallInteger),
sa.Column('parent_id', sa.Integer, sa.ForeignKey
('categories.id')))]
sa.orm.mapper(Category, categories_table, properties={
'subcategories' : sa.orm.relation(Category,
backref=sa.orm.backref('_parent',
remote_side=[categories_table.c.id])),
})
c = Category()
c.subcategories.append(Category()) # works great
c._parent.append(Category()) # fails, c._parent is NoneType???
Why isn't _parent a list? This is actually a one-to-many so there is
only one parent, but assigning doesn't work either:
the relation from Category to _parent is many-to-one, so its a scalar
in that direction (a one-to-many is always many-to-one in the reverse
direction, and vice versa)
c._parent = Category()
Traceback (most recent call last):
File C:\Program
Files\Python\lib\site-packages\sqlalchemy-0.4.0beta5-py2.5.egg
\sqlalchemy\orm\attributes.py,
line 76, in __set__
File C:\Program
Files\Python\lib\site-packages\sqlalchemy-0.4.0beta5-py2.5.egg
\sqlalchemy\orm\attributes.py,
line 364, in set
File C:\Program
Files\Python\lib\site-packages\sqlalchemy-0.4.0beta5-py2.5.egg
\sqlalchemy\orm\attributes.py,
line 300, in fire_replace_event
File C:\Program
Files\Python\lib\site-packages\sqlalchemy-0.4.0beta5-py2.5.egg
\sqlalchemy\orm\attributes.py,
line 130, in sethasparent
AttributeError: 'list' object has no attribute '_state'
I cant reproduce this, although the error to me seems like you are
actually saying c._parent = [] (some list object). If thats not it,
send along a reproducing test script.
I want to be able to create the tree of Category() objects from the
top down and set relations via _parent, but how can I do this?
just the way you are doing it, c._parent = Category(). works for me
on this end...
--~--~-~--~~~---~--~~
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] Changeset 2642's fix is in the wrong place [firebird]
Hi Michael, Do you remember this thread ? : http://tinyurl.com/2c5tzc We have recently upgraded SA from 0.3.7 (with firebird.py modified by ourself) to 0.3.10, and we got this exception: Updated rowcount 1 does not match number of objects updated 2 After some research, we found out that firebird.py has 2 supports_sane_rowcount methods: one from FBExecutionContext class and other from FBDialect and the FBExecutionContext's method is the one who should return False... The fix from [2642] is in FBDialect's method... I apologize for taking to long to spot this error. We were in a hurry to solve the problem described by [1] that we couldn't wait for the release of 0.3.8 (we patched firebird.py) ... so the wrong fix from [2642] wasn't noticed by us... BTW, I couldn't understand the role of FBDialect.sane_rowcount... It's like it doesn't make any difference its return value... what matter is the FBExecutionContext's method I hope there will be a 0.3.11 release with the right fix.. until then, we have patched firebird.py again.. (that's the beauty of the open source philosophy) :) Cheers, Roger --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
