On Wed, Nov 27, 2019, at 1:05 PM, Javier Collado Jiménez wrote: > One more thing about returning data. When I insert rows one by one, it works: > for datum in data: > statement=table.insert().values(**datum).returning(table.c.rowid) > inserted_rowids=conn.execute(statement) > rowid=inserted_rowids.fetchone()[0] > > But when I try to do it in a bundle if there is more than one row: > statement=table.insert().returning(table.c.rowid) > inserted_rowids=conn.execute(statement, data) > for rowid in inserted_rowids: > print(rowid) > > File > "/home/pyweb/projects/T4toBHStore/lib/python3.4/site-packages/sqlalchemy/engine/result.py", > line 1266, in fetchone > row = self._fetchone_impl() > File > "/home/pyweb/projects/T4toBHStore/lib/python3.4/site-packages/sqlalchemy/engine/result.py", > line 1146, in _fetchone_impl > return self.cursor.fetchone() > cx_Oracle.InterfaceError: not a query > > > Is there a way to do a batch insert and recover every rowid?
unfortunately no, DBAPI drivers do not support RETURNING with executemany(). > > Thank you!! > > > El miércoles, 20 de noviembre de 2019, 16:39:36 (UTC+1), Mike Bayer escribió: >> >> >> On Wed, Nov 20, 2019, at 9:05 AM, Javier Collado Jiménez wrote: >>> Hello, >>> >>> Using ORM, i'm inserting like that: >>> >>> session.add(table_mapper(**datum)) >>> >>> And updating: >>> >>> session.query(dest_table).filter_by(**filters).filter(filter_column > >>> from_date).update(datum, synchronize_session=False) >>> >>> >>> Is there a way to get returning rowid of the rows affected. >>> >>> I want to keep them to be used in further updates (it's really faster if >>> you use rowid in UPDATE WHERE clause) >> >> you would need to construct a core UPDATE statement adding this column using >> returning() (see >> https://docs.sqlalchemy.org/en/13/core/dml.html?highlight=returning#sqlalchemy.sql.expression.Update.returning >> ). You might want to add it to your table definition as a "system" column >> perhaps, not really sure. making a system column is illustrated at >> https://docs.sqlalchemy.org/en/13/orm/versioning.html?highlight=system#server-side-version-counters >> >> >> >>> >>> -- >>> 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 sqlal...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/cc45324e-e194-4030-b9f7-a48a36c19747%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/cc45324e-e194-4030-b9f7-a48a36c19747%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/5701f7d0-d8fb-4c32-97d9-396af37f8bcb%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/5701f7d0-d8fb-4c32-97d9-396af37f8bcb%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/b7371b21-c3d0-424e-b1e6-3bc7ac2dc507%40www.fastmail.com.
