Re: [Sqlalchemy-users] Unit of work error?

Michael Bayer Tue, 30 May 2006 13:00:03 -0700

Hey Nick -

I ran this test (which is well-written, youve understood the docsvery well) with my favorite constraint checker, Postgres, and it doesin fact fail on 0.2.1. Fortunately, it does not fail when testingwith the fixes I have just made today, i think its the same issuesomeone else raised today (a lot has changed with the persistencemechanism with regards to inheritance structures recently). Trychecking out the trunk, rev 1554. also, one slight change to yourprogram:

magazine_page_mapper = mapper(MagazinePage, magazine_page_table,inherits=page_mapper, polymorphic_identity='m', properties={

    'magazine': relation(Magazine, backref=backref('pages'))
})

and remove the extra property:

#magazine_mapper.add_property('pages', relation(MagazinePage,lazy=True, private=True))

this establishes 'pages' as a backreference (it will work either wayif you are just loading the object from the DB, but the "backref"makes it automatically set up the two-way relationship beforeanything is even saved to the DB).

the insert order is correct including on PG which wont let constraintviolations go by, and the output of your select at the bottom is then:


[<__main__.MagazinePage object at 0x10fea30>]

For test purposes, I threw a session.clear() right after the flush()to insure a full load from the DB works and i got the same result.



On May 30, 2006, at 3:15 PM, Nick Joyce wrote:

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 themagazine_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 REFERENCESmagazine_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 ASlocation_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
'''




-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Re: [Sqlalchemy-users] Unit of work error?

Reply via email to