Sequence / Identity support in python webframeworks

Question

Currently I'm evaluating web frameworks with an ORM layer and I've stumbled upon an interesting issue. I used tables with an ID column in a JEE (EJB3/JPA) application with different databases. In SAPDB I could define a sequence and use the jpa sequence generator annotation to deal with it, the same way I did on an oracle database previously. When I switched to SQL Server 2005 I suddenly had to replace the whole thing to an IDENTITY annotation because thats how SQL Server handles id generation apparently. I was kinda disappointed the JPA didn't give me an abstraction above that and I guess its not the only limitation I will come across using different databases.

Now to my question: I've read that in web2py for example all tables must be altered to use an auto_increment index. Does it support sequence generators or identity columns as well? How about the other web frameworks? Will they allow me to port my application across multiple legacy databases? That is to say, the table definitions are the same except for the identity column and the slight differences between data type definition for Strings (I remember altering the create statements there but I cant remember what exactly was necessary)

There's also a difference in obtaining the ID - with the sequence generator you can access the generated ID before the commit, with auto_increment in MySQL, afaik, you cant

Thanks in advance!

Answer 1

SQLAlchemy can handle both ways just fine. Given a schema declaration like this:

class Foo(Base):
    id = Column(Integer, Sequence('foo_seq', optional=True), primary_key=True)
    ...

SQLAlchemy will use the built in auto-incrementing paradigm of the database if it has one, otherwise it will define a sequence. It also manages the fetching of the generated id, but as the API is least common denominator you obviously can't access it before the insert. You can ofcourse fetch the id manually if you want to. For example for PostgreSQL that has sequences, but also has an auto-incrementing serial datatype that's backed by sequences, this will use the serial datatype. With older versions it will automatically prefetch the next sequence value to use in the insert, with SQLAlchemy 0.6 series and Postgres 8.3+ it will use the INSERT ... RETURNING ... feature to insert and fetch in one go.