[sqlalchemy] Using identity_key in after_commit session event (SQLAlchemy 0.6.7)
Hi Michael et al, basic question: Can I call identity_key in after_commit? If not, are there any alternatives? Explanation: For our GUI application I tried to extend SQLAlchemy to signal database updates. The following constraints apply: * Updates are done in background threads and should be visible in the GUI main thread. * Being a MVC architecture, the GUI shows only data as actually found in the database. All business objects implement the Observer pattern, so in-GUI changes (not yet committed) are shown in the GUI panels. This can not be used with background threads as the event is fired on attribute write - before the change is committed. The scheme I implemented is that a SessionExtension is keeping track of dirty, new and deleted instances in after_flush and generates events in after_commit. after_rollback clears the information of changed instances. To move the list of affected objects to another thread, I use the identity_key function to retrieve the primary keys. On the target thread, I use that information to reload ORM instances which are affected. However, while doing an unrelated change today, this broke down with the following backtrace. I can not make sense out of this: after_commit should only be called after a commit I would think (not after a rollback as the error message seems to indicate). I'd expect that identity_key should be available in after_commit as it is called *after* a commit and therefore the session should be in a sane state?! This error breaks the GUI, every later attribute access fails with DetachedInstanceError. Note: The mechanism is used also for same-thread updates and in this case the after_commit actually runs in the GUI thread for the ORM session used by the GUI thread. Any hints much appreciated. Thanks! Torsten File /home/torsten/workspace/loco2-git/loco2/storage/extensions.py, line 96, in after_commit notifier.signal_updated(instance) File /home/torsten/workspace/loco2-git/loco2/storage/notification.py, line 72, in signal_updated self._signal(_UPDATE, type(instance), identity_key(instance=instance)) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/util.py, line 185, in identity_key return mapper.identity_key_from_instance(instance) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/mapper.py, line 1294, in identity_key_from_instance self.primary_key_from_instance(instance)) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/mapper.py, line 1306, in primary_key_from_instance return self._primary_key_from_state(state) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/mapper.py, line 1311, in _primary_key_from_state column in self.primary_key] File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/mapper.py, line 1315, in _get_state_attr_by_column return self._columntoproperty[column]._getattr(state, dict_, column, passive=passive) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/properties.py, line 121, in _getattr return state.get_impl(self.key).get(state, dict_, passive=passive) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/attributes.py, line 388, in get value = callable_(passive=passive) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/state.py, line 287, in __call__ self.manager.deferred_scalar_loader(self, toload) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/mapper.py, line 2499, in _load_scalar_attributes only_load_props=attribute_names) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/query.py, line 1968, in _get return q.one() File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/query.py, line 1656, in one ret = list(self) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/query.py, line 1699, in __iter__ return self._execute_and_instances(context) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/query.py, line 1704, in _execute_and_instances mapper=self._mapper_zero_or_none()) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/session.py, line 723, in execute return self._connection_for_bind(engine, close_with_result=True).execute( File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/session.py, line 667, in _connection_for_bind return self.transaction._connection_for_bind(engine) File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.7dev-py2.6.egg/sqlalchemy/orm/session.py, line 319, in
Re: [sqlalchemy] Using identity_key in after_commit session event (SQLAlchemy 0.6.7)
On Thu, 2011-05-26 at 08:34 +0200, Torsten Landschoff wrote: basic question: Can I call identity_key in after_commit? If not, are there any alternatives? A bit of extra information: My usage of identity_key stems from this thread on this list: http://groups.google.com/group/sqlalchemy/browse_frm/thread/f62edf05c2696723/a3fc24f29bad7123?lnk=gstq=identity_key#a3fc24f29bad7123 (Message-Id is 1276873541.4601.19.camel@sharokan.intern if anybody cares) Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Deferred properties are sometimes compiled unnecessarily?
Hi all,
I've run into an issue when mapping a deferred column_property that uses a
custom SQL construct. I get a KeyError: 'default' because there is no
compile function for the default dialect, but it seems to me that the
default dialect should never be used.
Here's an extended stack trace of what's going on, the important bit is
lines 78-90:
http://pastebin.com/0kVf9q9q
And here's the a simplified test case:
class utcnow(expression.FunctionElement):
type = DateTime()
@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
return TIMEZONE('utc', CURRENT_TIMESTAMP)
mapper(AnyClass, any_table, properties={
'timestamp': column_property(select([utcnow()]), deferred=True)
})
Session.query(AnyClass).all()
For now I'm just adding a dummy @compiles(utcnow) function for the default
dialect, but I think this could be avoided entirely by changing the
NoSuchColumnError message to not cast key (ie, the select) to a string.
Perhaps the repr could be used instead?
If I'm way off base feel free to let me know, heh, but hopefully this helps!
:)
Cheers,
Nathan
--
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.
Re: [sqlalchemy] Deferred properties are sometimes compiled unnecessarily?
On May 25, 2011, at 10:52 PM, Nathan Wright wrote:
Hi all,
I've run into an issue when mapping a deferred column_property that uses a
custom SQL construct. I get a KeyError: 'default' because there is no compile
function for the default dialect, but it seems to me that the default dialect
should never be used.
Here's an extended stack trace of what's going on, the important bit is lines
78-90:
http://pastebin.com/0kVf9q9q
And here's the a simplified test case:
class utcnow(expression.FunctionElement):
type = DateTime()
@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
return TIMEZONE('utc', CURRENT_TIMESTAMP)
mapper(AnyClass, any_table, properties={
'timestamp': column_property(select([utcnow()]), deferred=True)
})
Session.query(AnyClass).all()
For now I'm just adding a dummy @compiles(utcnow) function for the default
dialect, but I think this could be avoided entirely by changing the
NoSuchColumnError message to not cast key (ie, the select) to a string.
Perhaps the repr could be used instead?
the __repr__() produces IMHO a less descriptive string in the case of a no
such column type of situation:
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column
'sqlalchemy.sql.expression._Label object at 0x1124490'
Being able to see the expression is most meaningful.
http://www.sqlalchemy.org/trac/ticket/2178 provides a patch, which is an
improvement on the situation.
--
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.
Re: [sqlalchemy] Using identity_key in after_commit session event (SQLAlchemy 0.6.7)
On May 26, 2011, at 2:34 AM, Torsten Landschoff wrote: Hi Michael et al, basic question: Can I call identity_key in after_commit? If not, are there any alternatives? absolutely. identity_key is strictly informational with regards to the state present on the target object. To move the list of affected objects to another thread, I use the identity_key function to retrieve the primary keys. On the target thread, I use that information to reload ORM instances which are affected. I would take care not to access any attributes on a session-attached object in a different thread than the originating thread of that Session (which identity_key() does). This would constitute sharing of the Session's functionality across threads which is not threadsafe. However, while doing an unrelated change today, this broke down with the following backtrace. I can not make sense out of this: after_commit should only be called after a commit I would think (not after a rollback as the error message seems to indicate). The stacktrace doesn't reveal the source of the issue as the source of the Session.commit() call is not shown. after_commit() is only called as a direct result of Session.commit(), or in the case that you're using a Session in autocommit mode (to which I would advise, don't) it would be called in the scope of flush(). But only if the flush() succeeded - otherwise the exception is propagated before commit() is reached. -- 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] Why we don't have a contains_eager_all?
Hi,
Just a easy question, why we don't have a contains_eager_all like we do with
joinedload?
By the way, I don't exactly understand why we shouldn't use the _all version
always.
Example (from
http://www.sqlalchemy.org/docs/orm/loading.html?highlight=contains_eager#contains-eager
):
query(User).options(contains_eager('orders', 'items'))
With this we are only loading the items of the orders objects. But to access
it, I must pass through the orders relation anyway, but without another:
contains_eager('orders')
It would make a new select, so I didn't get whats the use case of eager
loading the deepest children without loading all the way to it too.
(or maybe I'm wrong and it's behavior is like what I said and I'm missing
something in my tests)
Thanks in advance,
--
Bonus question: Wouldnt be nice if the joinedload (and all the variants)
could be used in the Query object? Ex:
query(User).joinedload('orders').all() is much more readable than:
query(User).options(joinedload('orders').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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Injecting custom comment into SQL generated by query(Clazz).all()
Hi, I got this habit of looking at which queries currently are live on mysql db (via innotop). And I like to add query name to each query.. example: SELECT /* custom comment here...users living in Poland */ id FROM users WHERE country = 'pl'; This way I can quickly identify queries that run/appear too slow/often.. How can I inject custom query text into query(User).filter(User.country=='pl').all() to achieve the final query contains custom text/comment as in the example below...? Thanks for suggestions, Tomasz -- _i__'simplicity_is_the_key'__tomasz_nazar _ii'i_am_concern_oriented'JKM-UPR _iii__'patsystem.sf.net'___linux_user _'aspectized.com'___prevayler -- 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.
Re: [sqlalchemy] Why we don't have a contains_eager_all?
On May 26, 2011, at 11:02 AM, Israel Ben Guilherme Fonseca wrote:
Hi,
Just a easy question, why we don't have a contains_eager_all like we do with
joinedload?
contains_eager() should always act in an all context since there's little use
otherwise. This was fixed in 0.7 and is ticket #2032:
http://www.sqlalchemy.org/trac/ticket/2032
07Migration is updated.
By the way, I don't exactly understand why we shouldn't use the _all version
always.
You might want to load a list of A, each has a collection of B. But you don't
want the B's by default. But, if you do in fact load a particular collection
of B, you'd like them to eagerly load their C.
We're falling victim a bit to favoring a rare use case over a common one here,
but that's how things have worked out and its not really worth changing around
at this point.
Bonus question: Wouldnt be nice if the joinedload (and all the variants)
could be used in the Query object? Ex:
query(User).joinedload('orders').all() is much more readable than:
query(User).options(joinedload('orders').all()
Again this is how things have worked out over the years, but also options()
does have a use in that you can also make your own MapperOption objects,
without any need to subclass Query. So there is some consistency in that
options() allows external functions to enter in and modify the state of Query,
without Query having any awareness of them.
--
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.
Re: [sqlalchemy] Why we don't have a contains_eager_all?
Hmm, nice, I'm still with the 0.6.7, so that's why I missed it.
Thanks again for the superfast-effective answer.
2011/5/26 Michael Bayer mike...@zzzcomputing.com
On May 26, 2011, at 11:02 AM, Israel Ben Guilherme Fonseca wrote:
Hi,
Just a easy question, why we don't have a contains_eager_all like we do
with joinedload?
contains_eager() should always act in an all context since there's little
use otherwise. This was fixed in 0.7 and is ticket #2032:
http://www.sqlalchemy.org/trac/ticket/2032
07Migration is updated.
By the way, I don't exactly understand why we shouldn't use the _all
version always.
You might want to load a list of A, each has a collection of B. But you
don't want the B's by default. But, if you do in fact load a particular
collection of B, you'd like them to eagerly load their C.
We're falling victim a bit to favoring a rare use case over a common one
here, but that's how things have worked out and its not really worth
changing around at this point.
Bonus question: Wouldnt be nice if the joinedload (and all the variants)
could be used in the Query object? Ex:
query(User).joinedload('orders').all() is much more readable than:
query(User).options(joinedload('orders').all()
Again this is how things have worked out over the years, but also options()
does have a use in that you can also make your own MapperOption objects,
without any need to subclass Query. So there is some consistency in that
options() allows external functions to enter in and modify the state of
Query, without Query having any awareness of them.
--
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.
--
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] help wanted: porting sqlalchemy-migrate for SQLAlchemy 0.7 and sqlalchemy-migrate maintenance
Hello, I work on making sqlalchemy-migrate [1] work with SQLAlchemy 0.7. I fixed all broken unit tests except for one related to adding a new column with a foreign key to an existing table. We have a continues integration system (Jenkins CI) at [2] that provides the output of the failing test. The problem is with some changed behaviour of the SchemaVisitor API (or the Column objects). Until SQLAlchemy 0.6 it was possible to get the constraints object for the ForeignKey arguments of a column. The test at [3] creates a new Column instance and adds it to the table. Afterwards our ANSIColumnGenerator [4] is triggered to generate the necessary SQL statements. Until SQLAlchemy 0.6 the code for generating the foreign key constraints could be generated properly but now the constraint is None instead of a ForeignKeyConstraint. I tried to just ignore fk.constraint if it is None, which expectedly did not generate a statement. I also tried to construct a ForeignKeyConstraint instance and pass that to the AddConstraint constructor. This approach added a second ForeignKey instance to the Column which is not desired too. Can you please give me hints in the right direction or provide help to fix this issue? We would also like to invite interested developers to join the sqlalchemy-migrate project because it has no maintainers with enough time to keep it in a good shape. I think it would be great if the test coverage and code quality would be improved but neither me nor the other current maintainers have enough time to do these necessary prerequisites. We have a quite long list of outstanding issues [5] that need some triaging and fixes and should give a good start for interested developers. [1] http://code.google.com/p/sqlalchemy-migrate/ [2] http://jenkins.gnuviech-server.de/job/sqlalchemy-migrate-all/ [3] http://code.google.com/p/sqlalchemy-migrate/source/browse/migrate/tests/changeset/test_changeset.py#160 [4] http://code.google.com/p/sqlalchemy-migrate/source/browse/migrate/changeset/ansisql.py#87 [5] http://code.google.com/p/sqlalchemy-migrate/issues/list Regards Jan Dittberner -- 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.
Re: [sqlalchemy] Using identity_key in after_commit session event (SQLAlchemy 0.6.7)
On May 26, 2011, at 11:58 AM, Torsten Landschoff wrote: How much more context would be helpful? Basically, this is the top of the backtrace. Above of that call is only the event handler hierarchy. File /home/torsten/workspace/loco2-git/loco2/ui/wizard/importwizard.py, line 69, in do_component_import session.commit() above is likely the offending line of code. commit() should not be called if an exception has been raised within flush() - the Session is in a state whereby it is waiting for rollback() to be called. I am actually using autocommit=True and autoflush=False. The reason is that autoflush=True caused a bunch of errors as I load hierarchical data in bottom up fashion and SQLAlchemy tried to flush objects to the database before a parent was available. What's so bad about autocommit=True? I don't really remember why I ended up using it. It would probably suffice to replace most flush() calls with commit() and be done with it. if you're using autocommit=True and you are not calling session.begin(), then session.commit() cannot be called in any case -there is no transaction present - it will raise an error directly: from sqlalchemy.orm import Session s = Session(autocommit=True) s.commit() output: sqlalchemy.exc.InvalidRequestError: No transaction is begun. autocommit=True is SQLAlchemy's original transaction behavior in the Session. It doesn't allow for the Session to have any context as to when it's safe to expire objects, and I can't currently think of any cases where it is truly needed for anything - it seems to be used either because it is misunderstood, as seems possibly the case here as it has nothing to do with autoflush, or because some users really don't want to have to figure out when their application should be saying commit(), which IMHO is a serious architectural problem. It is also used by legacy code and for some Zope extensions where they'd like to call Session.begin() explicitly in order to control scope better (and even there I'm not sure if they truly need it). BTW: The error is not reproducible anymore. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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. -- 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] Subselect that references the outer select
Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- 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.
Re: [sqlalchemy] help wanted: porting sqlalchemy-migrate for SQLAlchemy 0.7 and sqlalchemy-migrate maintenance
On May 26, 2011, at 11:41 AM, Jan Dittberner wrote:
Hello,
I work on making sqlalchemy-migrate [1] work with SQLAlchemy 0.7. I
fixed all broken unit tests except for one related to adding a new
column with a foreign key to an existing table. We have a continues
integration system (Jenkins CI) at [2] that provides the output of the
failing test. The problem is with some changed behaviour of the
SchemaVisitor API (or the Column objects). Until SQLAlchemy 0.6 it was
possible to get the constraints object for the ForeignKey arguments of
a column. The test at [3] creates a new Column instance and adds it to
the table.
the linked samples don't make it clear what specific behavior in SQLAlchemy has
changed. From your description, it appears as though you are saying
ForeignKeyConstraint is not generated for an append_column() operation. Below
is a test which illustrates this usage, it is the same in 0.6 and 0.7. If you
can alter this test case to illustrate the specific functionality that has
changed on the SQLA side from 0.6 to 0.7, it may very well be a bug in 0.7 or
some usage in Migrate that was never supported, but at the very least it would
isolate the issue.
from sqlalchemy import Table, Column, ForeignKey, MetaData, Integer,
ForeignKeyConstraint
m1 = MetaData()
t1 = Table('t1', m1,
Column('x', Integer, primary_key=True)
)
t2 = Table('t2', m1,
Column('x', Integer, primary_key=True),
Column('added_inline', Integer, ForeignKey('t1.x'))
)
assert set([
fkc.columns[0].name
for fkc in t2.constraints
if isinstance(fkc, ForeignKeyConstraint)]) == set(['added_inline'])
t2.append_column(
Column('added_externally', Integer, ForeignKey('t1.x'))
)
assert set([
fkc.columns[0].name
for fkc in t2.constraints
if isinstance(fkc, ForeignKeyConstraint)]) == set(['added_externally',
'added_inline'])
--
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.
Re: [sqlalchemy] or_(MyClass.relationship1.contains(item), MyClass.relationship2.contains(item)) returns empty list
This seems to be an issue of poor documentation on our part. Here are new documentation elements, linked from the ORM tutorial which was previously the only place contains() was mentioned, fully describing the behavior of contains(), and how any() and outerjoin() are more appropriate if OR conjunctions are used: http://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains On May 24, 2011, at 7:51 PM, Hector Blanco wrote: Hello everybody... Let's say I have a class like this: class Foo(declarativeBase): bars1 = relationship(Bar.Bar, secondary=foos_to_bars1, collection_class=set()) bars2 = relationship(Bar.Bar, secondary=foos_to_bars2, collection_class=list()) At a certain point, I want to get instances of Foos that have a bar (instance of Bar.Bar) in any of the relationships. If I try to do: def inAnyBar(bar) query(Foo).filter(or_(Foo.bars1.contains(bar), Foo.bars2.contains(bar)).all() I get an empty result. It looks (to me) like I'm doing something like: query(Foo).join(Foo.bars1).filter(Foo.bars1.contains(bar)).join(Foo.bars2).filter(Foo.bars1.contains(bar)) Since Foo.bars1 doesn't contain bar, the second filter gives empty results. I've been able to find a workaround with subqueries (each join+filter in a subquery, then or_ all the subqueries) but I'd like to know if there's a better way to do it... I'm still using SqlAlchemy 0.6.6, though. Thank you! -- 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. -- 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: Subselect that references the outer select
I did a bit more of digging in the docs and found the 'select_from' method. I thought that it would force the FROM statement to use ONLY what I pass as argument. But it didn't. session.query(func.max(alias.data)).select_from(alias).filter(alias.id_curso == Matricula.id_curso).subquery() It stills give-me two matricula in the from clause. The == Matricula.id_curso, is still enforcing the another matricula in the query. 2011/5/26 Israel Ben Guilherme Fonseca israel@gmail.com Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- 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.
Re: [sqlalchemy] help wanted: porting sqlalchemy-migrate for SQLAlchemy 0.7 and sqlalchemy-migrate maintenance
2011/5/26 Michael Bayer mike...@zzzcomputing.com:
On May 26, 2011, at 11:41 AM, Jan Dittberner wrote:
Hello,
I work on making sqlalchemy-migrate [1] work with SQLAlchemy 0.7. I
fixed all broken unit tests except for one related to adding a new
column with a foreign key to an existing table. We have a continues
integration system (Jenkins CI) at [2] that provides the output of the
failing test. The problem is with some changed behaviour of the
SchemaVisitor API (or the Column objects). Until SQLAlchemy 0.6 it was
possible to get the constraints object for the ForeignKey arguments of
a column. The test at [3] creates a new Column instance and adds it to
the table.
the linked samples don't make it clear what specific behavior in SQLAlchemy
has changed. From your description, it appears as though you are saying
ForeignKeyConstraint is not generated for an append_column() operation.
Below is a test which illustrates this usage, it is the same in 0.6 and 0.7.
If you can alter this test case to illustrate the specific functionality that
has changed on the SQLA side from 0.6 to 0.7, it may very well be a bug in
0.7 or some usage in Migrate that was never supported, but at the very least
it would isolate the issue.
from sqlalchemy import Table, Column, ForeignKey, MetaData, Integer,
ForeignKeyConstraint
m1 = MetaData()
t1 = Table('t1', m1,
Column('x', Integer, primary_key=True)
)
t2 = Table('t2', m1,
Column('x', Integer, primary_key=True),
Column('added_inline', Integer, ForeignKey('t1.x'))
)
assert set([
fkc.columns[0].name
for fkc in t2.constraints
if isinstance(fkc, ForeignKeyConstraint)]) == set(['added_inline'])
t2.append_column(
Column('added_externally', Integer, ForeignKey('t1.x'))
)
assert set([
fkc.columns[0].name
for fkc in t2.constraints
if isinstance(fkc, ForeignKeyConstraint)]) == set(['added_externally',
'added_inline'])
the table.append_column() call was the missing piece of information.
In SQLA 0.6 it was not needed, in SQLA 0.7 it seems to be required.
The table.create(col) call triggered the
ANSIColumnGenerator.visit_column(self, column) method and the column
object passed to the method had a ForeignKey instance with ForeignKey
objects' constraint properties set to a ForeignKeyConstraint instance
instead of None (as in SQLA 0.7).
I was able to fix the unit test by adding
self.table.append_column(col) for SQLA 0.7. I hope that the change in
http://code.google.com/p/sqlalchemy-migrate/source/detail?r=2426a14c98f556fc63be2d677378255bfd8e4c2f
is correct and does not only fix the test.
Regards
Jan Dittberner
--
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.
Re: [sqlalchemy] help wanted: porting sqlalchemy-migrate for SQLAlchemy 0.7 and sqlalchemy-migrate maintenance
On May 26, 2011, at 4:04 PM, Jan Dittberner wrote: the table.append_column() call was the missing piece of information. In SQLA 0.6 it was not needed, in SQLA 0.7 it seems to be required. the Column is guaranteed not at all associated with a Table if you didn't call table.append_column(col). Its a free standing object that's not in a meaningful state when used in a SQL schema context. The table.create(col) call triggered the ANSIColumnGenerator.visit_column(self, column) method and the column object passed to the method had a ForeignKey instance with ForeignKey objects' constraint properties set to a ForeignKeyConstraint instance instead of None (as in SQLA 0.7). e theres nothing I can see in the source that looks anything like that . ForeignKey is virtually identical in 0.6 and 0.7. The .constraint attribute is created when the ForeignKey is associated with a parent Table which occurs via the Column. I can't yet figure out a test that would behave differently on 0.6 vs. 0.7. I was able to fix the unit test by adding self.table.append_column(col) for SQLA 0.7. I hope that the change in http://code.google.com/p/sqlalchemy-migrate/source/detail?r=2426a14c98f556fc63be2d677378255bfd8e4c2f is correct and does not only fix the test. well if this is only the test that relies upon the table + col issue then you are probably OK. -- 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.
Re: [sqlalchemy] Re: Subselect that references the outer select
I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example: outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. (That example was really a useless scenario, I'll try to make a better one later) 2011/5/26 Michael Bayer mike...@zzzcomputing.com take a look at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries and note that when a subquery is used as a FROM clause, it acts like a table. Use the .c. attribute. On May 26, 2011, at 3:34 PM, Israel Ben Guilherme Fonseca wrote: I did a bit more of digging in the docs and found the 'select_from' method. I thought that it would force the FROM statement to use ONLY what I pass as argument. But it didn't. session.query(func.max(alias.data)).select_from(alias).filter(alias.id_curso == Matricula.id_curso).subquery() It stills give-me two matricula in the from clause. The == Matricula.id_curso, is still enforcing the another matricula in the query. 2011/5/26 Israel Ben Guilherme Fonseca israel@gmail.com Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- 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. -- 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. -- 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.
Re: [sqlalchemy] Re: Subselect that references the outer select
On May 26, 2011, at 5:03 PM, Israel Ben Guilherme Fonseca wrote: I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example: outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. oh then you're looking for correlation: innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).correlate(outeruser) outerselect = session.query(outeruser).filter(outeruser.id == innerselect.as_scalar()) for some reason the Query is disabling auto-correlation upon subquery(), .statement or as_scalar(). Sort of wish I had noticed that before releasing 0.7. Will add a ticket to possibly change that default for 0.8, see you in a year. -- 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.
Re: [sqlalchemy] Re: Subselect that references the outer select
Sweet, it's working. :) Now let's wait for the 0.8. 2011/5/26 Michael Bayer mike...@zzzcomputing.com On May 26, 2011, at 5:03 PM, Israel Ben Guilherme Fonseca wrote: I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example: outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. oh then you're looking for correlation: innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).correlate(outeruser) outerselect = session.query(outeruser).filter(outeruser.id == innerselect.as_scalar()) for some reason the Query is disabling auto-correlation upon subquery(), .statement or as_scalar(). Sort of wish I had noticed that before releasing 0.7. Will add a ticket to possibly change that default for 0.8, see you in a year. -- 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. -- 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.
Re: [sqlalchemy] or_(MyClass.relationship1.contains(item), MyClass.relationship2.contains(item)) returns empty list
Thank you! As I'm sure some of the people in the list already know, I also asked this same question in StackOverflow, and I got a couple of interesting answers. Just in case: http://stackoverflow.com/questions/6118783/sqlalchemy-check-if-one-object-is-in-any-relationship-or-object-relationship1 2011/5/26 Michael Bayer mike...@zzzcomputing.com: This seems to be an issue of poor documentation on our part. Here are new documentation elements, linked from the ORM tutorial which was previously the only place contains() was mentioned, fully describing the behavior of contains(), and how any() and outerjoin() are more appropriate if OR conjunctions are used: http://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains On May 24, 2011, at 7:51 PM, Hector Blanco wrote: Hello everybody... Let's say I have a class like this: class Foo(declarativeBase): bars1 = relationship(Bar.Bar, secondary=foos_to_bars1, collection_class=set()) bars2 = relationship(Bar.Bar, secondary=foos_to_bars2, collection_class=list()) At a certain point, I want to get instances of Foos that have a bar (instance of Bar.Bar) in any of the relationships. If I try to do: def inAnyBar(bar) query(Foo).filter(or_(Foo.bars1.contains(bar), Foo.bars2.contains(bar)).all() I get an empty result. It looks (to me) like I'm doing something like: query(Foo).join(Foo.bars1).filter(Foo.bars1.contains(bar)).join(Foo.bars2).filter(Foo.bars1.contains(bar)) Since Foo.bars1 doesn't contain bar, the second filter gives empty results. I've been able to find a workaround with subqueries (each join+filter in a subquery, then or_ all the subqueries) but I'd like to know if there's a better way to do it... I'm still using SqlAlchemy 0.6.6, though. Thank you! -- 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. -- 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. -- 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.
