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
-~----------~----~----~----~------~----~------~--~---
