>
> t_accounts = Table("accounts", meta.metadata, autoload=True,
> autoload_with=engine)
>
t_persons = Table("persons", meta.metadata, autoload=True,
> autoload_with=engine)
>
> I tried this syntax instead:
> orm.mapper(Account, t_accounts, properties={'persons':
>>
>> relation(Person,
>> primaryjoin=persons.c.account_id==accounts.c.id)})
>
>
try primaryjoin = *t_persons*.c.account_id==*t_accounts*.c.id
Your primary join is using the table name from the database, when you should
be using the python variable name which represents the Table object from
that table.
On Thu, Mar 26, 2009 at 11:36 AM, edgarsmolow <[email protected]> wrote:
>
> An account has many persons (usually members of immediate family)
> connected to it. So there's a many-to-one relationship between the
> persons table and the accounts table. Tables are reflected from a
> MySQL database. Here's the code:
>
> t_accounts = Table("accounts", meta.metadata, autoload=True,
> autoload_with=engine)
> t_persons = Table("persons", meta.metadata, autoload=True,
> autoload_with=engine)
>
> orm.mapper(Account, t_accounts, properties={'persons':relation
> (Person)})
> orm.mapper(Person, t_persons)
>
> class Account(object):
> def __init__(self, acct_no=None):
> self.id = id
> self.acct_no = acct_no
> persons = relation('Persons')
>
> class Person(object):
> def __init__(self, **kwargs):
> for k,v in kwargs:
> setattr(self, k, v)
>
> This is the code to add rows:
>
> acct_no = 'ABC123'
> account = model.Account(acct_no=acct_no)
> account.pwd = 'secret'
> account.status = 'P'
>
> psn = model.Person()
> psn.first_name = 'Fred'
> psn.last_name = 'Flintstone'
> account.persons.append(psn)
> db.add(account)
> db.commit()
>
> But, this error is produced:
>
> ArgumentError: Could not determine join condition between parent/child
> tables on relation Account.persons. Specify a 'primaryjoin'
> expression. If this is a many-to-many relation, 'secondaryjoin' is
> needed as well.
>
> I tried this syntax instead:
> orm.mapper(Account, t_accounts, properties={'persons':relation(Person,
> primaryjoin=persons.c.account_id==accounts.c.id)})
>
> but, it produced this error:
> orm.mapper(Account, t_accounts, properties={'persons':relation(Person,
> primaryjoin=persons.c.account_id==accounts.c.id)})
> NameError: global name 'persons' is not defined
>
> I'm clearly not understanding .........
>
> Thanks.
> Edgar
>
>
>
> On Mar 25, 7:54 pm, Wyatt Baldwin <[email protected]> wrote:
> > On Mar 25, 2:02 pm, edgarsmolow <[email protected]> wrote:
> >
> > > One more issue.... the tables are reflected; meaning that they are
> > > already defined in the database.
> > > Can this syntax still be used in the Account class definition?
> > > person_id = Column(Integer, ForeignKey('people'))
> >
> > I think you add a ForeignKeyConstraint to the table definition. For a
> > declarative class, it'd look like this:
> >
> > http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html?high...
> >
> > > And, your Account class is derived from Base. The example I saw
> > > online used object instead. Is there some inherent advantage to using
> > > Base?
> >
> > I'm using the declarative approach:
> >
> > http://www.sqlalchemy.org/docs/05/ormtutorial.html#creating-table-cla...
> >
> > I don't know if there's any inherent advantage. It's partly style and
> > partly technical, dependent on your app's requirements.
> >
> > > By the way, when I said that I could not find good examples, I was
> > > referring to retrieving the last row ID.
> >
> > > Edgar
> >
> > > On Mar 25, 4:26 pm, Wyatt Baldwin <[email protected]> wrote:
> >
> > > > On Mar 25, 11:12 am, edgarsmolow <[email protected]> wrote:
> >
> > > > > I have a set of interdependent tables, including accounts and
> persons,
> > > > > where this is their relationship (i.e., for certain persons):
> >
> > > > > PK: accounts.id INT AUTO_INCREMENT
> > > > > accounts.acct_holder_id = person.id
> >
> > > > > PK: person.id INT AUTO_INCREMENT
> > > > > person.account_id = account.id
> >
> > > > You probably only want one or the other of these foreign keys. Since
> > > > an account "belongs to" a person (I'm assuming one or many accounts
> > > > per person), you can probably get rid of person.account_id. Then, in
> > > > your person class (assuming you're using the declarative approach):
> >
> > > > from sqlalchemy.orm import relation
> >
> > > > class Person(Base):
> > > > __tablename__ = 'people'
> > > > # stuff
> > > > accounts = relation('Account')
> >
> > > > Over in your Account class:
> >
> > > > class Account(Base):
> > > > __tablename__ = 'accounts'
> > > > # stuff
> > > > person_id = Column(Integer, ForeignKey('people'))
> >
> > > > Then when you receive the form data, create a new person and append
> > > > the new account:
> >
> > > > p = Person(*args, **kwargs)
> > > > p.accounts.append(Account(*args, **kwargs))
> > > > db.add(p)
> >
> > > > Of course, I made a bunch of assumptions here, so this might not
> > > > exactly work for you. The basic idea, though, is that you need to set
> > > > up your relations properly and then what you want to do should be
> > > > fairly straightforward.
> >
> > > > > When a new person's contact info is entered in a form, I want to
> add
> > > > > both the person and the account to the database. The account is
> added
> > > > > first (including some fields like status):
> >
> > > > > account = model.Account()
> > > > > account.status = 'P'
> > > > > db = meta.Session()
> > > > > db.add(account)
> > > > > db.commit()
> >
> > > > > The account.id field will be needed to set a field on the person
> > > > > table:
> > > > > person = model.Person()
> > > > > person.first_name = 'Fred'
> > > > > person.last_name = 'Flintstone'
> > > > > person.account_id = account.id
> >
> > > > > How do I then determine account.id (i.e., the last row id on the
> > > > > account table)?
> >
> > > > In general, you should never need to take this approach.
> >
> > > > > I could not seem to find a practical code example.
> >
> > > > There are lots of good examples at sqlalchemy.org.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---
