DatabaseTemplate: how to get the lastrowid

[alex.dancu]

Hello,

Here is my usecase:

I have a Tag object

class Tag(object):
    def __init__(self, name = "New tag"):
        self.name = name
        self.id = 0

When I first save a Tag object:

self.dt.execute('INSERT INTO tag(name) VALUES(%s)', (tag.name,))

I need to get the id of the row I've just inserted in the tag table, so I can set the id property in my python object. At this point, if I was using an ORM like Hibernate in Java or SqlAlchemy in Python, the object would be considered "persistent" and the id would have been set by the ORM. However, I only need the id and set the property myself. This id is available in the cursor used to execute the INSERT statement and it's called lastrowid. But the only way to access that cursor was to make a small change in the execute() method from springpython.database.core.py.

Basically, the method now returns a tuple which contains both row_count and lastrowid.

def execute(self, sql_statement, args = None):
        """Issue a single SQL execute, typically a DDL statement."""
        sql_statement = self.connection_factory.convert_sql_binding(sql_statement)

        cursor = self.__db.cursor()
        error = None
        rows_affected = 0
        last_row_id = 0
        try:
            try:
                if args:
                    cursor.execute(sql_statement, args)
                    rows_affected = cursor.rowcount
                    last_row_id = cursor.lastrowid
                else:
                    cursor.execute(sql_statement)
                    rows_affected = cursor.rowcount
                    last_row_id = cursor.lastrowid
            except Exception, e:
                self.logger.debug("execute.execute: Trapped %s while trying to execute '%s'" % (e, sql_statement))
                error = e
        finally:
            try:
                cursor.close()
            except Exception, e:
                self.logger.debug("execute.close: Trapped %s, and throwing away." % e)
            
        if error:
            raise DataAccessException(error)
        
        return (rows_affected, last_row_id)

Maybe there is better way to do what I've just done. What are your thoughts?

Thanks,
Alex

[gregturn]

I've done some reading, and I saw where lastrowid is mentioned in the DB API2.0 spec. It appears to be part of the extensions, which I believe means it isn't guaranteed to exist for every database provider. I don't object to adding this as a feature to SP. But I want to think about how best to present it, in case it doesn't exist for a certain platform.

[gregturn]

I just finished https://jira.springsource.org/browse...NGPYTHONPY-158, which adds insert_and_return_id(), similar to execute(), to return the id of a newly inserted row instead of the number of row affected.

This will be in release 1.2.0.FINAL.

[gregturn]

With Spring Python 1.2.0.FINAL, you can now write code like this:

id = self.dt.insert_and_return_id('INSERT INTO tag(name) VALUES(%s)', (tag.name,))

If you dig into the source, you will find execute still there, doing the same.

You also have access to _execute, which returns a dictionary containing both the number of rows affected as well as the lastrowid, but I wouldn't recommend using it. _execute is not "public", and is subject to change which could break any apps you build on top of it.