The declarative syntax seems easy to use. Since the tables are reflected, is there a way to use the declarative syntax with reflection?
Thanks. Edgar On Mar 26, 12:36 pm, 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 -~----------~----~----~----~------~----~------~--~---
