Clone wiki

sqlalchemy / UsageRecipes / AutoSequenceGeneration

When using Oracle, it is necessary to associate a sequence with a Primary Key in order to allow auto-increment to work. A function such as this can be used to support auto sequence generation:

def PrimaryKey(seqprefix=None):
        global seqnum
        if not seqprefix:
                seqnum += 1
                seqname = "id_seq_%s" % seqnum
                seqname = "%s_id_seq" % seqprefix
        return Column(Integer, Sequence(seqname, optional=True), primary_key=True)

This 'PrimaryKey' function will return a Column with an associated sequence, which will be named uniquely, using either a name using an incrementing integer in the form "id_seq_%s", or if a seqprefix argument is specified, a name "%s_id_seq".

When using declarative, a metaclass can be used to automatically add a 'PrimaryKey' to your tables. This metaclass approach is required to name the sequence name based on the table name. For simpler situations, SQLAlchemy Mix-In Classes might be more appropriate:

class ClassDefaults(DeclarativeMeta):
        def __init__(cls,classname, bases, dict_):
                if not ( dict_.has_key('__mapper_args__') and dict_['__mapper_args__'].has_key('polymorphic_identity') ):
                        # Only add the key if we are not creating a polymorphic SQLAlchemy object, because SQLAlchemy
                        # does not want a separate 'id' key added in that case.
                        # seqprefix can be None
                        seqprefix = getattr(cls,'__tablename__',None)

                         # for SQLAlchemy 0.6_beta1 and earlier, use this:
                         dict_['id'] = PrimaryKey(seqprefix=seqprefix)

                         # for SQLAlchemy 0.6_beta2 and later, use this:
                = PrimaryKey(seqprefix=seqprefix)
                return DeclarativeMeta.__init__(cls, classname, bases, dict_)

# This base class will automatically add a PrimaryKey with Sequence with seq name based on __tablename__:
Base = declarative_base(metaclass=ClassDefaults)

class MyTable(Base):
        __tablename__ = "mytable"
        # This table will have a Primary Key called "id" and sequence "id_seq_mytable"
        name = Column(String(80))