SQLAlchemy intricacies with relationships and uncommitted objects

Question

I am using SQLAlchemy via Flask, and I want to add simple personal messaging to my webapp. The model has a User class, a PersonalMessage class and a PersonalMessageUser association class, with the latter setting up relationships to the former two—nothing fancy. Here is a stripped down version:

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)

Everything works fine basically, but I noticed something strange when I played around with it in the Python interpreter: when I create a new PersonalMessage with one sender and one recipient, the _pm_users backref actually lists each user twice. Once the object has been committed to the database, it looks okay, though. See the following session as an example:

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

At least the final result is what I expect it to be, but each user showing up twice before committing makes me feel uncomfortable; I’d like to understand what’s going on there. Do I miss something in my relationship/backref setup, or shall I just ignore this?

Answer 1

When you call

self._pm_users.append(PersonalMessageUser(
    message=self, user_type='From', user=from_,
))

you have twice append object to _pm_users list

This should work for you:

PersonalMessageUser(
    message=self, user_type='From', user=from_,
)

or

self._pm_users.append(
    PersonalMessageUser(user_type='From', user=from_,)
)       

When set relationship property, sqlalchemy associate objects for you