[CentralOH] SQLAlchemy

[Mark Erbaugh]

By default, SQLAlchemy converts child rows in a 1:N relationship into a Python list and instruments that list so that when you write the parent object back to the database, inserts, updates and deletes are issued to bring the database table in line with the local version.

OTOH, if you query a table, you can get multiple rows back also in a Python list.  But this list is not instrumented and it seems like you have to manually manage the session object to keep it in sync with changes to the list. I have a use case where the user will be editing rows from an entire table in a single GUI screen. It's possible that in addition to editing existing rows, the user can add new rows and delete existing rows. At first pass, I though I could use the session's add() and delete() methods. but I've run into a minor problem. It is possible that a user could add a new row then decide to delete that row before committing the changes to the database. It looks like SQLAlchemy doesn't like to delete a row that hasn't been persisted to the database. To remove a row that hasn't been persisted, you need to call the session's expunge() method.

It seems like I'm going to have to keep track of the status of the individual rows so I can properly apply add(), delete() and expunge() calls, but this seems like I may be duplicating something that SQLAlchemy already does when managing a child collection.

Or, am I trying to do to much?  Should the GUI retrieve and write individual rows as the user edits the table?  In this particular use case,  there's no real need to do all the updates at once as the edits to each individual row are independent.

Thanks,
Mark