使用pyodbc在INSERT上返回主键 [英] Returning primary key on INSERT with pyodbc

问题描述

我有一个程序可以将大量数据插入SQL数据库中.数据由报告组成，每个报告都有许多 Tags .

I have a program inserting a bunch of data into an SQL database. The data consists of Reports, each having a number of Tags.

标签具有字段 report_id ，该字段是对相关 Report 的主键的引用.

A Tag has a field report_id, which is a reference to the primary key of the relevant Report.

现在，每次我插入数据时，都会有200个 Reports 甚至更多，每个报告可能有400个 Tags .所以我现在用伪代码执行此操作:

Now, each time I insert the data, there can be 200 Reports or even more, each maybe having 400 Tags. So in pseudo-code I'm now doing this:

for report in reports:
    cursor_report = sql('INSERT report...')
    cursor_report.commit()
    report_id = sql('SELECT @@IDENTITY')

    for tag in report:
        cursor_tag += sql('INSERT tag, report_id=report_id')
    cursor_tag.commit()

出于某些原因，我不喜欢这样.通常，我不喜欢SELECT @@IDENTITY语句.

I don't like this for a couple of reasons. Mostly i don't like the SELECT @@IDENTITY statement.

1. 这是否意味着如果另一个进程在正确的时刻插入数据，那么该语句将返回错误的主键?
2. 我希望INSERT report...返回插入的主键，这可能吗?

1. Wouldn't this mean that if another process were inserting data at the right moment then the statement would return the wrong primary key?
2. I would rather like the INSERT report... to return the inserted primary key, is that possible?

由于我目前必须在报告之间提交这些时刻的程序暂停".如果我可以在最后完成所有事情，那么它将大大减少花费的时间.我一直在考虑在 Report 中创建一个单独的字段用于标识，以便可以使用report_id = (SELECT id FROM reports WHERE seperate_field=?)或 Tags 中的某些内容，但这看起来并不优雅.

Since I currently have to commit between reports the program "pauses" during these moments. If I could commit everything at the end then it would greatly reduce the time spent. I have been considering creating a seperate field in Report used for identification so I could report_id = (SELECT id FROM reports WHERE seperate_field=?) or something in the Tags, but that doesn't seem very elegant.

推荐答案

这是否意味着如果另一个进程在正确的时刻插入数据，那么["SELECT @@ IDENTITY"]语句将返回错误的主键?

Wouldn't this mean that if another process were inserting data at the right moment then the ["SELECT @@IDENTITY"] statement would return the wrong primary key?

不.数据库引擎跟踪为每个连接插入的最后一个身份值，并为在其上执行SELECT @@IDENTITY语句的连接返回适当的值.

No. The database engine keeps track of the last identity value inserted for each connection and returns the appropriate value for the connection on which the SELECT @@IDENTITY statement is executed.

这篇关于使用pyodbc在INSERT上返回主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！