Dear list, I have an SQLAlchemy problem that has ruined my last weekend. So I have reduced the problem to the bare minimum and thought I'd ask here on what my fault is. :)
Imagine a (web) application that has Users (employees) and Items (on the
shelf). Like in an online shop. Now there's a logbook that records every
change that happens over time. Like a User has taken an Item and done
something to it. So a logbook entry is both connected (many-to-one) to the
Users and the Items. So I can find out what a certain User did
(User.logbookentries) or see what has happened to a certain Item
(Item.logbookentries). In the reverse way I can see which User and which
Item a logbookentry refers to.
Allow me to show you my example code (you should be able to run it like
that and immediately reproduce the problem):
=============================================
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlalchemy as sql
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import MetaData, create_engine, orm
Session = scoped_session(sessionmaker())
metadata = MetaData()
# Define the tables
users_table = sql.Table(
'users', metadata,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('name', sql.Unicode()),
)
items_table = sql.Table(
'items', metadata,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('name', sql.Unicode),
)
logbook_table = sql.Table(
'logbook', metadata,
sql.Column('id', sql.Integer, primary_key=True),
sql.Column('item_id', sql.Integer, sql.ForeignKey('items.id')),
sql.Column('user_id', sql.Integer, sql.ForeignKey('users.id')),
sql.Column('text', sql.Unicode(100)),
)
# Define the classes for ORM mapping
class User(object): pass
class Item(object): pass
class LogbookEntry(object): pass
# ORM mapping
orm.mapper(User, users_table,
properties={
'logbookentries':orm.relation(LogbookEntry,
# either works without this backref:
backref=orm.backref('user', uselist=False),
# or works if this becomes cascade="all,delete-orphan":
),
}
)
orm.mapper(Item, items_table,
properties={
'logbookentries':orm.relation(LogbookEntry,
cascade="all, delete-orphan"),
}
)
orm.mapper(LogbookEntry, logbook_table)
# Connect to the database
engine = create_engine('sqlite:///mystuff.sqlite', echo=True)
Session.configure(bind=engine)
# Create database schema
metadata.create_all(bind=engine)
# Create a user
user = User()
user.name=u'SomeUser'
Session.save(user)
# Create an item
item = Item()
item.name=u'SomeItem'
Session.save(item)
# Save the previously created objects into the database
Session.commit()
# Create a logbook entry
logbookentry = LogbookEntry()
logbookentry.text = u'SomeLogText'
# Connect the LogbookEntry instance to the User and Item
logbookentry.user = user
item.logbookentries.append(logbookentry)
Session.commit()
=============================================
What actually happens when I run this code is this error message:
sqlalchemy.orm.exc.FlushError: Instance <LogbookEntry at 0x91f5b2c> is an
unsaved, pending instance and is an orphan (is not attached to any parent
'Item' instance via that classes' 'logbookentries' attribute)
I wasn't sure why this happens. After all I have connected the LogbookEntry
to the Item by saying "item.logbookentries.append(logbookentry)" so the
LogbookEntry isn't orphaned at all.
There were two remedies that made this code working. First one was to
remove the line
backref=orm.backref('user', uselist=False),
from the User.logbookentries mapper. And the second one was defining the
cascade as
cascade="all,delete-orphan"
instead of
cascade="all"
Does SQLAlchemy want to tell me that a backref wouldn't work unless I
enforce a user entry to be there by using a delete cacade from LogbookEntry
to User? After all if I delete a User then the backref would point nowhere.
The reason I don't use 'delete-orphan' here is that a User (employee) could
get fired and deleting the "User" from the database would automatically kill
all the "LogbookEntry"s. But I want to preserve the logbook even if the
referring User is gone. I would expect the logbook.user_id to be None if
the User is gone.
The funny fact is that this code works well with SQLAlchemy 0.4.8 but fails
on 0.5.3. And even if SQLAlchemy wants to save me from doing something
illogical I had expected another error message telling me that my cascade
without "delete-orphan" is useless if I want to have a backref on the
'user'.
Is this a case of an improvable error message? Or have I just misunderstood
it? After a lot of cursing I'd love to get your feedback on my issue.
Thanks.
Kindly
Christoph
signature.asc
Description: This is a digitally signed message part.
