postgresql 为什么 SQLAlchemy 不创建串行列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21328599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why isn't SQLAlchemy creating serial columns?
提问by Reece
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 wascreated:
然而，序列被创建：
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
-里斯
回答by zzzeek
this is because you provided it with an explicit Sequence. The SERIALdatatype in postgresql generates its ownsequence, 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 autoincrementargument in conjunction with Integer primary_key; it defaults to True). But when Sequenceis passed, SQLAlchemy sees the intent that you don't want the sequence implicitly created by SERIALbut instead the one you are specifying:
这是因为您为它提供了一个明确的Sequence. SERIALpostgresql 中的数据类型生成它自己的序列，SQLAlchemy 知道如何定位 - 因此，如果省略Sequence，SQLAlchemy 将呈现SERIAL，假设意图是该列是自动递增的（这由autoincrement与 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)
)
回答by David Rios
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)
回答by Keith Macdonald
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.
和 David 一样，我发现 Sequence 必须与类分开定义。对于任何使用“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
