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