Is there a way, at least, of preventing commit in every insert?
Doing that:
for datum in data:
statement=table.insert().values(**datum).returning(table.c.rowid)
inserted_rowids=conn.execute(statement)
rowid=inserted_rowids.fetchone()[0]
there is a commit after every insert
El miércoles, 27 de noviembre de 2019, 19:07:31 (UTC+1), Mike Bayer
escribió:
>
>
>
> 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 [email protected].
> 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 [email protected] <javascript:>.
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a1373cde-5de6-4d54-84dc-aa980d467f09%40googlegroups.com.
