Mailing List Archive: SQL rollback of multiple inserts involving constraints

SQL rollback of multiple inserts involving constraints

Nov 10, 2023, 1:15 AM

Post #1 of 3 (108 views)

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.
--
https://mail.python.org/mailman/listinfo/python-list

Re: SQL rollback of multiple inserts involving constraints [ In reply to ]

python-list at python

Nov 10, 2023, 8:03 AM

Post #2 of 3 (108 views)

Permalink

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
>
--
https://mail.python.org/mailman/listinfo/python-list

Re: SQL rollback of multiple inserts involving constraints [ In reply to ]

python-list at python

Nov 13, 2023, 1:20 AM

Post #3 of 3 (102 views)

Permalink

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
--
https://mail.python.org/mailman/listinfo/python-list