Deletion of a row from an association table

[William Phillips]

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.

[Jonathan Vanasco]

Can you share the full model for these 3 classes, which includes the relationship declarations?

[William Phillips]

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,

[Jonathan Vanasco]

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.

[William Phillips]

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?

[William Phillips]

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

[Simon King]

SQLAlchemy normally presents a many-to-many relationship as a list on
both sides. You've got "Machine.children", which is a list of Options,
and "Option.parents", which is a list of Machines.

If you remove one of the options from a machine.children list, you'll
find that SQLAlchemy removes the entry from the association table.
Something like this:

machine.children.remove(option_to_remove)

However, this does have the downside that when you access
"machine.children", SQLAlchemy will load all the Options for that
Machine from the database. This is a waste of effort if you are only
trying to delete one of them. (But if you've got them loaded anyway,
it doesn't matter)

The other option is to delete the row explicitly, something like this:

statement = Machine_Options.delete().where(
Machine_Options.c.machine_FK == machine.machine_ID,
Machine_Options.c.options_FK == option.option_ID
)
session.execute(statement)

But beware that if you do this, any machines or options already loaded
in your session won't be aware that the delete happened. If they had
already loaded their "parents" or "children" relationships, that
cached data will not match what is in the database.

Hope that helps,

Simon

> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/45da4231-3550-4f5b-882e-9e61bef86bd5o%40googlegroups.com.

[William Phillips]

Sorry I was not back sooner.  Thank-you Simon King, you have solved my problem.  As anyone can guess I am still in the learning stages of SqlAlchemy.  My python/Sqlite solution has shown that I have neglected SqlAlchemy core.  When I first downloaded and studied SqlAlchemy, I only glanced at the CORE.  I went directly to ORM which intrigued me.  I could have tried your CORE suggestion which works.  Didn't even think to try.

I am glad that you identified the opposite of my option "append" comand, "remove".  After your post, I even found the documentation for the code.  I have tried the command and it works the way I want it.

The code:
def removeOption(machineKey,  OptionKey):

    session = connectToDatabase()
   
    machineData = session.query(Machine).filter(Machine.machine_ID == machineKey).one()
    optionData = session.query(Options).filter(Options. options_ID == OptionKey).one()
    machineData.children.remove(optionData)
    session.add(machineData)
    session.commit()
    session.close()

Problem solved.  The option has been removed from the association table, thus disconnected from the machine and both the machine and option tables remain intact.

For completion purposes here is the code to connect an option to a machine which is the same except for append/remove: (there is probably a way of converting these two functions into a single function)

def connectOption(machineKey, optionKey):
   
    session = connectToDatabase()
   
    machineData = session.query(Machine).filter(Machine.machine_ID == machineKey).one()
    optionData = session.query(Options).filter(Options. options_ID == optionKey).one()
    machineData.children.append(optionData)
    session.add(machineData)
    session.commit()
    session.close()