python mysql lastrowid,mysql python上的UPDATE总是返回0作为lastrowid

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

解决方案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.