Re: [sqlalchemy] Update multiple rows in SQLite Databse
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
>
> .
--
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.
[sqlalchemy] Re: Deletion of a row from an association table
For the sake of completeness I am including the code to disconnect an option from a machine using only python/SQLite code. def removeOption(bladeKey, OptionKey): """ DELETE from blade_options WHERE blade_FK == ? AND options_FK == ? """ import sqlite3 dbPath = config.database_path sqliteConnection = sqlite3.connect(dbPath) cursor = sqliteConnection.cursor() sql = 'DELETE from blade_options WHERE blades_ID == ? AND options_ID == ?; ' cursor.execute(sql, (bladeKey, OptionKey, )) sqliteConnection.commit() sqliteConnection.close() return -- 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/45da4231-3550-4f5b-882e-9e61bef86bd5o%40googlegroups.com.
[sqlalchemy] Re: Deletion of a row from an association table
The situation is that I have two preloaded tables. The first is a Machine
to which one or more Options can be added. The second table has Options
can be connected to two or more machines. I've got the code to connect a
machine with an option but I can't devise the code to reverse the process,
remove an option from the machine (or vs-versa) . I seems that SqlAlchemy
does not map an association table. It does not recognize the machine_FK or
options_FK columns in a filter clause. I don't want to remove machine or
option data from the DB. I can remove the connection row from
'machine_options table by using Python/SQLite code directly. There must
be a way of doing the same action with SQLAlchemy.
It is true that if I remove a machine or an option from the database I will
have to cascade an automatic removal from the 'machine_options' table. I
will correct that aspect. This is just pulling the plug between two rows
in two different tables without changing the row data. Don't know if I am
clear?
On Tuesday, August 11, 2020 at 10:54:39 AM UTC-4, William Phillips wrote:
>
> I am working on an app using python3 and SqlAlchemy for SQLite3 database
> management. I have some tables that have a Many to Many relationship. I've
> created an association table to handle this relationship.
>
>
>
> Class Machine(Base):
> __tablename__ 'machine'
> machine_ID = Column(Integer, primary_key=True)
> etc...
> Class Options(Base):
> __tableName__ 'options'
> options_ID = Column(Integer, primary_key=True)
> etc...
>
> The association table
>
> Machine_Options = table('machine_options', Base.metadata,
> Column('machine_FK', Integer, ForeignKey('machine.machine_ID'),
> primary_key=True),
> Column('options_FK',Integer, ForeignKey('options.options_ID'),
> primary_key=True))
>
>
>
> All the items for the Machine and Options are inserted independently. When
> I want to associate a machine with an option I use an append query which
> works very well.
>
> My problem is when I want to break this association between a machine and
> an option. I have tried a direct row deletion from the association table
> using a FILTER() clause on the machine_FK and the options_FK but SqlAlchemy
> gives me an error informing me that 'Machine_Options' table has no field
> 'machine_FK'. It seems that SqlAlchemy does not map association tables. I
> have tried to remove the row from 'Machine_Options' indirectly using joins
> with the machine and options table but received another error that I can
> not delete or update using joins.
>
>
> I am looking for the code to only delete a row from the association table
> without affecting the original machine or options table.
>
>
> So far my internet search has been fruitless.
>
--
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/a4c68342-0082-4fb6-bb01-360eca5e9e46o%40googlegroups.com.
[sqlalchemy] Re: Deletion of a row from an association table
Thanks. IIRC, I think you just need to set a custom cascade on these relationships (see https://docs.sqlalchemy.org/en/13/orm/cascades.html) I am not sure which option that would be, because it sounds like your application is behaving with a "delete-orphan", but that's not set. -- 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/ba415856-5c1b-4d8e-b72a-eac9c5d90f98o%40googlegroups.com.
[sqlalchemy] Re: Deletion of a row from an association table
full model for these 3 classes:
# machine
class Machine(Base):
__tablename__ = 'machine'
machine_ID = Column(Integer, primary_key=True)
machine_Type = Column(Integer, nullable=False)
machine_model = Column(String(10), nullable=False)
machine_Weight = Column(Integer)
machine_price = Column(Float(), nullable=False)
saleRel = relationship('Sale', secondary='sale_product')
children = relationship("Options",
secondary='machine_options',
back_populates="parents")
# options
class Options(Base):
__tablename__ = 'options'
options_ID = Column(Integer, primary_key=True)
options_category = Column(String(10), nullable=False)
options_Price = Column(Float(), nullable=False)
options_Type_FK = Column(Integer, ForeignKey(
'options_Type.options_Type_ID'))
parents = relationship("Machine",
secondary=Machine_Options, back_populates="children")
#machine_options
Machine_Options = Table('machine_options', Base.metadata,
Column('machine_FK', Integer, ForeignKey('machine.machine_ID'),
primary_key=True),
Column('options_FK',Integer, ForeignKey('options.options_ID'),
primary_key=True))
--
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/f0a08fdd-1951-4b33-8ce8-5abbf4f87faeo%40googlegroups.com.
[sqlalchemy] Re: Deletion of a row from an association table
Can you share the full model for these 3 classes, which includes the relationship declarations? -- 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/f26d4470-6483-4d97-89d1-d11b0670b560o%40googlegroups.com.
[sqlalchemy] Update multiple rows in SQLite Databse
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.
[sqlalchemy] Deletion of a row from an association table
I am working on an app using python3 and SqlAlchemy for SQLite3 database
management. I have some tables that have a Many to Many relationship. I've
created an association table to handle this relationship.
Class Machine(Base):
__tablename__ 'machine'
machine_ID = Column(Integer, primary_key=True)
etc...
Class Options(Base):
__tableName__ 'options'
options_ID = Column(Integer, primary_key=True)
etc...
The association table
Machine_Options = table('machine_options', Base.metadata,
Column('machine_FK', Integer, ForeignKey('machine.machine_ID'),
primary_key=True),
Column('options_FK',Integer, ForeignKey('options.options_ID'),
primary_key=True))
All the items for the Machine and Options are inserted independently. When
I want to associate a machine with an option I use an append query which
works very well.
My problem is when I want to break this association between a machine and
an option. I have tried a direct row deletion from the association table
using a FILTER() clause on the machine_FK and the options_FK but SqlAlchemy
gives me an error informing me that 'Machine_Options' table has no field
'machine_FK'. It seems that SqlAlchemy does not map association tables. I
have tried to remove the row from 'Machine_Options' indirectly using joins
with the machine and options table but received another error that I can
not delete or update using joins.
I am looking for the code to only delete a row from the association table
without affecting the original machine or options table.
So far my internet search has been fruitless.
--
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/60cb5a82-ca33-46c9-bf1e-b70deb82b485o%40googlegroups.com.
