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.
On Tue, Aug 11, 2020, at 11:16 AM, William Phillips wrote:
> 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.
>
> --
> 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/0570a6cb-a63e-41c8-ab64-78ec0db13ed2o%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/0570a6cb-a63e-41c8-ab64-78ec0db13ed2o%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/7514d5a0-1a6f-4129-becd-02858a278609%40www.fastmail.com.
