[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Dec 14, 2007, at 4:02 PM, dykang wrote: > > Sorry, I didn't clarify, I was speaking about with 0.3, not with 0.4. 0.3 has a very limited add_entity() method, with no ability to set it against an arbitrary alias, so its impossible in 0.3 for an ORM Query to load parallel instances where the non-primary instance is against an alias or other table other than its mapper's mapped table. if you remove the add_entity() but keep the contains_alias(), the primary Test objects are loaded from the Query object against the s2 selectable in the same manner as 0.4. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
Sorry, I didn't clarify, I was speaking about with 0.3, not with 0.4. On Dec 14, 11:25 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Dec 14, 2007, at 1:34 PM, dykang wrote: > > > > > > > This example can't work in this sequence, or this query can't be run > > and mapped? > > If I wanted to execute this query: > > SELECT inner_query.test_id AS inner_query_test_id, > > inner_query.test_other_id AS inner_query_test_other_id, > > inner_query.test_active AS inner_query_test_active, > > inner_query.test2_id AS inner_query_test2_id, > > inner_query.test2_other_id AS inner_query_test2_other_id, > > inner_query.test2_active AS inner_query_test2_active > > FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, > > test.active AS test_active, test2.id AS test2_id, test2.other_id AS > > test2_other_id, test2.active AS test2_active > > FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query > > > is my only option to do this query through text and build out my own > >column_labelsdictionary? > > im not seeing how you have that impression? the constructed "s2" > query in your example produces that exact SQL. as I said, you only > need to inform the Query about the "s2" alias, using the > contains_alias() option for the primary mapper and add_entity() for > additional mappers, so that it can translate incoming columns for the > mappers. > > print > session > .query > (Test > ).options > (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute()) > print > session > .query > (Test > ).from_statement > (s2).options(contains_alias(s2)).add_entity(Test2,alias=s2).all() --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Dec 14, 2007, at 1:34 PM, dykang wrote: > > This example can't work in this sequence, or this query can't be run > and mapped? > If I wanted to execute this query: > SELECT inner_query.test_id AS inner_query_test_id, > inner_query.test_other_id AS inner_query_test_other_id, > inner_query.test_active AS inner_query_test_active, > inner_query.test2_id AS inner_query_test2_id, > inner_query.test2_other_id AS inner_query_test2_other_id, > inner_query.test2_active AS inner_query_test2_active > FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, > test.active AS test_active, test2.id AS test2_id, test2.other_id AS > test2_other_id, test2.active AS test2_active > FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query > > is my only option to do this query through text and build out my own > column_labels dictionary? im not seeing how you have that impression? the constructed "s2" query in your example produces that exact SQL. as I said, you only need to inform the Query about the "s2" alias, using the contains_alias() option for the primary mapper and add_entity() for additional mappers, so that it can translate incoming columns for the mappers. print session .query (Test ).options (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute()) print session .query (Test ).from_statement (s2).options(contains_alias(s2)).add_entity(Test2,alias=s2).all() --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
This example can't work in this sequence, or this query can't be run
and mapped?
If I wanted to execute this query:
SELECT inner_query.test_id AS inner_query_test_id,
inner_query.test_other_id AS inner_query_test_other_id,
inner_query.test_active AS inner_query_test_active,
inner_query.test2_id AS inner_query_test2_id,
inner_query.test2_other_id AS inner_query_test2_other_id,
inner_query.test2_active AS inner_query_test2_active
FROM (SELECT test.id AS test_id, test.other_id AS test_other_id,
test.active AS test_active, test2.id AS test2_id, test2.other_id AS
test2_other_id, test2.active AS test2_active
FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query
is my only option to do this query through text and build out my own
column_labels dictionary?
On Dec 14, 8:06 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> this example as is cant really work in any version because the query
> doesnt know to map the "s2" alias to the mappers that its using, so
> you have to explicitly connect them. 0.3 has more limited capability
> to do this.
>
> anyway in 0.4 you can do it like this:
>
> print
> session
> .query
> (Test
> ).options
> (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute())
>
> and if youre on trunk also like this:
>
> print session.query(Test).select_from(s2).add_entity(Test2,
> alias=s2).all()
>
> On Dec 14, 2007, at 3:30 AM, dykang wrote:
>
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
>
> > metadata = MetaData("mysql://mrlabs:[EMAIL PROTECTED]:3306/test")
> > metadata.bind.echo=True
>
> > table = Table("test", metadata,
> >Column("id", Integer, primary_key=True),
> >Column("other_id", Integer),
> >Column("active", Boolean))
>
> > table2 = Table("test2", metadata,
> >Column("id", Integer, primary_key=True),
> >Column("other_id", Integer),
> >Column("active", Boolean))
> > table.create()
> > table.insert().execute([{"other_id":1, "active": False}, {"other_id":
> > 2, "active": True}])
>
> > table2.create()
> > table2.insert().execute([{"other_id":1, "active": False}, {"other_id":
> > 2, "active": True}])
>
> > test = table.select(table.c.active).alias("test")
> > class Test(object):
> >pass
>
> > class Test2(object):
> >pass
>
> > mapper(Test, table)
> > mapper(Test2, table2)
>
> > session = create_session()
> > q = join(table, table2, table2.c.other_id == table.c.id)
> > s = q.select(use_labels=True).alias("inner_query")
> > s2 = s.select(use_labels=True).alias("outer_query")
>
> > print session.query(Test,
> > Test2).instances(s2.execute(use_labels=True))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
this example as is cant really work in any version because the query
doesnt know to map the "s2" alias to the mappers that its using, so
you have to explicitly connect them. 0.3 has more limited capability
to do this.
anyway in 0.4 you can do it like this:
print
session
.query
(Test
).options
(contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute())
and if youre on trunk also like this:
print session.query(Test).select_from(s2).add_entity(Test2,
alias=s2).all()
On Dec 14, 2007, at 3:30 AM, dykang wrote:
> from sqlalchemy import *
> from sqlalchemy.orm import *
>
> metadata = MetaData("mysql://mrlabs:[EMAIL PROTECTED]:3306/test")
> metadata.bind.echo=True
>
> table = Table("test", metadata,
>Column("id", Integer, primary_key=True),
>Column("other_id", Integer),
>Column("active", Boolean))
>
> table2 = Table("test2", metadata,
>Column("id", Integer, primary_key=True),
>Column("other_id", Integer),
>Column("active", Boolean))
> table.create()
> table.insert().execute([{"other_id":1, "active": False}, {"other_id":
> 2, "active": True}])
>
> table2.create()
> table2.insert().execute([{"other_id":1, "active": False}, {"other_id":
> 2, "active": True}])
>
> test = table.select(table.c.active).alias("test")
> class Test(object):
>pass
>
> class Test2(object):
>pass
>
> mapper(Test, table)
> mapper(Test2, table2)
>
> session = create_session()
> q = join(table, table2, table2.c.other_id == table.c.id)
> s = q.select(use_labels=True).alias("inner_query")
> s2 = s.select(use_labels=True).alias("outer_query")
>
> print session.query(Test,
> Test2).instances(s2.execute(use_labels=True))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
Hi, I was looking at this bug fix, and I couldn't figure out where
part 2 of the problem was actually fixed.
If you can ignore the absurdity of this example, it shows a situation
where the second problem still appears to
exist:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData("mysql://mrlabs:[EMAIL PROTECTED]:3306/test")
metadata.bind.echo=True
table = Table("test", metadata,
Column("id", Integer, primary_key=True),
Column("other_id", Integer),
Column("active", Boolean))
table2 = Table("test2", metadata,
Column("id", Integer, primary_key=True),
Column("other_id", Integer),
Column("active", Boolean))
table.create()
table.insert().execute([{"other_id":1, "active": False}, {"other_id":
2, "active": True}])
table2.create()
table2.insert().execute([{"other_id":1, "active": False}, {"other_id":
2, "active": True}])
test = table.select(table.c.active).alias("test")
class Test(object):
pass
class Test2(object):
pass
mapper(Test, table)
mapper(Test2, table2)
session = create_session()
q = join(table, table2, table2.c.other_id == table.c.id)
s = q.select(use_labels=True).alias("inner_query")
s2 = s.select(use_labels=True).alias("outer_query")
print session.query(Test,
Test2).instances(s2.execute(use_labels=True))
for which I get the output:
2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30
SELECT inner_query.test_id AS inner_query_test_id,
inner_query.test_other_id AS inner_query_test_other_id,
inner_query.test_active AS inner_query_test_active,
inner_query.test2_id AS inner_query_test2_id,
inner_query.test2_other_id AS inner_query_test2_other_id,
inner_query.test2_active AS inner_query_test2_active
FROM (SELECT test.id AS test_id, test.other_id AS test_other_id,
test.active AS test_active, test2.id AS test2_id, test2.other_id AS
test2_other_id, test2.active AS test2_active
FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query
2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30 []
Traceback (most recent call last):
File "test2.py", line 41, in ?
print session.query(Test,
Test2).instances(s2.execute(use_labels=True))
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/query.py", line 1047, in instances
self.select_mapper._instance(context, row, result)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/mapper.py", line 1443, in _instance
identitykey = self.identity_key_from_row(row)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/mapper.py", line 950, in
identity_key_from_row
return (self.class_, tuple([row[column] for column in
self.pks_by_table[self.mapped_table]]), self.entity_name)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 1172, in __getitem__
return self.__parent._get_col(self.__row, key)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 993, in _get_col
rec = self._convert_key(key)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 930, in _convert_key
raise exceptions.NoSuchColumnError("Could not locate column in row
for column '%s'" % (str(key)))
sqlalchemy.exceptions.NoSuchColumnError: "Could not locate column in
row for column 'test.id'"
This is from a checkout of the rel_0_3 branch rev 3936.
Thanks,
David
On Oct 17, 7:07 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Oct 17, 2007, at 4:07 AM, klaus wrote:
>
>
>
> > Thanks a lot! The solution is so simple that I feel a little
> > embarassed...
>
> im embarrased that bug's been present for so long !
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Oct 17, 2007, at 4:07 AM, klaus wrote: > > Thanks a lot! The solution is so simple that I feel a little > embarassed... > im embarrased that bug's been present for so long ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
Thanks a lot! The solution is so simple that I feel a little
embarassed...
On 16 Okt., 18:15, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Oct 16, 2007, at 10:45 AM, klaus wrote:
>
>
>
> > The only thing that I could find out about the reason is that in
> > engine/base.py (1290)
> >context.column_labels
> > contains the wrong entries. It should contain something like
> >{..., '_': '_', ...}.
> > In the case above, however, it contains
> >{..., '_': '', '': '', ...}.
> > (That is, it contains two items for each column, but the values are
> > '' instead of '_'.)
>
> > After that, I got lost. These values are generated by the postgres
> > driver. But I could not find where it takes its input from the Alias
> > object to generate something different than for the Table object.
>
> that is exactly correct, and was one of two issues present in 0.4.
> both issues, one of which occurs in 0.3 and 0.4 and the other just in
> 0.4 (though its hiding in 0.3 to some degree as well) originate from
> two distinct name confusions that arise because you're using the name
> of the table as the name of the alias. If you name the alias
> something other than "test" then all issues go away.
>
> so one issue was a hardcoded notion of bind parameter names used in
> the ORM by query.get(), which also is used for a many-to-one lazyload
> - it used the "label" of the column which in this case is "test_id",
> and conflicted with the existing "test_id" name (the compiler would
> rename the param as "test_id_1" but the Query object wasn't tracking
> that). So 0.3 now generates a "random" name whereas 0.4 uses
> anonymous bind parameters now (which are like "random" bind param
> names except they are compile-time generated in a deterministic
> fashion).
>
> second issue is that the column_labels dictionary was being populated
> with column labels from all selects embedded in the statement, not
> just the top level one, so again the "test_id" column at the top
> level conflicted with the embedded "id" column. since compilation
> maintains a stack of select objects, the fix is easy as it means we
> only operate when the stack is only one select object deep (i.e. its
> the outermost select).
>
> 0.3 rev 3624 and 0.4 rev 3625
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
On Oct 16, 2007, at 10:45 AM, klaus wrote:
>
> The only thing that I could find out about the reason is that in
> engine/base.py (1290)
>context.column_labels
> contains the wrong entries. It should contain something like
>{..., '_': '_', ...}.
> In the case above, however, it contains
>{..., '_': '', '': '', ...}.
> (That is, it contains two items for each column, but the values are
> '' instead of '_'.)
>
> After that, I got lost. These values are generated by the postgres
> driver. But I could not find where it takes its input from the Alias
> object to generate something different than for the Table object.
that is exactly correct, and was one of two issues present in 0.4.
both issues, one of which occurs in 0.3 and 0.4 and the other just in
0.4 (though its hiding in 0.3 to some degree as well) originate from
two distinct name confusions that arise because you're using the name
of the table as the name of the alias. If you name the alias
something other than "test" then all issues go away.
so one issue was a hardcoded notion of bind parameter names used in
the ORM by query.get(), which also is used for a many-to-one lazyload
- it used the "label" of the column which in this case is "test_id",
and conflicted with the existing "test_id" name (the compiler would
rename the param as "test_id_1" but the Query object wasn't tracking
that). So 0.3 now generates a "random" name whereas 0.4 uses
anonymous bind parameters now (which are like "random" bind param
names except they are compile-time generated in a deterministic
fashion).
second issue is that the column_labels dictionary was being populated
with column labels from all selects embedded in the statement, not
just the top level one, so again the "test_id" column at the top
level conflicted with the embedded "id" column. since compilation
maintains a stack of select objects, the fix is easy as it means we
only operate when the stack is only one select object deep (i.e. its
the outermost select).
0.3 rev 3624 and 0.4 rev 3625
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
