mysql python上的UPDATE总是返回0作为lastrowid [英] UPDATE on mysql python always returns 0 as lastrowid

问题描述

这是我的代码：

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()

这里是数据库：

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

当我执行INSERT操作时，所有内容都像一个符咒，
，但是当我尝试全部UPDATE操作时4尝试（打印）失败（打印0）。
我不知道我做错了什么。
谢谢

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.

仅当您插入新行时有效。如果您想以其他方式获取此值，则可以选择max（id）或查询模式，但这样做实际上没有什么意义。

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.

这篇关于mysql python上的UPDATE总是返回0作为lastrowid的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！