使用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.
- 这是否意味着如果另一个进程在正确的时刻插入数据,那么该语句将返回错误的主键?
- 我希望
INSERT report...
返回插入的主键,这可能吗?
- Wouldn't this mean that if another process were inserting data at the right moment then the statement would return the wrong primary key?
- 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屋!