How to catch exceptions (the best practice)

[Nestor Diaz]

Hello everybody.

I am experimenting with sqlalchemy and pyramid, following the tutorial
steps at:

https://docs.pylonsproject.org/projects/pyramid/en/latest/quick_tutorial/databases.html

The example above is a wiki, with a set of pages, every page have a
'title' and a 'body' and the 'title' have a 'unique' constraint at
database level.

Let's suppose that I added two pages, i.e. pages =
[{id:1,title:'titleA',body:'bodyA'},  {id:2,title:'titleB',body:'bodyB'}
, then I decide to update id number 2 and set title to 'titleA' which is
the same as id number 1, it then raise an exception, however I can not
catch the exception, even if I set up a try-catch block, however if I
add a DBSession.flush() inside the try, the sql sentence is executed and
therefore I can catch the exception.

I am copying the code with the 'Uncaught Exception' and the code with
the 'Caught Exception'.

The question is: Do I have to 'flush()' everytime to be sure the sql
sentence is executed, or there is a better way, or which way do you
suggest to manage the exceptions ?

Thanks for your time and attention.

This is the python code:

[...]

# Change the content and redirect to the view

try:
    from sqlalchemy.exc import IntegrityError
    page.title = appstruct['title']
    page.body = appstruct['body']
    DBSession.flush()
    url = self.request.route_url('wikipage_view', uid=uid)
    return HTTPFound(url)
except IntegrityError as exc:
    import traceback
    traceback.print_exc()
    error = colander.Invalid(wiki_form.schema)
    error['title'] = exc.args[0]
    wiki_form.error = error
    wiki_form.widget.handle_error(wiki_form, error)
    rendered_form = wiki_form.render()
    return dict(page=page,
form=rendered_form)                                                                                                                                                                   


[...]

The tracebacks:

1. Uncaught Exception:

2019-07-27 01:29:41,231 INFO  [sqlalchemy.engine.base.Engine][waitress]
UPDATE wikipages SET title=? WHERE wikipages.uid = ?
2019-07-27 01:29:41,231 INFO  [sqlalchemy.engine.base.Engine][waitress]
('titleA', 2)
2019-07-27 01:29:41,232 INFO  [sqlalchemy.engine.base.Engine][waitress]
ROLLBACK
2019-07-27 01:29:41,306 ERROR [pyramid_debugtoolbar][waitress] Uncaught
sqlalchemy.exc.IntegrityError at http://127.0.0.1:6543/2/edit
traceback url:
http://127.0.0.1:6543/_debug_toolbar/313430303936383730393639343136/exception
Traceback (most recent call last):
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
    cursor, statement, parameters, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: wikipages.title

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_debugtoolbar/toolbar.py",
line 257, in toolbar_tween
    response = _handler(request)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_debugtoolbar/panels/performance.py",
line 58, in resource_timer_handler
    result = handler(request)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
line 171, in tm_tween
    reraise(*exc_info)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/compat.py",
line 36, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
line 152, in tm_tween
    return _finish(request, manager.commit, response)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
line 96, in _finish
    reraise(*exc_info)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/compat.py",
line 36, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/pyramid_tm/__init__.py",
line 76, in _finish
    finisher()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_manager.py",
line 252, in commit
    return self.manager.commit()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_manager.py",
line 131, in commit
    return self.get().commit()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_transaction.py",
line 311, in commit
    reraise(t, v, tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_compat.py",
line 50, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_transaction.py",
line 302, in commit
    self._commitResources()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_transaction.py",
line 447, in _commitResources
    reraise(t, v, tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_compat.py",
line 50, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/transaction/_transaction.py",
line 419, in _commitResources
    rm.tpc_begin(self)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/zope/sqlalchemy/datamanager.py",
line 106, in tpc_begin
    self.session.flush()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2459, in flush
    self._flush(objects)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2597, in _flush
    transaction.rollback(_capture_exception=True)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py",
line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 154, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2557, in _flush
    flush_context.execute()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py",
line 422, in execute
    rec.execute(self)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py",
line 589, in execute
    uow,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py",
line 236, in save_obj
    update,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py",
line 996, in _emit_update_statements
    statement, multiparams
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 988, in execute
    return meth(self, multiparams, params)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1107, in _execute_clauseelement
    distilled_params,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 153, in reraise
    raise value.with_traceback(tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
    cursor, statement, parameters, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE
constraint failed: wikipages.title
[SQL: UPDATE wikipages SET title=? WHERE wikipages.uid = ?]
[parameters: ('titleA', 2)]
(Background on this error at: http://sqlalche.me/e/gkpj)

2. Caught Exception

2019-07-27 02:18:33,208 INFO  [sqlalchemy.engine.base.Engine][waitress]
UPDATE wikipages SET title=? WHERE wikipages.uid = ?
2019-07-27 02:18:33,209 INFO  [sqlalchemy.engine.base.Engine][waitress]
('titleA', 2)
2019-07-27 02:18:33,209 INFO  [sqlalchemy.engine.base.Engine][waitress]
ROLLBACK
Traceback (most recent call last):
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
    cursor, statement, parameters, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: wikipages.title

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File
"/home/ndiaz/tutrest/quick_tutorial/authentication/tutorial/views.py",
line 228, in wikipage_edit
    DBSession.flush()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/scoping.py",
line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2459, in flush
    self._flush(objects)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2597, in _flush
    transaction.rollback(_capture_exception=True)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py",
line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 154, in reraise
    raise value
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/session.py",
line 2557, in _flush
    flush_context.execute()
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py",
line 422, in execute
    rec.execute(self)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py",
line 589, in execute
    uow,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py",
line 236, in save_obj
    update,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py",
line 996, in _emit_update_statements
    statement, multiparams
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 988, in execute
    return meth(self, multiparams, params)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1107, in _execute_clauseelement
    distilled_params,
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/util/compat.py",
line 153, in reraise
    raise value.with_traceback(tb)
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1244, in _execute_context
    cursor, statement, parameters, context
  File
"/home/ndiaz/tutrest/env35/lib/python3.5/site-packages/sqlalchemy/engine/default.py",
line 550, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE
constraint failed: wikipages.title
[SQL: UPDATE wikipages SET title=? WHERE wikipages.uid = ?]
[parameters: ('titleA', 2)]
(Background on this error at: http://sqlalche.me/e/gkpj)

[Mike Bayer]

On Sat, Jul 27, 2019, at 4:53 PM, Nestor Diaz wrote:

Hello everybody.

I am experimenting with sqlalchemy and pyramid, following the tutorial

steps at:

https://docs.pylonsproject.org/projects/pyramid/en/latest/quick_tutorial/databases.html

The example above is a wiki, with a set of pages, every page have a

'title' and a 'body' and the 'title' have a 'unique' constraint at

database level.

Let's suppose that I added two pages, i.e. pages =

[{id:1,title:'titleA',body:'bodyA'},  {id:2,title:'titleB',body:'bodyB'}

, then I decide to update id number 2 and set title to 'titleA' which is

the same as id number 1, it then raise an exception, however I can not

catch the exception, even if I set up a try-catch block, however if I

add a DBSession.flush() inside the try, the sql sentence is executed and

therefore I can catch the exception.

I am copying the code with the 'Uncaught Exception' and the code with

the 'Caught Exception'.

The question is: Do I have to 'flush()' everytime to be sure the sql

sentence is executed, or there is a better way, or which way do you

suggest to manage the exceptions ?

Usually you wouldn't have a catch that is specific to an error like that, your pyramid application would have a generic error page routine that occurs for all requests, and it would occur normally at the point at which the Session is committed.   It looks like you essentially want a custom error page scheme here which I would think would be something that happens more at the pyramid level, e.g. in your controller you add some kind of token "use_this_error_page" so that Pyramid's usual error page routine can direct to this special page.  

When people do catch IntegrityError specifically, it is often because they want to try to insert a row and then switch to an UPDATE if the row already exists, and in this pattern you normally want to use a savepoint, but that doesn't seem like what's happening here.

Ultimately, for the code given, the answer is yes, you would need to flush to emit the SQL at this specific place if that's what you need, but I would seek out cleaner patterns from the Pyramid community.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/57fcf1fb-818e-0df2-df4c-906bc202a26e%40gmail.com.

[Jonathan Vanasco]

On Saturday, July 27, 2019 at 4:53:06 PM UTC-4, Nestor Diaz wrote:

it then raise an exception, however I can not
catch the exception, even if I set up a try-catch block, however if I
add a DBSession.flush() inside the try, the sql sentence is executed and
therefore I can catch the exception.

...

The question is: Do I have to 'flush()' everytime to be sure the sql
sentence is executed, or there is a better way, or which way do you
suggest to manage the exceptions ?

This is because in your specific application, you are using the `pyramid_tm` package.  `pyramid_tm` simplifies transaction based application development by wrapping your pyramid views in a 'tween' , where 'BEGIN' is issued before your view executes and a ROLLBACK or COMMIT is issued after your view executes.

The integrity error is raised by the database, so it is only raised by your explicit 'flush' or the implicit flush which pyramid_tm invokes during  the 'commit' in it's tween /after/ your view executes.  

The pyramid_tm docs have a section with dealing with errors, which goes into the 'custom error page' pattern that Mike brought up, along with some other patterns that involve making your error information persist.

 https://docs.pylonsproject.org/projects/pyramid_tm/en/latest/