Yet another error encountered:
ArgumentError: Could not determine relation direction for primaryjoin
condition 'persons.account_id = accounts.id', on relation
Account.persons. Are the columns in 'foreign_keys' present within the
given join condition ?
Here's the line of code where it failed:
dbsession = db.query(model.Session).filter
(model.Session.session_id==session_id).first()
Here's the mapper:
orm.mapper(Account, t_accounts, properties={'persons':relation(Person,
primaryjoin=t_persons.c.account_id==t_accounts.c.id,
foreign_keys='account_id')})
Why does a fetch from the sessions table cause SA to have a problem
with foreign keys (related to persons table)? Could someone supply
the correct syntax to specify the foreign key (accounts table)?
Thanks.
Edgar
On Mar 26, 1:38 pm, Brennan Todd <[email protected]> wrote:
> > 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
-~----------~----~----~----~------~----~------~--~---
