Python how to know if a record inserted successfully or not

Question

I'm using Python MySQL Connector, I inserted a record into database, and it was successful. But in Python code, how can I know if it is inserted or not? My Table does not have a primary key.

def insert(params) :
    db_connection = Model.get_db_connection()
    cursor = db_connection.cursor()
    try :
        cursor.execute("""INSERT INTO `User`(`UID`, `IP`) VALUES(%s,%s);""", (params))
        db_connection.commit()
    except :
        db_connection.rollback()
    Model.close_db(db_connection)
    return result

Solution

You can use .rowcount attribute:

cursor.execute("""INSERT INTO `User`(`UID`, `IP`) VALUES(%s,%s);""", params)
print("affected rows = {}".format(cursor.rowcount))

.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). [9]

Comments

[Gia Duong Duc Minh]

One more question @falsetru, I used .rowcount before posting this question. But it threw an error "'int' cannot callable". But now it worked, I really don't know why.

[falsetru]

@GiaDuongDucMinh, Did you call rowcount like method? .rowcount()? It is not a method.

[Gia Duong Duc Minh]

I cannot remember clearly, but maybe I made this mistakem @falsetru. Thanks!

[Marc Maxmeister]

This is the ONLY way to know if INSERT IGNORE... inserted or not when inserting into a table whose primary key does not auto-increment. (I know because I just encountered this problem). checking cursor.lastrowid will always be zero in this case. (but when auto-increment is ON, lastrowid will not be zero)

[code_dredd]

Note that if you use an UPDATE statement and it just so happens that the data for the row update is already the same as what's already in the database, there will be a matching row, but no rows will be affected, keeping .rowcount at 0.

[falsetru]

@ShubhamChaudhary, What's the point of changing quotation mark?

[Shubham Chaudhary]

pep8 guidelines about strings

[falsetru]

@ShubhamChaudhary, python.org/dev/peps/pep-0008/#string-quotes

[Shubham Chaudhary]

@falsetru Ah, well sounds like it's only our company convention, my bad. Learned something new.

[merlin]

@falsetru I am wondering why this does not work at all: print("rows: " + cursor.rowcount)

[falsetru]

@merlin, You're trying to concatenate int to str. print("rows: " + str(cursor.rowcount)), print("rows: {}".format(cursor.rowcount), ....

[merlin]

Thank you @falsetru That makes sense.

[Umair Ayub]

for UPDATE, even rows data is changed, cursor.rowcount still returns 0

[falsetru]

@UmairAyub, See python.org/dev/peps/pep-0249/#id48. ... Most databases will return the total number of rows that were found by the corresponding WHERE clause of the statement. Some databases use a different interpretation for UPDATEs and only return the number of rows that were changed by the UPDATE, ....