I recently had to implement a transaction on my app which goes as follows:
I have a:
I now want to perform this transaction or rollback if it fails:
My database is sqlite.
my code:
db_item = models.Items(title=item.title, description=item.description,
quantity=item.quantity, images=item.images,
category_name=item.category_name, status=item.status,
organization_id=item.organization_id, price=item.price)
db.begin(subtransactions=True)
db.add(db_item)
db.flush()
db_tags_to_items = [models.ItemsAndTags(tag_name=tag, item_id=db_item.id) for tag in item.tags]
db_short_descriptions = [models.ItemsShortDescription(line=line, item_id=db_item.id) for line in item.descriptions]
db.add_all(db_tags_to_items)
db.add_all(db_short_descriptions)
db.commit()
If i return db_item it returns it with the id, however if i check the db that item was not persisted nor the tags or descriptions. What am i doing wrong as i get no error.
If more of my code is needed pleaste let me know, i use the FastAPI framework with SQLAlchemy.
I managed to get it working myself. Turns out db.begin(subtransactions=True) was not really needed.
this is my crud func now and it does what i need:
# create item as transaction
def create_item_v2(db: Session, item: items_schemas.ItemCreateV2):
db_item = models.Items(title=item.title, description=item.description,
quantity=item.quantity, images=item.images,
category_name=item.category_name, status=item.status,
organization_id=item.organization_id, price=item.price)
try:
db.add(db_item)
db.flush()
db_tags_to_items = [models.ItemsAndTags(tag_name=tag, item_id=db_item.id) for tag in item.tags]
db_short_descriptions = [models.ItemsShortDescription(line=line, item_id=db_item.id) for line in item.descriptions]
db.add_all(db_tags_to_items)
db.add_all(db_short_descriptions)
db.commit()
db.refresh(db_item)
return db_item
except:
db.rollback()