Hey -
I wrote you a reasonable test application using your tables. So I
apologize, you werent mapping "secondary" to an association table, I
thought I saw that but it was an email formatting issue. You were
creating overlapping names to relations though, so the attached script
resolves that and illustrates some sample usage.
I also found a small bug in Query related to your extra
ForeignKeyConstraint (at least in 0.5) which you normally would not
come across, that will be fixed soon.
hope this helps.
- 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
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo='debug')
metadata = MetaData()
Session = sessionmaker(bind=engine, transactional=True, autoflush=True)
Base = declarative_base(metadata=metadata)
users = Table('users',metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', Unicode(25), unique = True))
categories = Table('categories',metadata,
Column('categ_id',Integer, primary_key = True),
Column('categ_name',Unicode(250), unique = True))
questions = Table('questions', metadata,
Column('quest_id', Integer, primary_key = True),
Column('question', Unicode(300)))
correspond = Table('categories_questions', metadata,
Column('quest_id', Integer,ForeignKey('questions.quest_id'), primary_key = True),
Column('categ_id', Integer,ForeignKey('categories.categ_id'), primary_key = True))
ask = Table('ask', metadata,
Column('user_id',Integer,ForeignKey('users.user_id'), primary_key = True),
Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key = True),
Column('data1',Integer, nullable = False, default= 50))
answer = Table('answer',metadata,
Column('user_id',Integer,ForeignKey('users.user_id'), primary_key=True),
Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key=True),
Column('data2',Integer),
# the double FK constraint here is not invalid, but has revealed a bug
# in current SA when using join() with aliased=True [ticket:1041]
#ForeignKeyConstraint(['user_id','quest_id'],['ask.user_id','ask.quest_id'])
)
class PrettyRepr(object):
def __repr__(self):
return "%s(%s)" % (
(self.__class__.__name__),
','.join(["%s=%s" % (key, repr(getattr(self, key))) for key in self.__dict__ if not key.startswith('_')])
)
class User(Base, PrettyRepr):
__table__ = users
def ask_question(self, question, data):
self.ask.append(AskAss(question=question, data1=data))
def answer_question(self, question, data):
self.answer.append(AnswerAss(question=question, data2=data))
class Category(Base, PrettyRepr):
__table__ = categories
class AskAss(Base):
__table__ = ask
user = relation(User, backref = 'ask')
class AnswerAss(Base):
__table__ = answer
user = relation(User, backref = 'answer')
class Question(Base):
__table__ = questions
categories = relation(Category, secondary=correspond, backref="questions")
ask_ass_users = relation(AskAss, backref='question')
answer_ass_users = relation(AnswerAss, backref='question')
metadata.create_all(engine)
sess = Session()
jack = User(user_name='jack')
ed = User(user_name='ed')
wendy = User(user_name='wendy')
[sess.save(x) for x in [jack, ed, wendy]]
colors = Category(categ_name='colors')
shapes = Category(categ_name='shapes')
cars = Category(categ_name='cars')
[sess.save(x) for x in [colors, shapes, cars]]
favorite_color = Question(question=u"What's your favorite color? 1. blue 2. green 3. red", categories=[colors])
favorite_shape = Question(question=u"What's your favorite shape? 1. square 2. circle 3. trapezoid", categories=[shapes])
blue_cars = Question(question=u"What's your favorite blue car? 1. pinto 2. nova 3. duster", categories=[colors, cars])
[sess.save(x) for x in [favorite_color, favorite_shape, blue_cars]]
jack.ask_question(favorite_shape, 2)
jack.ask_question(favorite_color, 1)
wendy.answer_question(favorite_color, 2)
ed.answer_question(blue_cars, 3)
ed.ask_question(favorite_shape, 3)
jack.answer_question(favorite_shape, 2)
sess.commit()
# all users who asked about colors
assert sess.query(User).join('ask', 'question', 'categories').filter(Category.categ_name=='colors').all() == [jack]
# all users who answered about cars
assert sess.query(User).join('answer', 'question', 'categories').filter(Category.categ_name=='cars').all() == [ed]
# all categories answered or asked by jack (this query can be done more flexibly in 0.5)
assert sess.query(Category).outerjoin('questions', 'ask_ass_users').\
outerjoin('questions', 'answer_ass_users', aliased=True).filter(
or_(
AnswerAss.user.has(User.user_name=='jack'),
AskAss.user.has(User.user_name=='jack')
)).order_by(Category.categ_name).all() == [colors, shapes]
On May 12, 2008, at 2:00 PM, [EMAIL PROTECTED] wrote:
>
>
>
> On 12 mai, 19:16, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> lets all repeat the mantra...."association tables with any columns
>> beyond the two foreign keys use the association object pattern". I
>> guess its a little too wordy to be catchy. Documented
>> athttp://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio
>> ...
>> .
>>
>> On May 12, 2008, at 10:45 AM, [EMAIL PROTECTED] wrote:
>
> Thanks for answering and for your great work, Michael.
> Sorry to ask stupid questions but I am learning...
>
> You remind me that "association tables with any columns beyond the
> two foreign keys use the association object pattern"
> I saw that.
> I thought that is what I did in the answerMapper or the askMapper and
> the questionMapper which link the tables users and questions).
>
> Isn't it what I have done ?
> No ?
> If not, could you please tell me what is wrong in my model ?
> May be the backref in the questionMapper should not be 'questions' ?
> (See: # ManyToMany AnswerAssociation between questions and users
> 'users': relation(AnswerAss,
> backref='questions') )
>
> What I can't manage to do corresponds to the Mapper configuration
> tutorial (Association object):
> "# create parent, append a child via association
> p = Parent()
> a = Association()
> a.child = Child()
> p.children.append(a)"
>
> I thought I could do:
> q = Question(question = 'blabla')
> a = askAss()
> a.users = User(user_name = 'Michael')
> q.users.append(a)
> Python forwards this error: "AttributeError: type object 'AskAss' has
> no attribute 'questions' ".
> I am a bit lost ...
>
> Thanks again for any help
> Dominique
> --~--~---------~--~----~------------~-------~--~----~
> 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
> -~----------~----~----~----~------~----~------~--~---
>
