UPDATE on mysql python always returns 0 as lastrowid

Question

Here is my code:

def insert_ip_info(instance):
# Cursor to db
cur = db.cursor()

int_ipaddr = ipaddress_string_to_int(instance.ip_address)  # converting the string into unsigned integer

# Check if IP address exits in table (stores results from previous scan)
sql_query = "SELECT * FROM " + ip_info_table_name + " WHERE ip_address = " + str(int_ipaddr)
cur.execute(sql_query)
ip_exists = len(
    cur.fetchall())  # Using the len of the results only, to determine if the IP address exists in the table

if (ip_exists > 0):  # The IP address is already found in the table
    # Update the IP address information in the table
    sql_query = "UPDATE " + ip_info_table_name + \
                " SET hostname = %s , ip_location_id = %s WHERE ip_address = " + str(int_ipaddr)
    sql_data = (instance.hostname, instance.ip_location_id)

else:

    # Write a new line into db
    sql_query = "INSERT INTO " + ip_info_table_name + \
                " (ip_address,hostname,ip_location_id) " + \
                "VALUES(%s,%s,%s);"
    sql_data = (str(int_ipaddr), instance.hostname, instance.ip_location_id)
cur.execute(sql_query, sql_data)
cur.execute('SELECT LAST_INSERT_ID()')
res = cur.fetchone()

cur.execute('SELECT @@identity')
res2 = cur.fetchone()
print cur.lastrowid
print db.insert_id()
print res
print res2
db.commit()

Here is the database:

Column  Type    Comment
id  int(11) Auto Increment   
ip_address  int(10) unsigned     
hostname    varchar(256) NULL    
ip_location_id  int(11) NULL     
Indexes
PRIMARY id
INDEX   ip_location_id

When I make the INSERT operation everything works like a charm, but when I try the UPDATE operation all 4 tries (prints) fail (print 0). I don't have any idea what I made wrong. Thanks

score 3 · Answer 1 · answered Apr 11 '16 at 07:32

3

cur.execute('SELECT LAST_INSERT_ID()')

This line says it all. However you request it, what you are looking at is the auto-incremented value of the last record inserted. When you do an update, the auto-increment value for that index has not changed and so there is no value generated for the current query.

It's only valid when you have inserted a new row. If you want to obtain this value otherwise then you can either select max(id) or interrogate the schema but there's really very little point in doing so.

answered Apr 11 '16 at 07:32

Mike

2,721
1
15
20

according to https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-lastrowid.html it should be updated in UPDATE operaion as well – azDev Apr 11 '16 at 07:34
It encapsulates mysql_insert_id(): "Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID(expr)." Since your update didn't (I assume) use the SQL function LAST_INSERT_ID to set its value then the result will be zero. – Mike Apr 11 '16 at 07:39
so how can I get the last updated id ? max(id) is irrelevant because the UPDATE can be to any row – azDev Apr 11 '16 at 07:41
2

http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql – Pholochtairze Apr 11 '16 at 07:53

UPDATE on mysql python always returns 0 as lastrowid

1 Answers1

Linked