使用pyodbc操作SQL数据库

Question

使用pyodbc操作sql数据库，同时往A表和B表中插入信息时，首先会先往A表中插入信息，插入的每条信息都会产生一个自增长的id，然后再往B表中插入信息，将把这个id作为一列插入到B表中，这个id不能为空，用代码如何实现?已经使用了如下的方法，打印出的id都为None。

self.cursor.execute("SELECT SCOPE_IDENTITY()")
id = self.cursor.fetchone()[0]

self.cursor.execute("SELECT @@IDENTITY AS last_id")
id = self.cursor.fetchone().last_id

Answer 1

引自免费微信小程序：皆我百晓生

在使用pyodbc操作SQL Server数据库时，要获取前一次插入操作生成的自增ID（IDENTITY值），应当确保执行顺序正确，并且使用恰当的方法来获取该ID。根据您的描述，您可能需要在插入A表后立即获取自增ID，然后将其用于B表的插入操作。

以下是一个示例代码片段，展示了如何实现这个过程：

import pyodbc

# 连接字符串，例如：Driver=...;Server=...;Database=...;UID=...;PWD=...
cnxn = pyodbc.connect('your_connection_string')

# 创建游标
cursor = cnxn.cursor()

# 插入数据到A表
insert_into_A_query = "INSERT INTO A (column1, column2) VALUES (?, ?)"
params_A = ('value1', 'value2')
cursor.execute(insert_into_A_query, params_A)

# 提交事务以确保ID立刻生成（对于某些数据库系统这可能是必需的）
cnxn.commit()

# 获取A表中刚插入记录的自增ID
get_last_id_query = "SELECT SCOPE_IDENTITY()"
cursor.execute(get_last_id_query)
last_inserted_id = cursor.fetchone()[0]

# 确保ID有效并非None
if last_inserted_id is not None:
    # 插入数据到B表，引用刚获取的ID
    insert_into_B_query = "INSERT INTO B (id_from_A, column3) VALUES (?, ?)"
    params_B = (last_inserted_id, 'value3')
    cursor.execute(insert_into_B_query, params_B)

    # 提交事务
    cnxn.commit()

# 关闭游标和连接
cursor.close()
cnxn.close()

注意：

• SCOPE_IDENTITY()函数是针对SQL Server的，它返回当前会话和当前作用域中的最后一个标识符值。
• 如果你的数据库不支持SCOPE_IDENTITY(), 而是使用@@IDENTITY或其他方法，请对应修改查询语句。
• 请确保在插入A表之后马上获取ID，并在同一个事务内进行B表的插入操作，避免其他并发操作影响到ID的获取。

在实际应用中，务必处理可能出现的异常情况，如插入失败、获取ID失败等。