SQLAlchemy sessions

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLAlchemy sessions

Shawn Church-3
I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:

>>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
>>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
>>>     timezone_offset=timezone_offset)
>>>
>>> [Traceback details deleted]
>>>
>>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
>>>     (mapperutil.state_str(state), self.key)
>>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed

In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?

_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy sessions

Shawn Church-3
BTW I am using SA 0.6.1 and PyAMF 0.5.1.  I'm trying to return a Declarative-mapped class with a lazy relationship.

On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <[hidden email]> wrote:
I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:

>>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
>>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
>>>     timezone_offset=timezone_offset)
>>>
>>> [Traceback details deleted]
>>>
>>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
>>>     (mapperutil.state_str(state), self.key)
>>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed

In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?


_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy sessions

Simon Bierbaum-2
I believe the error you see is not happening when you call close(), but afterwards when PyAMF tries to encode the instance, attempts to lazy load some attribute which is not loaded yet and promptly fails since the session has already been closed. Just make sure everything is loaded before you close the session and you should be fine.

Simon

Am 07.06.2010 um 23:53 schrieb Shawn Church:

> BTW I am using SA 0.6.1 and PyAMF 0.5.1.  I'm trying to return a Declarative-mapped class with a lazy relationship.
>
> On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <[hidden email]> wrote:
> I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:
>
> >>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
> >>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
> >>>     timezone_offset=timezone_offset)
> >>>
> >>> [Traceback details deleted]
> >>>
> >>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
> >>>     (mapperutil.state_str(state), self.key)
> >>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed
>
> In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users

_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy sessions

Shawn Church-3
Yes,  that is exactly the problem.  If I close the session I cannot use lazy-loaded attributes. Is there a way around this?

On Tue, Jun 8, 2010 at 12:15 AM, Simon Bierbaum <[hidden email]> wrote:
I believe the error you see is not happening when you call close(), but afterwards when PyAMF tries to encode the instance, attempts to lazy load some attribute which is not loaded yet and promptly fails since the session has already been closed. Just make sure everything is loaded before you close the session and you should be fine.

Simon

Am 07.06.2010 um 23:53 schrieb Shawn Church:

> BTW I am using SA 0.6.1 and PyAMF 0.5.1.  I'm trying to return a Declarative-mapped class with a lazy relationship.
>
> On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <[hidden email]> wrote:
> I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:
>
> >>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
> >>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
> >>>     timezone_offset=timezone_offset)
> >>>
> >>> [Traceback details deleted]
> >>>
> >>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
> >>>     (mapperutil.state_str(state), self.key)
> >>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed
>
> In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users

_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users


_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy sessions

Simon Bierbaum-2
You can eager-load attributes by using options(undefer()) on the query (see http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading), or you can just manually access them to trigger the lazy loading. There's no silver bullet solution that I know of.

Am 08.06.2010 um 21:05 schrieb Shawn Church:

> Yes,  that is exactly the problem.  If I close the session I cannot use lazy-loaded attributes. Is there a way around this?
>
> On Tue, Jun 8, 2010 at 12:15 AM, Simon Bierbaum <[hidden email]> wrote:
> I believe the error you see is not happening when you call close(), but afterwards when PyAMF tries to encode the instance, attempts to lazy load some attribute which is not loaded yet and promptly fails since the session has already been closed. Just make sure everything is loaded before you close the session and you should be fine.
>
> Simon
>
> Am 07.06.2010 um 23:53 schrieb Shawn Church:
>
> > BTW I am using SA 0.6.1 and PyAMF 0.5.1.  I'm trying to return a Declarative-mapped class with a lazy relationship.
> >
> > On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <[hidden email]> wrote:
> > I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:
> >
> > >>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
> > >>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
> > >>>     timezone_offset=timezone_offset)
> > >>>
> > >>> [Traceback details deleted]
> > >>>
> > >>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
> > >>>     (mapperutil.state_str(state), self.key)
> > >>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed
> >
> > In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?
> >
> > _______________________________________________
> > PyAMF users mailing list - [hidden email]
> > http://lists.pyamf.org/mailman/listinfo/users
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users

_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy sessions

Shawn Church-3
I thought that options(undefer()) referred to columns that are deferred (lazy loaded) as opposed to relationships.  My understanding is the Column(.... lazy = True | False | "select", "joined, etc) controlled the lazy loading of relationships: http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relationship

As the following test shows lazy = False does NOT work but accessing the individual attributes before the expunge does.  According to the documentation: "lazy=(sic)‘joined’ - items should be loaded “eagerly” in the same query as that of the parent, using a JOIN or LEFT OUTER JOIN"  and lazy=False is a synonym for lazy = 'joined' (I ran it both ways with the same results). 

I think I will refer this to the SQLAlchemy list for clarification.  In the mean time I will just NOT return relationships through my gateway.  I can just as easily write a AS3 function to load the data as needed.  Since I am using tables with multiple relationships I do not want ALL of the data loaded ALL of the time (i.e. I would need to access my Users.groups attribute AND the Group.permissions attributes for every group,  in addition every table has a reference back to Users such as Group.modified_by that would also have to be referenced.

What I WANT to happen is IF I access an attribute when I load an object then it is returned to Flex (as a scaler or a list).  If I don't access the attribute I just want it set to None.  I may check the PyAMF code to see what this would involve.

Thanks for your help,  hope I made sense it's 3AM here.


Shawn

Test results follow:

>>> import sqlalchemy
>>> from sqlalchemy import *
>>> from sqlalchemy.orm import sessionmaker, \
        scoped_session, relation
>>> from sqlalchemy.ext.declarative import \
        declarative_base

>>> Base = declarative_base()
>>> Session=scoped_session(sessionmaker())

>>> sqlalchemy.__version__
'0.6.1'

>>> def setup():
...     Base.metadata.bind = None
...     Base.metadata.bind = "sqlite:///:memory:"
...     Base.metadata.drop_all()
...     Base.metadata.create_all()
...
...     s = Session()
...     test1 = s.merge(Test1(junk = "Some junk"))
...     test2 = s.merge(Test2(other = test1))
...     s.commit()
...     return s, test1, test2

>>> class Test1(Base):
...     __tablename__ = "test1"
...     id = Column(Integer, primary_key = True)
...     junk = Column(String)
...     others = relation("Test2", lazy = "joined")

>>> class Test2(Base):
...     __tablename__ = "test2"
...     id = Column(Integer, primary_key = True)
...     other_id = Column(Integer, ForeignKey("test1.id"))
...     other = relation("Test1", lazy = "joined")

>>> s, test1, test2 = setup()

>>> test1 #doctest: +ELLIPSIS
<__main__.Test1 object at 0x...>

>>> test2 #doctest: +ELLIPSIS
<__main__.Test2 object at 0x...>

>>> test1.others #doctest: +ELLIPSIS
[<__main__.Test2 object at 0x...>]

>>> test2.other #doctest: +ELLIPSIS
<__main__.Test1 object at 0x...>

>>> s.expunge_all()

>>> test1.others #doctest: +ELLIPSIS
[<__main__.Test2 object at 0x...>]

>>> test2.other #doctest: +ELLIPSIS
<__main__.Test1 object at 0x...>

>>> s, test1, test2 = setup()
>>> s.expunge_all()

>>> test1 #doctest: +ELLIPSIS
<__main__.Test1 object at 0x...>

>>> test2 #doctest: +ELLIPSIS
<__main__.Test2 object at 0x...>

>>> test1.others #doctest: +ELLIPSIS,+NORMALIZE_WHITESPACE
Traceback (most recent call last):
    ...
DetachedInstanceError: Parent instance <Test1 at 0...>
    is not bound to a Session; lazy load operation of
    attribute 'others' cannot proceed

>>> test2.other #doctest: +ELLIPSIS,+NORMALIZE_WHITESPACE
Traceback (most recent call last):
    ...
DetachedInstanceError: Parent instance <Test2 at 0x...>
    is not bound to a Session; lazy load operation of
    attribute 'other' cannot proceed


On Tue, Jun 8, 2010 at 11:45 PM, Simon Bierbaum <[hidden email]> wrote:
You can eager-load attributes by using options(undefer()) on the query (see http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading), or you can just manually access them to trigger the lazy loading. There's no silver bullet solution that I know of.

Am 08.06.2010 um 21:05 schrieb Shawn Church:

> Yes,  that is exactly the problem.  If I close the session I cannot use lazy-loaded attributes. Is there a way around this?
>
> On Tue, Jun 8, 2010 at 12:15 AM, Simon Bierbaum <[hidden email]> wrote:
> I believe the error you see is not happening when you call close(), but afterwards when PyAMF tries to encode the instance, attempts to lazy load some attribute which is not loaded yet and promptly fails since the session has already been closed. Just make sure everything is loaded before you close the session and you should be fine.
>
> Simon
>
> Am 07.06.2010 um 23:53 schrieb Shawn Church:
>
> > BTW I am using SA 0.6.1 and PyAMF 0.5.1.  I'm trying to return a Declarative-mapped class with a lazy relationship.
> >
> > On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <[hidden email]> wrote:
> > I'm trying to use the SQLAlchemy adapter.  Everything seems to work OK until I try to close the session (as recommended by SQLAlcheny docs) an then I get:
> >
> > >>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most recent call last):
> > >>>   File "/usr/local/lib/python2.6/dist-packages/PyAMF-0.5.1-py2.6-linux-i686.egg/pyamf/remoting/gateway/wsgi.py", line 155, in __call__
> > >>>     timezone_offset=timezone_offset)
> > >>>
> > >>> [Traceback details deleted]
> > >>>
> > >>>   File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", line 578, in __call__
> > >>>     (mapperutil.state_str(state), self.key)
> > >>> DetachedInstanceError: Parent instance <User at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute 'groups' cannot proceed
> >
> > In the "address-book" example I do not see the session being closed at all.  Am I just supposed to leave sessions open?
> >
> > _______________________________________________
> > PyAMF users mailing list - [hidden email]
> > http://lists.pyamf.org/mailman/listinfo/users
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users
>
> _______________________________________________
> PyAMF users mailing list - [hidden email]
> http://lists.pyamf.org/mailman/listinfo/users

_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users


_______________________________________________
PyAMF users mailing list - [hidden email]
http://lists.pyamf.org/mailman/listinfo/users