Accessing rowid when adding or merging in Oracle

[Javier Collado Jiménez]

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)

[Mike Bayer]

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/cc45324e-e194-4030-b9f7-a48a36c19747%40googlegroups.com.

[Javier Collado Jiménez]

Thank you, it worked perfectly!

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.

[Javier Collado Jiménez]

Is there an easy way to construct the where clause for update with a dictionary?

I'm trying 

statement=table.update().where(**filters).values(**datum).returning(table.c.rowid)

but it doesn't work.

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.

[Simon King]

Something like this perhaps? (untested)

def getwhereclause(table, filters):
conditions = []
for colname, value in filters.items():
column = table.c[colname]
conditions.append(column == value)
return sqlalchemy.and_(conditions)

whereclause = getwhereclause(table, filters)
statement=table.update().where(whereclause).values(**datum).returning(table.c.rowid)

Hope that helps,

Simon

> 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/cbdf1b69-a0fc-41f5-a1cc-57c4fd156132%40googlegroups.com.

[Javier Collado Jiménez]

You nailed it! (it almost runs out of the box)

The only change needed is:

    return sqlalchemy.and_(*conditions) 

instead of

    return sqlalchemy.and_(conditions) 

THANK YOU!!

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/cbdf1b69-a0fc-41f5-a1cc-57c4fd156132%40googlegroups.com.

[Javier Collado Jiménez]

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?

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.

[Mike Bayer]

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().

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/5701f7d0-d8fb-4c32-97d9-396af37f8bcb%40googlegroups.com

[Javier Collado Jiménez]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5701f7d0-d8fb-4c32-97d9-396af37f8bcb%40googlegroups.com

[Simon King]

Sure, use an explicit transaction:

https://docs.sqlalchemy.org/en/13/core/connections.html#using-transactions

with connection.begin() as trans:
# might as well lift this out of the loop
insert = table.insert()
for datum in data:
statement = insert.values(**datum).returning(table.c.rowid)
# etc.

Simon

> 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/a1373cde-5de6-4d54-84dc-aa980d467f09%40googlegroups.com.

[Javier Collado Jiménez]

Thank you!