Re: documenting whether or not the seq_of_parameters to executemany is expected to be run in order given
Chris Clark <[email protected]> Fri, 7 Apr 2023 17:46:40 +0000
| Newsgroups | gmane.comp.python.db |
|---|---|
| Message-ID | <BYAPR06MB5925FCFF8A17AF2EAD7A1E5D9F969@BYAPR06MB5925.namprd06.prod.outlook.com> |
--===============8344678171012914243== Content-Language: en-US Content-Type: multipart/alternative; boundary="_000_BYAPR06MB5925FCFF8A17AF2EAD7A1E5D9F969BYAPR06MB5925namp_" --_000_BYAPR06MB5925FCFF8A17AF2EAD7A1E5D9F969BYAPR06MB5925namp_ Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Good idea! A statement to the effect that it is DBMS (and potentially driver) dependen= t seems a good clarification. Chris From: DB-SIG <[email protected]> On Behalf= Of Mike Bayer Sent: Friday, April 7, 2023 10:15 AM To: [email protected] Subject: [DB-SIG] documenting whether or not the seq_of_parameters to execu= temany is expected to be run in order given hey list - as $subject says, if we run: cursor.executemany("insert into table (a, b) values (?, ?)", [(1, 1), (2, 2= ), (3, 3)]) it should be obvious that most people would *expect* that the three paramet= er sets given are INSERTed in the order that was given. This could be an issue if perhaps the rows being inserted contained values = that refer to previous rows via foreign key. Or if attempting to run an= UPDATE, where we would like the order of rows UPDATEd to be deterministic,= so that we can avoid deadlocks with other processes that may be UPDATEing = some subset of those same rows in a different transaction. it's obviously also an issue for developers expecting server-generated valu= es to follow some sequence, however if you bring that use case up you will = get a flock of lecturers scolding you for this suggestion, so let's ignore = that use case (that is not my use case). However pep-249 doesn't indicate this behavior one way or the other, that i= s, whether we should not expect this, or we should expect this, or that it'= s up to the DBAPI to tell us what to expect. I bring this up because a common optimization for executemany of an INSERT = is to rewrite the statement like this: "INSERT INTO table (a, b) VALUES (1, 1), (2, 2), (3, 3)" For example see what Pymysql does, using the regex at https://github.com/Py= MySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors= .py#L5 to rewrite the statement here : https://github.com/PyMySQL/PyMySQL/b= lob/885841f3fee416c222a75d83a81f74d3dcd71b51/pymysql/cursors.py#L162 So it's also the case that most databases given the INSERT statement above = will run the VALUES entries in that order, after all, why *wouldnt* they. = But it turns out a similar statement run on MS SQL Server, using explicit = table-valued entries in order, in some cases will actually insert the rows = in some other order (optimizing for it seems like indexing of values in som= e way that relate to some foreign key constraint). My actual problem from= there gets into that I'm also trying to use RETURNING , but that's not the= scope of the question here. The scope here is, should pep-249 add some verbiage: "the order in which pa= rameters are processed by executemany() should not be assumed to be in the = order the parameters were given". which IMO would be crazy. but if that's= the reality, maybe it should be stated. I'd of course *prefer* if it wer= e stated that executemany() should process the given params in the order gi= ven. But I'm not too optimistic about that :) --_000_BYAPR06MB5925FCFF8A17AF2EAD7A1E5D9F969BYAPR06MB5925namp_ Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable <html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr= osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:= //www.w3.org/TR/REC-html40"> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"= > <meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)"> <style><!-- /* Font Definitions */ @font-face =09{font-family:"Cambria Math"; =09panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face =09{font-family:Calibri; =09panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal =09{margin:0in; =09font-size:11.0pt; =09font-family:"Calibri",sans-serif;} a:link, span.MsoHyperlink =09{mso-style-priority:99; =09color:blue; =09text-decoration:underline;} span.EmailStyle19 =09{mso-style-type:personal-reply; =09font-family:"Calibri",sans-serif; =09color:windowtext;} .MsoChpDefault =09{mso-style-type:export-only; =09font-size:10.0pt;} @page WordSection1 =09{size:8.5in 11.0in; =09margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 =09{page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext=3D"edit"> <o:idmap v:ext=3D"edit" data=3D"1" /> </o:shapelayout></xml><![endif]--> </head> <body lang=3D"EN-US" link=3D"blue" vlink=3D"purple" style=3D"word-wrap:brea= k-word"> <div class=3D"WordSection1"> <p class=3D"MsoNormal">Good idea!<o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal">A statement to the effect that it is DBMS (and poten= tially driver) dependent seems a good clarification.<o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal">Chris<o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <div> <div style=3D"border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in = 0in 0in"> <p class=3D"MsoNormal"><b>From:</b> DB-SIG <db-sig-bounces+chris.clark= [email protected]> <b>On Behalf Of </b>Mike Bayer<br> <b>Sent:</b> Friday, April 7, 2023 10:15 AM<br> <b>To:</b> [email protected]<br> <b>Subject:</b> [DB-SIG] documenting whether or not the seq_of_parameters t= o executemany is expected to be run in order given<o:p></o:p></p> </div> </div> <p class=3D"MsoNormal"><o:p> </o:p></p> <div> <p class=3D"MsoNormal">hey list -<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">as $subject says, if we run:<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">cursor.executemany("insert into table (a, b) va= lues (?, ?)", [(1, 1), (2, 2), (3, 3)])<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">it should be obvious that most people would *expect*= that the three parameter sets given are INSERTed in the order that was giv= en.<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">This could be an issue if perhaps the rows being ins= erted contained values that refer to previous rows via foreign key. &n= bsp; Or if attempting to run an UPDATE, where we would like the= order of rows UPDATEd to be deterministic, so that we can avoid deadlocks with other processes that may be UPDATEing some subset= of those same rows in a different transaction.<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">it's obviously also an issue for developers expectin= g server-generated values to follow some sequence, however if you bring tha= t use case up you will get a flock of lecturers scolding you for this sugge= stion, so let's ignore that use case (that is not my use case).<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">However pep-249 doesn't indicate this behavior one w= ay or the other, that is, whether we should not expect this, or we should e= xpect this, or that it's up to the DBAPI to tell us what to expect.<o:p></o= :p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">I bring this up because a common optimization for ex= ecutemany of an INSERT is to rewrite the statement like this:<o:p></o:p></p= > </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">"INSERT INTO table (a, b) VALUES (1, 1), (2, 2)= , (3, 3)"<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">For example see what Pymysql does, using the regex a= t <a href=3D"https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d= 83a81f74d3dcd71b51/pymysql/cursors.py#L5"> https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd= 71b51/pymysql/cursors.py#L5</a> to rewrite the statement here : <a href=3D"https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83= a81f74d3dcd71b51/pymysql/cursors.py#L162"> https://github.com/PyMySQL/PyMySQL/blob/885841f3fee416c222a75d83a81f74d3dcd= 71b51/pymysql/cursors.py#L162</a><o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">So it's also the case that most databases given the = INSERT statement above will run the VALUES entries in that order, after all= , why *wouldnt* they. But it turns out a similar statement run = on MS SQL Server, using explicit table-valued entries in order, in some cases will actually insert the rows in some othe= r order (optimizing for it seems like indexing of values in some way that r= elate to some foreign key constraint). My actual problem from t= here gets into that I'm also trying to use RETURNING , but that's not the scope of the question here.<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal">The scope here is, should pep-249 add some verbiage:= "the order in which parameters are processed by executemany() should = not be assumed to be in the order the parameters were given". wh= ich IMO would be crazy. but if that's the reality, maybe it should be stated. I'd of course *prefer* if it were s= tated that executemany() should process the given params in the order given= . But I'm not too optimistic about that :)<o:p></o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> <div> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> </div> </body> </html> --_000_BYAPR06MB5925FCFF8A17AF2EAD7A1E5D9F969BYAPR06MB5925namp_-- --===============8344678171012914243== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ DB-SIG maillist - [email protected] https://mail.python.org/mailman/listinfo/db-sig --===============8344678171012914243==--