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

falsetru · Accepted Answer · 2016-09-30T11:56:57.287

55

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]

edited Sep 30 '16 at 11:56

answered Sep 27 '13 at 08:32

falsetru

357,413
63
732
636

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. – Gia Duong Duc Minh Sep 27 '13 at 08:40
2

@GiaDuongDucMinh, Did you call rowcount like method? `.rowcount()`? It is not a method. – falsetru Sep 27 '13 at 08:42
I cannot remember clearly, but maybe I made this mistakem @falsetru. Thanks! – Gia Duong Duc Minh Sep 27 '13 at 08:43
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) – Marc Maxmeister Dec 29 '15 at 17:05
8

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`. – code_dredd Jun 16 '16 at 01:36
@ShubhamChaudhary, What's the point of changing quotation mark? – falsetru Sep 30 '16 at 11:58
pep8 guidelines about strings – Shubham Chaudhary Sep 30 '16 at 11:59
@ShubhamChaudhary, https://www.python.org/dev/peps/pep-0008/#string-quotes – falsetru Sep 30 '16 at 12:00
@falsetru Ah, well sounds like it's only our company convention, my bad. Learned something new. – Shubham Chaudhary Sep 30 '16 at 12:10
@falsetru I am wondering why this does not work at all: print("rows: " + cursor.rowcount) – merlin Jun 06 '20 at 04:26
@merlin, You're trying to concatenate int to str. `print("rows: " + str(cursor.rowcount))`, `print("rows: {}".format(cursor.rowcount)`, .... – falsetru Jun 06 '20 at 11:25
Thank you @falsetru That makes sense. – merlin Jun 06 '20 at 13:46
for UPDATE, even rows data is changed, cursor.rowcount still returns 0 – Umair Ayub Aug 04 '21 at 06:40
@UmairAyub, See https://www.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, .... – falsetru Aug 04 '21 at 09:20

Python how to know if a record inserted successfully or not

1 Answers1

Linked