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 <[email protected]>
> 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 [email protected].
> > 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a9c6f82a-79a9-45e6-94b6-8322454aad40n%40googlegroups.com.
