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

Reply via email to