1. Forum
  2. Usenet
  3. COMP.LANG.PYTHON

  • SQL rollback of multiple inserts involving constraints

    From Loris Bennett@21:1/5 to All on Fri Nov 10 10:15:51 2023
    Hi,

    In my MariaDB database I have a table 'people' with 'uid' as the primary
    key and a table 'groups' with 'gid' as the primary key. I have a third
    table 'memberships' with 'uid' and 'gid' being the primary key and the constraint that values for 'uid' and 'gid' exist in the tables 'people'
    and 'groups', respectively. I am using SQLAlchemy and writing a method
    to setup a membership for a new person in a new group.

    I had assumed that I should be able to perform all three inserts
    (person, group, membership) with a single transaction and then rollback
    if there is a problem. However, the problem is that if the both the
    insert into 'people' and that into 'groups' are not first committed, the constraint on the insertion of the membership fails.

    What am I doing wrong?

    Apologies if this is actually an SQL question rather than something
    related to SQLAlchemy.

    Cheers,

    Loris

    --
    This signature is currently under constuction.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jacob Kruger@21:1/5 to Loris Bennett via Python-list on Fri Nov 10 18:03:34 2023
    Think performing a session/transaction flush after the first two inserts
    should offer the workaround before you've committed all transaction
    actions to the database finally:

    https://medium.com/@oba2311/sqlalchemy-whats-the-difference-between-a-flush-and-commit-baec6c2410a9


    HTH


    Jacob Kruger
    +2782 413 4791
    "Resistance is futile!...Acceptance is versatile..."


    On 2023/11/10 11:15, Loris Bennett via Python-list wrote:
    Hi,

    In my MariaDB database I have a table 'people' with 'uid' as the primary
    key and a table 'groups' with 'gid' as the primary key. I have a third
    table 'memberships' with 'uid' and 'gid' being the primary key and the constraint that values for 'uid' and 'gid' exist in the tables 'people'
    and 'groups', respectively. I am using SQLAlchemy and writing a method
    to setup a membership for a new person in a new group.

    I had assumed that I should be able to perform all three inserts
    (person, group, membership) with a single transaction and then rollback
    if there is a problem. However, the problem is that if the both the
    insert into 'people' and that into 'groups' are not first committed, the constraint on the insertion of the membership fails.

    What am I doing wrong?

    Apologies if this is actually an SQL question rather than something
    related to SQLAlchemy.

    Cheers,

    Loris


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Loris Bennett@21:1/5 to Jacob Kruger on Mon Nov 13 10:20:55 2023
    Jacob Kruger <jacob.kruger.work@gmail.com> writes:

    Think performing a session/transaction flush after the first two
    inserts should offer the workaround before you've committed all
    transaction actions to the database finally:

    https://medium.com/@oba2311/sqlalchemy-whats-the-difference-between-a-flush-and-commit-baec6c2410a9


    HTH

    Yes, thank you, it does. I hadn't been aware of 'flush'.

    Jacob Kruger
    +2782 413 4791
    "Resistance is futile!...Acceptance is versatile..."


    On 2023/11/10 11:15, Loris Bennett via Python-list wrote:
    Hi,

    In my MariaDB database I have a table 'people' with 'uid' as the primary
    key and a table 'groups' with 'gid' as the primary key. I have a third
    table 'memberships' with 'uid' and 'gid' being the primary key and the
    constraint that values for 'uid' and 'gid' exist in the tables 'people'
    and 'groups', respectively. I am using SQLAlchemy and writing a method
    to setup a membership for a new person in a new group.

    I had assumed that I should be able to perform all three inserts
    (person, group, membership) with a single transaction and then rollback
    if there is a problem. However, the problem is that if the both the
    insert into 'people' and that into 'groups' are not first committed, the
    constraint on the insertion of the membership fails.

    What am I doing wrong?

    Apologies if this is actually an SQL question rather than something
    related to SQLAlchemy.

    Cheers,

    Loris


    --
    Dr. Loris Bennett (Herr/Mr)
    ZEDAT, Freie Universität Berlin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)