[sqlalchemy] Automatically create secondary tables for many to many relationship?
Given the arbitrary example below, I can't ever recall actually using the
FooKeyword association table other than to set up mappings. I came up with
a brute force method to generate the secondary table for me
automatically, and I'm hoping someone can show me a better way to do this.
My goal was to take something like this (imports excluded for brevity):
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key = True)
name = Column(String, unique = True, nullable = False)
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key = True)
... snip ...
keywords = relationship(Keyword, secondary='foo_keyword')
class FooKeyword(Base):
__tablename__ = 'foo_keyword'
foo_id = Column(Integer, ForeignKey('foo.id'), primary_key = True,
nullable = False)
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key
= True, nullable = False)
And replace it with something like this:
class Keyword(Base):
snip (same as before)
class Foo(Base):
snip
keywords = generate_many_to_many_for_me('Foo', 'Keyword')
So in the code above, the FooKeyword table would get generated
automatically, and a relationship like relationship(Keyword,
secondary='foo_keyword') would automatically get returned.
Here is my super naive solution that sort of works:
So below here, a call like secondary_relationship(Foo, Keyword)
would automatically generate a class called FooKeyword that is
mapped to a table called 'foo_keyword'. It then uses these to return a
relationship equivalent to relationship(Keyword,
secondary='foo_keyword').
You can specify the generated tablename using the tblname arg like so:
secondary_relationship(Foo, Keyword, my_tablename)
def secondary_relationship(cls1, cls2, tblname = None):
new_class_name = cls1.__name__ + cls2.__name__
tn1, tn2 = cls1.__tablename__, cls2.__tablename__
tbl = '%s_%s' % (tn1, tn2) if tblname is None else tblname
# Generate the FooKeyword table
t = type(new_class_name, (Base,), {
'__tablename__': tbl,
tn1 + '_id': Column(Integer,
ForeignKey('%s.id' % tn1),
primary_key = True, nullable = False),
tn2 + '_id': Column(Integer,
ForeignKey('%s.id' % tn2),
primary_key = True, nullable = False)
})
return relationship(cls2, secondary = tbl)
# the Keyword and Foo classes identical to first example...
class Keyword(Base):
snip (same as before)
class Foo(Base):
snip (same as before except keywords defined below)
# And this builds the many to many for us without having to build
FooKeyword class...
Foo.keywords = secondary_relationship(Foo, Keyword)
# You could also do like below to control the tablename generated:
Foo.keywords = secondary_relationship(Foo, Keyword, 'my_tablename')
This actually works, but you can't use this until *after* the definition
for Foo. I'm looking for a way to do this inline in Foo like so:
class Foo(Base):
snip
keywords = secondary_relationship('Foo', 'Keyword')
Is there a better way? I have spent the better part of the day reading the
source code to see how the declarative extension allows string class names
for relationship, but I still haven't been able to figure this out...
Thanks,
Jeff Peck
--
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/groups/opt_out.
Re: [sqlalchemy] Automatically create secondary tables for many to many relationship?
On Sep 22, 2013, at 5:24 PM, jpeck peck.j...@gmail.com wrote:
And replace it with something like this:
class Keyword(Base):
snip (same as before)
class Foo(Base):
snip
keywords = generate_many_to_many_for_me('Foo', 'Keyword')
there's a recipe for this at this blog post, you might consider building on
it's general example:http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/
the missing link is to just use mapping events to set things up at the
appropriate time.
signature.asc
Description: Message signed with OpenPGP using GPGMail
RE: [sqlalchemy] Automatically create secondary tables for many to many relationship?
And replace it with something like this:
class Keyword(Base):
snip (same as before)
class Foo(Base):
snip
keywords = generate_many_to_many_for_me('Foo', 'Keyword')
there's a recipe for this at this blog post, you might consider building
on it's general example:
http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/
the missing link is to just use mapping events to set things up at the
appropriate time.
Michael,
Your cats are awesome! I remember reading this the day you put it out there,
but I just didn't understand it at the time. I've been going over this and
now I understand it perfectly! Any chance of pulling some of this back into
sqlalchemy? many_to_many alone saves quite a bit of boilerplate code.
The only change I made was to let many_to_many set up default local / remote
columns on the secondary table so you can just do:
class Video(Base):
keywords = many_to_many(Keyword, video_keyword)
class many_to_many(DeferredProp):
Generates a many to many relationship.
def __init__(self, target, tablename, **kw):
self.target = target
self.tablename = tablename
self.local = kw.get('local')
self.remote = kw.get('remote')
self.kw = kw
def _config(self, cls, key):
Create an association table between parent/target
as well as a relationship().
target_cls = cls._decl_class_registry[self.target]
local_pk = list(cls.__table__.primary_key)[0]
target_pk = list(target_cls.__table__.primary_key)[0]
if not self.local:
self.local = cls.__tablename__.lower() + _id
if not self.remote:
self.remote = target_cls.__tablename__.lower() + _id
t = Table(
self.tablename,
cls.metadata,
Column(self.local, ForeignKey(local_pk),
primary_key=True),
Column(self.remote, ForeignKey(target_pk),
primary_key=True),
keep_existing=True
)
rel = relationship(target_cls,
secondary=t,
collection_class=self.kw.get('collection_class', set)
)
setattr(cls, key, rel)
self._setup_reverse(key, rel, target_cls)
Thanks,
Jeff Peck
--
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/groups/opt_out.
Re: [sqlalchemy] Automatically create secondary tables for many to many relationship?
On Sep 22, 2013, at 10:11 PM, Jeff Peck peck.j...@gmail.com wrote:
And replace it with something like this:
class Keyword(Base):
snip (same as before)
class Foo(Base):
snip
keywords = generate_many_to_many_for_me('Foo', 'Keyword')
there's a recipe for this at this blog post, you might consider building
on it's general example:
http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/
the missing link is to just use mapping events to set things up at the
appropriate time.
Michael,
Your cats are awesome! I remember reading this the day you put it out there,
but I just didn't understand it at the time. I've been going over this and
now I understand it perfectly! Any chance of pulling some of this back into
sqlalchemy? many_to_many alone saves quite a bit of boilerplate code.
i dont really think there's any near term chance, because it makes for a more
confusing API. not much long term chance either unless I stumble across some
way that it could be not in conflict with how everything else works. the point
of the blog post is that these helpers can be built out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
