Databases
StackOverflow2013
Postsdbo
POSQLAlchemy intricacies with relationships and uncommitted objects
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLAlchemy intricacies with relationships and uncommitted objects
    primarykey
    data
    text
    <p>I am using SQLAlchemy via Flask, and I want to add simple personal messaging to my webapp. The model has a <code>User</code> class, a <code>PersonalMessage</code> class and a <code>PersonalMessageUser</code> association class, with the latter setting up relationships to the former two—nothing fancy. Here is a stripped down version:</p> <pre><code>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 '&lt;User {0.username!r} (#{0.id})&gt;'.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 '&lt;PersonalMessage {0.subject!r} (#{0.id})&gt;'.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 ( '&lt;PersonalMessageUser ' '{0.user_type}: {0.user.username!r} ' '(PM #{0.pm_id}: {0.message.subject!r})&gt;' ).format(self) </code></pre> <p>Everything works fine basically, but I noticed something strange when I played around with it in the Python interpreter: when I create a new <code>PersonalMessage</code> with one sender and one recipient, the <code>_pm_users</code> 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:</p> <pre><code>&gt;&gt;&gt; al = User(username='al') &gt;&gt;&gt; db.session.add(al) &gt;&gt;&gt; steve = User(username='steve') &gt;&gt;&gt; db.session.add(steve) &gt;&gt;&gt; db.session.commit() BEGIN (implicit) INSERT INTO user (username) VALUES (?) ('al',) INSERT INTO user (username) VALUES (?) ('steve',) COMMIT &gt;&gt;&gt; pm = PersonalMessage('subject', 'body', from_=al, to=steve) &gt;&gt;&gt; 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,) [&lt;PersonalMessageUser From: u'al' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser From: u'al' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser To: u'steve' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser To: u'steve' (PM #None: 'subject')&gt;] &gt;&gt;&gt; len(pm._pm_users) 4 &gt;&gt;&gt; db.session.add(pm) &gt;&gt;&gt; pm._pm_users [&lt;PersonalMessageUser From: u'al' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser From: u'al' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser To: u'steve' (PM #None: 'subject')&gt;, &lt;PersonalMessageUser To: u'steve' (PM #None: 'subject')&gt;] &gt;&gt;&gt; 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 &gt;&gt;&gt; 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,) [&lt;PersonalMessageUser From: u'al' (PM #1: u'subject')&gt;, &lt;PersonalMessageUser To: u'steve' (PM #1: u'subject')&gt;] </code></pre> <p>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 <code>relationship</code>/<code>backref</code> setup, or shall I just ignore this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USigor
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLAlchemy intricacies with relationships and uncommitted objects
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQLAlchemy intricacies with relationships and uncommitted objects
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQLAlchemy intricacies with relationships and uncommitted objects
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy PersonalMessageUser.user_type defined as `primary_key`? http://stackoverflow.com/questions/2415842/sqlalchemy-with-multiple-primary-keys-does-not-automatically-set-any
      singulars
      1. POSQLAlchemy intricacies with relationships and uncommitted objects
      1. USestin
    2. CO@estin There is single compound primary key (`pm_id, user_id, user_type`) and it's common solution for association table. The question you refer to is not relevant since all columns composing the primary key are set manually (directly or through relationships).
      singulars
      1. POSQLAlchemy intricacies with relationships and uncommitted objects
      1. USDenis Otkidach
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload