I am using SQLAlchemy 1.4 on Python 3.8 with a PostgreSQL database. When I try to add new records to the database, if there is an error, add does not generate any exceptions. Instead, commit will complain if anything isn't right.
Note that this is not a duplicate of this question. The problem there was that the user did not initially set nullable=False when the database was created. That clearly isn't the case here, as you can see in the MRE, the database schema is created from scratch. This also doesn't address my question about managing the error.
Here is a MRE, adding a record with null entries that are marked as not nullable:
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
email = Column(String, unique=True, nullable=False)
name = Column(String, nullable=False)
def __repr__(self):
return "<User(email='{email}', name='{name}'')>".format(
email=self.email, name=self.name
)
if __name__ == '__main__':
engine = create_engine('postgresql://{username}:{password}@127.0.0.1/{db_name}')
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
with Session() as session:
session.begin()
try:
account = session.add(User())
except Exception as e:
print('Failed to add user: {error}'.format(error=str(e))) # This won't be printed
session.rollback()
exit()
try:
session.commit()
except Exception as e:
print('Failed to commit add user: {error}'.format(error=str(e))) # The error happens here
exit()
print('Successfully added user')
This is unexpected because the SQLAlchemy page on sessions doesn't describe this, it suggests that commit won't cause any errors. Only add is checked for an exception.
1. Am I doing something wrong?
Why does this not behave like the docs suggest it should?
2. How do I manage this error? Can I also call rollback after catching an error from commit?
