Mike, thank you very much for this very thorough answer. I now understand that my use case cannot be generally supported by SQLAlchemy, given the limitations of the backends.
Yet, I still wonder how to efficiently perform inserts of large numbers of rows where the server-generated ids are subsequently needed as foreign key values. Replacing one multi-row insert by many single-row inserts must impose an enormous performance penalty, whereas retrieving the ids via a subsequent SELECT would have to rely on the specifics of the inserted data (which may not be unique). On Thursday, May 13, 2021 at 7:29:42 PM UTC-7 Mike Bayer wrote: > So the use case you are doing there was never something supported, and in > particular the value you are getting on 1.3 is not really universally > reliable, as it is relying upon cursor.lastrowid that is not defined for > multiple-row inserts, even though for InnoDB specifically the behavior is > likely deterministic. > > Per MySQLs docs at > https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id, > > "If you insert multiple rows using a single INSERT > <https://dev.mysql.com/doc/refman/8.0/en/insert.html> statement, > LAST_INSERT_ID() > <https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id> > > returns the value generated for the *first* inserted row *only*. The > reason for this is to make it possible to reproduce easily the same INSERT > <https://dev.mysql.com/doc/refman/8.0/en/insert.html> statement against > some other server.". > > now can you assume that if you inserted five rows and last_insert_id() > gave you the number 1, the rows will be numbered 1, 2, 3, 4, 5? won't > concurrent inserts possibly interfere with this? the consensus seems to > be "the values probably are predictable, for a single server MySQL > database, but it's not documented anywhere", see > https://stackoverflow.com/questions/6895679/mysqls-auto-increment-behavior-in-a-multiple-row-insert > > . that MySQL won't document this I would say is pretty telling that this > is not a long term / generalized guarantee (like what happens if this is on > Galera for example?) even though it might be the current behavior. > > For SQLAlchemy, which gets cursor.lastrowid from other database engines > also, it was definitely never assumed that "cursor.lastrowid" is useful for > an INSERT statement that has multiple rows, and this has always been an > undefined use case. The spec itself documents this: > https://www.python.org/dev/peps/pep-0249/#lastrowid "The semantics of > .lastrowid are undefined in case the last executed statement modified > more than one row, e.g. when using INSERT with .executemany()." > > This is also not what we refer to as a "returned primary keys", which > usually refers to the RETURNING SQL syntax. Only MariaDB as of 10.5 > supports that syntax, which we have not yet added support for - other than > that MySQL DBs only give us cursor.lastrowid. > > 1.4's architecture is more explicit about how things are handled so when > you send an insert().values() with a list, that's automatically a > multi-row insert and we don't collect cursor.lastrowid in this case. this > caused no regressions because cursor.lastrowid was never considered to be > defined in this case and no tests have covered it. > > if you send just one row at a time, you get the inserted_primary_key back: > > with engine.begin() as conn: > result = conn.execute(messages.insert().values({'message': 'Hello > World'})) > first_id = result.inserted_primary_key[0] > > to bypass the result and go straight to the pymysql cursor if you'd like > to rely upon the driver-specfic behavior, you can get to it as > result.context.cursor.lastrowid: > > with engine.begin() as conn: > result = conn.execute(messages.insert().values(values)) > first_id = result.context.cursor.lastrowid > > > > > > > > On Thu, May 13, 2021, at 8:49 PM, Thorsten von Stein wrote: > > > I use SQLAlchemy with MySQL. In one application, I need to obtain the > server-generated primary key values of newly inserted rows by obtaining the > value of the first inserted row with the property inserted_primary_key. > However, after upgrading to version 1.4.15, this property return only > (None,). > > See the following trivial example: > > from sqlalchemy import create_engine > from sqlalchemy import Column, Integer, Text, MetaData, Table > > engine = > create_engine('mysql+pymysql://testuser:xyz6789@localhost/testdb?charset=utf8') > > > metadata = MetaData() > messages = Table( > 'messages', metadata, > Column('id', Integer, primary_key=True), > Column('message', Text), > ) > > # on first run: > # messages.create(bind=engine) > > > values = [ > {'message': 'Hello World'}, > {'message': 'Hallo Welt'}, > ] > > with engine.begin() as conn: > result = conn.execute(messages.insert().values(values)) > first_id = result.inserted_primary_key[0] > > > print(f'first_id = {first_id}') > > > Result in SQLAlchemy-1.3.15: > > first_id = 1 > > > But in SQLAlchemy-1.4.15: > > first_id = None > > Converting to new-style querying does not help. The new property > inserted_primary_key_rows returns just nested tuples of None whenever more > than one row is inserted. This is metioned in the documentation for > backends that do not support returned primary keys -- but MySQL obviously > does! > > Any help highly appreciated. Right now I cannot think of any other > solution than sticking with 1.3. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/dd6eb61d-da0a-48d0-88d7-ef0e3ba437d8n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/dd6eb61d-da0a-48d0-88d7-ef0e3ba437d8n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a40c392a-0b65-4526-bf21-a343d53ceafcn%40googlegroups.com.
