How to actively avoid duplicate rows from the angle of the code not database?
<p>I get a table called <code>tb_user_portrait</code> which saves customers' head portrait, its schema looks quite simple,</p>

<pre><code>CREATE TABLE `tb_user_portrait` (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    user_id BIGINT NOT NULL DEFAULT 0 COMMENT 'custormer id',
    portrait_hash CHAR(255) NOT NULL DEFAULT '' COMMENT 'portrait image hash',
    is_valid TINYINT NOT NULL DEFAULT 1 COMMENT 'validation flag of this row',
    primary key(id)
)
</code></pre>

<p>I don't set any unique key, because customers may upload head portraits several times. However each user is supposed to have only one valid portrait(i.e. each of them could have only one <code>is_valid=1</code> record).</p>

<p>My code to deal with the uploading operation is simple too, </p>

<pre><code>def upload(user_id, portrait_hash, is_valid=1):
    # find the last is_valid=1 records, and set invalid
    portrait = DBSession().query(UserPortrait).\
        filter(UserPortrait.user_id == user_id).\
        filter(UserPortrait.is_valid == 1).\
        scalar()
    if portrait:
        portrait.is_valid = 0
        DBSession().add(portrait)
    # create new valid portrait and save it to db
    new_portrait = UserPortrait(
        user_id=user_id,
        portrait_hash=portrait_hash,
        is_valid=is_valid)
    DBSession().add(new_portrait)
    DBSession().commit()
</code></pre>

<p>Although I firstly find out the valid record and set invalid, I always come across <code>MultipleRowsFound</code> and there're more than one records(<code>is_valid=1</code>) in the database.</p>

<p>By the way, I use <strong>SQLAlchemy</strong> and turn the <code>session.autoflush</code> on.</p>

<p>I'm wondering why it happens? Is there any best practice to avoid this?</p>

<p>From SQLAlchemy:</p>

<blockquote>
  <p>autoflush â When True, all query operations will issue a flush() call to this Session before proceeding. This is a convenience feature so that flush() need not be called repeatedly in order for database queries to retrieve results. Itâs typical that autoflush is used in conjunction with autocommit=False. In this scenario, explicit calls to flush() are rarely needed; you usually only need to call commit() (which flushes) to finalize changes.</p>
</blockquote>

<p>So basically you don't commit your change to database, that's why it doesn't work and you have more than one record with <code>is_invalid = 1</code>.</p>

<p>Instead of turning on <code>session.autoflush</code> try to do it manually, so here:</p>

<pre><code>...
if portrait:
    portrait.is_valid = 0
    DBSession().add(portrait)
    DBSession().flush() # &lt;-- here

new_portrait = UserPortrait(
    user_id=user_id,
    portrait_hash=portrait_hash,
    is_valid=is_valid)
...
</code></pre>

<p>And if it still doesn't work, instead of:</p>

<pre><code>    DBSession().flush()
</code></pre>

<p>just use:</p>

<pre><code>    DBSession().commit()
</code></pre>

python mysql python-2.7 sqlalchemy