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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a4c68342-0082-4fb6-bb01-360eca5e9e46o%40googlegroups.com.
