SQLAlchemy为什么不创建串行列？ [英] Why isn't SQLAlchemy creating serial columns?

问题描述

SQLAlchemy正在生成但未启用Postgresql中列的序列。我怀疑我在引擎设置中可能做错了。

SQLAlchemy is generating, but not enabling, sequences for columns in postgresql. I suspect I may be doing something wrong in engine setup.

使用SQLAlchemy教程中的示例（ http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html ）：

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

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

-里斯（Reece）

推荐答案

这是因为您为其提供了明确的序列。 postgresql中的 SERIAL 数据类型生成其 own 序列，SQLAlchemy知道如何定位该序列-因此，如果省略 Sequence ，SQLAlchemy将呈现 SERIAL ，假设目的是该列是自动递增的（由 autoincrement 参数与Integer primary_key结合使用；它默认为True）。但是，当传递 Sequence 时，SQLAlchemy会发现您不希望由 SERIAL 隐式创建的序列的意图是您指定的一个：

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)

输出：

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

这篇关于SQLAlchemy为什么不创建串行列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！