Why Isn't Sqlalchemy Creating Serial Columns?

SQLAlchemy is generating, but not enabling, sequences for columns in postgresql. I suspect I may be doing something wrong in engine setup. Using an example from the SQLAlchemy tut

Solution 1:

this is because you provided it with an explicit Sequence. The SERIAL datatype in postgresql generates its own sequence, which SQLAlchemy knows how to locate - so if you omit the Sequence, SQLAlchemy will render SERIAL, assuming the intent is that the column is auto-incrementing (which is determined by the autoincrement argument in conjunction with Integer primary_key; it defaults to True). But when Sequence is passed, SQLAlchemy sees the intent that you don't want the sequence implicitly created by SERIAL but instead the one you are specifying:

from sqlalchemy import create_engine, Column, Integer, String, Sequencefrom sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

classT1(Base):
    __tablename__ = 't1'# emits CREATE SEQUENCE + INTEGERid = Column(Integer, Sequence('user_id_seq'), primary_key=True)

classT2(Base):
    __tablename__ = 't2'# emits SERIALid = Column(Integer, primary_key=True)

classT3(Base):
    __tablename__ = 't3'# emits INTEGERid = Column(Integer, autoincrement=False, primary_key=True)

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(engine)

output:

CREATE SEQUENCE user_id_seq

CREATETABLE t1 (
    id INTEGERNOTNULL, 
    PRIMARY KEY (id)
)


CREATETABLE t2 (
    id SERIAL NOTNULL, 
    PRIMARY KEY (id)
)


CREATETABLE t3 (
    id INTEGERNOTNULL, 
    PRIMARY KEY (id)
)

Solution 2:

If you need to create the sequence explicitly for some reason, like setting a start value, and still want the same default value behavior as when using the Column(Integer, primary_key=True) notation, it can be accomplished with the following code:

#!/usr/bin/env pythonfrom sqlalchemy import create_engine, Column, Integer, String, Sequencefrom sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
USER_ID_SEQ = Sequence('user_id_seq')  # define sequence explicitlyclassUser(Base):
    __tablename__ = 'users'# use sequence in column definition, and pass .next_value() as server_defaultid = Column(Integer, USER_ID_SEQ, primary_key=True, server_default=USER_ID_SEQ.next_value())
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def__repr__(self):
        return"<User(name='%s', fullname='%s', password='%s')>" % (
                                self.name, self.fullname, self.password)

db_url = 'postgresql://localhost/serial'
engine = create_engine(db_url, echo=True)
Base.metadata.create_all(engine)

Solution 3:

Reece

I also used that tutorial as a model, and just could not get it to work with any Postgres tables that already existed and had key ID columns with serial sequences to generate the new key ID values.

Like David, I found the Sequence had to be defined separately to the class. For anyone using the "db.Model" approach, here's one example.

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy importSequence
db = SQLAlchemy()

pageimpression_imp_id_seq = Sequence('pageimpression_imp_id_seq')
classPageImpression(db.Model):
        __tablename__ = 'pageimpression'
        imp_id = db.Column(db.Integer,     
    pageimpression_imp_id_seq,           
    server_default=usersession_sessionid_seq.next_value(),primary_key=True)
    logdate = db.Column(db.DateTime)
    sessionid = db.Column(db.String)
    path = db.Column(db.String)
    referrer = db.Column(db.String)

def__init__(self, imp_id, logdate, sessionid, path, referrer):
    self.imp_id = imp_id
    self.logdate = logdate
    self.sessionid = sessionid
    self.path = path
    self.referrer = referrer

def__repr__(self):
   return"<PageImpression(imp_id='%s', logdate='%s',sessionid='%s', path='%s', referrer='%s')>" % (self.imp_id, self.logdate, self.sessionid, self.path, self.referrer)

defPageImpressionAdd(sessionid):
    sessionid = 0# dummy value for unit testing
    current_time = datetime.now().isoformat()
    if CurrentConfig.IMPRESSION_LOGGING_ON == True:     
        path = request.path
        if request.environ.get('HTTP_REFERER') andnot request.environ.get('HTTP_REFERER').isspace():
            referrer = request.environ.get('HTTP_REFERER') # the string is not-emptyelse:
            referrer = ''# the string is emptyfrom website.models import PageImpression
        thisPageImpression = PageImpression(None,current_time,sessionid, path, referrer)
        db.session.add(thisPageImpression)
        db.session.commit()
        # get the values created by the Postgres table defaults
        imp_id = thisPageImpression.imp_id
        logdate = thisPageImpression.logdate
    return current_time

Solution 4:

You can also change Sequence without any SQL script by GUI pgAdmin as below:

select your DB -> Schemas -> public -> Sequences -> right click -> properties -> Definition -> Current value.