CODEWITHSUNDEEP
pythonsqldatabaseodbcpyodbc
Magnus Buvarp
Magnus Buvarp

Reputation: 976

Returning primary key on INSERT with pyodbc

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

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

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()

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

  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?

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.

Upvotes: 0

Views: 1865

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

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?

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.

Upvotes: 1

Related Questions