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.