Re: [sqlalchemy] Re: Deletion of a row from an association table
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() On Saturday, August 15, 2020 at 3:58:37 PM UTC-4 Simon King wrote: > 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 > > On Wed, Aug 12, 2020 at 3:05 AM William Phillips > wrote: > > > > 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+...@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 - 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
Re: [sqlalchemy] Re: Deletion of a row from an association table
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 On Wed, Aug 12, 2020 at 3:05 AM William Phillips wrote: > > 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 - 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/CAFHwexdhhOPgkXi8owvS0t1hXE68zZYf9kam_MOYZ6PpjCe4Ew%40mail.gmail.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.
