[sqlalchemy] Re: How can I built two different ForeignKey relation refer to a same model.
OOps. Doesn't matter. It's all my fault.
I used Unittest and I delete management_company on tearDown. (duaring
management_company_id set nullable=False)
I set 'cascade=all' and everything goes Correct. Sorry.
On 8月20日, 午前11:41, Alisue hello.goodbye.by.beat...@gmail.com wrote:
Article has two company. 'management_company' and
'intermediate_company'
So I wrote code like below.
code
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
name = Column(String(255))
class Article(Base):
__tablename__ = 'articles'
id = Column(Integer, primary_key=True)
name = Column(String(255))
management_company_id = Column(Integer, ForeignKey
('companies.id'))
intermediate_company_id = Column(Integer, ForeignKey
('companies.id'))
management_company = relation('Company',
backref='management_articles',
primaryjoin='(Article.management_company_id==Company.id)')
intermediate_company = relation('Company',
backref='intermediate_articles',
primaryjoin='(Article.intermediate_company_id==Company.id)')
/code
However, This code doesn't work with a code below.
code
management = Company(name='A')
intermediate = Company(name='B')
session.add(management)
session.add(intermediate)
session.commit()
article = Article(name='C')
article.management_company = management
article.intermediate_company = intermediate
session.add(article)
session.commit()
/code
And The Error message was:
Traceback (most recent call last):
File /Users/alisue/Documents/workspaces/aptana/selling.iyeiye/
sellingiyeiye/tests/model/articles/test_article.py, line 29, in
tearDown
meta.Session.delete(self.intermediate_company)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/scoping.py, line 123, in do
return getattr(self.registry(), name)(*args, **kwargs)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1134, in delete
cascade_states = list(_cascade_state_iterator('delete', state))
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1556, in _cascade_state_iterator
for (o, m) in mapper.cascade_iterator(cascade, state, **kwargs):
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/mapper.py, line 1169, in cascade_iterator
instance, instance_mapper, corresponding_state = iterator.next()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/properties.py, line 684, in cascade_iterator
instances = state.value_as_iterable(self.key, passive=passive)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/state.py, line 110, in value_as_iterable
x = impl.get(self, dict_, passive=passive)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/attributes.py, line 374, in get
value = callable_()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/strategies.py, line 568, in __call__
result = q.all()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/query.py, line 1198, in all
return list(self)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/query.py, line 1291, in __iter__
self.session._autoflush()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 899, in _autoflush
self.flush()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1354, in flush
self._flush(objects)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1432, in _flush
flush_context.execute()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 257, in execute
UOWExecutor().execute(self, tasks)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 720, in execute
self.execute_save_steps(trans, task)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 735, in execute_save_steps
self.save_objects(trans, task)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 726, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/mapper.py, line 1376, in _save_obj
c = connection.execute(statement.values(value_params), params)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5
[sqlalchemy] How can I built two different ForeignKey relation refer to a same model.
Article has two company. 'management_company' and
'intermediate_company'
So I wrote code like below.
code
class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
name = Column(String(255))
class Article(Base):
__tablename__ = 'articles'
id = Column(Integer, primary_key=True)
name = Column(String(255))
management_company_id = Column(Integer, ForeignKey
('companies.id'))
intermediate_company_id = Column(Integer, ForeignKey
('companies.id'))
management_company = relation('Company',
backref='management_articles',
primaryjoin='(Article.management_company_id==Company.id)')
intermediate_company = relation('Company',
backref='intermediate_articles',
primaryjoin='(Article.intermediate_company_id==Company.id)')
/code
However, This code doesn't work with a code below.
code
management = Company(name='A')
intermediate = Company(name='B')
session.add(management)
session.add(intermediate)
session.commit()
article = Article(name='C')
article.management_company = management
article.intermediate_company = intermediate
session.add(article)
session.commit()
/code
And The Error message was:
Traceback (most recent call last):
File /Users/alisue/Documents/workspaces/aptana/selling.iyeiye/
sellingiyeiye/tests/model/articles/test_article.py, line 29, in
tearDown
meta.Session.delete(self.intermediate_company)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/scoping.py, line 123, in do
return getattr(self.registry(), name)(*args, **kwargs)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1134, in delete
cascade_states = list(_cascade_state_iterator('delete', state))
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1556, in _cascade_state_iterator
for (o, m) in mapper.cascade_iterator(cascade, state, **kwargs):
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/mapper.py, line 1169, in cascade_iterator
instance, instance_mapper, corresponding_state = iterator.next()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/properties.py, line 684, in cascade_iterator
instances = state.value_as_iterable(self.key, passive=passive)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/state.py, line 110, in value_as_iterable
x = impl.get(self, dict_, passive=passive)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/attributes.py, line 374, in get
value = callable_()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/strategies.py, line 568, in __call__
result = q.all()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/query.py, line 1198, in all
return list(self)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/query.py, line 1291, in __iter__
self.session._autoflush()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 899, in _autoflush
self.flush()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1354, in flush
self._flush(objects)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/session.py, line 1432, in _flush
flush_context.execute()
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 257, in execute
UOWExecutor().execute(self, tasks)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 720, in execute
self.execute_save_steps(trans, task)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 735, in execute_save_steps
self.save_objects(trans, task)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 726, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/orm/mapper.py, line 1376, in _save_obj
c = connection.execute(statement.values(value_params), params)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/engine/base.py, line 874, in _execute_clauseelement
return self.__execute_context(context)
File /Library/Python/2.5/site-packages/SQLAlchemy-0.5.5-py2.5.egg/
sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /Library/Python/2.5/site
[sqlalchemy] Re: How can I use count with group_by with webhelpers.paginate
Thanks! That is the what i want.
On 6月22日, 午後11:40, Michael Bayer mike...@zzzcomputing.com wrote:
Alisue wrote:
Oops. I found the solution with my self.
like below.
c.paginator = paginate.Page(
meta.Session.query(Article).select_from(query.subquery()),
page=int(request.params.get('page',1)),
items_per_page=int(request.params.get('items_per_page', 10))
)
But why can't I do like below? or similar way?
query.select_from(query.subquery())
that is what query.from_self() does.
On 6月22日, 午後6:34, Alisue
hello.goodbye.by.beat...@gmail.com wrote:
I have a Article table may relate with a Room table and I want to find
Article sometime with Room.name.
So I wrote the code like below.
query = meta.Session.query(Article)
query = query.outerjoin('rooms')
if room_name is not None:
query = query.filter(Room.name == room_name)
query = query.group_by(Article.id)
return query
A result is correct. I got all Article name without room_name and
Articles which has rooms named as room_name.
However, when I use this query with webhelpers.paginate(http://
beta.pylonshq.com/docs/ja/0.9.7/thirdparty/webhelpers/paginate/
#webhelpers.paginate.Page), everything goes wrong. 'paginator' doesn't
work correct.
So I chacked logs and find this output.
'SELECT COUNT(1) AS count_1 FROM articles LEFT OUTER JOIN rooms ON
articles.id = rooms.article_id GROUP BY articles.id'
This is might made by .count() function of sqlalchemy in somewhere in
webhelpers.paginate code I think. However what I want to get is
'SELECT COUNT(1) AS count_1 FROM (SELECT * FROM articles LEFT OUTER
JOIN rooms ON articles.id = rooms.article_id GROUP BY articles.id)'
I have no idea to fix this problem. Anyone? thank you.
SQLAlchemy: 0.5.4
Pylons: 0.9.7
--~--~-~--~~~---~--~~
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] How can I use count with group_by with webhelpers.paginate
I have a Article table may relate with a Room table and I want to find
Article sometime with Room.name.
So I wrote the code like below.
query = meta.Session.query(Article)
query = query.outerjoin('rooms')
if room_name is not None:
query = query.filter(Room.name == room_name)
query = query.group_by(Article.id)
return query
A result is correct. I got all Article name without room_name and
Articles which has rooms named as room_name.
However, when I use this query with webhelpers.paginate(http://
beta.pylonshq.com/docs/ja/0.9.7/thirdparty/webhelpers/paginate/
#webhelpers.paginate.Page), everything goes wrong. 'paginator' doesn't
work correct.
So I chacked logs and find this output.
'SELECT COUNT(1) AS count_1 FROM articles LEFT OUTER JOIN rooms ON
articles.id = rooms.article_id GROUP BY articles.id'
This is might made by .count() function of sqlalchemy in somewhere in
webhelpers.paginate code I think. However what I want to get is
'SELECT COUNT(1) AS count_1 FROM (SELECT * FROM articles LEFT OUTER
JOIN rooms ON articles.id = rooms.article_id GROUP BY articles.id)'
I have no idea to fix this problem. Anyone? thank you.
SQLAlchemy: 0.5.4
Pylons: 0.9.7
--~--~-~--~~~---~--~~
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: How can I use count with group_by with webhelpers.paginate
Oops. I found the solution with my self.
like below.
c.paginator = paginate.Page(
meta.Session.query(Article).select_from(query.subquery()),
page=int(request.params.get('page',1)),
items_per_page=int(request.params.get('items_per_page', 10))
)
But why can't I do like below? or similar way?
query.select_from(query.subquery())
On 6月22日, 午後6:34, Alisue hello.goodbye.by.beat...@gmail.com wrote:
I have a Article table may relate with a Room table and I want to find
Article sometime with Room.name.
So I wrote the code like below.
query = meta.Session.query(Article)
query = query.outerjoin('rooms')
if room_name is not None:
query = query.filter(Room.name == room_name)
query = query.group_by(Article.id)
return query
A result is correct. I got all Article name without room_name and
Articles which has rooms named as room_name.
However, when I use this query with webhelpers.paginate(http://
beta.pylonshq.com/docs/ja/0.9.7/thirdparty/webhelpers/paginate/
#webhelpers.paginate.Page), everything goes wrong. 'paginator' doesn't
work correct.
So I chacked logs and find this output.
'SELECT COUNT(1) AS count_1 FROM articles LEFT OUTER JOIN rooms ON
articles.id = rooms.article_id GROUP BY articles.id'
This is might made by .count() function of sqlalchemy in somewhere in
webhelpers.paginate code I think. However what I want to get is
'SELECT COUNT(1) AS count_1 FROM (SELECT * FROM articles LEFT OUTER
JOIN rooms ON articles.id = rooms.article_id GROUP BY articles.id)'
I have no idea to fix this problem. Anyone? thank you.
SQLAlchemy: 0.5.4
Pylons: 0.9.7
--~--~-~--~~~---~--~~
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.
',
'http://www.google.com/', '', '\xe9\x9b\xa8\xe6\xbc\x8f\xe3\x82\x8a
\xe3\x81\x82\xe3\x82\x8a', 1, '\xe5\xb8\x82\xe5\xbd
\xb9\xe6\x89\x80\xe7\x9b\xae\xe3\x81\xae\xe5\x89\x8d\xef\xbc\x81', 1,
1, 'over 5LDK', '4*4', 6L, None, None, 1.0, 4000L, 400L, 1L,
1L, 4L, datetime.datetime(2009, 3, 17, 11, 20, 16), None)
11:25:02,300 DEBUG [sqlalchemy.engine.base.Engine.0x...48ec.__init__]
Row (77L, '2063\xe5\x8f\xb7\xe5\xae\xa4', 15.0, 12L, 'west', 'http://
iyeiye.jp/', '', '\xe9\x9b\xa8\xe6\xbc\x8f\xe3\x82\x8a
\xe3\x81\x82\xe3\x82\x8a', 0, '', 1, 1, 'over 5LDK', '4*4', 6L,
1.0, 0.5, 1.0, 9000L, 300L, None, 1L, 8L, datetime.datetime(2009, 3,
17, 11, 20, 16), None)
-
Having no idea :-(
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, rooms.key_money AS key_money
[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
