在python中使用MySQLdb插入mysql后，如何安全地和有效地获取行id ?

I have a simple table in mysql with the following fields:

我有一个简单的mysql表格，包含以下字段:

id -- Primary key, int, autoincrement
id——主键，int，自动增量。
name -- varchar(50)
名字——varchar(50)
description -- varchar(256)
描述——varchar(256)

Using MySQLdb, a python module, I want to insert a name and description into the table, and get back the id.

使用python模块MySQLdb，我希望向表中插入一个名称和描述，并返回id。

In pseudocode:

在伪代码:

db = MySQLdb.connection(...)
queryString = "INSERT into tablename (name, description) VALUES" % (a_name, a_desc);"

db.execute(queryString);
newID = ???

4 个解决方案

#1

I think it might be

我想可能是

newID = db.insert_id()

Edit by Original Poster

编辑,楼主

Turns out, in the version of MySQLdb that I am using (1.2.2) You would do the following:

结果是，在我正在使用的MySQLdb版本(1.2.2)中，您将执行以下操作:

conn = MySQLdb(host...)

c = conn.cursor()
c.execute("INSERT INTO...")
newID = c.lastrowid

I am leaving this as the correct answer, since it got me pointed in the right direction.

我把这个作为正确答案，因为它让我指向正确的方向。

#2

I don't know if there's a MySQLdb specific API for this, but in general you can obtain the last inserted id by SELECTing LAST_INSERT_ID()

我不知道是否有特定于MySQLdb的API，但通常您可以通过选择LAST_INSERT_ID()来获取最后插入的id

It is on a per-connection basis, so you don't risk race conditions if some other client performs an insert as well.

它是基于每个连接的，所以如果其他客户端也执行插入操作，您就不会冒竞争条件的风险。

#3

You could also do a

你也可以做a

conn.insert_id

conn.insert_id

#4

-7

The easiest way of all is to wrap your insert with a select count query into a single stored procedure and call that in your code. You would pass in the parameters needed to the stored procedure and it would then select your row count.

最简单的方法是将insert与select count查询打包到一个存储过程中，并在代码中调用它。您将向存储过程传递所需的参数，然后它将选择行计数。

#1