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