Why doesn't SQLAlchemy create sequential columns?

Question

SQLAlchemy generates, but does not allow, sequences for columns in postgresql. I suspect that I can do something wrong in tuning the engine.

Using the example from the SQLAlchemy tutorial ( http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html ):

#!/usr/bin/env python from sqlalchemy import create_engine, Column, Integer, String, Sequence from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, Sequence('user_id_seq'), primary_key=True) 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) 

Using this script, the following table is created:

 serial=# \d+ users Table "public.users" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------+-----------+----------+--------------+------------- id | integer | not null | plain | | name | character varying(50) | | extended | | fullname | character varying(50) | | extended | | password | character varying(12) | | extended | | Indexes: "users_pkey" PRIMARY KEY, btree (id) Has OIDs: no 

However, the sequence was created:

 serial=# select sequence_schema,sequence_name,data_type from information_schema.sequences ; sequence_schema | sequence_name | data_type -----------------+---------------+----------- public | user_id_seq | bigint 

SQLAlchemy 0.9.1, Python 2.7.5+, Postgresql 9.3.1, Ubuntu 13.10

-Reece

Answer 1

this is because you provided it with an explicit Sequence . The postgresql SERIAL data type generates its own sequence that SQLAlchemy knows how to find - so if you omit Sequence , SQLAlchemy will display SERIAL , assuming the goal is that the column grows automatically (as determined by the autoincrement argument in conjunction with Integer primary_key , the default value is True). But when the Sequence is passed, SQLAlchemy sees the intention that you do not want the sequence implicitly created by SERIAL , but instead of the one you specify:

 from sqlalchemy import create_engine, Column, Integer, String, Sequence from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class T1(Base): __tablename__ = 't1' # emits CREATE SEQUENCE + INTEGER id = Column(Integer, Sequence('user_id_seq'), primary_key=True) class T2(Base): __tablename__ = 't2' # emits SERIAL id = Column(Integer, primary_key=True) class T3(Base): __tablename__ = 't3' # emits INTEGER id = 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 CREATE TABLE t1 ( id INTEGER NOT NULL, PRIMARY KEY (id) ) CREATE TABLE t2 ( id SERIAL NOT NULL, PRIMARY KEY (id) ) CREATE TABLE t3 ( id INTEGER NOT NULL, PRIMARY KEY (id) ) 

Answer 2

If you need to create a sequence explicitly for any reason, for example, set the initial value and still want the same default behavior as when using the Column(Integer, primary_key=True) notation Column(Integer, primary_key=True) , this can be done using the following code:

 #!/usr/bin/env python from sqlalchemy import create_engine, Column, Integer, String, Sequence from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() USER_ID_SEQ = Sequence('user_id_seq') # define sequence explicitly class User(Base): __tablename__ = 'users' # use sequence in column definition, and pass .next_value() as server_default id = 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) 

Answer 3

Riis

I also used this tutorial as a model and simply could not get it to work with any existing Postgres tables and had columns of key identifiers with sequential sequences to generate new key identifier values.

Like David, I found that the sequence must be defined separately for the class. For anyone using the "db.Model" approach, here is one example.

 from flask.ext.sqlalchemy import SQLAlchemy from sqlalchemy import Sequence db = SQLAlchemy() pageimpression_imp_id_seq = Sequence('pageimpression_imp_id_seq') class PageImpression(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) def PageImpressionAdd(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') and not request.environ.get('HTTP_REFERER').isspace(): referrer = request.environ.get('HTTP_REFERER') # the string is not-empty else: referrer = '' # the string is empty from 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