My problem seems far from solved. Searching the internet for information on
how to apply "unit of work" has proven difficult. I found general
references but no practical examples on how to concretely apply the
concept. I finally found one practical (but short) explanation at : Link
to Unit of Work
<https://medium.com/craftsmenltd/sqlalchemy-and-the-unit-of-work-pattern-dfa91a098023>
My application of the code on an SQLite database adjusting the code to my
data model.
The database model
class Recipe(Base):
__tablename__ = 'tblRecipe'
recipeKey = Column(Integer, primary_key=True)
recipeName = Column(String(100))
recipeCategory = Column(Integer, ForeignKey("tblCategory.categoryKey"))
recipeDescription = Column(String(500))
ingredient = relationship("Ingredients", cascade="save-update,
merge,delete, delete-orphan",
backref='tblRecipe',
uselist=True)
class Catagory(Base):
__tablename__ = 'tblCategory'
categoryKey = Column(Integer, primary_key=True)
categoryName = Column(String(100))
class Ingredients(Base):
__tablename__ = 'tblIngredients'
ingredientKey = Column(Integer, primary_key=True)
ingredientDescription = Column(String(100))
ingredientRecipeKey = Column(Integer, ForeignKey("tblRecipe.recipeKey"))
ingredientQuantity = Column(String(100))
recipe = relationship("Recipe", back_populates="tblIngredients")
====================================================================================
The code: (my last modification) but like my other versions it does not
work.
def updateRecipe(data):
session = connectToDatabase()
ingredDict = data.pop('Ingredients')
recipeKey = data['Recipe'].pop('recipeKey')
recipe = session.query(Recipe).filter(Recipe.recipeKey ==
recipeKey).one()
recipe.recipeName = data['Recipe']['recipeName']
recipe.recipeCategory = data['Recipe']['recipeCategory']
recipe.recipeDescription = data['Recipe']['recipeDescription']
Ingredients_item_object_list = []
for item in ingredDict:
ingredient_item = Ingredients(ingredientKey=item['ingredientKey'],
ingredientDescription=item['ingredientDescription'],
ingredientRecipeKey=recipeKey,
ingredientQuantity=item['ingredientQuantity'])
Ingredients_item_object_list.append(ingredient_item)
recipe.ingredient=Ingredients_item_object_list
session.add(recipe)
session.commit()
session.close()
The error message is:
"The debugged program raised the exception unhandled
sqlalchemy.excIntegrityError.
(sqlite3.IntegityError) UNIQUE constraint failed:
tblIngredients.ingredientKey[SQL:INSERT INTO
"tblIngredients"("ingredientKey", "ingredientDescription",
"ingredientRecipeKey", "ingredientQuantity") VALUES (?,?,?,?)] [parameters:
(('60','Butter or Margerine', 23, '4 tablespoons'), ('61', 'Beef..', 23,
'2'), ('62',....),('64', ..),(...))] (Background on this error at:
http://qlalche.me/e/13/gkpj)" File: /home.....
To shorten the error message I have eliminated some of the data to be
updated and the file and line location.
Can anyone help?
On Friday, August 14, 2020 at 2:45:48 PM UTC-4 William Phillips wrote:
> Thank Mike for the input. My preliminary research on "Unit of work" has
> shown it is exactly what I need. Still have to learn how to use it but
> I'll get there. Again thanks.
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1cbe6faf-5176-4122-907c-f19573377cd4n%40googlegroups.com.
