CODEWITHSUNDEEP
pythonsqlalchemy
spannerj
spannerj

Reputation: 758

Why am I getting an sqlalchemy.exc.ProgrammingError rather than a sqlalchemy.exc.IntegrityError?

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.

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.

I've cut down the model and code for clarity but the model looks like:

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)

And the main bit of the code looks like:

@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!'

So if I post in some json for example:

{
  "userId": "Fred", 
  "email": "[email protected]"
}

and then post again with a different userId but the same email:

{
  "userId": "Bob", 
  "email": "[email protected]"
}

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

sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError)
('ERROR', 
 '23505', 
 'duplicate key value violates unique constraint "idx_email"', 
 'Key (email)=([email protected]) 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', '[email protected]')]

What am I missing?

Upvotes: 4

Views: 2223

Answers (1)

donkopotamus
donkopotamus

Reputation: 23176

Unfortunately, when it comes to DBAPI errors SQLAlchemy merely wraps the exception raised by the underlying dbapi compatible library.

That is, SQLAlchemy raises a ProgrammingError in particular only because pg8000 chose to raise a ProgrammingError.

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

According to the pg8000 documentation, it will never raise an IntegrityError. In fact it will not raise any of the following:

  • IntegrityError;
  • DataError;
  • DatabaseError;
  • OperationalError;

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.

Upvotes: 5

Related Questions