[prev in list] [next in list] [prev in thread] [next in thread]
List: pyamf-users
Subject: Re: [pyamf-users] SQLAlchemy sessions
From: Shawn Church <shawn () ghs ! l ! google ! com>
Date: 2010-06-09 10:10:08
Message-ID: AANLkTinxs5vNoMbUmKPEoaJlNuPyzNz-yIyjRJ0AXBZd () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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 <bierbaum@manui.de> 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 <bierbaum@manui.de>
> 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 <
> shawn@schurchcomputers.com> 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 - users@pyamf.org
> > > http://lists.pyamf.org/mailman/listinfo/users
> >
> > _______________________________________________
> > PyAMF users mailing list - users@pyamf.org
> > http://lists.pyamf.org/mailman/listinfo/users
> >
> > _______________________________________________
> > PyAMF users mailing list - users@pyamf.org
> > http://lists.pyamf.org/mailman/listinfo/users
>
> _______________________________________________
> PyAMF users mailing list - users@pyamf.org
> http://lists.pyamf.org/mailman/listinfo/users
>
[Attachment #5 (text/html)]
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: <a href="http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqla \
lchemy.orm.relationship">http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.relationship</a>. \
<br> <br> 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). <br><br>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.<br> <br>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.<br> <br>Thanks for \
your help, hope I made sense it's 3AM here.<br><br><br>Shawn<br><br>Test results \
follow:<br><br>>>> import sqlalchemy<br>>>> from sqlalchemy import \
*<br>>>> from sqlalchemy.orm import sessionmaker, \<br> scoped_session, \
relation<br>>>> from sqlalchemy.ext.declarative import \<br> \
declarative_base<br><br>>>> Base = declarative_base()<br>>>> \
Session=scoped_session(sessionmaker())<br> <br>>>> \
sqlalchemy.__version__<br>'0.6.1'<br><br>>>> def setup():<br>... \
Base.metadata.bind = None<br>... Base.metadata.bind = \
"sqlite:///:memory:"<br>... \
Base.metadata.drop_all()<br>
... Base.metadata.create_all()<br>...<br>... s = Session()<br>... test1 = \
s.merge(Test1(junk = "Some junk"))<br>... test2 = s.merge(Test2(other = \
test1))<br>... s.commit()<br>... return s, test1, test2<br> <br>>>> \
class Test1(Base):<br>... __tablename__ = "test1"<br>... id = \
Column(Integer, primary_key = True)<br>... junk = Column(String)<br>... \
others = relation("Test2", lazy = "joined")<br> <br>>>> \
class Test2(Base):<br>... __tablename__ = "test2"<br>... id = \
Column(Integer, primary_key = True)<br>... other_id = Column(Integer, \
ForeignKey("<a href="http://test1.id">test1.id</a>"))<br>
... other = relation("Test1", lazy = \
"joined")<br><br>>>> s, test1, test2 = setup()<br><br>>>> \
test1 #doctest: +ELLIPSIS<br><__main__.Test1 object at \
0x...><br><br>>>> test2 #doctest: +ELLIPSIS<br> <__main__.Test2 object \
at 0x...><br><br>>>> test1.others #doctest: \
+ELLIPSIS<br>[<__main__.Test2 object at 0x...>]<br><br>>>> test2.other \
#doctest: +ELLIPSIS<br><__main__.Test1 object at 0x...><br> <br>>>> \
s.expunge_all()<br><br>>>> test1.others #doctest: \
+ELLIPSIS<br>[<__main__.Test2 object at 0x...>]<br><br>>>> test2.other \
#doctest: +ELLIPSIS<br><__main__.Test1 object at 0x...><br> <br>>>> s, \
test1, test2 = setup()<br>>>> s.expunge_all()<br><br>>>> test1 \
#doctest: +ELLIPSIS<br><__main__.Test1 object at 0x...><br><br>>>> \
test2 #doctest: +ELLIPSIS<br><__main__.Test2 object at 0x...><br> \
<br>>>> test1.others #doctest: +ELLIPSIS,+NORMALIZE_WHITESPACE<br>Traceback \
(most recent call last):<br> ...<br>DetachedInstanceError: Parent instance \
<Test1 at 0...> <br> is not bound to a Session; lazy load operation of <br> \
attribute 'others' cannot proceed<br><br>>>> test2.other #doctest: \
+ELLIPSIS,+NORMALIZE_WHITESPACE<br>Traceback (most recent call last):<br> \
...<br>DetachedInstanceError: Parent instance <Test2 at 0x...> <br> is not \
bound to a Session; lazy load operation of <br> attribute 'other' cannot \
proceed<br><br><br><div class="gmail_quote">On Tue, Jun 8, 2010 at 11:45 PM, Simon \
Bierbaum <span dir="ltr"><<a \
href="mailto:bierbaum@manui.de">bierbaum@manui.de</a>></span> wrote:<br> \
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px \
solid rgb(204, 204, 204); padding-left: 1ex;">You can eager-load attributes by using \
options(undefer()) on the query (see <a \
href="http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading" \
target="_blank">http://www.sqlalchemy.org/docs/mappers.html#deferred-column-loading</a>), \
or you can just manually access them to trigger the lazy loading. There's no \
silver bullet solution that I know of.<br>
<br>
Am 08.06.2010 um 21:05 schrieb Shawn Church:<br>
<div><div></div><div class="h5"><br>
> Yes, that is exactly the problem. If I close the session I cannot use \
lazy-loaded attributes. Is there a way around this?<br> ><br>
> On Tue, Jun 8, 2010 at 12:15 AM, Simon Bierbaum <<a \
href="mailto:bierbaum@manui.de">bierbaum@manui.de</a>> wrote:<br> > 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.<br>
><br>
> Simon<br>
><br>
> Am 07.06.2010 um 23:53 schrieb Shawn Church:<br>
><br>
> > 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.<br> > ><br>
> > On Mon, Jun 7, 2010 at 2:40 PM, Shawn Church <<a \
href="mailto:shawn@schurchcomputers.com">shawn@schurchcomputers.com</a>> \
wrote:<br> > > 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:<br> > ><br>
> > >>> 2010-06-07 13:54:10,945 - pyamf - ERROR - Traceback (most \
recent call last):<br> > > >>> 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__<br> > > >>> \
timezone_offset=timezone_offset)<br> > > >>><br>
> > >>> [Traceback details deleted]<br>
> > >>><br>
> > >>> File \
"/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/strategies.py", \
line 578, in __call__<br> > > >>> (mapperutil.state_str(state), \
self.key)<br> > > >>> DetachedInstanceError: Parent instance <User \
at 0xa21c3ac> is not bound to a Session; lazy load operation of attribute \
'groups' cannot proceed<br> > ><br>
> > In the "address-book" example I do not see the session being \
closed at all. Am I just supposed to leave sessions open?<br> > ><br>
> > _______________________________________________<br>
> > PyAMF users mailing list - <a \
href="mailto:users@pyamf.org">users@pyamf.org</a><br> > > <a \
href="http://lists.pyamf.org/mailman/listinfo/users" \
target="_blank">http://lists.pyamf.org/mailman/listinfo/users</a><br> ><br>
> _______________________________________________<br>
> PyAMF users mailing list - <a \
href="mailto:users@pyamf.org">users@pyamf.org</a><br> > <a \
href="http://lists.pyamf.org/mailman/listinfo/users" \
target="_blank">http://lists.pyamf.org/mailman/listinfo/users</a><br> ><br>
> _______________________________________________<br>
> PyAMF users mailing list - <a \
href="mailto:users@pyamf.org">users@pyamf.org</a><br> > <a \
href="http://lists.pyamf.org/mailman/listinfo/users" \
target="_blank">http://lists.pyamf.org/mailman/listinfo/users</a><br> <br>
_______________________________________________<br>
PyAMF users mailing list - <a href="mailto:users@pyamf.org">users@pyamf.org</a><br>
<a href="http://lists.pyamf.org/mailman/listinfo/users" \
target="_blank">http://lists.pyamf.org/mailman/listinfo/users</a><br> \
</div></div></blockquote></div><br>
_______________________________________________
PyAMF users mailing list - users@pyamf.org
http://lists.pyamf.org/mailman/listinfo/users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic