sqlalchemy 和 postgresql 自动增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15439514/
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
sqlalchemy and auto increments with postgresql
提问by Freaktor
I created a table with a primary key and a sequence but via the debug ad later looking at the table design, the sequence isn't applied, just created.
我创建了一个带有主键和序列的表，但是通过稍后查看表设计的调试广告，没有应用序列，只是创建了。
from sqlalchemy import create_engine, MetaData, Table, Column,Integer,String,Boolean,Sequence
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import json
class Bookmarks(object):
pass
#----------------------------------------------------------------------
engine = create_engine('postgresql://iser:p@host/sconf', echo=True)
Base = declarative_base()
class Tramo(Base):
__tablename__ = 'tramos'
__mapper_args__ = {'column_prefix':'tramos'}
id = Column(Integer, Sequence('seq_tramos_id', start=1, increment=1),primary_key=True)
nombre = Column(String)
tramo_data = Column(String)
estado = Column(Boolean,default=True)
def __init__(self,nombre,tramo_data):
self.nombre=nombre
self.tramo_data=tramo_data
def __repr__(self):
return '[id:%d][nombre:%s][tramo:%s]' % self.id, self.nombre,self.tramo_data
Session = sessionmaker(bind=engine)
session = Session()
tabla = Tramo.__table__
metadata = Base.metadata
metadata.create_all(engine)
the table is just created like this
表格就是这样创建的
CREATE TABLE tramos (
id INTEGER NOT NULL,
nombre VARCHAR,
tramo_data VARCHAR,
estado BOOLEAN,
PRIMARY KEY (id)
)
I was hoping to see the declartion of the default nexval of the sequence but it isn't there.
我希望看到序列的默认 nexval 的声明，但它不存在。
I also used the __mapper_args__but looks like it's been ignored.
我也使用了__mapper_args__但看起来它被忽略了。
Am I missing something?
我错过了什么吗？
回答by Martijn Pieters
You specified an explicit Sequence()object with name. If you were to omit that, then SERIALwould be added to the idprimary key specification:
您指定了一个Sequence()带有名称的显式对象。如果您要省略它，则将SERIAL添加到id主键规范中：
CREATE TABLE tramos (
id INTEGER SERIAL NOT NULL,
nombre VARCHAR,
tramo_data VARCHAR,
estado BOOLEAN,
PRIMARY KEY (id)
)
A DEFAULTis only generated if the column is not a primary key.
ADEFAULT仅在列不是主键时生成。
When inserting, SQLAlchemy will issue a select nextval(..)as needed to create a next value. See the PostgreSQL documentationfor details.
插入时，SQLAlchemy 将select nextval(..)根据需要发出 a以创建下一个值。有关详细信息，请参阅PostgreSQL 文档。
回答by David Rios
I realize this is an old thread, but I stumbled on it with the same problem and were unable to find a solution anywhere else.
我意识到这是一个旧线程，但我偶然发现了同样的问题，并且无法在其他任何地方找到解决方案。
After some experimenting I was able to solve this with the following code:
经过一些实验，我能够使用以下代码解决这个问题：
TABLE_ID = Sequence('table_id_seq', start=1000)
class Table(Base):
__tablename__ = 'table'
id = Column(Integer, TABLE_ID, primary_key=True, server_default=TABLE_ID.next_value())
This way the sequence is created and is used as the default value for column id, with the same behavior as if created implicitly by SQLAlchemy.
通过这种方式创建序列并用作 column 的默认值id，其行为与由 SQLAlchemy 隐式创建的行为相同。
回答by mdh
I ran into a similar issue with composite multi-column primary keys. The SERIALis only implicitly applied to a single column primary key. However, this behaviour can be controlled via the autoincrementargument (defaults to "auto"):
我遇到了与复合多列主键类似的问题。在SERIAL仅隐含地施加到单个列主键。但是，可以通过自动增量参数（默认为"auto"）控制此行为：
id = Column(Integer, primary_key=True, autoincrement=True)
