Python/MySQL: using cursor.rowcount before and after inserting a row

Question

I'm trying to get the rowcount of a database table before and after having inserted a new row into the table. At a basic level I want to take tthe rowcount, insert a row, then take the rowcount again (i.e. it should be 1 more). However, the second call of cursor.rowcount always returns the same number as the first cursor.rowcount call.

My code:

from mysql.connector import MySQLConnection, Error
from single_api_insert import insert

def query_insert():
    try:
        con = MySQLConnection(host='localhost',
               database='test',
               user='root',
               password='')
        if con.is_connected():
            print('Connected to database')

        cursor = con.cursor()
        cursor.execute("select * from testapi")
        row = cursor.fetchall()
        print(cursor.rowcount)
        insert(2, 'mk', 'km')
        print(cursor.rowcount)

    except Error:
        print(Error)

    finally:
        cursor.close()
        con.close()

if __name__ == '__main__':
    query_insert()

The insert method inserts a row into my table testapi. With an empty table this should print

Connected to database
0
1

but the rowcount does not increase to 1.

Is there a better way to do this or a way to solve rowcount not working correctly the second time?

Thanks

Leo K · Accepted Answer · 2018-08-02 16:24:07Z

cursor.rowcount is probably not what you want to use.

See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-rowcount.html.

rowcount does not track the number of rows continuously, it is updated only upon executing a command with the cursor (and it will be equal to the number of rows fetched, if it was 'SELECT' or the number of rows affected for other commands such as 'INSERT', 'UPDATE' or 'DELETE').

Also, it seems that your 'insert' command is not executed with the cursor (and not even with the same connection, either), therefore it will likely not affect the view of the database as seen by the connection, so you won't see the inserted rows even if you re-fetched them with the same cursor object (unless you do something to turn this off, each connection represents a separate database transaction).

If you want to to fetch some rows, then add a few and find out how many you have now (within that connection's view, that is), you should do this:

fetch the rows and save rowcount
make a change (e.g., insert some rows) and get the rowcount from that change (i.e., call cursor.execute('something'), then read cursor.rowcount right away).
add the two numbers

(if you do this within the same transaction, i.e, without calling con.commit(), the result should be the number of rows, as seen by your transaction, i.e., not affected by any changes made in other processes).

Collectives™ on Stack Overflow

Python/MySQL: using cursor.rowcount before and after inserting a row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related