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.