Why am I getting an sqlalchemy.exc.ProgrammingError rather than a sqlalchemy.exc.IntegrityError?
<p>I'm writing some account creation code and trying to catch a particular sqlalchemy exception so that I can feed back an appropriate error message when a user registers an account with an email that is already associated to an existing account.</p>

<p>I was expecting to get an IntegrityError when this happened but I am instead getting a ProgrammingError. I could happily catch ProgrammingError instead but I'm trying to understand why I'm not getting what I'd expect.</p>

<p>I've cut down the model and code for clarity but the model looks like:</p>

<pre><code>from service import db
from sqlalchemy import Index

class UserProfile(db.Model):
user_id = db.Column(db.String(255), nullable=False, primary_key=True)
email = db.Column(db.String(255), nullable=False)

def __init__(self, account_data):
    self.user_id = account_data['userId']
    self.email = account_data['email'].lower()

def __repr__(self):
    return 'UserID-{}, Email-{}'.format(self.user_id,self.email)                                                                            

Index('idx_email', UserProfile.email, unique=True)
</code></pre>

<p>And the main bit of the code looks like:</p>

<pre><code>@app.route('/create_account', methods=['POST'])
def create_account():

account_data = request.get_json()

account_details = UserProfile(account_data)
try:
    db.session.add(account_details)
    db.session.flush()

    # do stuff

    db.session.commit()

except ProgrammingError as err:
    db.session.rollback()
    if "duplicate key value violates unique constraint \"idx_email\"" in str(err):
        LOGGER.error('Email address already in use!'
</code></pre>

<p>So if I post in some json for example:</p>

<pre><code>{
  "userId": "Fred", 
  "email": "a@b.com"
}
</code></pre>

<p>and then post again with a different userId but the same email:</p>

<pre><code>{
  "userId": "Bob", 
  "email": "a@b.com"
}
</code></pre>

<p>I would expect the second post to raise an IntegrityError but I'm seeing it raise a ProgrammingError:</p>

<pre><code>sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError)
('ERROR', 
 '23505', 
 'duplicate key value violates unique constraint "idx_email"', 
 'Key (email)=(a@b.com) already exists.', 
 'public', 
 'user_profile', 
 'idx_email',  
 'nbtinsert.c', 
 '406', 
 '_bt_check_unique', '', '') 
[SQL: 'INSERT INTO user_profile (user_id, email) VALUES (%s, %s)']
[parameters: ('Bob', 'a@b.com')]
</code></pre>

<p>What am I missing?</p>

<p>Unfortunately, when it comes to <a href="https://www.python.org/dev/peps/pep-0249" rel="nofollow">DBAPI</a> errors SQLAlchemy merely wraps the exception raised by the underlying dbapi compatible library.</p>

<p>That is, SQLAlchemy raises a <code>ProgrammingError</code> in particular only <em>because <code>pg8000</code> chose to raise a <code>ProgrammingError</code></em>.  </p>

<p>If you had been using <code>psycopg2</code> to manage your underlying connection, your error would have manifested itself as an <code>IntegrityError</code> (as expected).</p>

<p>According to the <a href="http://pythonhosted.org/pg8000/dbapi.html#pg8000.IntegrityError" rel="nofollow"><code>pg8000</code> documentation</a>, it will never raise an <code>IntegrityError</code>.  In fact it will not raise any of the following:</p>

<ul>
<li>IntegrityError;</li>
<li>DataError;</li>
<li>DatabaseError;</li>
<li>OperationalError; </li>
</ul>

<p>The lesson here is that when it comes to database level errors, you cannot guarantee the type that SQLAlchemy will throw across different dbapi connectors.</p>

python sqlalchemy