Why isn't SQLAlchemy creating serial columns?

Question

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 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) 

With this script, the following table is generated:

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, a 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 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, 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 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 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)