How to return Flask-SqlAlchemy error details

Question

I'm using Flask 1.0, Flask-SqlAlchemy 2 and Angular 7.

When SqlAlchemy throws an error I want to show a tailored error message in the frontend.

There is a section in the official Flask documentation about how to handle errors and also a similar question here on SO, that is related to Flask-Restless though. And yet I'm not able to connect the dots.

When SqlAlchemy throws an error, it looks something like this:

DETAIL:  Key (id)=(123) is not present in table "foo".

I'm returning the error to the route:

try:
    db.session.commit()
except Exception as error:
    db.session.flush()
    db.session.rollback()
    return error

In the route I'm checking if it is an error:

if status == True:
    return jsonify( { "success": True } ), 201
else:
    return error_response(500, str(status))

And my error_response class looks like this:

def error_response(status_code, message=None):
    payload = {"error": HTTP_STATUS_CODES.get(status_code, "Unknown error")}
    if message:
        payload["message"] = message
        response = jsonify(payload)
        response.status_code = status_code
    return response

But the response json just contains a generic error message:

"message": "Http failure response for http://127.0.0.1:5000/database/add_foo: 0 Unknown Error"

score 3 · Answer 1 · answered May 26 '19 at 15:30

You can read the error object and create your own custom message out of it. for all details try printing error.\__dict__ in console.

for example:

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import Column
from sqlalchemy import Integer, String

db_uri = 'sqlite:///'
engine = create_engine(db_uri)
conn = engine.connect()

# Create a metadata instance
meta = MetaData(engine)
table = Table('user', meta,
   Column('id', Integer, primary_key=True),
   Column('l_name', String),
   Column('f_name', String))
meta.create_all()
# Insert Data
conn.execute(table.insert(),[
   {'id':1,'l_name':'Hi','f_name':'bob'},
   {'id':2,'l_name':'Hello','f_name':'john'},
   {'id':3,'l_name':'yo','f_name':'bob-john'}])

result =conn.execute("SELECT * FROM user")
for res in result:
  print(res)
# Intensionally violating unique constraint
try:
  ins = table.insert().values(
      id=3,
      l_name='Hello',
      f_name='World')
# conn = engine.connect()
  conn.execute(ins)
except Exception as error:
  print(str(error.orig) + " for parameters" + str(error.params))

output will be :-

Thank you very much for your help. It took me a while to figure why your solution didn't work for me (see below). Anyhow I'm sure it's helpful for other readers, so it's really appreciated! — user3255061, Jun 08 '19 at 19:25

score 0 · Answer 2 · answered Jun 08 '19 at 19:24

Turns out the error was returned when I tried to do a bulk save:

db.session.bulk_save_objects(companies_to_add, return_defaults = True)

I was under the impression that an error would just be raised when performing either

db.session.commit()

or

db.session.flush()

Apparantly I was wrong. I now put the bulk save in a try block:

try:
    db.session.bulk_save_objects(companies_to_add, return_defaults = True)
except Exception as error:
    db.session.rollback()
    return error

Now I'm able to catch the error in the frontend.

How to return Flask-SqlAlchemy error details

2 Answers2