[sqlalchemy] Re: Update multiple rows in SQLite Databse

[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()


-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d5d302f4-d03b-45b0-b7fb-1c9b40cb933dn%40googlegroups.com.

[sqlalchemy] Re: Update multiple rows in SQLite Databse

[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.

On Friday, August 28, 2020 at 12:51:30 PM UTC-4 Jonathan Vanasco wrote:

> 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).
>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c84e74e8-7145-40da-b3ff-33b3f31ad436n%40googlegroups.com.

[sqlalchemy] Re: Update multiple rows in SQLite Databse

['Jonathan Vanasco' via sqlalchemy]

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).

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/36add23c-b1c5-4c6a-a494-d9d71addc1a8o%40googlegroups.com.

[sqlalchemy] Re: Update multiple rows in SQLite Databse

[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?

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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/1cbe6faf-5176-4122-907c-f19573377cd4n%40googlegroups.com.

[sqlalchemy] Re: Update multiple rows in SQLite Databse

[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.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5940569f-5849-4cff-ae16-c6f5cfb84939o%40googlegroups.com.