sqlalchemy and auto increments with postgresql

Question

I created a table with a primary key and a sequence but via the debug ad later looking at the table design, the sequence isn't applied, just created.

from sqlalchemy import create_engine, MetaData, Table, Column,Integer,String,Boolean,Sequence
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import json
class Bookmarks(object):
    pass

#----------------------------------------------------------------------
engine = create_engine('postgresql://iser:p@host/sconf', echo=True)
Base = declarative_base()

class Tramo(Base):
    __tablename__ = 'tramos'
    __mapper_args__ = {'column_prefix':'tramos'}

    id = Column(Integer, Sequence('seq_tramos_id', start=1, increment=1),primary_key=True)
    nombre = Column(String)
    tramo_data = Column(String)
    estado = Column(Boolean,default=True)

    def __init__(self,nombre,tramo_data):
        self.nombre=nombre
        self.tramo_data=tramo_data

    def __repr__(self):
        return '[id:%d][nombre:%s][tramo:%s]' % self.id, self.nombre,self.tramo_data

Session = sessionmaker(bind=engine)
session = Session()

tabla = Tramo.__table__
metadata = Base.metadata
metadata.create_all(engine)

the table is just created like this

CREATE TABLE tramos (
    id INTEGER NOT NULL, 
    nombre VARCHAR, 
    tramo_data VARCHAR, 
    estado BOOLEAN, 
    PRIMARY KEY (id)
)

I was hoping to see the declartion of the default nexval of the sequence but it isn't there.

I also used the __mapper_args__ but looks like it's been ignored.

Am I missing something?

Answer 1

I realize this is an old thread, but I stumbled on it with the same problem and were unable to find a solution anywhere else.

After some experimenting I was able to solve this with the following code:

TABLE_ID = Sequence('table_id_seq', start=1000)

class Table(Base):
    __tablename__ = 'table'

    id = Column(Integer, TABLE_ID, primary_key=True, server_default=TABLE_ID.next_value())

This way the sequence is created and is used as the default value for column id, with the same behavior as if created implicitly by SQLAlchemy.