如何使用Python中的MySQLdb检查插入是否成功？

I have this code:

我有这个代码：

cursor = conn.cursor()
cursor.execute(("insert into new_files (videos_id, filename, "
                "is_processing) values (%s,%s,1)"), (id, filename))
logging.warn("%d", cursor.rowcount)
if (cursor.rowcount == 1):
    logging.info("inserted values %d, %s", id, filename)
else:
    logging.warn("failed to insert values %d, %s", id, filename)
cursor.close()

Fun as it is, cursor.rowcount is always one, even though i updated my database to make the videos_id a unique key. That is, the insert fails because in my tests the same videos_id is going to appear (and when I check the database, nothing was inserted). But for whatever reason, the rowcount is always 1 - even the logging.warn I have spits out a rowcount of 1.

有趣的是，cursor.rowcount总是一个，即使我更新了我的数据库，使videos_id成为一个唯一的密钥。也就是说，插入失败，因为在我的测试中会出现相同的videos_id（当我检查数据库时，没有插入任何内容）。但无论出于何种原因，rowcount始终为1 - 甚至是logging.warn我吐出的行数为1。

So, the question:
Can I use rowcount to work out if an insert went fine? If so, what am I (presumably) doing wrong? otherwise, how do i check if an insert went fine?

所以，问题是：如果插入正常，我可以使用rowcount吗？如果是这样，我（大概）做错了什么？否则，我如何检查插入是否正常？

4 个解决方案

#1

Your code does not commit after the modifications (your modifications are rolled back). That is you should add the following line after cursor.execute:

修改后您的代码不会提交（您的修改将被回滚）。那就是你应该在cursor.execute之后添加以下行：

conn.commit()

Failed insert will throw MySQLdb.IntegrityError, so you should be ready to catch it.

插入失败将抛出MySQLdb.IntegrityError，因此您应该准备好捕获它。

Thus, your code should look something like:

因此，您的代码应该类似于：

sql_insert = """insert into new_files (videos_id, filename, is_processing)
values (%s,%s,1)"""

cursor = conn.cursor()
try:
    affected_count = cursor.execute(sql_insert, (id, filename))
    conn.commit()
    logging.warn("%d", affected_count)
    logging.info("inserted values %d, %s", id, filename)
except MySQLdb.IntegrityError:
    logging.warn("failed to insert values %d, %s", id, filename)
finally:
   cursor.close()

#2

I don't have enough reputation to make a comment, but here's an important note:

我没有足够的声誉发表评论，但这里有一个重要的说明：

It is also possible for execute() to fail silently if you don't commit after the call. Namely, MyISAM tables don't require the commit, but InnoDB ones do.

如果您在调用后未提交，则execute（）也可能会以静默方式失败。也就是说，MyISAM表不需要提交，但InnoDB表可以。

#3

If the insert fails you'll get a raised exception or someone will yell or you'll fall off your chair.

如果插入失败，你会得到一个凸起的例外，或者有人会喊叫，否则你会从椅子上掉下来。

#4

How about using a try/catch block instead of looking at rowcount. If it catches an exception then there is a problem; otherwise, no problem.

如何使用try / catch块而不是查看rowcount。如果它捕获异常则存在问题;否则，没问题。

#1