Use session.commit and session.rollback appropriately

Following the example here: https://docs.sqlalchemy.org/en/20/orm/session_basics.html#framing-out-a-begin-commit-rollback-block

session.add actually throws the error, so just put that in the try block, and add session.rollback in the except block. The final session.commit gets placed afterwards. This solves a latent bug where if you attempt to create an invalid object it would still use the ID that was auto-generated.

Also, adjusts tests to have the session.add call throw the error as appropriate.