Python Number of rows affected by cursor execute SELECT

Question

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

User · Answer

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     -   Example   print len cursor fetchall

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To get the number of selected rows I usually use the following  cursor execute sql  count    len cursor fetchall

User · Answer

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

[python] Python: Number of rows affected by cursor.execute("SELECT ...)

Examples related to python

Examples related to sql

Examples related to rows

Examples related to database