76

How can I access the number of rows affected by:

cursor.execute("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")
Tie-fighter
  • 1,035
  • 2
  • 8
  • 9
  • 40
    This question doesn't make sense. A select statement does not affect **any** rows. – Mark E. Haase Nov 21 '12 at 17:00
  • 9
    I think the intention is to get the number of rows returned by `COUNT(*)` which means the actual question is "How to access the result of `cursor.execute`. – malana Oct 25 '14 at 17:07
  • 6
    Also never, NEVER use python string concatenations see http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters, or you will be in the world of pain! – Alex Nov 28 '14 at 10:31
  • 4
    What @Alex means is, never use python string concatenation *to substitute variable values into a SQL query string.* – LarsH Mar 27 '15 at 08:09
  • 1
    Thanks @Larsh :) I meant in the context of SQL queries in Python. – Alex Mar 28 '15 at 12:33

7 Answers7

106

Try using fetchone:

cursor.execute("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")
result=cursor.fetchone()

result will hold a tuple with one element, the value of COUNT(*). So to find the number of rows:

number_of_rows=result[0]

Or, if you'd rather do it in one fell swoop:

cursor.execute("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")
(number_of_rows,)=cursor.fetchone()

PS. It's also good practice to use parametrized arguments whenever possible, because it can automatically quote arguments for you when needed, and protect against sql injection.

The correct syntax for parametrized arguments depends on your python/database adapter (e.g. mysqldb, psycopg2 or sqlite3). It would look something like

cursor.execute("SELECT COUNT(*) from result where server_state= %s AND name LIKE %s",[2,digest+"_"+charset+"_%"])
(number_of_rows,)=cursor.fetchone()
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • 3
    Normal Python string substitution to build statements is not the preferred approach. Or as the official documentation puts it ["Never do this -- insecure!"](https://docs.python.org/2/library/sqlite3.html). Use the `?` syntax instead. – malana Oct 25 '14 at 17:03
  • not working in 3.5, need to fetchall for count, then re- fetch for processing. – Alex Martian Mar 04 '18 at 15:27
  • @AlexeiMartianov: not sure what you did or what database adapter you used, but this works just fine on Python 3.5 with just about any database adapter you can lay your hands on. Without your exact code that didn't work, I have to assume the problem was local. – Martijn Pieters Oct 24 '18 at 15:12
  • @lmichelbacher: which is why Unutbu has already covered how to use SQL parameters in the answer. – Martijn Pieters Oct 24 '18 at 15:13
102

From PEP 249, which is usually implemented by Python database APIs:

Cursor Objects should respond to the following methods and attributes:

[…]

.rowcount
This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like 'select') or affected (for DML statements like 'update' or 'insert').

But be careful—it goes on to say:

The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface. [7]

Note:
Future versions of the DB API specification could redefine the latter case to have the object return None instead of -1.

So if you've executed your statement, and it works, and you're certain your code will always be run against the same version of the same DBMS, this is a reasonable solution.

Michael Scheper
  • 6,514
  • 7
  • 63
  • 76
AndiDog
  • 68,631
  • 21
  • 159
  • 205
  • 3
    .rowcount for Select appears to always be 1 – Tie-fighter Mar 25 '10 at 00:08
  • 7
    @Tie-fighter: One row was produced, containing the value `COUNT(*)`. If you query `SELECT name FROM result WHERE server_state='2'`, for example, you will get zero, one or multiple rows. – AndiDog Mar 25 '10 at 16:55
  • 12
    SQLite *always* produces `cursor.rowcount == -1` for `SELECT` statements as it doesn't know how many rows will be returned until you've returned all rows; it's iterators all the way down for SQLite. – Martijn Pieters Feb 17 '14 at 12:47
  • 4
    I found @MartijnPieters comments to be correct for MariaDB via the Connector/Python. I always get back -1 for SELECT statements. – SMGreenfield May 29 '15 at 23:13
  • I just added a caveat to account for the experience @MartijnPieters was having, and I. Hopefully it'll save future down-votes, because for a lot of cases, this is a good answer. – Michael Scheper Oct 24 '18 at 14:59
  • @MichaelScheper: then why not just update the PEP 249 quote to be complete? The biggest issue here is that the PEP explicitly states that `rowcount` can be `-1` in case the the rowcount could not be determined, but this answer papers over that caveat. I had to check if that was a later addition, but rowcount not always being set has been part since the initial PEP commit from 2001. – Martijn Pieters Oct 24 '18 at 15:06
  • @MartijnPieters: Good idea—done. And given the note about the API possibly changing so it returns `None` in future, I now think this isn't actually a good solution for my case. So your suggestion has saved me from possible future bugs. – Michael Scheper Oct 29 '18 at 14:49
44

The number of rows effected is returned from execute:

rows_affected=cursor.execute("SELECT ... ")

of course, as AndiDog already mentioned, you can get the row count by accessing the rowcount property of the cursor at any time to get the count for the last execute:

cursor.execute("SELECT ... ")
rows_affected=cursor.rowcount

From the inline documentation of python MySQLdb:

 def execute(self, query, args=None):

    """Execute a query.

    query -- string, query to execute on server
    args -- optional sequence or mapping, parameters to use with query.

    Note: If args is a sequence, then %s must be used as the
    parameter placeholder in the query. If a mapping is used,
    %(key)s must be used as the placeholder.

    Returns long integer rows affected, if any

    """
Boaz
  • 25,331
  • 21
  • 69
  • 77
  • 3
    +1 Boaz, "rows_affected=cursor.rowcount" is absolutely the best answer. It does not require an additional query as the value is always present. rowcount is useful for determining the number of records inserted, updated, deleted, or retrieved through a query. – Joshua Burns Feb 03 '11 at 16:14
  • 12
    According to the already mentioned [PEP 249](http://www.python.org/dev/peps/pep-0249/) the value returned by the `cursor.execute` method is no longer defined (in previous version of the specification it was expected to work as in Boaz' example). The specification explicitly suggests using of the _the more flexible .rowcount attribute instead_ as the value returned by the `execute` method is database interface implementation dependent. – Dariusz Walczak Oct 28 '11 at 10:57
  • 2
    @Boaz -- I found this approach DID NOT WORK for MariaDB 10.4.17, Python 2.7.8, and Connector/Python 2.0.4. Sadly, I could not find ANY posted approach that worked for that combination of tools. I finally had to iterate through the rows and count them myself! – SMGreenfield May 29 '15 at 23:20
  • a Greate answer that don't require coding or communicating to server again and will not consume any memory thanks – Charif DZ Mar 22 '18 at 09:59
  • 1
    `cursor.rowcount` *may be left at `-1`*. The PEP specification leaves this up to the database adapter, so it is not guaranteed to work for all databases. The return value of `cursor.execute()` is not prescribed by the PEP, and some database drivers (such as `sqlite3`) return the cursor, not a row count. – Martijn Pieters Oct 24 '18 at 15:11
21

In my opinion, the simplest way to get the amount of selected rows is the following:

The cursor object returns a list with the results when using the fetch commands (fetchall(), fetchone(), fetchmany()). To get the selected rows just print the length of this list. But it just makes sense for fetchall(). ;-)

print len(cursor.fetchall)

# python3
print(len(cur.fetchall()))
Milovan Tomašević
  • 6,823
  • 1
  • 50
  • 42
harvey_c
  • 227
  • 2
  • 2
  • this is more straight forward and it worked like a charm. thanks. – Anthony Dec 23 '13 at 22:16
  • 4
    do not forget that cursor is empty (at end) after that. – Alex Martian Mar 04 '18 at 15:29
  • 3
    fetchall will create the a list what if i'm selecting in my case more than 600000 !!! of every tuple contains 10 fields this is not a good answer only for small chunk of data – Charif DZ Mar 22 '18 at 09:56
  • 3
    You are not ever calling `cursor.fetchall` here, so the literal code, as written, produces a type error. And why fetch all rows **just to use `len()` on the list**? That's just a waste of resources. Use `SELECT COUNT(*)` to have the server do the counting. – Martijn Pieters Oct 24 '18 at 15:10
3

To get the number of selected rows I usually use the following:

cursor.execute(sql)
count = len(cursor.fetchall())
Kirill Bulygin
  • 3,658
  • 1
  • 17
  • 23
FedericoSala
  • 148
  • 6
1

when using count(*) the result is {'count(*)': 9}

-- where 9 represents the number of rows in the table, for the instance.

So, in order to fetch the just the number, this worked in my case, using mysql 8.

cursor.fetchone()['count(*)']
Harshit Ruwali
  • 1,040
  • 2
  • 10
  • 22
Jenner
  • 11
  • 2
0

The accepted answer is fine, but here's how you can get it in one line after executing the cursor:

result = cur.fetchone()[0]

user2332865
  • 1
  • 2