为什么SQLAlchemy不能创建串行列？

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

SQLAlchemy正在为postgresql中的列生成(但不启用)序列。我怀疑我可能在引擎设置方面做错了。

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

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

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

3 个解决方案

#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:

这是因为你为它提供了一个明确的序列。 postgresql中的SERIAL数据类型生成自己的序列,SQLAlchemy知道如何定位 - 所以如果省略Sequence,SQLAlchemy将呈现SERIAL,假设意图是该列是自动递增的(由自动递增参数结合确定)使用Integer primary_key;默认为True)。但是当Sequence传递时,SQLAlchemy会看到你不想要SERIAL隐式创建的序列的意图,而是你要指定的那个:

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

#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:

如果由于某种原因需要显式创建序列,例如设置起始值,并且仍然需要与使用Column(Integer,primary_key = True)表示法时相同的默认值行为,则可以使用以下代码完成:

#!/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)

#3

Reece

I also used that tutorial as a model, and just could not get it to work with any Postgres tables that already existed and had key ID columns with serial sequences to generate the new key ID values.

我还将该教程用作模型,并且无法使其与已存在的任何Postgres表一起使用,并且具有带有序列序列的密钥ID列以生成新的密钥ID值。

Like David, I found the Sequence had to be defined separately to the class. For anyone using the "db.Model" approach, here's one example.

像大卫一样,我发现序列必须与班级分开定义。对于任何使用“db.Model”方法的人来说,这是一个例子。

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