support sqlalchemy orm objects to be passed to pyamf

[mvtellingen]

The attached patch fixes the issue that amf didn't support sqlalchemy orm objects. Basically the patch ignores all 'private' (starting with an underscore) attributes since sqlalchemy uses those to store metadata.

[thijs]

A few things: getting rid of all underscored attrs is probably not the best solution. we have an adapters package, and it would be nice to have a sqlalchemy adapter, that handles orm objects when they're encountered in a stream. second thing: there are no unit tests whatsoever, so the patch as it is now is more a hack.

So if you can dig into the adapters package, write a sqlalchemy adapter, like we did with google, and add the required unit tests we can consider the patch for the trunk. If you have questions, use the dev MailingList or the IrcChannel.

[mvtellingen]

Updated patch to move to the suggested adaptor approach. Also changed pyamf/init.py so that it doesn't cache the required write method when the type is a CustomTypeFunc?. This is actually required to make the sqlalchemy adaptor work, and caching the result of a possibly dynamic function is just not logical.

[thijs]

I ran the unit tests with your patch against the trunk (r1464) and SQLAlchemy 0.5beta1 with the following errors:

===============================================================================
[ERROR]: adapters.test_sqlalchemy.SATestCase.test_array_collection

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/unittest.py", line 260, in run
    testMethod()
  File "/pyamf/branches/sqlalchemy-adapter-277/pyamf/tests/adapters/test_sqlalchemy.py", line 54, in test_array_collection
    encoder.writeElement(ac)
  File "/pyamf/branches/sqlalchemy-adapter-277/pyamf/amf3.py", line 1243, in writeElement
    raise pyamf.EncodeError, "Unable to encode '%r'" % data
pyamf.EncodeError: Unable to encode '<flex.messaging.io.ArrayCollection {0: <adapters.test_sqlalchemy.User object at 0xe28ab0>, 1: <adapters.test_sqlalchemy.User object at 0xe28d90>, 2: <adapters.test_sqlalchemy.User object at 0xe28d70>, 3: <adapters.test_sqlalchemy.User object at 0xe28850>, 4: <adapters.test_sqlalchemy.User object at 0xe28830>, 5: <adapters.test_sqlalchemy.User object at 0xe288f0>, 6: <adapters.test_sqlalchemy.User object at 0xe283b0>, 7: <adapters.test_sqlalchemy.User object at 0xe287b0>, 8: <adapters.test_sqlalchemy.User object at 0xe286f0>, 9: <adapters.test_sqlalchemy.User object at 0xe280d0>, 10: <adapters.test_sqlalchemy.User object at 0xe28650>, 11: <adapters.test_sqlalchemy.User object at 0xe28510>, 12: <adapters.test_sqlalchemy.User object at 0xe285d0>, 13: <adapters.test_sqlalchemy.User object at 0xe28470>, 14: <adapters.test_sqlalchemy.User object at 0xe28550>, 15: <adapters.test_sqlalchemy.User object at 0xe284d0>, 16: <adapters.test_sqlalchemy.User object at 0xe283f0>, 17: <adapters.test_sqlalchemy.User object at 0xe280b0>, 18: <adapters.test_sqlalchemy.User object at 0xe28050>, 19: <adapters.test_sqlalchemy.User object at 0xe2fb50>}>'
===============================================================================
[ERROR]: adapters.test_sqlalchemy.SATestCase.test_model

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/unittest.py", line 260, in run
    testMethod()
  File "/pyamf/branches/sqlalchemy-adapter-277/pyamf/tests/adapters/test_sqlalchemy.py", line 40, in test_model
    encoder.writeElement(my_sa_user)
  File "/pyamf/branches/sqlalchemy-adapter-277/pyamf/amf3.py", line 1243, in writeElement
    raise pyamf.EncodeError, "Unable to encode '%r'" % data
pyamf.EncodeError: Unable to encode '<adapters.test_sqlalchemy.User object at 0xe28270>'
-------------------------------------------------------------------------------
Ran 463 tests in 2.227s

FAILED (errors=2, successes=461)

[mvtellingen]

Nice catch, i've attached a new patch which also works on trunk of sqlalchemy (r4882). The problem was that sqlalchemy 0.4 creates a private variable '_state' in the given object, but 0.5 seems to have renamed it to '_sa_instance_state'

[thijs]

I tested the new patch and all tests pass, nice work!

Ran 463 tests in 3.160s

PASSED (successes=463)

[gudbergur]

error when passing elixir/sa object through pyamf second time

[gudbergur]

just added pyamf-sqlalchemy-error.zip which describes itself as when i run the twisted server, it works to pass the orm object once to the client but if i try more oftenit thows error regarding _sa_state_instance.

[thijs]

assigning back to mvtellingen

[mvtellingen]

Ok thanks for the testcase. I've updated the test somewhat to work without requiring mysql.

The problem was that the adapter removed properties in the object passed to it. I now make a copy of the object using copy.copy() and removing the properties of that object so that the original object never gets edited.

Also note that if you want to modify the retrieved object on the client and pass it back to the server, you have to merge it back in your sqlalchemy session using session.merge() before you can call .update() on the server

[mvtellingen]

modified testcase to work without mysql

[thijs]

Replying to gudbergur:

just added pyamf-sqlalchemy-error.zip which describes itself as when i run the twisted server, it works to pass the orm object once to the client but if i try more oftenit thows error regarding _sa_state_instance.

Did mvtellingen's latest patch fix that issue for you gudbergur?

[gudbergur]

Replying to thijs:

Replying to gudbergur:

just added pyamf-sqlalchemy-error.zip which describes itself as when i run the twisted server, it works to pass the orm object once to the client but if i try more oftenit thows error regarding _sa_state_instance.

Did mvtellingen's latest patch fix that issue for you gudbergur?

Yep, it fixed my problem. However, earlier this evening i noticed another issue when pyamf is trying to encode a SQLAlchemy object that has an attribute with multiple other SQLAlchemy objects, like Person has many Pets. I did a little digging, and ended up adding this to _sqlalchemy.py which fixed it:

def writeSAInstrList(obj):

robj = [] for eobj in obj:

robj.append(writeSAObject(eobj))

return robj

def isSqlAlchemyInstrList(data):

if not isinstance(data, object):

return False

return isinstance(data, sqlalchemy.orm.collections.InstrumentedList?)

pyamf.add_type(isSqlAlchemyInstrList, writeSAInstrList)

[gudbergur]

Um,

def writeSAInstrList(obj):
	robj = []
	for eobj in obj:
		robj.append(writeSAObject(eobj))
	return robj

def isSqlAlchemyInstrList(data):
	if not isinstance(data, object):
		return False
	return isinstance(data, sqlalchemy.orm.collections.InstrumentedList)

pyamf.add_type(isSqlAlchemyInstrList, writeSAInstrList)

[mvtellingen]

Replying to gudbergur:

Replying to thijs:

Replying to gudbergur:

just added pyamf-sqlalchemy-error.zip which describes itself as when i run the twisted server, it works to pass the orm object once to the client but if i try more oftenit thows error regarding _sa_state_instance.

Did mvtellingen's latest patch fix that issue for you gudbergur?

Yep, it fixed my problem. However, earlier this evening i noticed another issue when pyamf is trying to encode a SQLAlchemy object that has an attribute with multiple other SQLAlchemy objects, like Person has many Pets.

Do you have a simple example/testcase for this? Thanks

[mvtellingen]

gudbergur, could you try the latest patch? It checks if properties of the SQLAlchemy object are of instance sqlalchemy.orm.collections.InstrumentedList? and converts it to a list in that case while updating back references.

[mvtellingen]

update to trunk (r1525)

[mvtellingen]

I've added the patch in the sqlalchemy-adapter-277 branch

[gudbergur]

Replying to mvtellingen:

gudbergur, could you try the latest patch? It checks if properties of the SQLAlchemy object are of instance sqlalchemy.orm.collections.InstrumentedList? and converts it to a list in that case while updating back references.

This seems to work flawlessly :)

[thijs]

Our build farm now has the ability to build branches as well, and while testing the branch, the buildbot reported some errors for Python 2.6 / SQLAlchemy 0.4.6.

[gudbergur]

Should be noted i tested this with trunk version of both sqlalchemy and elixir

[mvtellingen]

Yes I specifically added that testcase while knowing it doesn't work with sqlalchemy 0.4. I don't think i'm able to fix it, since it seems to be more of a problem in sqlalchemy 0.4

Trunk of sqlalchemy (0.5) doesn't have this problem, perhaps we should require sqlalchemy 0.5 (although most things do work in 0.4)

[thijs]

We're planning to make pyamf 0.5 the first release to include the sqlalchemy adapter. That way we can release 0.4 asap and not delay it any longer because of this adapter.

[papagr]

A patch to the adapter by Dave Thompson and some improvements by Nikos Papagrigoriou

[papagr]

Please, have a look at the attached patch. It was discussed on http://lists.pyamf.org/archives/dev/2008-September/000489.html).