[sqlalchemy] Column anon_1.anon_2 is generated in a select statement
Column anon_1.anon_2 is generated in the following scenario:
dbsession.query(FirstThing, FirstThing.moved_by.any(User.id ==
user_id)).options(joinedload_all('some_property'))
query = query.join(SecondThing, SecondThing.first_thing_id == FirstThing.id)
query = query.order_by(OneThing.ordering_field).limit(count)
Also, it is important that both FirstThing and SecondThing polymorphically
inherit from Thing.
Effectively, query.all() generates a query like
SELECT ... anon_1.anon_2 AS anon_1_anon_2 ...
FROM
(SELECT first_thing.id AS first_thing.id, EXISTS (SELECT 1 FROM
first_thing_moves, users ...) AS anon_2
FROM thing JOIN first_thing ON ... JOIN (SELECT ... FROM thing JOIN
second_thing) AS anon_3 ON ... ORDER BY ... LIMIT ...) AS anon_1 ORDER BY
...
Why would anon_1.anon_2 column be generated there - it is, I think, not
even a valid syntax?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/VmrpL96y64UJ.
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.
Re: [sqlalchemy] Column anon_1.anon_2 is generated in a select statement
I should have pointed out that I got a NoSuchColumnError because of
anon_1.anon_2. There is no column anon_2 in any of the tables. It's
just an alias name of a derived table.
Is table_name_1.table_name_2 supposed to mean anything?
On Tuesday, February 28, 2012 5:53:42 PM UTC+2, Michael Bayer wrote:
On Feb 28, 2012, at 9:40 AM, naktinis wrote:
Column anon_1.anon_2 is generated in the following scenario:
dbsession.query(FirstThing, FirstThing.moved_by.any(User.id ==
user_id)).options(joinedload_all('some_property'))
query = query.join(SecondThing, SecondThing.first_thing_id ==
FirstThing.id)
query = query.order_by(OneThing.ordering_field).limit(count)
Also, it is important that both FirstThing and SecondThing polymorphically
inherit from Thing.
Effectively, query.all() generates a query like
SELECT ... anon_1.anon_2 AS anon_1_anon_2 ...
FROM
(SELECT first_thing.id AS first_thing.id, EXISTS (SELECT 1 FROM
first_thing_moves, users ...) AS anon_2
FROM thing JOIN first_thing ON ... JOIN (SELECT ... FROM thing JOIN
second_thing) AS anon_3 ON ... ORDER BY ... LIMIT ...) AS anon_1 ORDER BY
...
Why would anon_1.anon_2 column be generated there - it is, I think, not
even a valid syntax?
it's valid, anon_1 is the label applied to a subquery, you can see where
it has (SELECT ) AS anon_1. anon_1 becomes what we sometimes call
a derived table in the query and is then valid like any other alias name.
The join is because when we have a joined inheritance class B inherits
from A, then we join to it from C, we are effectively joining:
SELECT * FROM C JOIN (A JOIN B ON A.id=B.id) ON C.x=A.y
That is valid SQL, however, it doesn't work on SQLite, and also doesn't
work on MySQL versions before 5. It also may or may not have issues on
some other backends.So SQLAlchemy turns A JOIN B into a subquery:
SELECT * FROM C JOIN (SELECT * FROM A JOIN B ON A.id=B.id) AS anon_1 ON
C.x=anon_1.y
as it turns out, this approach generalizes much more nicely than just
putting A JOIN B in there. Suppose classes B1 and B2 inherit from A in a
concrete fashion, using tables B1 and B2 to represent the full row.
Then you wanted to join from C to A.SQLAlchemy would have you doing a
polymorphic union which means you select from the UNION of B1 and B2:
SELECT * FROM C JOIN (SELECT * FROM B1 UNION SELECT * FROM B2) AS anon_1
ON C.x=anon_1.y
where anon_1.y here would be y from B1 unioned to y from B2.
Anyway, SQLAlchemy is very quick to wrap up a series of rows in a
subquery, applying an alias to it, since that syntax works the most
consistently across not only all backends but across a really wide range of
scenarios.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/g5juNMWd4moJ.
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.
Re: [sqlalchemy] Column anon_1.anon_2 is generated in a select statement
Cool! Thanks so much! I'll give the workaround a try.
Yes, now I see - anon_2 was a column name, not a table name.
On Tuesday, February 28, 2012 6:54:42 PM UTC+2, Michael Bayer wrote:
OK it's another limit + joinedload - subquery targeting issue, so this is
http://www.sqlalchemy.org/trac/ticket/2419 and workaround for now is use
subqueryload_all() instead of joinedload_all() for this specific query.
On Feb 28, 2012, at 11:43 AM, Michael Bayer wrote:
Here's a test which generates essentially the same form and runs fine,
I'll try to simulate more of exactly what you're doing. Or if you had a
real test case ready to go, would save me a ton of time.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
bs = relationship(B)
class B(Base):
__tablename__ = b
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(bs=[B(), B()])
])
s.commit()
print s.query(A.bs.any()).select_from(A).from_self().all()
SQL:
SELECT anon_1.anon_2 AS anon_1_anon_2
FROM (SELECT EXISTS (SELECT 1
FROM b
WHERE a.id = b.a_id) AS anon_2
FROM a) AS anon_1
2012-02-28 11:41:19,912 INFO sqlalchemy.engine.base.Engine ()
[(True,)]
On Feb 28, 2012, at 11:37 AM, Michael Bayer wrote:
it appears here the anon_2 is a label being given to your otherwise
unnamed FirstThing.moved_by.any() call, which is a subquery.
you're not showing me the full query being rendered but I would imagine
the important bits are:
SELECT anon_1.anon_2 AS anon_1_anon_2 FROM
(SELECT EXISTS (...) AS anon_2) AS anon_1
which is valid. The query would fail to execute if it weren't.
NoSuchColumnError here would likely be alleviated if you just said
FirstThing.moved_by.any().label(some_label).
I'll look into seeing why an anonymous any() subquery doesn't get targeted
by Query correctly here.
On Feb 28, 2012, at 11:02 AM, naktinis wrote:
I should have pointed out that I got a NoSuchColumnError because of
anon_1.anon_2. There is no column anon_2 in any of the tables. It's
just an alias name of a derived table.
Is table_name_1.table_name_2 supposed to mean anything?
On Tuesday, February 28, 2012 5:53:42 PM UTC+2, Michael Bayer wrote:
On Feb 28, 2012, at 9:40 AM, naktinis wrote:
Column anon_1.anon_2 is generated in the following scenario:
dbsession.query(FirstThing, FirstThing.moved_by.any(User.id ==
user_id)).options(joinedload_all('some_property'))
query = query.join(SecondThing, SecondThing.first_thing_id ==
FirstThing.id)
query = query.order_by(OneThing.ordering_field).limit(count)
Also, it is important that both FirstThing and SecondThing
polymorphically inherit from Thing.
Effectively, query.all() generates a query like
SELECT ... anon_1.anon_2 AS anon_1_anon_2 ...
FROM
(SELECT first_thing.id AS first_thing.id, EXISTS (SELECT 1 FROM
first_thing_moves, users ...) AS anon_2
FROM thing JOIN first_thing ON ... JOIN (SELECT ... FROM thing JOIN
second_thing) AS anon_3 ON ... ORDER BY ... LIMIT ...) AS anon_1 ORDER BY
...
Why would anon_1.anon_2 column be generated there - it is, I think, not
even a valid syntax?
it's valid, anon_1 is the label applied to a subquery, you can see
where it has (SELECT ) AS anon_1. anon_1 becomes what we
sometimes call a derived table in the query and is then valid like any
other alias name.
The join is because when we have a joined inheritance class B inherits
from A, then we join to it from C, we are effectively joining:
SELECT * FROM C JOIN (A JOIN B ON A.id=B.id) ON C.x=A.y
That is valid SQL, however, it doesn't work on SQLite, and also doesn't
work on MySQL versions before 5. It also may or may not have issues on
some other backends.So SQLAlchemy turns A JOIN B into a subquery:
SELECT * FROM C JOIN (SELECT * FROM A JOIN B ON A.id=B.id) AS anon_1 ON
C.x=anon_1.y
as it turns out, this approach generalizes much more nicely than just
putting A JOIN B in there. Suppose classes B1 and B2 inherit from A in a
concrete fashion, using tables B1 and B2 to represent the full row.
Then you wanted to join from C to A.SQLAlchemy would have you doing a
polymorphic union which means you select from the UNION of B1 and B2:
SELECT * FROM C JOIN (SELECT * FROM B1 UNION SELECT * FROM B2) AS anon_1
ON C.x=anon_1.y
where anon_1.y here would be y from B1 unioned to y from B2.
Anyway, SQLAlchemy is very quick to wrap up a series of rows in a
subquery, applying an alias to it, since that syntax works the most
consistently across not only all backends but across a really wide range of
scenarios.
--
You received this message because you are subscribed
[sqlalchemy] Mapper on several columns only (multiple column_property)
What is the best way to map several columns from one table to another
table? Let's say I have two classes: LargeComplicatedClass and
AnotherClass.
LargeComplicatedClass which is has a lot of eager loads and columns
with a lot of data, which is expensive to query.
I also have a AnotherClass to which I want to map
LargeComplicatedClass.title columns, so it would be many-to-many
relationship. If I would setup a usual mapper it would look something
like:
mapper(AnotherClass, another_class_table, properties={
'related_large_objects': relation(LargeComplicatedClass,
secondary=...)
})
But that would load the whole LargeComplicatedClass (I can't turn off
eager load for that class, because usually I have to use all its
fields, except for rare cases like this one).
What I need is something like this:
mapper(AnotherClass, another_class_table, properties={
'related_large_titles': customwhateverrelation
([large_complicated_table.c.title], secondary=...)
})
It is similar to column_property, except that this would be a many-to-
many relation.
Does that make sense? Or maybe I've missed some point?
--
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: union with two different orders
On 2 Rugs, 20:41, Michael Bayer mike...@zzzcomputing.com wrote: naktinis wrote: I tried calling .subquery() method on each union subquery like this: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit (1).subquery() q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit (1).subquery() q = q1.union(q2).order_by(Thing.id) I know you're not doing that since the alias object returned by subquery() does not have a union() method. Sorry, you are right. I used union(q1, q2). Just copied the old source from previous post. you'll have to wrap each subquery in a SELECT like this: q1 = sess.query(C).order_by(C.data).limit(2).subquery().select() q2 = sess.query(C).order_by(C.data.desc()).limit(2).subquery().select() print sess.query(C).select_from(union(q1, q2)).order_by(C.data) Thanks, it works. --~--~-~--~~~---~--~~ 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] Session mapper and Class.query() method
I've seen that Session.mapper is deprecated. While I agree that auto- adding objects to a session may be a bad idea, I don't see why is it so bad to add the query() method directly to the mapped class. Is there a reason, why I shouldn't do 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] Rollback behaviour in 0.4.8
I have a session created this way: Session = scoped_session(sessionmaker(autoflush=True, transactional=True)) Then I have this piece of code: print User.query().count() u = User(name='Jim') Session.flush([u]) print User.query().count() Session.rollback() Session.clear() print User.query().count() Which outputs: 0 1 1 What am I doing wrong? Why is the new user still in session? --~--~-~--~~~---~--~~ 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: Rollback behaviour in 0.4.8
Oh.. Yes, sorry. That's my mistake, because testing engine created MyISAM tables (they were default) and it broke the tests. Thanks! On May 7, 12:39 am, Michael Bayer mike...@zzzcomputing.com wrote: are you using MyISAM tables ? naktinis wrote: I have a session created this way: Session = scoped_session(sessionmaker(autoflush=True, transactional=True)) Then I have this piece of code: print User.query().count() u = User(name='Jim') Session.flush([u]) print User.query().count() Session.rollback() Session.clear() print User.query().count() Which outputs: 0 1 1 What am I doing wrong? Why is the new user still in session? --~--~-~--~~~---~--~~ 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: problem with ExtensionMapper
Ok, so the problem might be because the first test fails while doing
Session.clear():
Traceback (most recent call last):
File .../tests/__init__.py, line 35, in tearDown
Session.clear()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.6-py2.5.egg/
sqlalchemy/orm/scoping.py, line 98, in do
return getattr(self.registry(), name)(*args, **kwargs)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.6-py2.5.egg/
sqlalchemy/orm/session.py, line 668, in clear
self._unattach(instance)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.6-py2.5.egg/
sqlalchemy/orm/session.py, line 1141, in _unattach
if instance._sa_session_id == self.hash_key:
AttributeError: 'User' object has no attribute '_sa_session_id'
The background for this situation is in the first post of this thread:
create_instance simply returns User() object. However, if instead of
that I return EXT_CONTINUE - everything works fine. So maybe there's
something missing in my create_instance definition?
On 4 Rugp, 18:11, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 4, 2008, at 4:29 AM, naktinis wrote:
User.__init__() only contains attribute assignments like:
def __init__(self, **kw):
self.name = kw.get('name', u'')
...
self.email = kw.get('email', u'')
Nothing more. What details would be more helpful?
something else is going on. you'd have to provide us with a complete
test case which reproduces the issue.
On 2 Rugp, 20:35, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 2, 7:46 am, naktinis [EMAIL PROTECTED] wrote:
Hi,
I have created an ExtensionMapper:
class UserMapperExtension(MapperExtension):
defcreate_instance(self, mapper, selectcontext, row, class_):
return User()
Session.mapper(User, user_table, extension=UserMapperExtension())
At first everything seemed to work fine, but when I runned my unit
tests (nosetests) I get errors (if I turn off the extension they are
gone). After every:
super(UserTestCase, self).setUp()
Session.flush()
I get:
ConcurrentModificationError: Updated rowcount 0 does not match
number
of objects updated 1
Or sometimes:
IntegrityError: (IntegrityError) (1452, 'Cannot add or update a
child
row: a foreign key constraint fails ... CONSTRAINT
'some_constraint'
FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))') u'UPDATE other
SET
user_id=%s WHERE other.id = %s'
which were not present before adding the ExtensionMapper.
What could be the problem?
what kind of behavior fires off in User.__init__() ? other than
that
you'd have to provide a more concrete example.
--~--~-~--~~~---~--~~
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] error while accessing row in create_instance
In MapperExtension's method create_instance(self, mapper,
selectcontext, row, class_) I try to call row.has_key('some_key') but
I get:
AttributeError: 'str' object has no attribute 'proxy_set'
This is in version 0.4.6. It worked fine in 0.4.2p3.
What could be the problem?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
