10

I created a table called participant composed by participant_ID (which is the primary key and is identity[has an auto-value]) and session_ID (which is a foreign key).

When I create a new participant, I would like to store its participant_ID.

I have the following code, but I get the following message error: 'Syntax problem near returning'

connection = pyodbc.connect('Driver={SQL Server Native Client 11.0};Serve=:xxx;Database=xxx;Uid=xxx;Pwd=xxx')
cur = connection.cursor()
pID = cur.execute('INSERT INTO participant(sessions_ID) VALUES (40) RETURNING participant_ID')

Many thanks in advance!

Benjamin Gruenbaum
  • 270,886
  • 87
  • 504
  • 504
vcvd
  • 422
  • 2
  • 7
  • 13
  • Possible duplicate / related - [Returning a value from an INSERT statement in SQL Server 2008](http://stackoverflow.com/questions/7825005/returning-a-value-from-an-insert-statement-in-sql-server-2008). – Bernhard Barker Oct 15 '13 at 10:56

3 Answers3

11

This is a really, really old post, I know - but I had the same question and came upon it. I got mine working and thought I would share for those that stumble here as I have. Please be kind, this was my first pass at the code. It isn't pretty, but it should get you started. 

def exec_query(self, query_type, query):
    # query_type: This is an enumumeration defining the CRUD operation of the query.

    # Note, the native client (11.0) might change with time and Windows updates)
    # Note, the driver varlue would normally go in a constant, I put it here just for clarity
    with pypyodbc.connect('DRIVER={SQL Server Native Client 11.0}; ' + self.cnx_str) as connection:
        cursor = connection.cursor()
        cursor.execute(query)

        if query_type is QueryType.create:
            try:
                cursor.execute("SELECT SCOPE_IDENTITY()")
                row = cursor.fetchone()
                seed_id = row[0]
            except AttributeError:
                seed_id = 0
            cursor.commit()
            return seed_id

        # ... Additional code for non 'create' operations
SteveJ
  • 3,034
  • 2
  • 27
  • 47
  • The problem with this answer is that it doesn't explain where `query_type` and `QueryType` come from. – Paul Carlton Oct 26 '16 at 00:50
  • @DataHerder; I can see how that could be confusing -- its just an enumeration that I created to pass to my method. It can be ignored, the important part is the code inside the create 'if' statement. – SteveJ Nov 04 '16 at 04:36
  • I had this error no results. Previous SQL was not a query. I m using a stored procedure. How can I get the id identity – GSandro_Strongs Oct 21 '20 at 20:36
9

You can do it in 2 ways

  1. select SCOPE_IDENTITY() - this is preferred as it's limited by scope to identity you created.

  2. select @@IDENTITY - this is the last identity that was created regardless of the scope (e.g. a trigger can create additional identities).

See more in here: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Szymon
  • 42,577
  • 16
  • 96
  • 114
0

Please try SELECT @@Identity after insert query this will return Primary key Identity column value for current connection.

Kevin Shah
  • 1,589
  • 1
  • 13
  • 20