Update multiple rows in SQLite Databse

[William Phillips]

I am developing a small cooking recipe application.  I have a problem when it comes to updating data taken from a table (QTbableWidget).  The updated data may contain more rows than the original already in the database ( or less). 

I usually use a dictionary with the keys corresponding to the table column fields to transfer the data to the database.

My first impression is to delete all the rows in the database as it pertains to the data and insert the new data but this does not seem very elegant.  Is there a way to insert, delete and update all at one series (grouping) of commands?

The database:

"""
define the database:
tblCategory,   tblIngredients,   tblRecipe
"""
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")
   
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)
    ingredientRecipeKey = Column(Integer, ForeignKey("tblRecipe.recipeKey"))
    ingredientDescription = Column(String(100))
    ingredientQuantity = Column(String(100))
    recipe = relationship("Recipe", back_populates="tblIngredients")

The target table is: Ingredients
The dialog uses a Table to display the data.

[Mike Bayer]

since you need to track removals also I would likely keep track of individual data manipulation operations on the data from the point that it's loaded til the point that it's persisted again.

The SQLAlchemy ORM's "unit of work" does exactly this, in fact, so if you loaded 25 Ingredient objects, then displayed that in your GUI, allowed the user to edit the Ingredient objects, you would simply session.delete() the objects that the user deleted, and session.add() the Ingredient objects that are new since you last looked at the database.  then you call session.commit() and it would emit INSERT/UPDATE/DELETE as necessary.

essentially you just keep track of the primary keys and make sure you can represent keys to be deleted vs. keys to be updated or inserted.

--

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/0570a6cb-a63e-41c8-ab64-78ec0db13ed2o%40googlegroups.com.

[William Phillips]

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.

[William Phillips]

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

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?

[Jonathan Vanasco]

I believe your error is tied to this section of code:

 

    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)

It looks like you are iterating through this dict, creating new ingredients, and adding them to the recipe/database.

This is fine for CREATE, but is raising an integrity error on UPDATE because the ingredients already exist and you are creating a new entry on every iteration.

A lazy way to address this would be something like: remove all the existing ingredients, flush, then run this loop. 

A common way to handle this is the bit of Mike's suggestion which you missed: calculate the differences between the set of old and new items to determine which ingredients need to be added or removed (or updated, as that seems possible now).  Within the Unit of Work, as mike said, you need to delete and add (and also update it would seem).

[William Phillips]

You are right, I misinterpreted Mike's suggestion.  I expected "Unit of work" to be an code process but it's a concept.  The changes to related data rows of the table in the recipe unit must be handled by my python code not SqlAlchemy.  Since reading your comments, I have begun to develop a general process: handle the row deletions (if any) first then the updates and finally the inserts.  Flagging changes to the table rows as they occur seems to be the way to go.

[William Phillips]

I have finally found a way to insert, update and delete ingredients from the recipe application.
It is probably not the best way but it works.
First since I had a button in the form to add an ingredient, I used a button to delete a selected ingredient.

As for the Update and insert into an existing recipe placed the entire recipe into a dictionary of dictionaries

Data = {'Recipe': {'recipeName': 'Test', 'recipeDescription': '1. Mix everything\n2. Cook\n3. Turn off Stove', 'recipeCategory': 2, 'recipeKey': 33}, 'Ingredients': {'update': [['133', 'Item1', 33, '1'], ['134', 'Item2', 33, '6'], ['136', 'Item4', 33, '4']], 'insert': [[None, 'Item5', 33, '5']]}}

The 'Recipe' key updates the Recipe table and the 'Ingredients' updates (if necessary inserts) ingredient data into the Ingredient Table

The code:

def updateRecipe(data):
    session = connectToDatabase()
    

    primeKey = data['Recipe']['recipeKey']
    
    # Update the recipe table
    recipeResult = session.query(Recipe).filter(Recipe.recipeKey==primeKey).one()
   
    recipeResult.recipeName = data['Recipe']['recipeName']
    recipeResult.recipeCategory = data['Recipe']['recipeCategory']
    recipeResult.recipeDescription = data['Recipe']['recipeDescription']
    
    
    # update the ingredients table
    IngredData = data.pop('Ingredients')
    ingredientResult = session.query(Ingredients).filter(Ingredients.ingredientRecipeKey==primeKey)
    for Ingred in ingredientResult:
        for item in IngredData['update']:
            if int(Ingred.ingredientKey) == int(item[0]):
                Ingred.ingredientDescription = item[1]
                #Ingred.ingredientRecipeKey = item[2]
                Ingred.ingredientQuantity = item[3]
                session.add(Ingred)
                
    # insert New Ingredients:
    key = 'insert'
    if key in IngredData.keys():
        for item in IngredData['insert']:
            recipeResult.tblIngredients.append(Ingredients(ingredientDescription=item[1],
                                                ingredientQuantity=item[3]))
    session.add(recipeResult)
    session.commit()
    session.close()