[sqlalchemy] Re: Many to many self referential relationship.
Hi Michael, As expected the trunk works perfectly. I am also looking into the comparator example you pointed to as this looks like it will help with a couple of other implementation details as well. Thanks again, Martin On Feb 20, 10:25 pm, Martin Pengelly-Phillips [EMAIL PROTECTED] wrote: Thank you Michael - it's always good to get some validation when pushing your own knowledge of a system. I'll check out the trunk tomorrow, give it a whirl and report any findings then. Thanks again for the quick response, Martin On Feb 20, 6:31 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2008, at 1:01 PM, Martin Pengelly-Phillips wrote: I have attempted to solve this by using the concept of 'forwardAssociations' with a backref of 'backwardAssociations' and then a custom property 'associations' that retrieves and sets the real attributes accordingly (let me know if you would like to see this code as well). However, even with this set up I find it hard to figure out how to then perform a query such as: # Get all entities that are in some way associated with entity 2 (without using entity2.associations property) session.query(Entity).filter(or_( Entity.forwardAssociations.any(Entity.id==2), Entity.backwardAssociations.any(Entity.id==2) )).all() The query this generates though uses the same id for both sides of the association table which cannot therefore result in a match: entities.id = entitiesEntities.entity1_id AND entitiesEntities.entity2_id = entities.id AND entities.id = ? well i think everything you're doing is on the right track. The any() operator and its friends have just been repaired in the latest trunk to recognize a self-referential relation and apply the appropriate aliasing to half of the join condition. I haven't tested it with a many-to-many self referential yet but I think it should work. give it a try and let me know what you get; I can add some m2m tests for it and ensure that its working if its not already. we also are working on getting better support for user-defined custom properties going such that they can be seamlessly used within Queries, so that you could also construct your query using your associations property, if you can define how comparison operations should be done. There is a way to do this right now using a slight bit of non-public API, where you can see an example of such inhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/vert... ; the comparable_property allows the injection of a Comparator object from where you can define things like __eq__(), any(), etc. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Behavior question with association proxy with custom collection class
I was exploring the association proxy example in the documentation and
then explored the use of a mapped collection as the custom collection
class with mixed results. When attempting to append, an
AttributeError _AssociationDict object has no attribute append
occurred. Then, I tried subclassing MappedCollection, created my own
append method and have SQLAlchemy instrument it but the results were
the same. Here's the code I was testing it with:
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String,
Table
from sqlalchemy.orm import relation
from sqlalchemy.orm.collections import mapped_collection,
MappedCollection, collection
from sqlalchemy.ext.associationproxy import association_proxy
from turbogears.database import metadata, session
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(64))
)
keywords_table = Table('keywords', metadata,
Column('id', Integer, primary_key=True),
Column('keyword', String(64))
)
userkeywords_table = Table('userkeywords', metadata,
Column('user_id', Integer, ForeignKey(users.id),
primary_key=True),
Column('keyword_id', Integer, ForeignKey(keywords.id),
primary_key=True)
)
def _create_uk(keyword):
return UserKeyword(keyword=keyword)
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
keywords = association_proxy('user_keywords', 'keyword',
creator=_create_uk)
class Keyword(object):
def __init__(self, id, keyword):
self.id = id
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)
class UserKeyword(object):
def __init__(self, user=None, keyword=None):
self.user = user
self.keyword = keyword
session.mapper(Keyword, keywords_table)
session.mapper(User, users_table, properties={
'user_keywords':relation(UserKeyword)
})
session.mapper(UserKeyword, userkeywords_table, properties={
'user':relation(User),
'keyword':relation(Keyword)
})
user = User('1', 'number')
kw1 = Keyword('0', 'zero')
user.user_keywords.append(UserKeyword(user, kw1))
for kw in (Keyword('1', 'one'), Keyword('2', 'two'), Keyword('3',
'three')):
user.keywords.append(kw)
Portion of code changed with mapped collection (which won't work):
session.mapper(User, users_table, properties={
#'user_keywords':relation(UserKeyword)
'user_keywords':relation(UserKeyword,
collection_class=mapped_collection(lambda item: item.keyword.id))
})
Code for customized collection (also won't work):
class MyCollection(MappedCollection):
def __init__(self, keyfunc=lambda item: item.id):
MappedCollection.__init__(self, keyfunc=keyfunc)
@collection.appender
def append(self, item):
MappedCollection.set(self, item)
@collection.remover
def remove(self, item):
MappedCollection.remove(self, item)
u_collection = lambda: MyCollection(keyfunc=lambda item:
item.keyword.id)
session.mapper(User, users_table, properties={
#'user_keywords':relation(UserKeyword)
'user_keywords':relation(UserKeyword,
collection_class=u_collection)
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Behavior question with association proxy with custom collection class
I assume a getset_factory is needed for the association proxy. Any
examples?
On Feb 21, 8:38 am, Ronald Lew [EMAIL PROTECTED] wrote:
I was exploring the association proxy example in the documentation and
then explored the use of a mapped collection as the custom collection
class with mixed results. When attempting to append, an
AttributeError _AssociationDict object has no attribute append
occurred. Then, I tried subclassing MappedCollection, created my own
append method and have SQLAlchemy instrument it but the results were
the same. Here's the code I was testing it with:
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String,
Table
from sqlalchemy.orm import relation
from sqlalchemy.orm.collections import mapped_collection,
MappedCollection, collection
from sqlalchemy.ext.associationproxy import association_proxy
from turbogears.database import metadata, session
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(64))
)
keywords_table = Table('keywords', metadata,
Column('id', Integer, primary_key=True),
Column('keyword', String(64))
)
userkeywords_table = Table('userkeywords', metadata,
Column('user_id', Integer, ForeignKey(users.id),
primary_key=True),
Column('keyword_id', Integer, ForeignKey(keywords.id),
primary_key=True)
)
def _create_uk(keyword):
return UserKeyword(keyword=keyword)
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
keywords = association_proxy('user_keywords', 'keyword',
creator=_create_uk)
class Keyword(object):
def __init__(self, id, keyword):
self.id = id
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)
class UserKeyword(object):
def __init__(self, user=None, keyword=None):
self.user = user
self.keyword = keyword
session.mapper(Keyword, keywords_table)
session.mapper(User, users_table, properties={
'user_keywords':relation(UserKeyword)})
session.mapper(UserKeyword, userkeywords_table, properties={
'user':relation(User),
'keyword':relation(Keyword)
})
user = User('1', 'number')
kw1 = Keyword('0', 'zero')
user.user_keywords.append(UserKeyword(user, kw1))
for kw in (Keyword('1', 'one'), Keyword('2', 'two'), Keyword('3',
'three')):
user.keywords.append(kw)
Portion of code changed with mapped collection (which won't work):
session.mapper(User, users_table, properties={
#'user_keywords':relation(UserKeyword)
'user_keywords':relation(UserKeyword,
collection_class=mapped_collection(lambda item: item.keyword.id))
})
Code for customized collection (also won't work):
class MyCollection(MappedCollection):
def __init__(self, keyfunc=lambda item: item.id):
MappedCollection.__init__(self, keyfunc=keyfunc)
@collection.appender
def append(self, item):
MappedCollection.set(self, item)
@collection.remover
def remove(self, item):
MappedCollection.remove(self, item)
u_collection = lambda: MyCollection(keyfunc=lambda item:
item.keyword.id)
session.mapper(User, users_table, properties={
#'user_keywords':relation(UserKeyword)
'user_keywords':relation(UserKeyword,
collection_class=u_collection)
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Behavior question with association proxy with custom collection class
Ronald Lew wrote:
I wanted to use a mapped collection or a customized collection instead
of the default list for the collection class and have the keyword's id
as the key. In addition, I wanted to keep using the append but that
seems impossible now since I cannot fool association dict to use it.
I wanted to not have to change the below code:
for kw in (Keyword('1','one'), Keyword('2','two'),
Keyword('3','three')):
user.keywords.append(kw) # cannot use this any more
The association proxy accepts the proxy implementation type as an
optional argument. You can subclass the proxy dict type and add an
append method. See:
http://www.sqlalchemy.org/docs/04/sqlalchemy_ext_associationproxy.html#docstrings_sqlalchemy.ext.associationproxy_AssociationProxy
and:
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/ext/associationproxy.py#L227
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: DPAPI Error
Mike,
Sorry to get back to you on this so late. I installed 0.4.3 and things
seem to be humming along nicely. Should that be the case or should I
expect some madness from my app soon?
Thanks a million!
Jon
On Jan 30, 1:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
hey jon -
OK, I found an issue that is very likely to be what you are
experiencing; easy to fix as always but unless you can run on SVN
trunk r4106, you'll have to workaround it.
There may be a schema identifier somewhere in your app that contains
the identifier 'roleseq' as a unicode string, which is getting cached
as a u''.
So if you can't hunt that down (which you shouldn't have to), you can
force it to cache the non-unicode string for now if you do this to
your engine as early as possible (i.e. before the incorrect value gets
cached):
engine.dialect.identifier_preparer.format_sequence(Sequence('roleseq'))
hope thats the issue.
- mike
On Jan 30, 2008, at 3:14 PM, jon wrote:
Hi Mike,
Thanks for your patience...here is the entry I have for that table in
model/__init__.py
role_table = Table('role', metadata,
Column('roleseq', Integer, Sequence('roleseq'),
primary_key=True),
I know that I specifically didn't set things up for Unicode in this
app either...this file is the only one that contains the Sequence
declaration.
Thanks,
Jon
On Jan 30, 11:34 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jan 30, 2008, at 2:10 PM, jon wrote:
Thanks for getting back to me and apologies for the stacktrace
barf ;-)
One thing...I have the following line in my environment.py:
config['pylons.g'].sa_engine = engine_from_config(config,
'sqlalchemy.', convert_unicode=True, pool_size=1, max_overflow=3)
I can turn on/off the convert_unicode option and I still get the
same
error. I spoke with one of our DBAs and he said the results of
roleseq.nextval is a number. SA is looking for a String here or
None,
according to the exception error:
exceptions.TypeError: expecting None or a string
Sooo...is this still an SA thing and if so what do you suggest as a
workaround?
jon -
none of that has anything to do with the error message here. You
need
to grep thorugh your code for the specific string :
Sequence(u'roleseq'), or something equivalent. I know its there
because the Oracle dialect does not invent or reflect any sequence
names. You need to make that sequence name into a regular string
object; as above you'd take out the u inside the parenthesis.
let me know when you find it as I'd like to confirm this is the
source
of the issuethanks !
- mike
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
