Message-ID:

Portability should be the default. -- Larry Wall in <199711072201.OAA01123@wall.org>

devel / comp.lang.python / Re: SQL rollback of multiple inserts involving constraints

Re: SQL rollback of multiple inserts involving constraints

<mailman.207.1699632225.3828.python-list@python.org>

https://www.novabbs.com/devel/article-flat.php?id=24424&group=comp.lang.python#24424

copy link Newsgroups: comp.lang.python

Path: i2pn2.org!rocksolid2!news.neodome.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: jacob.kr...@gmail.com (Jacob Kruger)
Newsgroups: comp.lang.python
Subject: Re: SQL rollback of multiple inserts involving constraints
Date: Fri, 10 Nov 2023 18:03:34 +0200
Lines: 40
Message-ID: <mailman.207.1699632225.3828.python-list@python.org>
References: <877cmqt1dk.fsf@zedat.fu-berlin.de>
<9cd27924-cd96-8013-794c-97acbbd4562f@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de jyOe5K55o8xCqoCmHyrTWw8nFH+78+gnKVJMfxd6JXsg==
Cancel-Lock: sha1:kCrTZi2LZZeg+kGESC9h1kDQs9k= sha256:mgOOJcYImgkF50tK7fJlq9eed5u9jIwdMw20VbfT/+E=
Return-Path: <jacob.kruger.work@gmail.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
Authentication-Results: mail.python.org; dkim=pass
reason="2048-bit key; unprotected key"
header.d=gmail.com header.i=@gmail.com header.b=bTdaaNef;
dkim-adsp=pass; dkim-atps=neutral
X-Spam-Status: OK 0.009
X-Spam-Evidence: '*H*': 0.98; '*S*': 0.00; 'performing': 0.07; 'url-
ip:162.159/16': 0.07; 'url-ip:162.159.152.4/32': 0.09; 'url-
ip:162.159.152/24': 0.09; 'url-ip:162.159.153.4/32': 0.09; 'url-
ip:162.159.153/24': 0.09; 'url:medium': 0.09; 'cheers,': 0.11;
'problem.': 0.15; 'bennett': 0.16; 'insertion': 0.16; 'key.':
0.16; 'wrote:': 0.16; 'problem': 0.16; 'values': 0.17; 'message-
id:@gmail.com': 0.18; 'group,': 0.19; 'to:addr:python-list': 0.20;
'perform': 0.26; 'group.': 0.28; 'header:User-Agent:1': 0.30;
'think': 0.32; 'question': 0.32; 'actions': 0.32; 'python-list':
0.32; 'there': 0.33; 'able': 0.34; 'header:In-Reply-To:1': 0.34;
'received:google.com': 0.34; 'from:addr:gmail.com': 0.35; "skip:'
10": 0.37; 'using': 0.37; 'received:192.168': 0.37; 'two': 0.39;
'single': 0.39; 'table': 0.39; 'both': 0.40; 'something': 0.40;
'should': 0.40; 'method': 0.61; 'key': 0.64; 'transaction': 0.64;
'primary': 0.67; 'offer': 0.71; 'skip:f 20': 0.75; 'database':
0.80; 'tables': 0.84
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=20230601; t=1699632223; x=1700237023; darn=python.org;
h=content-transfer-encoding:in-reply-to:from:references:to
:content-language:subject:user-agent:mime-version:date:message-id
:from:to:cc:subject:date:message-id:reply-to;
bh=OHW6PZrInPABQAvYkGral0scst8T65o03c74LQKABVE=;
b=bTdaaNefJLHzKoEmbAzsH0dxiSqXZrEu22tzWypWAT3AfxzVsU/TNPRfA/VYlKviN3
lpEA0KXfUP9xdrCiIi/huqH8MD0jN9gUlmJM8N7Mj1J83Q73WVzQ0vOJnvjDmArzGvIU
dwMuuUEV7coM6DAP35GhFJrLx5NtrBrwlavZUZwl/HkyWNDQMg0TcfHTZmoqdf+rzqN2
2efKLbnNmGHNnw4buYyvzCLKFFs0p9/yD2tvHJI6LdwAfGAA5gWg6LYSlYW464dQWcIT
Z0b0RiYHeG9/VhxewUc/pQSXUlvQvefesHNT+nZMTG185rZjBHY5b2eNcyDP+8qhYEuJ
Eg+Q==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=1e100.net; s=20230601; t=1699632223; x=1700237023;
h=content-transfer-encoding:in-reply-to:from:references:to
:content-language:subject:user-agent:mime-version:date:message-id
:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to;
bh=OHW6PZrInPABQAvYkGral0scst8T65o03c74LQKABVE=;
b=jPmIGCcXKlOd5PGSjd12/gVl/UGUxDiH583vRttUqrSIhkDPcd9K3xQ8sBBggXOOwS
/RMseL40CDYRdfL3uEmgfNQdLBY/wEhX+vYIN4T9lZMlqhLd/U9xpdiuYFus8ST2ByPI
IbulZOW8GOtO8Ye5EnzVlpcZJ1llMLk/IPNaWWC+b6Rb1P5s9YkO2I/Y5FiOHxzz2ekA
0MVK5xMSI7RDhpK5zxtc+/MfOA+gacAA7qvuQrIkfyEhwotJQ1BIxEWb8swQbjMF4TqB
QifYjjAv8bPnGxz2T8NPinxl0XkOyY/r5iCoXJ7gYMkzG5q5TyZTeNEXbkKnQ56FPr0k
F3Zg==
X-Gm-Message-State: AOJu0Yzz+c7ZNn7zWJGoy/ofrQehVGF2oTQv1i2hYFfXcWZLBdWLcRG3
5fyZcv6VA7QENOomwBUVyqcE6kDMM+w=
X-Google-Smtp-Source: AGHT+IFdiO8GozAgQ3s0Fmw7Z3RIrJhpezl5O6FKo4NzzQpRrDg7M2bbuCybsf36x9R6YTbJpOXhOA==
X-Received: by 2002:a50:8702:0:b0:540:4f18:7faf with SMTP id
i2-20020a508702000000b005404f187fafmr6650279edb.13.1699632223187;
Fri, 10 Nov 2023 08:03:43 -0800 (PST)
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
Thunderbird/102.15.1
Content-Language: en-US
In-Reply-To: <877cmqt1dk.fsf@zedat.fu-berlin.de>
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.39
Precedence: list
List-Id: General discussion list for the Python programming language
<python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>,
<mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <https://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>,
<mailto:python-list-request@python.org?subject=subscribe>
X-Mailman-Original-Message-ID: <9cd27924-cd96-8013-794c-97acbbd4562f@gmail.com>
X-Mailman-Original-References: <877cmqt1dk.fsf@zedat.fu-berlin.de>

by: Jacob Kruger - Fri, 10 Nov 2023 16:03 UTC

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
>

Re: SQL rollback of multiple inserts involving constraints

<87sf5aau14.fsf@zedat.fu-berlin.de>

copy mid

https://www.novabbs.com/devel/article-flat.php?id=24433&group=comp.lang.python#24433

copy link Newsgroups: comp.lang.python

Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: loris.be...@fu-berlin.de (Loris Bennett)
Newsgroups: comp.lang.python
Subject: Re: SQL rollback of multiple inserts involving constraints
Date: Mon, 13 Nov 2023 10:20:55 +0100
Organization: ZEDAT, Freie Universität Berlin
Lines: 47
Message-ID: <87sf5aau14.fsf@zedat.fu-berlin.de>
References: <877cmqt1dk.fsf@zedat.fu-berlin.de>
<9cd27924-cd96-8013-794c-97acbbd4562f@gmail.com>
<mailman.207.1699632225.3828.python-list@python.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
X-Trace: news.uni-berlin.de wO/hR2WIXc7BLerVrbHTCQAOax/2PzRl4UVmvWtufKUF2F
Cancel-Lock: sha1:aJ9yIclsw3qCqauYWF4Uy/X/63I= sha1:YI8aGJZ7qvP4Ps42TttujNvGA5E= sha256:UrNe7/Hf6Jf0KgKGRF0ydIeT8JUVDvpsr7l4aqQTQCU=
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.2 (gnu/linux)

by: Loris Bennett - Mon, 13 Nov 2023 09:20 UTC

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

Subject	Author
Re: SQL rollback of multiple inserts involving constraints	Jacob Kruger
Re: SQL rollback of multiple inserts involving constraints	Loris Bennett