Re: [sqlalchemy] Commands out of sync; you can't run this command now
Thanks for those debug tips. It doesn't seem to be a celery related problem. Sessions are initiated after the fork and SQLAlchemy is not used as the messaging backend (We use Redis). Playing with the pool_recycle option in the Pyramid configuration I clearly limited the number of exceptions. But I still have some transaction related errors. I'll continue my investigations and come back when I'll found the cause(s). Le 17/04/2019 à 16:26, Jonathan Vanasco a écrit : On Wednesday, April 17, 2019 at 4:36:30 AM UTC-4, tonthon wrote: May the scoped_session factory, used in both services (web and celery), may cause such a problem ? Apologies for my earlier reply, that would only affect exceptions in Celery. I do suggest checking your Celery code to make sure you are not connecting to the database before the fork - Celery does run as multi-processing by default. Celery can potentially use SqlAlchemy in two places: the messaging backend and the worker task runners. If you are using SqlAlchemy for the messaging backend, that is potentially the cause. If you are using another technology (Redis/Rabbit/etc) for messaging, Pyramid should never be invoking the scoped_session for Celery. In either situation, you can drop some debug lines around when the DB connection is made to ensure you're connecting at the right time. I've debugged issues in this area with PostgreSQL by enabling query logging and including the transaction id, then looking backwards at each error to find the query that broke the connection's state. If your issue happens frequently enough, an hour or two of logging might be enough to show you what happened. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Commands out of sync; you can't run this command now
Yes, celery is running in a separate service. The objects are never passed from the web app to the celery service, only ids are passed, Objects are then retrieved on the celery side. May the scoped_session factory, used in both services (web and celery), may cause such a problem ? Le 16/04/2019 à 15:41, Mike Bayer a écrit : On Tue, Apr 16, 2019 at 4:49 AM tonthon wrote: Celery tasks are using the same scoped_session factory, could it cause the errors we're facing here ? what's important is if these tasks run in the same process or not ? ( i thought celery runs as a separate service?) If you are passing ORM objects which are associated with a thread local Session in process to a Celery task queue which is running distinct threads in the same process, then yes that will exactly lead to this issue and needs to be fixed. You can't share a persistent (meaning, associated with a Session) ORM loaded object with another thread, because that means you are sharing the whole Session across threads. The object needs to be detached first (e.g. session.expunge(obj), or just session.close()) , or the celery thread needs to get just the primary keys and load the objects on its own.Another option is to merge() the objects into the Session to be used by the celery worker but this also has to be done carefully so that neither Session emits SQL on the wrong thread. Le 15/04/2019 à 15:39, Mike Bayer a écrit : On Mon, Apr 15, 2019 at 5:41 AM tonthon wrote: I tried to set a lower value for the pool_recycle value and it seems to work. There is a celery service running in the background, maybe it could affect the session management. this will reduce the problem but the architectural issue that is causing it is likely still present. I'd want to look at how the interaction with Celery is occurring within the same process. Le 12/04/2019 à 15:58, Mike Bayer a écrit : it's likely that a database connection is being returned to the pool in an invalid state. Switching to NullPool temporarily might reveal that this solves all the issues ; at the very least, I would try setting pool_recycle to a low number, like 5 minutes, however this won't prevent the problem, just make it less likely. What you do need to find are stack traces that precede the error, to give a clue why a connection would be placed in the pool in a bad state. The other possibility is that your application is actually sharing a single connection across threads in some way which would be a different problem though with a lot of similar behaviors. Are there any global in-memory caches being used of objects where an ORM object might be shared out among threads, or a background worker thread of some kind, anything like that ?Does the application use a "scoped_session" pattern and maybe the session being passed around in some cases isn't actually scoped? On Fri, Apr 12, 2019 at 4:03 AM tonthon wrote: Le 10/04/2019 à 17:12, Mike Bayer a écrit : On Wed, Apr 10, 2019 at 9:23 AM tonthon wrote: Hi, We're using sqlalchemy in a Pyramid Web Application. We use the ZopeTransactionExtension and our session factory is initialized this way : DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) We use the pyramid_tm that wraps each web requests in a transaction. Our services are served through apache and mod_wsgi (1 process, 10 threads). We recently faced the following error : 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) Traceback (most recent call last): File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 712, in _finalize_fairy fairy._reset(pool) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 881, in _reset self._reset_agent.rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback self._do_rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1670, in _do_rollback self.connection._rollback_impl() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 704, in _rollback_impl self.engine.dialect.do_rollback(
Re: [sqlalchemy] Commands out of sync; you can't run this command now
Celery tasks are using the same scoped_session factory, could it cause the errors we're facing here ? Le 15/04/2019 à 15:39, Mike Bayer a écrit : On Mon, Apr 15, 2019 at 5:41 AM tonthon wrote: I tried to set a lower value for the pool_recycle value and it seems to work. There is a celery service running in the background, maybe it could affect the session management. this will reduce the problem but the architectural issue that is causing it is likely still present. I'd want to look at how the interaction with Celery is occurring within the same process. Le 12/04/2019 à 15:58, Mike Bayer a écrit : it's likely that a database connection is being returned to the pool in an invalid state. Switching to NullPool temporarily might reveal that this solves all the issues ; at the very least, I would try setting pool_recycle to a low number, like 5 minutes, however this won't prevent the problem, just make it less likely. What you do need to find are stack traces that precede the error, to give a clue why a connection would be placed in the pool in a bad state. The other possibility is that your application is actually sharing a single connection across threads in some way which would be a different problem though with a lot of similar behaviors. Are there any global in-memory caches being used of objects where an ORM object might be shared out among threads, or a background worker thread of some kind, anything like that ?Does the application use a "scoped_session" pattern and maybe the session being passed around in some cases isn't actually scoped? On Fri, Apr 12, 2019 at 4:03 AM tonthon wrote: Le 10/04/2019 à 17:12, Mike Bayer a écrit : On Wed, Apr 10, 2019 at 9:23 AM tonthon wrote: Hi, We're using sqlalchemy in a Pyramid Web Application. We use the ZopeTransactionExtension and our session factory is initialized this way : DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) We use the pyramid_tm that wraps each web requests in a transaction. Our services are served through apache and mod_wsgi (1 process, 10 threads). We recently faced the following error : 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) Traceback (most recent call last): File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 712, in _finalize_fairy fairy._reset(pool) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 881, in _reset self._reset_agent.rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback self._do_rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1670, in _do_rollback self.connection._rollback_impl() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 704, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1804, in do_rollback dbapi_connection.rollback() ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out of sync; you can't run this command now") (Background on this error at: http://sqlalche.me/e/f405) I can't reproduce this problem that seems to happen randomly. Could anyone help me giving some points to investigate ? Is there some tuning to do (I already set the pool_recycle var) ? the most important thing is what driver are you using and what version? are you doing anything with SAVEPOINTs or greenlets / eventlet ? is the web application experiencing timeouts of some kind, such that transactions are being dropped ? is the above error only showing up in logs or is it occurring synchronously with a web request and causing the request to fail ? We use mysqlclient 1.4.1. We don't use any greenlet or eventlet nor savepoints. I havn't seen any timeout happening so far. The given error is happening during the request's lifecycle resulting in a HTTP 500 error code After the error, the user refreshes and it works. I don't know if it could be related, but we also see things like
Re: [sqlalchemy] Commands out of sync; you can't run this command now
I tried to set a lower value for the pool_recycle value and it seems to work. There is a celery service running in the background, maybe it could affect the session management. Le 12/04/2019 à 15:58, Mike Bayer a écrit : it's likely that a database connection is being returned to the pool in an invalid state. Switching to NullPool temporarily might reveal that this solves all the issues ; at the very least, I would try setting pool_recycle to a low number, like 5 minutes, however this won't prevent the problem, just make it less likely. What you do need to find are stack traces that precede the error, to give a clue why a connection would be placed in the pool in a bad state. The other possibility is that your application is actually sharing a single connection across threads in some way which would be a different problem though with a lot of similar behaviors. Are there any global in-memory caches being used of objects where an ORM object might be shared out among threads, or a background worker thread of some kind, anything like that ?Does the application use a "scoped_session" pattern and maybe the session being passed around in some cases isn't actually scoped? On Fri, Apr 12, 2019 at 4:03 AM tonthon wrote: Le 10/04/2019 à 17:12, Mike Bayer a écrit : On Wed, Apr 10, 2019 at 9:23 AM tonthon wrote: Hi, We're using sqlalchemy in a Pyramid Web Application. We use the ZopeTransactionExtension and our session factory is initialized this way : DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) We use the pyramid_tm that wraps each web requests in a transaction. Our services are served through apache and mod_wsgi (1 process, 10 threads). We recently faced the following error : 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) Traceback (most recent call last): File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 712, in _finalize_fairy fairy._reset(pool) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 881, in _reset self._reset_agent.rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback self._do_rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1670, in _do_rollback self.connection._rollback_impl() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 704, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1804, in do_rollback dbapi_connection.rollback() ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out of sync; you can't run this command now") (Background on this error at: http://sqlalche.me/e/f405) I can't reproduce this problem that seems to happen randomly. Could anyone help me giving some points to investigate ? Is there some tuning to do (I already set the pool_recycle var) ? the most important thing is what driver are you using and what version? are you doing anything with SAVEPOINTs or greenlets / eventlet ? is the web application experiencing timeouts of some kind, such that transactions are being dropped ? is the above error only showing up in logs or is it occurring synchronously with a web request and causing the request to fail ? We use mysqlclient 1.4.1. We don't use any greenlet or eventlet nor savepoints. I havn't seen any timeout happening so far. The given error is happening during the request's lifecycle resulting in a HTTP 500 error code After the error, the user refreshes and it works. I don't know if it could be related, but we also see things like "NoSuchColumnError: "Could not locate column in row for column 'count(*)'". Thanks in advance Best regards Gaston -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You
Re: [sqlalchemy] Commands out of sync; you can't run this command now
Le 10/04/2019 à 17:12, Mike Bayer a écrit : On Wed, Apr 10, 2019 at 9:23 AM tonthon wrote: Hi, We're using sqlalchemy in a Pyramid Web Application. We use the ZopeTransactionExtension and our session factory is initialized this way : DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) We use the pyramid_tm that wraps each web requests in a transaction. Our services are served through apache and mod_wsgi (1 process, 10 threads). We recently faced the following error : 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) Traceback (most recent call last): File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 712, in _finalize_fairy fairy._reset(pool) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 881, in _reset self._reset_agent.rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback self._do_rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1670, in _do_rollback self.connection._rollback_impl() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 704, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1804, in do_rollback dbapi_connection.rollback() ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out of sync; you can't run this command now") (Background on this error at: http://sqlalche.me/e/f405) I can't reproduce this problem that seems to happen randomly. Could anyone help me giving some points to investigate ? Is there some tuning to do (I already set the pool_recycle var) ? the most important thing is what driver are you using and what version? are you doing anything with SAVEPOINTs or greenlets / eventlet ? is the web application experiencing timeouts of some kind, such that transactions are being dropped ? is the above error only showing up in logs or is it occurring synchronously with a web request and causing the request to fail ? We use mysqlclient 1.4.1. We don't use any greenlet or eventlet nor savepoints. I havn't seen any timeout happening so far. The given error is happening during the request's lifecycle resulting in a HTTP 500 error code After the error, the user refreshes and it works. I don't know if it could be related, but we also see things like "NoSuchColumnError: "Could not locate column in row for column 'count(*)'". Thanks in advance Best regards Gaston -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Commands out of sync; you can't run this command now
Hi, We're using sqlalchemy in a Pyramid Web Application. We use the ZopeTransactionExtension and our session factory is initialized this way : >>> DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) We use the pyramid_tm that wraps each web requests in a transaction. Our services are served through apache and mod_wsgi (1 process, 10 threads). We recently faced the following error : 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) Traceback (most recent call last): File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 712, in _finalize_fairy fairy._reset(pool) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 881, in _reset self._reset_agent.rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1632, in rollback self._do_rollback() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1670, in _do_rollback self.connection._rollback_impl() File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 706, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 704, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1804, in do_rollback dbapi_connection.rollback() ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out of sync; you can't run this command now") (Background on this error at: http://sqlalche.me/e/f405) I can't reproduce this problem that seems to happen randomly. Could anyone help me giving some points to investigate ? Is there some tuning to do (I already set the pool_recycle var) ? Thanks in advance Best regards Gaston -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Orm models, transaction and multiple binds
It should solve my problem. I'm still amazed by the very high quality of this mailing-list. The answers are always targeting very precisely the solutions to the problems I'm sharing. Thanks a lot !! Le ven. 11 janv. 2019 à 20:45, Mike Bayer a écrit : > On Fri, Jan 11, 2019 at 1:19 PM tonthon wrote: > > > > Hi, > > > > We're using Sqlalchemy in a Pyramid web application, with the pyramid_tm > package (a transaction that wraps every web request inside a DB > transaction). It's very usefull and it works like a charm. > > > > In order to follow some security rules and to be able to certify part of > our app, we want to change some things : > > - For a specific MyModel, the main bind should have only read access. > > - Another bind, dedicated to MyModel and only used by a separated > library should have RW privileges. > > > > The problem comes with the following : > > - A "transaction" begins > > - A "session1" object is initialized with the Read-only bind > > - An "instance1" of MyModel is loaded in "session1" > > - A "session2" using a binding with RW privileges is initialized > > - It loads a MyModel "instance2" in the "session2" and modifies it (then > merges it) > > - "session2" is commited (instance2 is persisted) > > - I can't get the "instance1" of MyModel initially loaded to be > refreshed (surely due to the transaction isolation level). > > > > Has anyone an advice to share on how to refresh "instance1" ? > > if these "binds" point to exactly the same database schema and just > have different permissions you might find it much easier to use one > session with two binds.You can override get_bind() to use a > different engine for write operations on a specific model.there's > an example of this at: > > https://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ > .there's also a special hook where you can persist individual > instances of a class on different database connections too but you > shouldn't need that here. > > > > > > > > Thanks in advance > > Best regards > > Gaston Tjebbes > > http://majerti.fr > > > > > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Orm models, transaction and multiple binds
Hi, We're using Sqlalchemy in a Pyramid web application, with the pyramid_tm package (a transaction that wraps every web request inside a DB transaction). It's very usefull and it works like a charm. In order to follow some security rules and to be able to certify part of our app, we want to change some things : - For a specific MyModel, the main bind should have only read access. - Another bind, dedicated to MyModel and only used by a separated library should have RW privileges. The problem comes with the following : - A "transaction" begins - A "session1" object is initialized with the Read-only bind - An "instance1" of MyModel is loaded in "session1" - A "session2" using a binding with RW privileges is initialized - It loads a MyModel "instance2" in the "session2" and modifies it (then merges it) - "session2" is commited (instance2 is persisted) - I can't get the "instance1" of MyModel initially loaded to be refreshed (surely due to the transaction isolation level). Has anyone an advice to share on how to refresh "instance1" ? Thanks in advance Best regards Gaston Tjebbes http://majerti.fr -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Events and bidirectionnal datas modification
Thanks a lot for this helpfull and precise answer.
I'll tend to say "as usual" :).
Have a nice day
Gaston
Le 07/02/2018 à 16:13, Mike Bayer a écrit :
> On Wed, Feb 7, 2018 at 7:16 AM, tonthon wrote:
>> Hi,
>>
>> I'd like to setup bidirectionnal data synchornization between the lastname
>> attribute of two related models
>>
>> class User(Base):
>> __tablename__ = 'users'
>> id = Column(Integer, primary_key=True)
>> lastname = Column(String(50))
>> userdatas = relationship('UserDatas',
>> primaryjoin='User.id==UserDatas.user_id', back_populates='user',
>> uselist=False)
>>
>> class UserDatas(Base):
>> __tablename__ = 'userdatas'
>> id = Column(Integer, primary_key=True)
>> lastname = Column(String(50))
>> user_id = Column(ForeignKey('users.id'))
>> user = relationship('User', primaryjoin='User.id==UserDatas.user_id')
>>
>>
>> I thought I could do something like this :
>>
>> def sync_lastname_user_to_userdatas(target, value, oldvalue, initiator):
>> target.userdatas.lastname = value
>>
>> listen(User.lastname, 'set', sync_lastname_user_to_userdatas)
>>
>> def sync_lastname_userdatas_to_user(target, value, oldvalue, initiator):
>> target.user.lastname = value
>>
>> listen(UserDatas.lastname, 'set', sync_lastname_userdatas_to_user)
>>
>>
>> The obvious problem here is the infinite loop that is generated
>>
>> So :
>> Is there a way to set an attribute without firing the 'set' event ?
>>
>> I tend to think there is a better way to do that, does anybody have an
>> advice to share ?
> below is an example of the most correct way to do this, to support
> this use case I will add the "initiator" argument to the
> set_attribute() function within SQLAlchemy but this will work in all
> 1.1, 1.2 versions for now:
>
> from sqlalchemy import Column, Integer, String, ForeignKey
> from sqlalchemy.orm import relationship
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import event
> from sqlalchemy import inspect
>
>
> def set_attribute(instance, key, value, initiator=None):
> """Set the value of an attribute, firing history events.
>
> This function is copied from the attributes module but adds the
> "initiator" argument.
>
> """
> state = inspect(instance)
> dict_ = state.dict
> state.manager[key].impl.set(state, dict_, value, initiator)
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
> lastname = Column(String(50))
> userdatas = relationship(
> 'UserDatas', primaryjoin='User.id==UserDatas.user_id',
> back_populates='user', uselist=False)
>
>
> class UserDatas(Base):
> __tablename__ = 'userdatas'
> id = Column(Integer, primary_key=True)
> lastname = Column(String(50))
> user_id = Column(ForeignKey('users.id'))
> user = relationship('User', primaryjoin='User.id==UserDatas.user_id')
>
>
> @event.listens_for(User.lastname, "set")
> def sync_lastname_user_to_userdatas(target, value, oldvalue, initiator):
> parentclass = initiator.parent_token.parent.class_
> if parentclass is User:
> set_attribute(target.userdatas, "lastname", value, initiator)
>
>
> @event.listens_for(UserDatas.lastname, 'set')
> def sync_lastname_userdatas_to_user(target, value, oldvalue, initiator):
> parentclass = initiator.parent_token.parent.class_
> if parentclass is UserDatas:
> set_attribute(target.user, "lastname", value, initiator)
>
> u1 = User(userdatas=UserDatas())
>
> u1.lastname = 'foo'
>
> assert u1.userdatas.lastname == u1.lastname == 'foo'
>
> u1.userdatas.lastname = 'bar'
> assert u1.userdatas.lastname == u1.lastname == 'bar'
>
>
>
>
>
>
>
>> Thanks in advance
>>
>> Regards,
>> Gaston Tjebbes
>>
>> https://www.majerti.fr
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> descriptio
Re: [sqlalchemy] Relationships - crash when class instantiation
Hi,
There's nothing that could cause the problem you describe in the example
you provide maybe the guilty lines where not included.
The easier way to go should be to step by step reproduce your model
structure until you face the problem you describe and then post an
example code : http://stackoverflow.com/help/mcve.
Regards
Gaston
Le 07/02/2018 à 12:09, Sven a écrit :
> Hello everybody,
>
> Today, I have a strange problem regarding relationships.
>
> My project contains already several relationships and until now, I
> never had any problem with these. But now, when I try to add a new
> relationship between two classes, the entire program suddenly crashes
> without displaying any error message. It looks like an infinite loop.
>
> I am sadly not able to reproduce the problem in a small example.
>
> I have a lot of classes with several relationships and the
> informations are sometimes redundant and not optimized. For example:
>
> Player has a relationship with Race.
> Race has a relationship with Skeleton.
> Player has a relationship with Skeleton.
>
> It means that the Skeleton is also present in Player while the
> Skeleton may also be accessible via Race...
>
> This is due to the fact that I am working from an existing project
> whose save system was based on Pickle.
>
> Let's take the following example and let's assume that it reflects my
> problem and crashes.
>
> |
> classPlayer(Base):
>
> __tablename__ ='player'
> id =Column(Integer,primary_key=True)
>
> id_weapon =Column(Integer,ForeignKey('weapon.id'))
> weapon =relationship("Weapon",back_populates="players")
>
> id_room =Column(Integer,ForeignKey('room.id'))
> room =relationship("Room",back_populates="players")
>
> ...other relationships
>
> classWeapon(Base):
>
> __tablename__ ='weapon'
> id =Column(Integer,primary_key=True)
>
> players =relationship("Player",back_populates="weapon")
>
> ...other relationships
>
>
> def__init__(arg1,arg2):
> print("I will never be executed.")
> ...some code
>
> classRoom(Base):
>
> __tablename__ ='room'
> id =Column(Integer,primary_key=True)
>
> players =relationship("Player",back_populates="room")
>
>
> ...other relationships
> |
>
>
>
> I tried to determine which line makes the whole thing crashes and it
> happens when I try to do this:
>
> *w = Weapon(arg1, arg2)*
>
> What is strange is that the __init__ is not executed. It crashes
> between the call *Weapon(arg1, arg2)* and the __init__. The print
> function*print("I will never be executed.")* will for example not be
> executed.
>
> And I have just one __init__ in this class.
>
>
> When I delete one of the relationships in the class Weapon, everything
> works fine.
>
>
> My hypothesis is that SQLAlchemy try to do something with
> relationships when Weapon(arg1, arg2) is executed and for some
> reasons, it crashes. Like I said, my relationships network is a bit
> strange and absolutly not optimized. Is it possible that the
> relationships and the back_populates are causing Infinite Loops ?
>
> Has someone already see that ? How could I learn more about the
> problem and figure out what SQLALchemy is trying to do and where it
> crashes ?
>
> Thank you.
>
>
> Sven
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> .
> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Help resolving Could not determine join condition between parent/child tables on relationship error message
Hi,
When SQALchemy can't guess the join condition to use for a relationship,
you have to use the primaryjoin parameter :
ursmst = relationship('Usrmst',
primaryjoin="Workgrp.workgrp_owner==Usrmst.id")
Hope this helps.
Regards
Gaston
Le 07/02/2018 à 12:01, Jeremy Flowers a écrit :
> Hi
> I've recently used sqlacodegen
>
> When I try and run against the generated code it get this message
> that I've been unable to fix:
>
> Could not determine join condition between parent/child tables on
> relationship Workgrp.usrmst - there are multiple foreign key paths
> linking the tables. Specify the 'foreign_keys' argument, providing a
> list of those columns which should be counted as containing a foreign
> key reference to the parent table.
>
> I'm wondering if someone can shed some light:
>
> Here is the generated code for the relevant tables causing the issue:
>
> class Workgrp(Owner):
> __tablename__ = 'workgrp'
>
> workgrp_id = Column(ForeignKey('owner.owner_id'), primary_key=True)
> workgrp_prntid = Column(Numeric(scale=0, asdecimal=False))
> workgrp_name = Column(String(256))
> workgrp_desc = Column(String(4000))
> workgrp_owner = Column(ForeignKey('usrmst.usrmst_id'))
> workgrp_lstchgtm = Column(DateTime, index=True)
> workgrp_externid = Column(String(20))
> workgrp_profile = Column(Text)
> workgrp_usrmodtm = Column(DateTime)
>
> usrmst = relationship('Usrmst')
>
>
> class Usrmst(Owner):
> __tablename__ = 'usrmst'
> __table_args__ = (
> Index('usrmst_ak1', 'usrmst_domain', 'usrmst_name'),
> )
>
> usrmst_id = Column(ForeignKey('owner.owner_id'), primary_key=True)
> usrmst_domain = Column(String(256))
> usrmst_name = Column(String(256), nullable=False)
> usrmst_fullname = Column(String(1024))
> usrmst_desc = Column(String(4000))
> usrmst_phoneno = Column(String(40))
> usrmst_pagerno = Column(String(40))
> usrmst_email = Column(String(1024))
> usrmst_emailtype = Column(Numeric(scale=0, asdecimal=False))
> secmst_id = Column(ForeignKey('secmst.secmst_id'))
> lngmst_id = Column(ForeignKey('lngmst.lngmst_id'))
> usrmst_password = Column(String(1024))
> usrmst_externid = Column(String(20))
> usrmst_suser = Column(String(1))
> usrmst_lstchgtm = Column(DateTime, index=True)
> usrmst_orapassword = Column(String(144))
> usrmst_wingroup = Column(String(1))
> usrmst_tmpacct = Column(String(1))
> usrmst_profile = Column(Text)
> usrmst_usrmodtm = Column(DateTime)
> usrmst_principal = Column(String(256))
> usrmst_keytab = Column(String(4000))
>
> lngmst = relationship('Lngmst')
> secmst = relationship('Secmst')
>
> I've looked at the SQLAlchemy docs, and tried things with
> foreign_keys, primaryjoin,
> http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.foreign_keys
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-custom-foreign
>
> But ended up with messages like '
> Table' object has no attribute 'usrmst_id'
> AttributeError: 'Table' object has no attribute 'workgrp_owner'
>
> Also for Mike Bayer: Why is there no consistency in naming within
> SQLAlchemy?:
> foreign_keys uses underscore
> primaryjoin doesn't
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> .
> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Events and bidirectionnal datas modification
Hi,
I'd like to setup bidirectionnal data synchornization between the
lastname attribute of two related models
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
lastname = Column(String(50))
userdatas = relationship('UserDatas',
primaryjoin='User.id==UserDatas.user_id', back_populates='user',
uselist=False)
class UserDatas(Base):
__tablename__ = 'userdatas'
id = Column(Integer, primary_key=True)
lastname = Column(String(50))
user_id = Column(ForeignKey('users.id'))
user = relationship('User', primaryjoin='User.id==UserDatas.user_id')
I thought I could do something like this :
def sync_lastname_user_to_userdatas(target, value, oldvalue, initiator):
target.userdatas.lastname = value
listen(User.lastname, 'set', sync_lastname_user_to_userdatas)
def sync_lastname_userdatas_to_user(target, value, oldvalue, initiator):
target.user.lastname = value
listen(UserDatas.lastname, 'set', sync_lastname_userdatas_to_user)
The obvious problem here is the infinite loop that is generated
So :
Is there a way to set an attribute without firing the 'set' event ?
I tend to think there is a better way to do that, does anybody have an
advice to share ?
Thanks in advance
Regards,
Gaston Tjebbes
https://www.majerti.fr
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] deferred groups
Thanks a lot for the time you spent on my question (and for the answer).
I'll try to make it directly clear next time.
Regards
Le 16/03/2015 17:49, Michael Bayer a écrit :
>
> tonthon wrote:
>
>> Sorry, still a few mistakes in what I wrote (first testing what you
>> paste is a far better way to work) :
>>
>>
>>
>> 121 from sqlalchemy.orm import
>> deferred
>> 122 from sqlalchemy import
>> Text
>>
>> 123
>>
>>
>> 124 class
>> Base(DBBASE):
>> 125 __tablename__ =
>> 'base'
>> 126 __mapper_args__ = {'polymorphic_identity': 'base',
>> 'polymorphic_on':'type_'}
>> 127 id = Column(Integer,
>> primary_key=True)
>> 128 name =
>> Column(String(255))
>> 129 description =
>> deferred(
>> 130
>> Column(Text()),
>> 131
>> group="full"
>> 132
>> )
>> 133 type_ = Column( String(30),
>> nullable=False)
>>
>> 134
>>
>>
>> 135 class
>> Element(Base):
>> 136 __tablename__ =
>> 'element'
>> 137 __mapper_args__ = {'polymorphic_identity':
>> 'element'}
>> 138 id = Column(ForeignKey("base.id"),
>> primary_key=True)
>> 139 comments = deferred( Column(Text()), group="full", )
>>
>>
>> print(DbSession().query(Element).options(undefer_group('full')))
>>
>> SELECT element.comments AS element_comments, element.id AS element_id,
>> base.id AS base_id, base.name AS base_name, base.type_ AS base_type_
>> FROM base INNER JOIN element ON base.id = element.id
> looks like it was fixed in 0.9.9 in #3287. Please upgrade.
>
>
>
>
>
>
>
>>
>> Le 16/03/2015 17:33, tonthon a écrit :
>>> Sorry I was a bit too speed when writing that one :)
>>>
>>> So I've got a Base model :
>>> """
>>> class Base(DBBASE):
>>>__tablename__ =
>>> 'base'
>>>__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
>>> 'type_'}
>>>id = Column(Integer, primary_key=True)
>>> name = Column(String(255))
>>> description = deferred(
>>> Column(Text()),
>>> group="full"
>>> )
>>>type_ = Column( String(30), nullable=False)
>>> """
>>>
>>> and a child model
>>> """
>>> class Element(DBBASE):
>>>__tablename__ = 'element'
>>>__mapper_args__ = {'polymorphic_identity': 'element'}
>>>id = Column(ForeignKey("base.id"))
>>> comments = deferred( Column(Text()), group="full", )
>>> """
>>>
>>> The following query :
>>> """
>>> Element.query().options(undefer_group('full')).all()
>>> """
>>>
>>> doesn't load the description column, is that the expected behaviour ?
>>>
>>> Regards
>>>
>>>
>>> Le 16/03/2015 17:21, Michael Bayer a écrit :
>>>> tonthon wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm using polymorphism and I set up some deferred columns at each level
>>>>> of inheritance belonging to the same deferred group :
>>>>>
>>>>> """
>>>>> class Base(DBBASE):
>>>>> id = Column(Integer, primary_key=True)
>>>>> name =
Re: [sqlalchemy] deferred groups
Sorry, still a few mistakes in what I wrote (first testing what you
paste is a far better way to work) :
121 from sqlalchemy.orm import
deferred
122 from sqlalchemy import
Text
123
124 class
Base(DBBASE):
125 __tablename__ =
'base'
126 __mapper_args__ = {'polymorphic_identity': 'base',
'polymorphic_on':'type_'}
127 id = Column(Integer,
primary_key=True)
128 name =
Column(String(255))
129 description =
deferred(
130
Column(Text()),
131
group="full"
132
)
133 type_ = Column( String(30),
nullable=False)
134
135 class
Element(Base):
136 __tablename__ =
'element'
137 __mapper_args__ = {'polymorphic_identity':
'element'}
138 id = Column(ForeignKey("base.id"),
primary_key=True)
139 comments = deferred( Column(Text()), group="full", )
print(DbSession().query(Element).options(undefer_group('full')))
SELECT element.comments AS element_comments, element.id AS element_id,
base.id AS base_id, base.name AS base_name, base.type_ AS base_type_
FROM base INNER JOIN element ON base.id = element.id
Le 16/03/2015 17:33, tonthon a écrit :
> Sorry I was a bit too speed when writing that one :)
>
> So I've got a Base model :
> """
> class Base(DBBASE):
> __tablename__ =
> 'base'
> __mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
> 'type_'}
> id = Column(Integer, primary_key=True)
>name = Column(String(255))
>description = deferred(
>Column(Text()),
>group="full"
>)
> type_ = Column( String(30), nullable=False)
> """
>
> and a child model
> """
> class Element(DBBASE):
> __tablename__ = 'element'
> __mapper_args__ = {'polymorphic_identity': 'element'}
> id = Column(ForeignKey("base.id"))
>comments = deferred( Column(Text()), group="full", )
> """
>
> The following query :
> """
> Element.query().options(undefer_group('full')).all()
> """
>
> doesn't load the description column, is that the expected behaviour ?
>
> Regards
>
>
> Le 16/03/2015 17:21, Michael Bayer a écrit :
>> tonthon wrote:
>>
>>> Hi,
>>>
>>> I'm using polymorphism and I set up some deferred columns at each level
>>> of inheritance belonging to the same deferred group :
>>>
>>> """
>>> class Base(DBBASE):
>>>id = Column(Integer, primary_key=True)
>>>name = Column(String(255))
>>>description = deferred(
>>>Column(Text()),
>>>group="full"
>>>)
>>>
>>> class Element(DBBASE):
>>>id = Column(ForeignKey("base.id"))
>>>comments = deferred(
>>>Column(Text()),
>>>group="full",
>>>)
>>> """
>>>
>>> The following query :
>>>
>>> """
>>> Element.query().options(undefer_group('full')).all()
>>> """
>>>
>>> doesn't defer the description column, is that the expected behaviour ?
>> a query for Element here will not load objects of type “Base”, so I don’t
>> see where “description” comes into play.
>>
>> If you can provide a more complete example that would help.
>>
>>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] deferred groups
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
"""
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group="full"
)
type_ = Column( String(30), nullable=False)
"""
and a child model
"""
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey("base.id"))
comments = deferred( Column(Text()), group="full", )
"""
The following query :
"""
Element.query().options(undefer_group('full')).all()
"""
doesn't load the description column, is that the expected behaviour ?
Regards
Le 16/03/2015 17:21, Michael Bayer a écrit :
>
> tonthon wrote:
>
>> Hi,
>>
>> I'm using polymorphism and I set up some deferred columns at each level
>> of inheritance belonging to the same deferred group :
>>
>> """
>> class Base(DBBASE):
>>id = Column(Integer, primary_key=True)
>>name = Column(String(255))
>>description = deferred(
>>Column(Text()),
>>group="full"
>>)
>>
>> class Element(DBBASE):
>>id = Column(ForeignKey("base.id"))
>>comments = deferred(
>>Column(Text()),
>>group="full",
>>)
>> """
>>
>> The following query :
>>
>> """
>> Element.query().options(undefer_group('full')).all()
>> """
>>
>> doesn't defer the description column, is that the expected behaviour ?
> a query for Element here will not load objects of type “Base”, so I don’t see
> where “description” comes into play.
>
> If you can provide a more complete example that would help.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] deferred groups
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
"""
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group="full"
)
class Element(DBBASE):
id = Column(ForeignKey("base.id"))
comments = deferred(
Column(Text()),
group="full",
)
"""
The following query :
"""
Element.query().options(undefer_group('full')).all()
"""
doesn't defer the description column, is that the expected behaviour ?
Regards
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Handling detached instances
Le mardi 30 septembre 2014 13:28:16 UTC+2, Simon King a écrit : > > On Tue, Sep 30, 2014 at 8:52 AM, tonthon > > wrote: > > Hi, > > > > I'm using dogpile cache to store objects in a redis database, here's the > > sample function I use for my tests : > > > >>>> @region.cache_on_arguments() > >>>> def get_my_model(id): > >>>>return DBSession().query(Model).get(id) > > > > I retrieve models : > > > >>>> model1 = get_my_model(5) > >>>> model2 = get_my_model(5) > > > > model2 is retrieved from the cache. > > Since it's not attached to any session, when accessing a lazy-related > > object : > > > >>>> model2.owner > > > > I get a a DetachedInstanceError > > I found a turnaround : > > > >>>> DBSession().enable_relationship_loading(model2) > >>>> model2.owner > > > > that allows me to do what I want, but I'm not really satisfied with this > > design. > > > > Is it a clean way to go or should I review my caching strategy ? > > > > The normal approach is to use session.merge() to attach a cached > object to a session. Note that session.merge() actually returns a *new > instance* attached to the session - it doesn't attach the instance > that you passed in: > > http://docs.sqlalchemy.org/en/latest/orm/session.html#merging > > There's also a sophisticated example of using dogpile.cache at: > > http://docs.sqlalchemy.org/en/latest/orm/examples.html#examples-caching > > Hope that helps, > > Simon > Hi, I didn't knew merge was supposes to be used in such a case, it works like a charm. Following that tip, I've added this decorator : >>> def cache_wrapper(func): >>> """ ensure a model returned from a cached function is attached to the current session """ >>> def cached_func_wrapper(*args, **kwargs): >>> obj = func(*args, **kwargs) >>> if object_session(obj) is None and has_identity(obj): >>> obj = DBSESSION().merge(obj) >>> return obj >>> return cached_func_wrapper So the cached function is like this one : >>> @cache_wrapper >>> @region.cache_on_arguments() >>> def get_my_model(id): >>>return DBSession().query(Model).get(id) I'll have a look at the examples you pointed. Thanks a lot for your answer, Regards, Gaston http://www.majerti.fr -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Handling detached instances
Hi, I'm using dogpile cache to store objects in a redis database, here's the sample function I use for my tests : >>> @region.cache_on_arguments() >>> def get_my_model(id): >>>return DBSession().query(Model).get(id) I retrieve models : >>> model1 = get_my_model(5) >>> model2 = get_my_model(5) model2 is retrieved from the cache. Since it's not attached to any session, when accessing a lazy-related object : >>> model2.owner I get a a DetachedInstanceError I found a turnaround : >>> DBSession().enable_relationship_loading(model2) >>> model2.owner that allows me to do what I want, but I'm not really satisfied with this design. Is it a clean way to go or should I review my caching strategy ? Thanks in advance, Regards Gaston Tjebbes http://www.majerti.fr -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] joined inheritance and cascading delete to relationship
Hi,
I've got a joined inheritance :
class Task(Base):
__tablename__ = 'task'
id = Column(Integer, primary_key=True)
class Invoice(Task):
__tablename__ = 'invoice'
id = Column(ForeignKey("task.id"))
When I delete an invoice, the associated task is also deleted, that's ok.
I've got a model TaskStatus related to the Task object and I'd like all
related status to be deleted when I delete an invoice:
class TaskStatus(Base):
__tablename__ = 'task_status'
id = Column(Integer, primary_key=True)
task_id = Column(ForeignKey("task.id"))
task = relationship("Task", backref=backref("statuses", cascade="all,
delete-orphan"))
How could I achieve that one ?
Regards,
Gaston
--
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] with_polymorphic query and parent attribute filtering
Le 14/11/2012 06:19, Michael Bayer a écrit :
> On Nov 13, 2012, at 3:34 AM, tonthon wrote:
>
>> Hi,
>>
>> I'm using polymorphism for some of my models and I'm wondering how I
>> should use the with_polymorphic query method.
>>
>> Consider the following:
>> """
>> class A(Base):
>> type_ = Column(Integer, nullable=False)
>> arg = Column(String(20))
>> __mapper_args__ = {'polymorphic_on': type_, 'polymorphic_identity': 0}
>>
>> class B(A):
>>__mapper_args__ = {'polymorphic_identity': 1}
>>
>> class C(A):
>>__mapper_args__ = {'polymorphic_identity': 2}
>>
>> class D(A):
>>__mapper_args__ = {'polymorphic_identity': 3}
>> """
>>
>> When I query :
>>
>> """
>> session.query(A).with_polymorphic([B,C]).filter(A.arg == 'test')
>> """
>>
>> I get D elements in my result.
>> I've tried :
>>
>> """
>> session.query(A).with_polymorphic([B,C]).filter(or_(B.arg=='test',
>> C.arg=='test'))
>> """
>>
>> But it doesn't work neither.
>>
>> Could somebody explain me what I did wrong and how I should do ?
> with_polymorphic() is generally only useful with joined table inheritance
> (note this is single table inheritance), and is used to add those subclass
> tables to the Query so that you can filter() on their criterion, as well as
> to allow more columns to be pulled in via a single query rather than needing
> to invoke additional queries for subclass tables. It does not indicate a
> subset of subclasses to be included.
>
> If you want to load A's of type B, C, but not D, you'd need to do this
> manually via the discriminator - since B, C and D are all As you normally
> will get back all three as the rows determine.
>
> session.query(A).filter(A.type_.in(0, 1, 2))
>
Thanks for the answer, I had misunderstood the role of the
with_polymorphic method (In my app I'm using joined table inheritance).
Now it's far clearer.
Regards
Gaston
--
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] with_polymorphic query and parent attribute filtering
Hi,
I'm using polymorphism for some of my models and I'm wondering how I
should use the with_polymorphic query method.
Consider the following:
"""
class A(Base):
type_ = Column(Integer, nullable=False)
arg = Column(String(20))
__mapper_args__ = {'polymorphic_on': type_, 'polymorphic_identity': 0}
class B(A):
__mapper_args__ = {'polymorphic_identity': 1}
class C(A):
__mapper_args__ = {'polymorphic_identity': 2}
class D(A):
__mapper_args__ = {'polymorphic_identity': 3}
"""
When I query :
"""
session.query(A).with_polymorphic([B,C]).filter(A.arg == 'test')
"""
I get D elements in my result.
I've tried :
"""
session.query(A).with_polymorphic([B,C]).filter(or_(B.arg=='test',
C.arg=='test'))
"""
But it doesn't work neither.
Could somebody explain me what I did wrong and how I should do ?
Regards
Gaston
--
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] Issue with `filter_by`?
what result do you get with : >>> [obj.my_column for obj in Session.query(Model).filter_by(my_column=123).all()] ? Le 25/07/2012 07:17, Amos a écrit : > > I've defined a column declaratively like so > > my_column = Column(Unicode(30), index=True, unique=True) > > If I pass in an integer instead of a string, it will actually return > all records that start with the string representation of that integer > > >>> [obj.code for obj in > Session.query(Model).filter_by(my_column=123).all()] > > [u'123ad', u'123lpb', u'123xd8', u'123za0'] > > I would expect no results as no column exactly matches the string > representation of my number > > -- > 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/-/mGMKZ0NB9_AJ. > 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] Concrete inheritance and child relationship query
Le 29/06/2012 16:14, Michael Bayer a écrit : > On Jun 29, 2012, at 6:31 AM, tonthon wrote: > >> I had also to add : >> c1 = aliased(Client) >> c2 = aliased(Client) > oh right, forgot that part > >> query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1, >> Invoice.project).outerjoin(p2, Estimation.project).outerjoin(c1, >> p1.client).outerjoin(c2, p2.client) >> >> And I can filter on common parameters, that's what I was looking for so far. >> >> Is it possible to get Invoices and Estimations as result objects (in >> place of Task objects) ? > if the Estimation/Invoice classes use "polymorphic_on" against a column in > the Task table that acts as the "discriminator" then sure. > > if there isn't such a column, then things are trickier - we don't have a 100% > endorsed pattern for that right now. there's hacky workarounds, id have to > work one up. > > Ok, thanks a lot for all your explanations, I don't have any polymorphic_on discriminator but I can try to insert one (an update sql command line should be very easy to perform), it should make the trick. Regards Gaston -- 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] Aliased element and attributes
Hi, I'm using the following (as discussed in another post) : p1 = aliased(Project) when using it in a filter : query = session.query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1, Invoice.project).outerjoin(p2, Estimation.project) query = query.filter(p1.company_id==cid) I get the following error AttributeError: company_id Since the Project class has a company_id Column defined in it, I was expecting I could access it in p1 as well. Did I misunderstood something ? Regards Gaston -- 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] Concrete inheritance and child relationship query
Le 28/06/2012 16:40, Michael Bayer a écrit :
> On Jun 28, 2012, at 6:38 AM, tonthon wrote:
>
>> Hi,
>>
>> I've got a problem understanding how to handle relationships with
>> polymorphism
>> Sorry for the db design (I took it overs as it is ).
>>
>> I've got a parent class Task
>>
>> class Task(DBBASE):
>>__tablename__ = 'task'
>>id = Column(Integer, primary_key=True)
>>amount = Column(Integer)
>>
>> and two child classes
>>
>> class Invoice(DBBASE):
>>__tablename__ = 'invoice'
>>id = Column(Integer, ForeignKey('task.id'), primary_key=True)
>>date = Column(Date)
>>project_id = Column(Integer, ForeignKey('project.id'))
>>project = relationship("Project", backref=backref('invoices',
>> order_by='Invoice.date'))
>>specific_attr1 = Column(Integer)
>>
>> class Esimation(DBBASE):
>>__tablename__ = 'estimation'
>>id = Column(Integer, ForeignKey('task.id'), primary_key=True)
>>date = Column(Date)
>>project_id = Column(Integer, ForeignKey('project.id'))
>>project = relationship("Project", backref=backref('estimations',
>> order_by='Invoice.date'))
>>specific_attr2 = Column(Integer)
>>specific_attr3 = Column(Integer)
>>
>> I'd like to query something like:
>>
>> dbession.query(Task).with_polymorphic([Invoice,
>> Estimation]).join(Invoice.project).join(Estimation.project).join(Project.client)
>>
>> I actually get an error :
>> (1066, "Not unique table/alias: 'project' ")
> OK you need here to think about SQL. Task, Invoice, Estimation are all
> different tables.A particular row in Task will either have an Invoice, or
> Estimation row - but never both. So with_polymorphic() implies an outer join
> from Task to both of these tables.
>
> Once you're outer joining, anything that you join to from those joins, also
> needs to be an outer join else you'll get no rows back when the parent is
> NULL - so we are talking about Invoice.project as well as Estimation.project,
> and also each of those to Project.client - unfortunately you'll have to also
> join to client twice if you really need all of these columns present.The
> "not unique" error refers to the fact that a particular name can only be
> mentioned as a FROM target once in SQL.
>
> So you'll need to alias out those targets, like this:
>
> p1 = aliased(Project)
> p2 = aliased(Project)
>
> then use outerjoins and distinct joins to client:
>
> query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1,
> Invoice.project).outerjoin(p2,
> Estimation.Project).outerjoin(p1.client).outerjoin(p2.client)
>
> the above query has a lot of outer joins and is generally not going to
> perform very well. Depending on what you're ultimately trying to do, you may
> want to join to a union of the estimation/invoice->project->client target, if
> you're looking to have a simple path from Task to Client (that is,
> query(Task).join(my_union, Task.id=my_union.c.id), and I'd create my_union
> using Table/select() objects directly for "select client.* from
> invoice_table->project->client UNION select client.* from
> estimation_table->project_client".I'd experiment with SQL directly to see
> what works best.
>
> (also this is joined inheritance, not concrete, since Invoice/Estimation join
> to Task with the "task.id" FK).
>
>
I had also to add :
c1 = aliased(Client)
c2 = aliased(Client)
query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1,
Invoice.project).outerjoin(p2, Estimation.project).outerjoin(c1,
p1.client).outerjoin(c2, p2.client)
And I can filter on common parameters, that's what I was looking for so far.
Is it possible to get Invoices and Estimations as result objects (in
place of Task objects) ?
Cheers,
Gaston
--
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] Sqlalchemy tuning : pool_size and pool_recycle
Hi, I was wondering what values to setup for pool_size and pool_recycyle parameters. I think you have to set them up regarding your sql server configuration (mysql in my case), but how ? Cheers, Gaston -- 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] Concrete inheritance and child relationship query
Le 28/06/2012 16:40, Michael Bayer a écrit :
> On Jun 28, 2012, at 6:38 AM, tonthon wrote:
>
>> Hi,
>>
>> I've got a problem understanding how to handle relationships with
>> polymorphism
>> Sorry for the db design (I took it overs as it is ).
>>
>> I've got a parent class Task
>>
>> class Task(DBBASE):
>>__tablename__ = 'task'
>>id = Column(Integer, primary_key=True)
>>amount = Column(Integer)
>>
>> and two child classes
>>
>> class Invoice(DBBASE):
>>__tablename__ = 'invoice'
>>id = Column(Integer, ForeignKey('task.id'), primary_key=True)
>>date = Column(Date)
>>project_id = Column(Integer, ForeignKey('project.id'))
>>project = relationship("Project", backref=backref('invoices',
>> order_by='Invoice.date'))
>>specific_attr1 = Column(Integer)
>>
>> class Esimation(DBBASE):
>>__tablename__ = 'estimation'
>>id = Column(Integer, ForeignKey('task.id'), primary_key=True)
>>date = Column(Date)
>>project_id = Column(Integer, ForeignKey('project.id'))
>>project = relationship("Project", backref=backref('estimations',
>> order_by='Invoice.date'))
>>specific_attr2 = Column(Integer)
>>specific_attr3 = Column(Integer)
>>
>> I'd like to query something like:
>>
>> dbession.query(Task).with_polymorphic([Invoice,
>> Estimation]).join(Invoice.project).join(Estimation.project).join(Project.client)
>>
>> I actually get an error :
>> (1066, "Not unique table/alias: 'project' ")
> OK you need here to think about SQL. Task, Invoice, Estimation are all
> different tables.A particular row in Task will either have an Invoice, or
> Estimation row - but never both. So with_polymorphic() implies an outer join
> from Task to both of these tables.
>
> Once you're outer joining, anything that you join to from those joins, also
> needs to be an outer join else you'll get no rows back when the parent is
> NULL - so we are talking about Invoice.project as well as Estimation.project,
> and also each of those to Project.client - unfortunately you'll have to also
> join to client twice if you really need all of these columns present.The
> "not unique" error refers to the fact that a particular name can only be
> mentioned as a FROM target once in SQL.
>
> So you'll need to alias out those targets, like this:
>
> p1 = aliased(Project)
> p2 = aliased(Project)
>
> then use outerjoins and distinct joins to client:
>
> query(Task).with_polymorphic([Invoice, Estimation]).outerjoin(p1,
> Invoice.project).outerjoin(p2,
> Estimation.Project).outerjoin(p1.client).outerjoin(p2.client)
>
> the above query has a lot of outer joins and is generally not going to
> perform very well. Depending on what you're ultimately trying to do, you may
> want to join to a union of the estimation/invoice->project->client target, if
> you're looking to have a simple path from Task to Client (that is,
> query(Task).join(my_union, Task.id=my_union.c.id), and I'd create my_union
> using Table/select() objects directly for "select client.* from
> invoice_table->project->client UNION select client.* from
> estimation_table->project_client".I'd experiment with SQL directly to see
> what works best.
>
> (also this is joined inheritance, not concrete, since Invoice/Estimation join
> to Task with the "task.id" FK).
>
Thanks a lot, you made the things far clearer for me, I'll give it a try
and I'll give some feedback.
My goal is to query both estimations and invoices (and their related
objects) and being able to filter and order them, I'll see if I succeed.
Cheers,
Gaston
--
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] Concrete inheritance and child relationship query
Hi,
I've got a problem understanding how to handle relationships with
polymorphism
Sorry for the db design (I took it overs as it is ).
I've got a parent class Task
class Task(DBBASE):
__tablename__ = 'task'
id = Column(Integer, primary_key=True)
amount = Column(Integer)
and two child classes
class Invoice(DBBASE):
__tablename__ = 'invoice'
id = Column(Integer, ForeignKey('task.id'), primary_key=True)
date = Column(Date)
project_id = Column(Integer, ForeignKey('project.id'))
project = relationship("Project", backref=backref('invoices',
order_by='Invoice.date'))
specific_attr1 = Column(Integer)
class Esimation(DBBASE):
__tablename__ = 'estimation'
id = Column(Integer, ForeignKey('task.id'), primary_key=True)
date = Column(Date)
project_id = Column(Integer, ForeignKey('project.id'))
project = relationship("Project", backref=backref('estimations',
order_by='Invoice.date'))
specific_attr2 = Column(Integer)
specific_attr3 = Column(Integer)
I'd like to query something like:
dbession.query(Task).with_polymorphic([Invoice,
Estimation]).join(Invoice.project).join(Estimation.project).join(Project.client)
I actually get an error :
(1066, "Not unique table/alias: 'project' ")
1- I've tried to add a aliased=True to the with_polymorphic call, but it
gives me "() got an unexpected keyword argument 'aliased'"
2- I've tried to build two queries and use union_all but then I need to
specify all the columns and there are something like 40 columns to query.
How should I do to build this query ?
Cheers,
Gaston
--
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.
