[sqlalchemy] Mapped Orm Object Notifier
Hi I've been reading through the documentation and i can't see an existing feature for this. The table i'm working with stores properties about part of my program. I get a record from the session.queryone() function and i pass that around. The record is somewhere in a properties change dialog box and somewhere else as a label. Is there currently a way of having the class sets the label to register a listener with the orm record so that when the record is updated, It notifies the listener which updates the label? Regards, Jar. PS, I was thinking tonight that sqlAchemy would make an awesome content for a university subject. --~--~-~--~~~---~--~~ 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: Using sqlalchemy in twisted.
Pedro, I don't really have much of anything special as far as the connection goes--SQLAlchemy already does a pretty awesome job of handling all that for you. I just keep a module-level variable that I can import as needed, and call a function in that module to set up the connection with settings from a config file. As far as the models/mapping goes, I have all of the tables, classes, and mapping between then defined in one module, and I can just import from there whenever needed. Nothing about sessions/connections in there at all--that way there's no need to worry about them. It's really a pretty ordinary set-up. Unfortunately, all this code is for work, so I can't share it. I'll happily answer your questions, though. -Jeff On Mar 15, 2:42 pm, Pedro Algarvio, aka, s0undt3ch u...@ufsoft.org wrote: On Mar 11, 2:13 pm, 一首诗 newpt...@gmail.com wrote: Hi Jeff, In my project I use the *model* to indicate an instance of Database. I don't really need multiple instances of Database. But when I wrote unit test, I always create an new one database in setup, which create a new sqlite in memory database to avoid conflicts between test cases. About the trick to make *sess* a keywork parameter, that's really clever! Thanks a lot! On Mar 11, 9:05 pm, Jeff FW jeff...@gmail.com wrote: Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it model in your decorator, but it's really an instance of Database when it gets passed in as self. One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, self will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote: Hi Jeff, Thanks for your kind suggestion. I first add some log decorators, but i found when it might cause to print sqalchemy objects which has not been bound to any session. And I am not quite sure about how to make the decorator mor genreal. Actually, I think I must use model as the first parameter because as a instance method, _getObjectById require the first parameter to be self. Can you write a few lines of code to show your suggestion? On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote: That's pretty similar to what I do, actually, if a bit simpler (but that's good!) One suggestion would be to throw an except (maybe for the base SQLAlchemy exception class) in your try block, otherwise you run the risk of things dying in an ugly way. I'm not familiar with pyamf, so I don't know how it would handle errors, but twisted usually carries on as if nothing happens. Also, I'd make the decorator a bit more general--don't put the model argument in wrapper(). Put sess first, then take *args and **kwargs, and pass those right to the inner function f(). That way you can reuse it for anything that requires a DB session. Other things you could add (if so inclined) are decorators for logging and other types of error handling (like catching IntegrityErros thrown by duplicates.) I do those things, but I might be a bit OCD :-) -Jeff On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote: Hi, Thanks for your reply. I'm using it the way like you. The only difference is that I am using pyamf instead of PB. On every request, I delegate required db operations to a class called Database, similar to these code below. I used to use scope_session instead of create and close session every time. But as I said in my earlier mails, they don't work. These code below seems to work right now. But if you have more suggestion, I will be very thankful. #= def require_session(f): '''create and close session for each synchronous method''' def wrapper(model, *args, **kw): sess =
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. --~--~-~--~~~---~--~~ 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] Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
SQLAlchemy: 0.5.2
SQLite: 3.5.9
MySQL: 5.0.67-0ubuntu6
To short. The problem is 'The result come differently from session's
query and plain sql which built by session's query.'
Well... It is too difficult to explain the situation so just have a
look my code first please.
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
raise str(len(query.all()))
1 - So stupid. Should be '10'.
However. The result should be '10' because ...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
# raise str(len(query.all()))
sql = str(query)
query = meta.Session.query(model.Room).from_statement(sql)
raise str(len(query.all()))
10 - This is correct.
Or execute sql (written bottom of this post, built by session's
query) on Terminal with sqlite3/MySQL return '10' as well (when I
count the rows).
I'm using SQLAlchemy on Pylons 0.9.7 and the module named 'paginator'
doesn't allow to pass the data type of 'SQLAlchemy 0.5.2 's select
object (SQL Expression Table Data). That's why (and else) I need to
use ORM. (And I don't know that even SQL Expression could return
correct value or not.)
Does anyone have idea? Or is it just bug and i have to wait to be
fixed this?
By the way. I'm using 'declarative_base()' to build my ORM.
Thanks. and sorry for my horrible english :-(
sql
SELECT rooms.id AS rooms_id, rooms.name AS rooms_name,
rooms.space_utilized AS rooms_space_utilized, rooms.floor AS
rooms_floor, rooms.direction AS rooms_direction, rooms.reference_url
AS rooms_reference_url, rooms.remarks AS rooms_remarks,
rooms.private_remarks AS rooms_private_remarks, rooms.is_recommand AS
rooms_is_recommand, rooms.advertisement AS rooms_advertisement,
rooms.has_parking_space AS rooms_has_parking_space,
rooms.immediately_moveable AS rooms_immediately_moveable,
rooms.arrangement AS rooms_arrangement, rooms.arrangement_detail AS
rooms_arrangement_detail, rooms.house_rent AS rooms_house_rent,
rooms.security_deposit AS rooms_security_deposit, rooms.key_money AS
rooms_key_money, rooms.right_money AS rooms_right_money,
rooms.management_fee AS rooms_management_fee, rooms.communal_charge AS
rooms_communal_charge, rooms.parking_charge AS rooms_parking_charge,
rooms.property_id AS rooms_property_id, rooms.company_id AS
rooms_company_id, rooms.created AS rooms_created, rooms.modified AS
rooms_modified
FROM rooms JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
the Query will consolidate multiple rows with the same primary key into a
single object instance - comparing the rows returned by the two different
approaches will reveal the source of the issue. set echo='debug' to see
that output.
Alisue wrote:
SQLAlchemy: 0.5.2
SQLite: 3.5.9
MySQL: 5.0.67-0ubuntu6
To short. The problem is 'The result come differently from session's
query and plain sql which built by session's query.'
Well... It is too difficult to explain the situation so just have a
look my code first please.
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
raise str(len(query.all()))
1 - So stupid. Should be '10'.
However. The result should be '10' because ...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
# raise str(len(query.all()))
sql = str(query)
query = meta.Session.query(model.Room).from_statement(sql)
raise str(len(query.all()))
10 - This is correct.
Or execute sql (written bottom of this post, built by session's
query) on Terminal with sqlite3/MySQL return '10' as well (when I
count the rows).
I'm using SQLAlchemy on Pylons 0.9.7 and the module named 'paginator'
doesn't allow to pass the data type of 'SQLAlchemy 0.5.2 's select
object (SQL Expression Table Data). That's why (and else) I need to
use ORM. (And I don't know that even SQL Expression could return
correct value or not.)
Does anyone have idea? Or is it just bug and i have to wait to be
fixed this?
By the way. I'm using 'declarative_base()' to build my ORM.
Thanks. and sorry for my horrible english :-(
sql
SELECT rooms.id AS rooms_id, rooms.name AS rooms_name,
rooms.space_utilized AS rooms_space_utilized, rooms.floor AS
rooms_floor, rooms.direction AS rooms_direction, rooms.reference_url
AS rooms_reference_url, rooms.remarks AS rooms_remarks,
rooms.private_remarks AS rooms_private_remarks, rooms.is_recommand AS
rooms_is_recommand, rooms.advertisement AS rooms_advertisement,
rooms.has_parking_space AS rooms_has_parking_space,
rooms.immediately_moveable AS rooms_immediately_moveable,
rooms.arrangement AS rooms_arrangement, rooms.arrangement_detail AS
rooms_arrangement_detail, rooms.house_rent AS rooms_house_rent,
rooms.security_deposit AS rooms_security_deposit, rooms.key_money AS
rooms_key_money, rooms.right_money AS rooms_right_money,
rooms.management_fee AS rooms_management_fee, rooms.communal_charge AS
rooms_communal_charge, rooms.parking_charge AS rooms_parking_charge,
rooms.property_id AS rooms_property_id, rooms.company_id AS
rooms_company_id, rooms.created AS rooms_created, rooms.modified AS
rooms_modified
FROM rooms JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
Roy H. Han wrote: On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. ill be there monday and tuesday. PostGIS is a PG extension though so support for it should remain separateid most like an external tool along the lines of geodjango. --~--~-~--~~~---~--~~ 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: Mapped Orm Object Notifier
class OrmRecord(object): def _get_column_value(self, name): # return self.__dict__[name] return getattr(self, name) def _set_column_value(self, name, value): val = setattr(self, name, value) self._notify_change_functions() # Return result just because return val def add_change_function(self, instance, function): Add Change Function Add a function to this record that will be modified when this record is changed This function will be called and passed the record it wants to be notified about instance The instance of the object to be called back function The function to be called and passed the instance try: self._change_functions except AttributeError: self._change_functions = weakref.WeakKeyDictionary () self._change_functions[instance] = function def _notify_change_functions(self): try: self._change_functions except AttributeError: return for instance in self._change_functions: self._change_functions[instance](self) self._OrmRecord = OrmRecord print 'Mapped ' + self.schema_table_name + '\n\t' + str (self._OrmRecord) + '\n\t' + str(metadata.tables [self.schema_table_name]) self._mapper = mapper(self._OrmRecord, self._metadata_table) This is the code i put together I set the value of the orm_record using the setter and getter functions above. Anyone know of a more sqlalchemy way of doing it? --~--~-~--~~~---~--~~ 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: Mapped Orm Object Notifier
you can add event handlers to attributes using @validates: http://www.sqlalchemy.org/docs/05/mappers.html?highlight=validates#simple-validators or the more longhand AttributeExtension: http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.html?highlight=attributeextension#sqlalchemy.orm.interfaces.AttributeExtension jarrod.ches...@gmail.com wrote: class OrmRecord(object): def _get_column_value(self, name): # return self.__dict__[name] return getattr(self, name) def _set_column_value(self, name, value): val = setattr(self, name, value) self._notify_change_functions() # Return result just because return val def add_change_function(self, instance, function): Add Change Function Add a function to this record that will be modified when this record is changed This function will be called and passed the record it wants to be notified about instance The instance of the object to be called back function The function to be called and passed the instance try: self._change_functions except AttributeError: self._change_functions = weakref.WeakKeyDictionary () self._change_functions[instance] = function def _notify_change_functions(self): try: self._change_functions except AttributeError: return for instance in self._change_functions: self._change_functions[instance](self) self._OrmRecord = OrmRecord print 'Mapped ' + self.schema_table_name + '\n\t' + str (self._OrmRecord) + '\n\t' + str(metadata.tables [self.schema_table_name]) self._mapper = mapper(self._OrmRecord, self._metadata_table) This is the code i put together I set the value of the orm_record using the setter and getter functions above. Anyone know of a more sqlalchemy way of doing it? --~--~-~--~~~---~--~~ 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] getting referenced *class* from relation
Hi,
I'd like to get programmatically the class to which a relation
points. Suppose I have the following situation:
class Project(Base):
...
staff = relation(User, secondary=project_manager)
manager = relation(User, secondary=project_staff)
Now I want to get User class starting from Project and 'staff'.
Project.__mapper__.get_property('manager')._get_target().class_
seems to do that but the leading underscore in _get_target suggest it's
private, so I wandererd if that's the best way...
thanks in advance
sandro
*:-)
--
Sandro Dentella *:-)
http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy
--~--~-~--~~~---~--~~
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: Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
Well... sorry I can't find the issue from my code and output.
---output--
11:25:02,197 INFO [iyeiye.lib.search.wringout]
-
11:25:02,220 INFO [sqlalchemy.engine.base.Engine.0x...
48ec._begin_impl] BEGIN
11:25:02,224 INFO [sqlalchemy.engine.base.Engine.0x...
48ec._cursor_execute] SELECT count(1) AS count_1
FROM rooms INNER JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_id
11:25:02,224 INFO [sqlalchemy.engine.base.Engine.0x...
48ec._cursor_execute] []
11:25:02,224 DEBUG [sqlalchemy.engine.base.Engine.0x...
48ec._init_metadata] Col ('count_1',)
11:25:02,225 DEBUG [sqlalchemy.engine.base.Engine.0x...48ec.__init__]
Row (1L,)
11:25:02,229 INFO [sqlalchemy.engine.base.Engine.0x...
48ec._cursor_execute] SELECT rooms.id AS rooms_id, rooms.name AS
rooms_name, rooms.space_utilized AS rooms_space_utilized, rooms.floor
AS rooms_floor, rooms.direction AS rooms_direction,
rooms.reference_url AS rooms_reference_url, rooms.remarks AS
rooms_remarks, rooms.private_remarks AS rooms_private_remarks,
rooms.is_recommand AS rooms_is_recommand, rooms.advertisement AS
rooms_advertisement, rooms.has_parking_space AS
rooms_has_parking_space, rooms.immediately_moveable AS
rooms_immediately_moveable, rooms.arrangement AS rooms_arrangement,
rooms.arrangement_detail AS rooms_arrangement_detail, rooms.house_rent
AS rooms_house_rent, rooms.security_deposit AS rooms_security_deposit,
rooms.key_money AS rooms_key_money, rooms.right_money AS
rooms_right_money, rooms.management_fee AS rooms_management_fee,
rooms.communal_charge AS rooms_communal_charge, rooms.parking_charge
AS rooms_parking_charge, rooms.property_id AS rooms_property_id,
rooms.company_id AS rooms_company_id, rooms.created AS rooms_created,
rooms.modified AS rooms_modified
FROM rooms INNER JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_id
LIMIT 0, 1
11:25:02,230 INFO [sqlalchemy.engine.base.Engine.0x...
48ec._cursor_execute] []
11:25:02,232 DEBUG [sqlalchemy.engine.base.Engine.0x...
48ec._init_metadata] Col ('rooms_id', 'rooms_name',
'rooms_space_utilized', 'rooms_floor', 'rooms_direction',
'rooms_reference_url', 'rooms_remarks', 'rooms_private_remarks',
'rooms_is_recommand', 'rooms_advertisement',
'rooms_has_parking_space', 'rooms_immediately_moveable',
'rooms_arrangement', 'rooms_arrangement_detail', 'rooms_house_rent',
'rooms_security_deposit', 'rooms_key_money', 'rooms_right_money',
'rooms_management_fee', 'rooms_communal_charge',
'rooms_parking_charge', 'rooms_property_id', 'rooms_company_id',
'rooms_created', 'rooms_modified')
11:25:02,232 DEBUG [sqlalchemy.engine.base.Engine.0x...48ec.__init__]
Row (69L, '4528\xe5\x8f\xb7\xe5\xae\xa4', 2.0, 6L, 'south', 'http://
iyeiye.jp/',
[sqlalchemy] Re: Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
Oops. actually the out put was the below... sorry
11:34:23,867 INFO [iyeiye.lib.search.wringout]
-
11:34:23,887 INFO [sqlalchemy.engine.base.Engine.
0x...e8ec._begin_impl] BEGIN
11:34:23,890 INFO [sqlalchemy.engine.base.Engine.
0x...e8ec._cursor_execute] SELECT count(1) AS count_1
FROM rooms INNER JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit, rooms.key_money AS key_money, rooms.right_money AS
right_money, rooms.management_fee AS management_fee,
rooms.communal_charge AS communal_charge, rooms.parking_charge AS
parking_charge, rooms.property_id AS property_id, rooms.company_id AS
company_id, rooms.created AS created, rooms.modified AS modified
FROM rooms) AS anon_2 GROUP BY anon_2.property_id) AS anon_1 ON
rooms.property_id = anon_1.property_id AND rooms.house_rent =
anon_1.minprice GROUP BY rooms.property_id
11:34:23,890 INFO [sqlalchemy.engine.base.Engine.
0x...e8ec._cursor_execute] []
11:34:23,890 DEBUG [sqlalchemy.engine.base.Engine.
0x...e8ec._init_metadata] Col ('count_1',)
11:34:23,890 DEBUG [sqlalchemy.engine.base.Engine.0x...e8ec.__init__]
Row (1L,)
On 3月17日, 午前2:45, Michael Bayer mike...@zzzcomputing.com wrote:
the Query will consolidate multiple rows with the same primary key into a
single object instance - comparing the rows returned by the two different
approaches will reveal the source of the issue. set echo='debug' to see
that output.
Alisue wrote:
SQLAlchemy: 0.5.2
SQLite: 3.5.9
MySQL: 5.0.67-0ubuntu6
To short. The problem is 'The result come differently from session's
query and plain sql which built by session's query.'
Well... It is too difficult to explain the situation so just have a
look my code first please.
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
raise str(len(query.all()))
1 - So stupid. Should be '10'.
However. The result should be '10' because ...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
# raise str(len(query.all()))
sql = str(query)
query = meta.Session.query(model.Room).from_statement(sql)
raise str(len(query.all()))
10 - This is correct.
Or execute sql (written bottom of this post, built by session's
query) on Terminal with sqlite3/MySQL return '10' as well (when I
count the rows).
I'm using SQLAlchemy on Pylons 0.9.7 and the module named 'paginator'
doesn't allow to pass the data type of 'SQLAlchemy 0.5.2 's select
object (SQL Expression Table Data). That's why (and else) I need to
use ORM. (And I don't know that even SQL Expression could return
correct value or not.)
Does anyone have idea? Or is it just bug and i have to wait to be
fixed this?
By the way. I'm using 'declarative_base()' to build my ORM.
Thanks. and sorry for my horrible english :-(
sql
SELECT rooms.id AS rooms_id, rooms.name AS rooms_name,
rooms.space_utilized AS rooms_space_utilized, rooms.floor AS
rooms_floor, rooms.direction AS rooms_direction, rooms.reference_url
AS rooms_reference_url, rooms.remarks AS rooms_remarks,
rooms.private_remarks AS rooms_private_remarks, rooms.is_recommand AS
rooms_is_recommand, rooms.advertisement AS rooms_advertisement,
rooms.has_parking_space AS rooms_has_parking_space,
rooms.immediately_moveable AS rooms_immediately_moveable,
rooms.arrangement AS rooms_arrangement, rooms.arrangement_detail AS
rooms_arrangement_detail, rooms.house_rent AS rooms_house_rent,
rooms.security_deposit AS rooms_security_deposit,
[sqlalchemy] Re: Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
Hey thanks! I figured out but I still don't know why...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id)
# The lines below are the magic.
subquery = query.subquery()
query = meta.Session.query(model.Room)
query = query.join((subquery, model.Room.id == subquery.c.id))
I found it because 'len(query.all())' return the 'Correct Value' but
'query.count()'
How did it happen? I cant' understand
On 3月17日, 午前2:45, Michael Bayer mike...@zzzcomputing.com wrote:
the Query will consolidate multiple rows with the same primary key into a
single object instance - comparing the rows returned by the two different
approaches will reveal the source of the issue. set echo='debug' to see
that output.
Alisue wrote:
SQLAlchemy: 0.5.2
SQLite: 3.5.9
MySQL: 5.0.67-0ubuntu6
To short. The problem is 'The result come differently from session's
query and plain sql which built by session's query.'
Well... It is too difficult to explain the situation so just have a
look my code first please.
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
raise str(len(query.all()))
1 - So stupid. Should be '10'.
However. The result should be '10' because ...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id) # This line break
everything.
# raise str(len(query.all()))
sql = str(query)
query = meta.Session.query(model.Room).from_statement(sql)
raise str(len(query.all()))
10 - This is correct.
Or execute sql (written bottom of this post, built by session's
query) on Terminal with sqlite3/MySQL return '10' as well (when I
count the rows).
I'm using SQLAlchemy on Pylons 0.9.7 and the module named 'paginator'
doesn't allow to pass the data type of 'SQLAlchemy 0.5.2 's select
object (SQL Expression Table Data). That's why (and else) I need to
use ORM. (And I don't know that even SQL Expression could return
correct value or not.)
Does anyone have idea? Or is it just bug and i have to wait to be
fixed this?
By the way. I'm using 'declarative_base()' to build my ORM.
Thanks. and sorry for my horrible english :-(
sql
SELECT rooms.id AS rooms_id, rooms.name AS rooms_name,
rooms.space_utilized AS rooms_space_utilized, rooms.floor AS
rooms_floor, rooms.direction AS rooms_direction, rooms.reference_url
AS rooms_reference_url, rooms.remarks AS rooms_remarks,
rooms.private_remarks AS rooms_private_remarks, rooms.is_recommand AS
rooms_is_recommand, rooms.advertisement AS rooms_advertisement,
rooms.has_parking_space AS rooms_has_parking_space,
rooms.immediately_moveable AS rooms_immediately_moveable,
rooms.arrangement AS rooms_arrangement, rooms.arrangement_detail AS
rooms_arrangement_detail, rooms.house_rent AS rooms_house_rent,
rooms.security_deposit AS rooms_security_deposit, rooms.key_money AS
rooms_key_money, rooms.right_money AS rooms_right_money,
rooms.management_fee AS rooms_management_fee, rooms.communal_charge AS
rooms_communal_charge, rooms.parking_charge AS rooms_parking_charge,
rooms.property_id AS rooms_property_id, rooms.company_id AS
rooms_company_id, rooms.created AS rooms_created, rooms.modified AS
rooms_modified
FROM rooms JOIN (SELECT anon_2.property_id AS property_id, min
(anon_2.house_rent) AS minprice
FROM (SELECT rooms.id AS id, rooms.name AS name, rooms.space_utilized
AS space_utilized, rooms.floor AS floor, rooms.direction AS direction,
rooms.reference_url AS reference_url, rooms.remarks AS remarks,
rooms.private_remarks AS private_remarks, rooms.is_recommand AS
is_recommand, rooms.advertisement AS advertisement,
rooms.has_parking_space AS has_parking_space,
rooms.immediately_moveable AS immediately_moveable, rooms.arrangement
AS arrangement, rooms.arrangement_detail AS arrangement_detail,
rooms.house_rent AS house_rent, rooms.security_deposit AS
security_deposit,
[sqlalchemy] Re: Is this SQLAlchemy's bug? The result come differently from session's query and plain sql which built by session's query.
On Mar 16, 2009, at 10:48 PM, Alisue wrote:
Hey thanks! I figured out but I still don't know why...
subquery = query.subquery()
minroom = meta.Session.query(subquery.c.property_id, func.min
(subquery.c.house_rent).label('minprice'))
minroom = minroom.group_by(subquery.c.property_id)
minroom = minroom.subquery()
query = meta.Session.query(model.Room).join((minroom,
(model.Room.property_id==minroom.c.property_id)
(model.Room.house_rent == minroom.c.minprice)))
query = query.group_by(model.Room.property_id)
# The lines below are the magic.
subquery = query.subquery()
query = meta.Session.query(model.Room)
query = query.join((subquery, model.Room.id == subquery.c.id))
I found it because 'len(query.all())' return the 'Correct Value' but
'query.count()'
How did it happen? I cant' understand
there's no count in there, so not enough detail is given here to
determine how the SQL is generated (i.e. we can't see what query
starts out as).
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
