How to get number of affected rows in sqlalchemy?

sqlalchemy resultproxy
sqlalchemy resultproxy rowcount
sqlalchemy count
sqlalchemy insert multiple rows
sqlalchemy delete
sqlalchemy inserted rows
sqlalchemy with
sqlalchemy update

I have one question concerning Python and the sqlalchemy module. What is the equivalent for cursor.rowcount in the sqlalchemy Python?

ResultProxy objects have a rowcount property as well.

How to get number of affected rows in sqlalchemy? I have one question concerning Python and the sqlalchemy module. What is the equivalent for cursor.​rowcount  So for both of the following scenarios rowcount will report 1. Because of Rows matched: 1. one row changed with update statement. Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0. same update statement is executed. Query OK, 0 row affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0.

rowcount is not the number of affected rows. Its the number of matched rows. See what doc says

This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified - an UPDATE statement, for example, may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured by default to return the match count in all cases

So for both of the following scenarios rowcount will report 1. Because of Rows matched: 1

  1. one row changed with update statement.

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
  2. same update statement is executed.

    Query OK, 0 row affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    

Pretty simple question - How do I go about in getting the number of affected rows using SQLAlchemy? Also, wanted to know what the equivalent  Pretty simple question - How do I go about in getting the number of affected rows using SQLAlchemy? Also, wanted to know what the equivalent for cursor.rowcount is in SQLAlchemy Python. Using this for a project of mine.

What Shiplu says is correct of course, however, to answer the question, in many cases you can easily make the matched columns equal the changed columns by including the condition that the value is different in the WHERE clause, i.e.:

UPDATE table
SET column_to_be_changed = "something different"
WHERE column_to_be_changed != "something different" AND [...your other conditions...]

rowcount will then return the number of affected rows, because it equals the number of matched rows.

The 'rowcount' reports the number of rows matched by the WHERE criterion of such as MySQL, rowcount is configured by default to return the match count in  Sorry to ask this question, but I can not found any useful answers after searching this question. I have known that in a session of SQLAlchemy, update() will not work, maybe even not communicate w

Can I know How many rows affected on update or detete on You should take a look at session.dirty (which contains rows to update) I want to determine the total number of rows in the table without having to wait a whole bunch of time querying the database. Currently, I have a bit of code: rows = session.query(Congress).all() Then I can access them from list, but this requires me to load everything into memory at once.

We have a MySQL database and we're switching from DB API to is supposed to report the number of affected rows by an UPDATE statement. Okay, found the answer \☺/ You can re-execute the select statement that was used to select the rows for bulk deletion. Unfortunately you can't query the ORM objects, but you can get a list of all the deleted rows with the old column values as tuples: def after_bulk_delete(session, query, query_context, result): affected_table = query_context.statement.froms[0] affected_rows = query_context.statement.execute().fetchall() sqlalchemy.event.listen(Session, "after_bulk_delete", after_bulk_delete)

Also is there a way to get the num rows inserted from the information schema in snowflake by a query id? The information_schema.query_history  To get the count of rows a SELECT query would return, you either need to do a SELECT COUNT(*) up front, or you need to fetch all the rows into an array and perform len() on the array.

Comments
  • rowcount is not actually the count of affected rows. See my answer for detail.
  • The docs do include rowcount these days: docs.sqlalchemy.org/en/latest/core/…
  • So what's the answer to OP's question?
  • Although this useful insight clarifies a misconception, it does not provide a helpful answer to this question... If there is no answer, then it should also be specified. Thank you
  • I haven't found any answer on this yet. But later realized that actual affected number of rows is implementation detail. Rather it's important that it ensures rowcount amount of rows are in the requested updated state.