[sqlalchemy] with_polymorphic() and more than one level of inheritance
Hello,
I have the following mappers:
orm.mapper(Content, table.content,
polymorphic_on = table.content.c.content_type_id,
...)
orm.mapper(News, table.news, inherits = Content,
polymorphic_on = table.content.c.content_type_id)
orm.mapper(PlatformNews, table.platform_news, inherits = News,
polymorphic_identity = _get_type_id('platform news'))
When I do:
Content.query.with_polymorphic([PlatformNews])
SQLAlchemy forgot to add a JOIN for the news (News) table (because there
is more than one level of inheritance I guess), I wondered if there was
a way to do that automatically ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
attachment: jcigar.vcf
[sqlalchemy] query and subquery problem with anonymous field
I have heirachial data stored as a nested set model and I am trying to
retreive the data as a adjacent list model to supply to a tree widget
in an application. I have been playing around with and modifying the
nested set example provided with sqlalchemy to develop the queries. I
am using python 3.1.2 and sqlalchemy 0.6.2 on windows xp
I have created alisases for the table with the following:
tbl1 = aliased(NSM)
tbl2 = aliased(NSM)
tbl3 = aliased(NSM)
I am trying to implement the following query:
rawsql =
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
executing the raw SQL query as:
raw_query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).from_statement(rawsql)
rset = raw_sql.all()
works fine and the adjacent list model form is returned.
However, when I try to implement it with subquery in sqlalchemy in the
following form:
sub_query =
session.query(func.max(tbl3.left).label('max_left')).filter(tbl1.left
tbl3.left).filter(tbl1.left tbl3.right).subquery()
query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).outerjoin((tbl2,
tbl2.left==sub_query.c.max_left))
rset = query.all()
I get the following error:
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
anon_1.max_left
...
I have printed the queries for the raw SQL, subquery and query objects
to assist in diagnosing what I am missing in the query/subquery.
***
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
***
SELECT max(nsm_1.lft) AS max_left
FROM nsm AS nsm_1, nsm AS nsm_2
WHERE nsm_2.lft nsm_1.lft AND nsm_2.lft nsm_1.rgt
***
SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
parent
FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft =
anon_1.max_left
***
Any help in resolving this would be greatly appreciated.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] with_polymorphic() and more than one level of inheritance
On Jul 16, 2010, at 7:34 AM, Julien Cigar wrote:
Hello,
I have the following mappers:
orm.mapper(Content, table.content,
polymorphic_on = table.content.c.content_type_id,
...)
orm.mapper(News, table.news, inherits = Content,
polymorphic_on = table.content.c.content_type_id)
orm.mapper(PlatformNews, table.platform_news, inherits = News,
polymorphic_identity = _get_type_id('platform news'))
When I do:
Content.query.with_polymorphic([PlatformNews])
SQLAlchemy forgot to add a JOIN for the news (News) table (because there is
more than one level of inheritance I guess), I wondered if there was a way to
do that automatically ?
not sure here but the two possibiltiies are: the extra polymorphic_on, or
perhaps the with_polymorphic() call.I only say that because the second
polymorphic_on is not what the mapper expects and we have a lot of tests for
a-b-c inheritance which work fine. At least, you should only have the
polymorphic_on defined on the Content table. I'm not sure we've exercised
with_polymorphic() as much with multilevel, so perhaps you should place News in
that list as well as a workaround for now (or join to the tables directly).
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
jcigar.vcf
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] query and subquery problem with anonymous field
On Jul 16, 2010, at 4:55 AM, dr wrote:
I have heirachial data stored as a nested set model and I am trying to
retreive the data as a adjacent list model to supply to a tree widget
in an application. I have been playing around with and modifying the
nested set example provided with sqlalchemy to develop the queries. I
am using python 3.1.2 and sqlalchemy 0.6.2 on windows xp
I have created alisases for the table with the following:
tbl1 = aliased(NSM)
tbl2 = aliased(NSM)
tbl3 = aliased(NSM)
I am trying to implement the following query:
rawsql =
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
executing the raw SQL query as:
raw_query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).from_statement(rawsql)
rset = raw_sql.all()
works fine and the adjacent list model form is returned.
However, when I try to implement it with subquery in sqlalchemy in the
following form:
sub_query =
session.query(func.max(tbl3.left).label('max_left')).filter(tbl1.left
tbl3.left).filter(tbl1.left tbl3.right).subquery()
query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).outerjoin((tbl2,
tbl2.left==sub_query.c.max_left))
sub_query is a scalar subquery here, you don't want it in the FROM clause. do
it like this:
sub_query = session.query(func.max(tbl3.left).label('max_left')).\
filter(tbl1.left tbl3.left).\
filter(tbl1.left tbl3.right).subquery().as_scalar()
query = session.query(tbl1.id, tbl1.text, tbl2.id.label('parent')).\
outerjoin((tbl2,tbl2.left==sub_query))
rset = query.all()
I get the following error:
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
anon_1.max_left
...
I have printed the queries for the raw SQL, subquery and query objects
to assist in diagnosing what I am missing in the query/subquery.
***
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
***
SELECT max(nsm_1.lft) AS max_left
FROM nsm AS nsm_1, nsm AS nsm_2
WHERE nsm_2.lft nsm_1.lft AND nsm_2.lft nsm_1.rgt
***
SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
parent
FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft =
anon_1.max_left
***
Any help in resolving this would be greatly appreciated.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: query and subquery problem with anonymous field
On 16 July, 15:15, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 16, 2010, at 4:55 AM, dr wrote:
I have heirachial data stored as a nested set model and I am trying to
retreive the data as a adjacent list model to supply to a tree widget
in an application. I have been playing around with and modifying the
nested set example provided with sqlalchemy to develop the queries. I
am using python 3.1.2 and sqlalchemy 0.6.2 on windows xp
I have created alisases for the table with the following:
tbl1 = aliased(NSM)
tbl2 = aliased(NSM)
tbl3 = aliased(NSM)
I am trying to implement the following query:
rawsql =
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
executing the raw SQL query as:
raw_query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).from_statement(rawsql)
rset = raw_sql.all()
works fine and the adjacent list model form is returned.
However, when I try to implement it with subquery in sqlalchemy in the
following form:
sub_query =
session.query(func.max(tbl3.left).label('max_left')).filter(tbl1.left
tbl3.left).filter(tbl1.left tbl3.right).subquery()
query = session.query(tbl1.id, tbl1.text,
tbl2.id.label('parent')).outerjoin((tbl2,
tbl2.left==sub_query.c.max_left))
sub_query is a scalar subquery here, you don't want it in the FROM clause.
do it like this:
sub_query = session.query(func.max(tbl3.left).label('max_left')).\
filter(tbl1.left tbl3.left).\
filter(tbl1.left tbl3.right).subquery().as_scalar()
query = session.query(tbl1.id, tbl1.text, tbl2.id.label('parent')).\
outerjoin((tbl2,tbl2.left==sub_query))
rset = query.all()
I get the following error:
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
anon_1.max_left
...
I have printed the queries for the raw SQL, subquery and query objects
to assist in diagnosing what I am missing in the query/subquery.
***
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
***
SELECT max(nsm_1.lft) AS max_left
FROM nsm AS nsm_1, nsm AS nsm_2
WHERE nsm_2.lft nsm_1.lft AND nsm_2.lft nsm_1.rgt
***
SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
parent
FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft =
anon_1.max_left
***
Any help in resolving this would be greatly appreciated.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
Michael,
I have implemented what you suggested and below is a comparison of the
results:
***
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
BEGIN
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
()
(1, 'Albert', None)
(2, 'Bert', 1)
(3, 'Chuck', 1)
(4, 'Donna', 3)
(5, 'Eddie', 3)
(6, 'Fred', 3)
***
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
parent
FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft = (SELECT
max(nsm_3.lft) AS max_left
FROM nsm AS nsm_3, nsm AS nsm_1
WHERE nsm_1.lft nsm_3.lft AND nsm_1.lft nsm_3.rgt)
2010-07-16 15:44:31,536 INFO sqlalchemy.engine.base.Engine.0x...7910
()
(1, 'Albert', 6)
(2, 'Bert', 6)
(3, 'Chuck', 6)
(4, 'Donna', 6)
(5, 'Eddie', 6)
(6, 'Fred', 6)
***
Comparing the raw sql (correct) and query/subquery results you will
notice that the query/subquery does not return the correct parent id
Any further ideas about why this is producing this result?
Kind Regards
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from
Re: [sqlalchemy] checking for C extensions at runtime
I'm actually not sure, I did a bit of googling and couldn't really find much. Pep 386 talks about version comparison in distutils: http://www.python.org/dev/peps/pep-0386/#id10 As for scratching my itch it wouldn't have to be in the __version__ string, I was just wondering how I would check if the SQLAlchemy on a system had the C extensions enabled on it or not. On 07/15/2010 01:40 PM, Michael Bayer wrote: I was wondering if there was a way to check at runtime if the C extensions were built with SQLAlchemy? If not I was wondering if you would consider adding something to the __version__ string? is there some guideline for the format on that ? -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Unknown Issue Causing Extremely Slow Query
Hello -- I'm working with a database populated with data originally from a file structure of files that are ~150MB each. We are dealing with a lot of data that is being stored in the database using the 'ARRAY' datatype, specifically numeric[]. After loading some of the data into the database I tried performing a query to get back some data, and comparing it with code that reads directly from the file system - but the database query took ~50 times longer to complete. For instance, to retrieve 100 records that contain a few 4000 element arrays each using the code that reads the filesystem it took less than a second, but the query on the database took around 25 seconds to complete. Has anyone else had issues with array types slowing down queries or does this sound more like another issue? Thanks! Adrian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Unknown Issue Causing Extremely Slow Query
You absolutely need to turn in echoing and locate the specific SQL query which causes the issue. Queries can take excessive time for a very wide variety of reasons. On Jul 16, 2010, at 12:56 PM, Adrian Price-Whelan wrote: Hello -- I'm working with a database populated with data originally from a file structure of files that are ~150MB each. We are dealing with a lot of data that is being stored in the database using the 'ARRAY' datatype, specifically numeric[]. After loading some of the data into the database I tried performing a query to get back some data, and comparing it with code that reads directly from the file system - but the database query took ~50 times longer to complete. For instance, to retrieve 100 records that contain a few 4000 element arrays each using the code that reads the filesystem it took less than a second, but the query on the database took around 25 seconds to complete. Has anyone else had issues with array types slowing down queries or does this sound more like another issue? Thanks! Adrian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
