How to get the last created ID in SQL

Question

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!

Answer 1

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