[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.
[sqlalchemy] How to map to read only descriptor
I am trying to map to a simple read only property. According to the
docs, I *think* I am supposed to use synonym. The problem is that I am
getting a None value for the mapped descriptor's column.
For example:
import datetime
from sqlalchemy import Column, Table, Integer, String, MetaData,
create_engine
from sqlalchemy.orm import mapper, sessionmaker, synonym
meta = MetaData()
foo_table = Table('foo', meta,
Column('id', String(3), primary_key=True),
Column('description', String(64), nullable=False),
Column('calculated_value', Integer, nullable=False),
)
class Foo(object):
def __init__(self, id, description):
self.id = id
self.description = description
@property
def calculated_value(self):
self._calculated_value = datetime.datetime.now().second + 10
return self._calculated_value
mapper(Foo, foo_table,
properties = {
'calculated_value' : synonym('_calculated_value',
map_column=True)
}
)
engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
meta.create_all(bind=engine)
session = Session()
session.add( Foo('xyz', 'test only') )
session.commit()
Since self._calculated_value is created within the property, it does
not exist until you actually access the property. How do I set this up
to get sqlalchemy to go use the actual return value of the property
when session.commit is called?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to map to read only descriptor
On Jun 11, 10:13 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 11, 2010, at 5:16 AM, jpeck wrote:
I am trying to map to a simple read only property. According to the
docs, I *think* I am supposed to use synonym. The problem is that I am
getting a None value for the mapped descriptor's column.
For example:
import datetime
from sqlalchemy import Column, Table, Integer, String, MetaData,
create_engine
from sqlalchemy.orm import mapper, sessionmaker, synonym
meta = MetaData()
foo_table = Table('foo', meta,
Column('id', String(3), primary_key=True),
Column('description', String(64), nullable=False),
Column('calculated_value', Integer, nullable=False),
)
class Foo(object):
def __init__(self, id, description):
self.id = id
self.description = description
�...@property
def calculated_value(self):
self._calculated_value = datetime.datetime.now().second + 10
return self._calculated_value
mapper(Foo, foo_table,
properties = {
'calculated_value' : synonym('_calculated_value',
map_column=True)
}
)
wouldn't this be simpler ?
class Foo(object):
def __init__(self, id, description):
self.id = id
self.description = description
self.calculated_value = datetime.now().second + 10
mapper(Foo, foo_table)
want it read only ?
class Foo(object):
def __init__(self, id, description):
self.id = id
self.description = description
self._calculated_value = datetime.now().second + 10
@property
def calculated_value(self):
return self._calculated_value
mapper(Foo, foo_table,
properties={'_calculated_value':foo_table.c.calculated_value})
another option is to stick datetime.now().second + 10 into theTable
directly as a default for the Column. If it were still empty at flush
time, it would get persisted at that point.
Michael - thanks for the response. My original post was just a
contrived example, and I should probably have included something
closer to what I was actually doing. In my production code I was using
the read only descriptor to iterate over a list of objects to
calculate a value. I need the property to calculate the value on the
fly in case something gets added/deleted from the list of objects. The
issue I had was that I needed the property to fire just before
insertion into the database to update the value referenced by synonym.
I ended up using MapperExtension to access the property before the
insertion occurred...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: 0.6 Beta 3: specifying bindparams in update()
Michael - Thank you so much for the explanation. Rereading this error message now makes perfect sense and this is a really good change. Also - thanks for the explanation about implicit vs explicit bindparams. I am embarrassed to admit that I actually did not know this, and I use the expression language 90% of the time! I must have picked this up somewhere and just always did it that way. Thanks Again, Jeff Peck On Apr 12, 10:30 am, Michael Bayer mike...@zzzcomputing.com wrote: Michael Bayer wrote: I will add further detail to the error message. the new message is: CompileError: bindparam() name 'x' is reserved for automatic usage in the VALUES or SET clause of this insert/update statement. Please use a name other than column name when using bindparam() with insert() or update() (for example, 'b_x'). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: in_() with composite primary key
On Jan 25, 3:08 pm, Michael Bayer mike...@zzzcomputing.com wrote: jpeck wrote: Depending on your database, you may have a function-style row constructor that does what you want. For example, PostgreSQL treats (a, b) and ROW(a, b) as equivalent. If this works for you, then I think you can then use SQLAlchemy's func object: select([...]).where(func.row(a, b).in_([func.row(a1, b1), func.row(a2, b2)])) I have used func.row to build row objects, but I haven't used it with the in_ operator, so YMMV. Conor - that was exactly what I was looking for! Much nicer than generating via text(). Sorry for top posting before! (I lose the internets today). I just added `tuple_()` into trunk since its about time we had this. http://www.sqlalchemy.org/docs/06/reference/sqlalchemy/expressions.ht... Michael - you are the man! I'm going to try this out first thing in the morning. I've been following this list for the past couple of years, and you always seem to come through for people. This may sound funny, but what makes or breaks a software component for me is the quality of people working on the project. People like Tom Lane are why I choose Postgresql, and you are the reason I choose SQLAlchemy. Thanks for all of your hard work! Jeff Peck -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
