since there is no way for SA to guess which column joins are used in
which relationships, youre looking to be able to implement custom (or
explicit) join conditions on relationships, which is not currently
supported by the ActiveMapper extension...this would be tough to
support since when constructing the ActiveMapper class, you dont have
a handle on any Table objects with which to express the join
condition. (it would have to be expressed as strings, which starts
becoming a major reinvention of the wheel, as SA already has a SQL
expression language).
this is the whole point of the Data Mapper pattern; in a nutshell,
its better than any Active Record pattern. :) its SQLAlchemy's
primary design and really not all that hard. When you use
ActiveMapper its just giving you a thin layer of "shortcut" on top of
things SA does just fine without it.
if you use the assignmapper extension, your classes will behave
exactly like ActiveMapper classes, including a default constructor,
and all the query methods etc. (since assignmapper is what
ActiveMapper uses for that anyway).
One extra trick with your example is that your many-to-many table
contains a "favorite" column, meaning we also have to use an
association object (so we're way out of ActiveMapper territory now).
This is a basic example of your model. Read the "plugins" chapter in
the docs for a description of SessionContext and assign_mapper:
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
context = SessionContext(create_session)
# set echo=False to disable echoing of SQL statements
metadata = BoundMetaData('sqlite://', echo=True)
# table definitions
person_table = Table('person', metadata,
Column('id', Integer, primary_key=True),
Column('firstname', String(30), nullable=False),
Column('lastname', String(30), nullable=False)
)
contact_table = Table('contact', metadata,
Column('person_id', Integer, ForeignKey('person.id'), nullable=False),
Column('contact_id', Integer, ForeignKey('person.id'), nullable=False),
Column('favorite', Boolean, default=False)
)
metadata.create_all()
# class definitions. just have __repr__ methods which we will use to
verify things worked.
class Contact(object):
def __repr__(self):
return "Contact: " + repr(self.person) + " Favorite: " + repr
(self.favorite)
class Person(object):
def __repr__(self):
return ("Person: %s %s" % (self.firstname, self.lastname)) +
" Contacts: " + repr(self.contacts)
# set up mappers. associates classes with tables. as we are using
the "assign_mapper" extension, we also associate
# those mappers with our SessionContext, which is used to give us the
"current" session local to the current
# thread.
assign_mapper(context, Person, person_table, properties = {
'contacts': relation(Contact,
primaryjoin=person_table.c.id==contact_table.c.person_id,
association=Person)
})
assign_mapper(context, Contact, contact_table, primary_key=
[contact_table.c.person_id, contact_table.c.contact_id], properties={
'person' : relation(Person,
primaryjoin=person_table.c.id==contact_table.c.contact_id)
})
# current session as defined by the SessionContext. all new objects
get saved to this session
# automatically.
session = context.current
# assign_mapper applies default constructors to our classes
p1 = Person(firstname='john', lastname='smith')
p2 = Person(firstname='ed', lastname='helms')
p3 = Person(firstname='jonathan', lastname='lacour')
# add a contact
p1.contacts.append(Contact(person=p2))
# didnt assign "favorite", so it starts as None
assert p1.contacts[0].favorite is None
# add a second contact
p1.contacts.append(Contact(person=p3, favorite=True))
# ed helms also knows john smith.
p2.contacts.append(Contact(person=p1))
# save everything.
session.flush()
# after insert, the "default" fired off on the first contact and set
"favorite" to False
assert p1.contacts[0].favorite is False
# string representation of the classes that we've created
previous = repr(p1)
print previous
# clear the session, test loading the data back
session.clear()
newp1 = Person.get(p1.id)
print newp1
assert newp1 is not p1
assert repr(newp1) == previous
On Jul 15, 2006, at 8:03 AM, Sanjaya Kumar Patel wrote:
> Hi All,
>
> Summary:
> 1. Seeking help on how to relate a table to itself (using an
> association
> table).
> 2. Facing issue while using more than one foreign keys from table1
> pointing
> to table2.
>
> Elaborated:
> Consider the requirement - A person can have multiple persons in
> his contact
> list, and a person can be present in the contact list of multiple
> persons.
> The postgres schema below clears the scenario:
>
> CREATE TABLE person (
> id SERIAL,
> first_name VARCHAR(30) NOT NULL,
> last_name VARCHAR(30) NOT NULL,
> PRIMARY KEY(id)
> );
>
> CREATE TABLE contact (
> person_id INTEGER NOT NULL REFERENCES person,
> contact_id INTEGER NOT NULL REFERENCES person,
> favorite BOOLEAN NOT NULL DEFAULT FALSE,
> PRIMARY KEY(person_id, contact_id)
> );
>
> Being new to SQLAlchemy, ActiveMapper and Python, I could not
> conclude the
> code pattern for this for ActiveMapper. Kindly help.
>
> Forgeting about the many_to_many relationship, I then, for the sake of
> trying, viewed the scenario as a one_to_many relationship between
> person and
> contact object. That is "a person has many contacts." I got some
> strange
> results. Presenting the code below:
>
> #Trying to have 'Jonathan LaCour' in 'Sanjay Patel's contact list:
> from sqlalchemy.ext.activemapper import *
> metadata.connect('postgres://username:[EMAIL PROTECTED]:5432/mydb')
>
> class Person(ActiveMapper):
> class mapping:
> __autoload__ = True
> contacts = one_to_many('Contact', colname='person_id',
> backref='contact_of')
> class Contact(ActiveMapper):
> class mapping:
> __autoload__ = True
>
> jonathan = Person(first_name = "Jonathan", last_name = "LaCour");
> objectstore.flush()
> print jonathan.id # 1
> sanjay = Person(first_name = "Sanjay", last_name = "Patel",
> contacts = [Contact(contact_id=jonathan.id,
> favorite=True)])
> objectstore.flush()
> print sanjay.id # 2
> print sanjay.contacts[0].person_id # 2
> print sanjay.contacts[0].contact_id # 2 (it should be 1!)
>
> Is it a bug, or I am missing something?
>
> Thanks
> Sanjay
>
>
>
>
> ----------------------------------------------------------------------
> ---
> Using Tomcat but need to do more? Need to support web services,
> security?
> Get stuff done quickly with pre-integrated technology to make your
> job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache
> Geronimo
> http://sel.as-us.falkag.net/sel?
> cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
