SQLAlchemy正在生成但未启用Postgresql中列的序列。我怀疑我在引擎设置中可能做错了什么。
使用SQLAlchemy教程(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)
使用此脚本,将生成下表:
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
然而,序列 被 创建:
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
这是因为您为其提供了明确的Sequence。SERIALpostgresql中的数据类型生成其 自己的 序列,SQLAlchemy知道如何定位- 因此,如果省略Sequence,则SQLAlchemy将呈现SERIAL,假设目的是该列是自动递增的(由autoincrement参数与Integer primary_key结合确定;它默认为True)。但是,当Sequence传递时,SQLAlchemy会看到您不希望隐式创建序列的意图,而是希望SERIAL您指定的序列:
Sequence
SERIAL
autoincrement
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)
输出:
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) )