[sqlalchemy] order_by with property of related table
Hi!
I have two tables: A and B defined something like that:
A:
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
Column('b_id', Integer, ForeignKey('b.id'))
B:
Column('id', Integer, primary_key=True),
Column('name', Unicode(256)),
mapper of A is created with:
properties={
'b': relation(B, primaryjoin=A.b_id == B.id, lazy=False),
}
(lazy = False is important in this case)
How can I select all elements from A sorted by B.name? I checked that
I can't use
SESSION.query(A).order_by(B.name)
because it's not working - query(A) consist of JOIN with B table and B
table has alias B_1 in this query and sqlalchemy interprets
order_by(B.name) as ORDER BY B.name but there is no B alias in
query(A).
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: saving existing db object
Thanks Michael! I've got a small bug in my get_from_dto - I was getting object from db, and that was reason the object was updating. On Apr 8, 7:42 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 8, 2011, at 12:59 PM, gwozdziu wrote: #before executing this code we have object with id = 5 in our database obj = self.mapper.get_from_dto(dto) # creating the object from values provided by user , this object has id = 5 self.mapper.add(obj) # we are putting new object (with id = 5). It conflicts with already existing object self.mapper.commit() # commit. New object overrides old one this all depends on what get_from_dto() does. If it says: obj = MyObject(values from the user) now obj is known as transient. When you add() it to the session, it becomes pending. When flush() proceeds, you will get an Integrity Error if ID # 5 already exists. If you want to merge the values from MyObject onto a possibly already existing object in the DB, you use merge: obj = session.merge(obj). On the other hand, if get_from_dto() says: obj = session.query(MyObject).get(5) then obj is persistent, and is already in the session. Calling session.add(obj) has no net change. There's no error but also nothing has actually happened. Finally, if get_from_dto() is more like: obj = some_other_session.query(MyObject).get(5) some_other_session.close() then obj is detached. When you call session.add(obj), obj becomes the object that represents row #5 in the session. This might be what you're looking for, but this is an unusual case. There is of course no error because you are handing the Session an object that is detached, and becomes persistent again - it represents row #5 from the database already, so its the correct row, though it may be out of date. If you've done something to it that you don't want to persist, that's more on your end, but it's a little strange your application isn't able to be aware of that much sooner. Anyway, you can test for identity using: from sqlalchemy.orm.util import has_identity if not has_identity(obj): session.add(obj) if the case is as simple as, the row is out of date and you want to guard against that, you can use a version_id_col for that use case - it will raise if the version you give to the session is older than what's currently in the database. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: saving existing db object
On Apr 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 8, 2011, at 10:54 AM, gwozdziu wrote: Hi! In SqlAlchemy 0.4 when I tried to save (using method save) to database object which interferes row which already exists in database (for example I have table with primary key `id`, row with id = 3 in this table, and I am trying to save object with the same id = 3) the operation raises error that object is already persistent. In Sqlalchemy 0.5.7 there is no save method, but there is add method. But when I'm trying to save (using add method) object which interferes row which is already in database - operation is successful. But I need to have some error raised in that situation, or maybe other solution that informs me that I'm not actually inserting object but updating. What method can I use? I read documentation and I didn't found solution. Am I missing something? you should be getting an IntegrityError raised if you add() a new object to the session that has a primary key which is the same as one already in the database. add() is the same as the former save_or_update() method, which would call straight down to save() if the object were pending. When you refer to needing to know if you're inserting or updating, im not clear on what the issue is - if you have an object that is detached, meaning it has a row in the DB, you add() it to a session, the session already has an object with that identity, you get an error. So I'm not really sure what the specific problem is here. 1. I'm not getting IntergrityError. You said that add is the same as save_or_update() method, but I'm interested in method which is the same as save() method - this method raises IntegrityError when I am trying to add new object to the the session that has a primary key which is the same as one already in the database. 2. My issue is that I'm creating REST API on my database. When somebody is trying to create object with existing id (there is an object in db with this id) I don't want to allow him to update this object - I want to reject his request. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: saving existing db object
On Apr 8, 6:48 pm, gwozdziu sz.gwo...@gmail.com wrote: On Apr 8, 6:34 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 8, 2011, at 10:54 AM, gwozdziu wrote: Hi! In SqlAlchemy 0.4 when I tried to save (using method save) to database object which interferes row which already exists in database (for example I have table with primary key `id`, row with id = 3 in this table, and I am trying to save object with the same id = 3) the operation raises error that object is already persistent. In Sqlalchemy 0.5.7 there is no save method, but there is add method. But when I'm trying to save (using add method) object which interferes row which is already in database - operation is successful. But I need to have some error raised in that situation, or maybe other solution that informs me that I'm not actually inserting object but updating. What method can I use? I read documentation and I didn't found solution. Am I missing something? you should be getting an IntegrityError raised if you add() a new object to the session that has a primary key which is the same as one already in the database. add() is the same as the former save_or_update() method, which would call straight down to save() if the object were pending. When you refer to needing to know if you're inserting or updating, im not clear on what the issue is - if you have an object that is detached, meaning it has a row in the DB, you add() it to a session, the session already has an object with that identity, you get an error. So I'm not really sure what the specific problem is here. 1. I'm not getting IntergrityError. You said that add is the same as save_or_update() method, but I'm interested in method which is the same as save() method - this method raises IntegrityError when I am trying to add new object to the the session that has a primary key which is the same as one already in the database. 2. My issue is that I'm creating REST API on my database. When somebody is trying to create object with existing id (there is an object in db with this id) I don't want to allow him to update this object - I want to reject his request. here is the piece of code: #before executing this code we have object with id = 5 in our database obj = self.mapper.get_from_dto(dto) # creating the object from values provided by user , this object has id = 5 self.mapper.add(obj) # we are putting new object (with id = 5). It conflicts with already existing object self.mapper.commit() # commit. New object overrides old one This is not what I'm expecting. I don't want to overriding (updating) old object (row). -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
