SQLAlchemy中关系和未提交对象的复杂性

Question

我在用Flask搭配SQLAlchemy，想给我的网页应用加个简单的个人消息功能。模型里有一个 User 类，一个 PersonalMessage 类，还有一个 PersonalMessageUser 关联类，后者用来建立前两个类之间的关系——没什么复杂的。下面是一个简化版：

import collections
import datetime

from flaskext.sqlalchemy import SQLAlchemy
from . import app

db = SQLAlchemy(app)

def current_ts():
    return datetime.datetime.utcnow()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(127), nullable=False, unique=True)

    def __repr__(self):
        return '<User {0.username!r} (#{0.id})>'.format(self)

class PersonalMessage(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    subject = db.Column(db.String(127), nullable=False)
    body = db.Column(db.Text, nullable=False)
    date = db.Column(db.DateTime, nullable=False, default=current_ts)

    def __repr__(self):
        return '<PersonalMessage {0.subject!r} (#{0.id})>'.format(self)

    def __init__(self, subject, body, from_, to=None, cc=None, bcc=None):
        self.subject = subject
        self.body = body
        if not to and not cc and not bcc:
            raise ValueError, 'No recipients defined'
        self._pm_users.append(PersonalMessageUser(
            message=self, user_type='From', user=from_,
        ))
        for type, values in {'To': to, 'CC': cc, 'BCC': bcc}.items():
            if values is None:
                continue
            if not isinstance(values, collections.Iterable):
                values = [values]
            for value in values:
                self._pm_users.append(PersonalMessageUser(
                    message=self, user=value, user_type=type,
                ))

class PersonalMessageUser(db.Model):
    pm_id = db.Column(db.ForeignKey(PersonalMessage.id), nullable=False,
                      primary_key=True)
    message = db.relationship(PersonalMessage, backref='_pm_users',
                              lazy='subquery')
    user_id = db.Column(db.ForeignKey(User.id), nullable=False,
                        primary_key=True)
    user = db.relationship(User, backref='_personal_messages')
    user_type = db.Column(
        db.Enum('From', 'To', 'CC', 'BCC', name='user_type'),
        nullable=False, default='To', primary_key=True,
    )

    def __repr__(self):
        return (
            '<PersonalMessageUser '
            '{0.user_type}: {0.user.username!r} '
            '(PM #{0.pm_id}: {0.message.subject!r})>'
        ).format(self)

基本上，一切都运作得很好，但我在Python解释器里玩的时候发现了点奇怪的事：当我创建一个新的 PersonalMessage，有一个发件人和一个收件人时，_pm_users 的反向引用居然把每个用户列了两次。不过，一旦这个对象被提交到数据库后，看起来就正常了。以下是一个会话的例子：

>>> al = User(username='al')
>>> db.session.add(al)
>>> steve = User(username='steve')
>>> db.session.add(steve)
>>> db.session.commit()
BEGIN (implicit)
INSERT INTO user (username) VALUES (?)
('al',)
INSERT INTO user (username) VALUES (?)
('steve',)
COMMIT
>>> pm = PersonalMessage('subject', 'body', from_=al, to=steve)
>>> pm._pm_users
BEGIN (implicit)
SELECT user.id AS user_id, user.username AS user_username 
FROM user 
WHERE user.id = ?
(1,)
SELECT user.id AS user_id, user.username AS user_username 
FROM user 
WHERE user.id = ?
(2,)
[<PersonalMessageUser From: u'al' (PM #None: 'subject')>,
 <PersonalMessageUser From: u'al' (PM #None: 'subject')>,
 <PersonalMessageUser To: u'steve' (PM #None: 'subject')>,
 <PersonalMessageUser To: u'steve' (PM #None: 'subject')>]
>>> len(pm._pm_users)
4
>>> db.session.add(pm)
>>> pm._pm_users
[<PersonalMessageUser From: u'al' (PM #None: 'subject')>,
 <PersonalMessageUser From: u'al' (PM #None: 'subject')>,
 <PersonalMessageUser To: u'steve' (PM #None: 'subject')>,
 <PersonalMessageUser To: u'steve' (PM #None: 'subject')>]
>>> db.session.commit()
INSERT INTO personal_message (subject, body, date) VALUES (?, ?, ?)
('subject', 'body', '2011-08-10 19:48:15.641249')
INSERT INTO personal_message_user (pm_id, user_id, user_type) VALUES (?, ?, ?)
((1, 1, 'From'), (1, 2, 'To'))
COMMIT
>>> pm._pm_users
BEGIN (implicit)
SELECT personal_message.id AS personal_message_id,
    personal_message.subject AS personal_message_subject,
    personal_message.body AS personal_message_body,
    personal_message.date AS personal_message_date 
FROM personal_message 
WHERE personal_message.id = ?
(1,)
SELECT personal_message_user.pm_id AS personal_message_user_pm_id,
    personal_message_user.user_id AS personal_message_user_user_id,
    personal_message_user.user_type AS personal_message_user_user_type 
FROM personal_message_user 
WHERE ? = personal_message_user.pm_id
(1,)
SELECT user.id AS user_id, user.username AS user_username 
FROM user 
WHERE user.id = ?
(1,)
SELECT user.id AS user_id, user.username AS user_username 
FROM user 
WHERE user.id = ?
(2,)
[<PersonalMessageUser From: u'al' (PM #1: u'subject')>,
 <PersonalMessageUser To: u'steve' (PM #1: u'subject')>]

至少最后的结果是我预期的样子，但在提交之前每个用户出现两次让我觉得不太舒服；我想弄明白这是怎么回事。我是不是在 relationship / backref 的设置上漏掉了什么，还是说我就应该忽略这个问题？

数据库 sqlalchemy 会话管理对象关系映射 flask 关系模型反向引用未提交对象

SQLAlchemy中关系和未提交对象的复杂性

1 个回答

撰写回答