I'm starting to work with an existing MySQL setup, where there's a master
database (or, effectively, schema, it is all within one MySQL instance)
with tables of general usefulness, and separate schemas for each specific
project. So there is a table "master.users" with all the basic information
for a user, and each project would have something like project7.userlink
with additional info. "userlink" also has a couple of columns which
duplicate those in users, so that, for instance, user.jobtitle can be
overridden by userlink.jobtitle for that specific project, if the userlink
column is non-NULL. The existing usage (mainly PHP) has a view in the
project database which presents the appropriate values via COALESCE, which
was fine, but needs to change now that creating and updating the info is
required (the view obviously wasn't updatable).
I've used the "Mapping a Class against Multiple Tables" pattern in the
SQLAlchemy docs to use a join selectable:
MasterUser = Table("users", metadata, Column("id", Integer,
primary_key=True) ... schema="master")
ProjUser = Table("userlink", metadata, Column("user_id", Integer,
ForeignKey('master.users.id'), primary_key=True, )...)
UserMerge_join = join(MasterUser, ProjUser)
class UMerge(Base):
__table__ = UserMerge_join
id = column_property(MasterUser.c.id, ProjUser.c.user_id)
...
This seemed to work well for creating new users (no records yet in
master.users and the project's userlink table), and retrieving those that
have a userlink row in the whatever project database being connected to
(which means the master.users row exists as well). It failed trying to
retrieve UserMerge instances where a matching master.users row existed but
no row in the project's userlink table yet (not all users belong to every
project), but I changed to the join to:
UserMerge_join = join(MasterUser, ProjUser, isouter=True)
and I can retrieve non-member users if necessary, in preparation to add
them to the project. But if I then modify and commit, it fails with
"StaleDataError: UPDATE statement on table 'userlink' expected to update 1
row(s); 0 were matched.". SQLAlchemy seems to be saying that even though
no row from userlink was present on the retrieval, it is expecting one on
the update. I can understand the error, since this same situation would be
produced if indeed there had been a userlink record which was deleted by
something outside the session. I may be abusing the "isouter" feature, and
I can handle this some other way (add existing users into a project by
adding a bare ProjUser entry for them), but I thought I'd check to make
sure there wasn't one more little trick I might be missing to have
SQLAlchemy generate the necessary INSERT instead of UPDATE in this case for
the "userlink" table portion of this composite object.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/4RqGSE-ywvsJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
