CODEWITHSUNDEEP
pythonsqlalchemy
honzas
honzas

Reputation: 1831

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 in the sqlalchemy Python?

Upvotes: 54

Views: 35473

Answers (6)

Himanshu Punjabi
Himanshu Punjabi

Reputation: 51

Here is what worked for me in MS SQL.

  1. Execute the SQL UPDATE Query and do not Commit.
  2. Execute the SQL Query @@ROWCOUNT and store the Result in a Variable say var_updated_rows.
  3. var_updated_rows.scalar() will fetch the value from first column and first row of the result from @@ROWCOUNT which will be the number of rows updated.
  4. Commit the changes to the Database.

Reference: CursorResult.scalar() from SQLAlchemy

Upvotes: 0

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57660

Actually there is no way to know this precisely for some databases (e.g. postgres).

The closest thing is rowcount. But 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

Upvotes: 33

Thering
Thering

Reputation: 121

You can use .returning to give you the rows which have been updated, and then use result.rowcount to count this eg

insertstmt = insert(mytable).values(myvalues).returning(mytable.c.mytableid)

with get_engine().begin() as conn:
        result = conn.execute(insertstmt)
        print(result.rowcount)

Upvotes: 3

Nicolas
Nicolas

Reputation: 363

Shiplu's analysis is 100% correct.

Pushing the discussion a bit further, here is how to display the updated rowcount and not the matched rowcount using sqlalchemy for MySQL: the engine needs to be created with the flag client_flag=0.

from sqlalchemy import create_engine
engine = create_engine(
    'mysql+pymysql://user:password@host:port/db', 
    connect_args={'client_flag':0}
)

To give a bit more details, the rowcount returned by MySQL depends on the CLIENT_FOUND_ROWS flag provided to the C-API function mysql_real_connect() as stated in MySQL documentation:

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

The flag value is 2 (MySQL constants), and is added automatically by sqlalchemy when creating the engine as visible here.

The value of client_flag in connect_args allows to override this value.

Note: this might break sth from the sane_rowcount (only used in the ORM apparently) - in my case, I only use the Core of sqlalchemy. In last link:

# FOUND_ROWS must be set in CLIENT_FLAGS to enable
# supports_sane_rowcount.

Upvotes: 7

DNS
DNS

Reputation: 38189

ResultProxy objects have a rowcount property as well.

Upvotes: 47

flxvctr
flxvctr

Reputation: 23

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.

Upvotes: 2

Related Questions