sqlalchemy and auto increments with postgresql

postgresql, python, sqlalchemy

Solution

You specified an explicit `Sequence()` object with name. If you were to omit that, then `SERIAL` would be added to the `id` primary key specification:

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

A `DEFAULT` is only generated if the column is not a primary key.

When inserting, SQLAlchemy will issue a `select nextval(..)` as needed to create a next value. See the PostgreSQL documentation for details.

Problem

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?

Original source