Hi, I am new to SQLAlchemy and have been testing it using a real-world project of mine. (See attached test.py)
I am using 0.2.1 magazine_page.magazine_id has a foreign key to magazine.id Excerpt from debug.log: [2006-05-30 17:42:11,724] [engine]: BEGIN [2006-05-30 17:42:11,724] [engine]: INSERT INTO location_name (name) VALUES (?) [2006-05-30 17:42:11,725] [engine]: ['London'] [2006-05-30 17:42:11,726] [engine]: INSERT INTO page (page_no, type) VALUES (?, ?) [2006-05-30 17:42:11,726] [engine]: [1, 'm'] [2006-05-30 17:42:11,728] [engine]: INSERT INTO magazine_page (page_id, magazine_id, orders) VALUES (?, ?, ?) [2006-05-30 17:42:11,728] [engine]: [1, '0', ''] [2006-05-30 17:42:11,729] [engine]: INSERT INTO page_size (width, height, name) VALUES (?, ?, ?) [2006-05-30 17:42:11,730] [engine]: [210, 297, 'A4'] [2006-05-30 17:42:11,731] [engine]: INSERT INTO publication (name) VALUES (?) [2006-05-30 17:42:11,731] [engine]: ['Test'] [2006-05-30 17:42:11,732] [engine]: INSERT INTO issue (publication_id, issue) VALUES (?, ?) [2006-05-30 17:42:11,733] [engine]: [1, 46] [2006-05-30 17:42:11,734] [engine]: INSERT INTO location (issue_id, ref, location_name_id) VALUES (?, ?, ?) [2006-05-30 17:42:11,734] [engine]: [1, 'ABC', 1] [2006-05-30 17:42:11,736] [engine]: INSERT INTO magazine (location_id, page_size_id) VALUES (?, ?) [2006-05-30 17:42:11,736] [engine]: [1, 1] [2006-05-30 17:42:11,737] [engine]: COMMIT In enforcing the referential integrity shouldn't the magazine insert come before the magazine_page (and update the magazine_page.magazine_id)? Perhaps I have the mapping wrong, if so can someone give me some pointers in the right direction? I have extensively consulted the documentation, but perhaps I missed something ... Cheers, Nick
[2006-05-30 17:42:11,694] [engine]: PRAGMA table_info(classified_page) [2006-05-30 17:42:11,694] [engine]: {} [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(magazine_page) [2006-05-30 17:42:11,696] [engine]: {} [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(magazine) [2006-05-30 17:42:11,696] [engine]: {} [2006-05-30 17:42:11,696] [engine]: PRAGMA table_info(location) [2006-05-30 17:42:11,696] [engine]: {} [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(location_name) [2006-05-30 17:42:11,697] [engine]: {} [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(issue) [2006-05-30 17:42:11,697] [engine]: {} [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(publication) [2006-05-30 17:42:11,697] [engine]: {} [2006-05-30 17:42:11,697] [engine]: PRAGMA table_info(page) [2006-05-30 17:42:11,698] [engine]: {} [2006-05-30 17:42:11,698] [engine]: PRAGMA table_info(page_size) [2006-05-30 17:42:11,698] [engine]: {} [2006-05-30 17:42:11,699] [engine]: PRAGMA table_info(page_size) [2006-05-30 17:42:11,699] [engine]: {} [2006-05-30 17:42:11,700] [engine]: CREATE TABLE page_size( id INTEGER NOT NULL PRIMARY KEY, width INTEGER, height INTEGER, name VARCHAR(45) ) [2006-05-30 17:42:11,700] [engine]: None [2006-05-30 17:42:11,700] [engine]: COMMIT [2006-05-30 17:42:11,701] [engine]: PRAGMA table_info(page) [2006-05-30 17:42:11,701] [engine]: {} [2006-05-30 17:42:11,701] [engine]: CREATE TABLE page( id INTEGER NOT NULL PRIMARY KEY, page_no INTEGER, type CHAR(1) ) [2006-05-30 17:42:11,702] [engine]: None [2006-05-30 17:42:11,702] [engine]: COMMIT [2006-05-30 17:42:11,702] [engine]: PRAGMA table_info(publication) [2006-05-30 17:42:11,702] [engine]: {} [2006-05-30 17:42:11,703] [engine]: CREATE TABLE publication( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(45) ) [2006-05-30 17:42:11,703] [engine]: None [2006-05-30 17:42:11,704] [engine]: COMMIT [2006-05-30 17:42:11,704] [engine]: PRAGMA table_info(issue) [2006-05-30 17:42:11,704] [engine]: {} [2006-05-30 17:42:11,705] [engine]: CREATE TABLE issue( id INTEGER NOT NULL PRIMARY KEY, publication_id INTEGER REFERENCES publication(id), issue INTEGER ) [2006-05-30 17:42:11,705] [engine]: None [2006-05-30 17:42:11,705] [engine]: COMMIT [2006-05-30 17:42:11,706] [engine]: PRAGMA table_info(location_name) [2006-05-30 17:42:11,706] [engine]: {} [2006-05-30 17:42:11,706] [engine]: CREATE TABLE location_name( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(45) ) [2006-05-30 17:42:11,707] [engine]: None [2006-05-30 17:42:11,707] [engine]: COMMIT [2006-05-30 17:42:11,707] [engine]: PRAGMA table_info(location) [2006-05-30 17:42:11,707] [engine]: {} [2006-05-30 17:42:11,708] [engine]: CREATE TABLE location( id INTEGER NOT NULL PRIMARY KEY, issue_id INTEGER REFERENCES issue(id), ref CHAR(3), location_name_id INTEGER REFERENCES location_name(id) ) [2006-05-30 17:42:11,708] [engine]: None [2006-05-30 17:42:11,709] [engine]: COMMIT [2006-05-30 17:42:11,709] [engine]: PRAGMA table_info(magazine) [2006-05-30 17:42:11,709] [engine]: {} [2006-05-30 17:42:11,710] [engine]: CREATE TABLE magazine( id INTEGER NOT NULL PRIMARY KEY, location_id INTEGER REFERENCES location(id), page_size_id INTEGER REFERENCES page_size(id) ) [2006-05-30 17:42:11,710] [engine]: None [2006-05-30 17:42:11,711] [engine]: COMMIT [2006-05-30 17:42:11,711] [engine]: PRAGMA table_info(magazine_page) [2006-05-30 17:42:11,711] [engine]: {} [2006-05-30 17:42:11,712] [engine]: CREATE TABLE magazine_page( page_id INTEGER NOT NULL PRIMARY KEY REFERENCES page(id), magazine_id INTEGER REFERENCES magazine(id), orders TEXT ) [2006-05-30 17:42:11,712] [engine]: None [2006-05-30 17:42:11,713] [engine]: COMMIT [2006-05-30 17:42:11,713] [engine]: PRAGMA table_info(classified_page) [2006-05-30 17:42:11,713] [engine]: {} [2006-05-30 17:42:11,714] [engine]: CREATE TABLE classified_page( magazine_page_id INTEGER NOT NULL PRIMARY KEY REFERENCES magazine_page(page_id), titles VARCHAR(45) ) [2006-05-30 17:42:11,714] [engine]: None [2006-05-30 17:42:11,714] [engine]: COMMIT [2006-05-30 17:42:11,718] [engine]: SELECT location_name.id AS location_name_id, location_name.name AS location_name_name FROM location_name WHERE location_name.name = ? ORDER BY location_name.oid LIMIT 1 OFFSET 0 [2006-05-30 17:42:11,718] [engine]: ['London'] [2006-05-30 17:42:11,724] [engine]: BEGIN [2006-05-30 17:42:11,724] [engine]: INSERT INTO location_name (name) VALUES (?) [2006-05-30 17:42:11,725] [engine]: ['London'] [2006-05-30 17:42:11,726] [engine]: INSERT INTO page (page_no, type) VALUES (?, ?) [2006-05-30 17:42:11,726] [engine]: [1, 'm'] [2006-05-30 17:42:11,728] [engine]: INSERT INTO magazine_page (page_id, magazine_id, orders) VALUES (?, ?, ?) [2006-05-30 17:42:11,728] [engine]: [1, '0', ''] [2006-05-30 17:42:11,729] [engine]: INSERT INTO page_size (width, height, name) VALUES (?, ?, ?) [2006-05-30 17:42:11,730] [engine]: [210, 297, 'A4'] [2006-05-30 17:42:11,731] [engine]: INSERT INTO publication (name) VALUES (?) [2006-05-30 17:42:11,731] [engine]: ['Test'] [2006-05-30 17:42:11,732] [engine]: INSERT INTO issue (publication_id, issue) VALUES (?, ?) [2006-05-30 17:42:11,733] [engine]: [1, 46] [2006-05-30 17:42:11,734] [engine]: INSERT INTO location (issue_id, ref, location_name_id) VALUES (?, ?, ?) [2006-05-30 17:42:11,734] [engine]: [1, 'ABC', 1] [2006-05-30 17:42:11,736] [engine]: INSERT INTO magazine (location_id, page_size_id) VALUES (?, ?) [2006-05-30 17:42:11,736] [engine]: [1, 1] [2006-05-30 17:42:11,737] [engine]: COMMIT
import sqlalchemy.mods.threadlocal from sqlalchemy import * # set-up tables metadata = BoundMetaData('sqlite://:memory:') publication_table = Table('publication', metadata, Column('id', Integer, primary_key=True, default=None), Column('name', String(45), default=''), ) issue_table = Table('issue', metadata, Column('id', Integer, primary_key=True, default=None), Column('publication_id', Integer, ForeignKey('publication.id'), default='0'), Column('issue', Integer, default='0'), ) location_table = Table('location', metadata, Column('id', Integer, primary_key=True, default=None), Column('issue_id', Integer, ForeignKey('issue.id'), default='0'), Column('ref', CHAR(3), default=''), Column('location_name_id', Integer, ForeignKey('location_name.id'), default='0'), ) location_name_table = Table('location_name', metadata, Column('id', Integer, primary_key=True, default=None), Column('name', String(45), default=''), ) magazine_table = Table('magazine', metadata, Column('id', Integer, primary_key=True, default=None), Column('location_id', Integer, ForeignKey('location.id'), default='0'), Column('page_size_id', Integer, ForeignKey('page_size.id'), default='0'), ) page_table = Table('page', metadata, Column('id', Integer, primary_key=True, default=None), Column('page_no', Integer, default='0'), Column('type', CHAR(1), default='p'), ) magazine_page_table = Table('magazine_page', metadata, Column('page_id', Integer, ForeignKey('page.id'), primary_key=True, default='0'), Column('magazine_id', Integer, ForeignKey('magazine.id'), default='0'), Column('orders', TEXT, default=''), ) classified_page_table = Table('classified_page', metadata, Column('magazine_page_id', Integer, ForeignKey('magazine_page.page_id'), primary_key=True, default='0'), Column('titles', String(45), default=''), ) page_size_table = Table('page_size', metadata, Column('id', Integer, primary_key=True, default=None), Column('width', Integer, default='0'), Column('height', Integer, default='0'), Column('name', String(45), default=''), ) # mapping objects class BaseObject(object): def __init__(self, *args, **kwargs): for key, value in kwargs.iteritems(): setattr(self, key, value) class Publication(BaseObject): pass class Issue(BaseObject): pass class Location(BaseObject): def _get_name(self): return self._name def _set_name(self, name): session = objectstore.get_session() s = session.query(LocationName).selectfirst(location_name_table.c.name==name) if s is not None: self._name = s return found = False for i in session.new: if isinstance(i, LocationName) and i.name == name: self._name = i found = True break if found == False: self._name = LocationName(name=name) name = property(_get_name, _set_name) class LocationName(BaseObject): pass class PageSize(BaseObject): pass class Magazine(BaseObject): pass class Page(BaseObject): pass class MagazinePage(Page): def __init__(self, *args, **kwargs): Page.__init__(self, *args, **kwargs) class ClassifiedPage(MagazinePage): pass # define mapping publication_mapper = mapper(Publication, publication_table) issue_mapper = mapper(Issue, issue_table, properties = { 'publication': relation(Publication, backref=backref('issues', cascade="add, delete-orphan")), }) location_name_mapper = mapper(LocationName, location_name_table) location_mapper = mapper(Location, location_table, properties = { 'issue': relation(Issue, backref='locations'), '_name': relation(LocationName), }) issue_mapper.add_property('locations', relation(Location, lazy=False, private=True, backref='issue')) page_size_mapper = mapper(PageSize, page_size_table) page_join = polymorphic_union( { 'm': page_table.join(magazine_page_table), 'p': page_table.select(page_table.c.type=='p'), }, None, 'page_join') magazine_mapper = mapper(Magazine, magazine_table, properties = { 'location': relation(Location, backref=backref('magazine', uselist=False)), 'size': relation(PageSize), }) page_mapper = mapper(Page, page_table, select_table=page_join, polymorphic_on=page_join.c.type, polymorphic_identity='p') magazine_page_mapper = mapper(MagazinePage, magazine_page_table, inherits=page_mapper, polymorphic_identity='m', properties={ 'magazine': relation(Magazine) }) magazine_mapper.add_property('pages', relation(MagazinePage, lazy=True, private=True)) #classified_page_mapper = mapper(ClassifiedPage, classified_page_table, inherits=MagazinePage.mapper, polymorphic_identity='c') session = objectstore.get_session() # do some operations metadata.engine.echo = True # metadata.drop_all() metadata.create_all() pub = Publication(name='Test') issue = Issue(issue=46,publication=pub) location = Location(ref='ABC',name='London',issue=issue) page_size = PageSize(name='A4',width=210,height=297) magazine = Magazine(location=location,size=page_size) page = MagazinePage(magazine=magazine,page_no=1) session.flush() ''' p = session.query(Publication).selectone_by(name='Test') print p.issues[0].locations[0].magazine.pages '''