知识问答

sqlalchemy在多列中唯一

内容 隐藏
问题：sqlalchemy在多列中唯一
回答 0
回答 1

问题：sqlalchemy在多列中唯一

假设我有一个代表位置的类。位置“属于”客户。位置由Unicode 10个字符代码标识。对于特定客户，“位置代码”在位置之间应该是唯一的。

The two below fields in combination should be unique
customer_id = Column(Integer,ForeignKey('customers.customer_id')
location_code = Column(Unicode(10))

因此，如果我有两个客户，客户“ 123”和客户“ 456”。它们都可以有一个称为“ main”的位置，但都不能有两个称为main的位置。

我可以在业务逻辑中处理此问题，但我想确保没有办法轻松地在sqlalchemy中添加需求。unique = True选项似乎仅在应用于特定字段时才起作用，这将导致整个表仅对所有位置具有唯一代码。

点击查看英文原文

Let’s say that I have a class that represents locations. Locations “belong” to customers. Locations are identified by a unicode 10 character code. The “location code” should be unique among the locations for a specific customer.

The two below fields in combination should be unique
customer_id = Column(Integer,ForeignKey('customers.customer_id')
location_code = Column(Unicode(10))

So if i have two customers, customer “123” and customer “456”. They both can have a location called “main” but neither could have two locations called main.

I can handle this in the business logic but I want to make sure there is no way to easily add the requirement in sqlalchemy. The unique=True option seems to only work when applied to a specific field and it would cause the entire table to only have a unique code for all locations.

回答 0

从以下文档的摘录Column：

unique –为True时，指示此列包含唯一约束，或者，如果index也为True，则指示应使用唯一标志创建索引。要在约束/索引中指定多个列或指定一个显式名称，请显式使用 UniqueConstraint或Index构造。

由于这些属于表而不属于映射的类，因此可以在表定义中声明它们，或者如果使用声明性声明，例如__table_args__：

# version1: table definition
mytable = Table('mytable', meta,
    # ...
    Column('customer_id', Integer, ForeignKey('customers.customer_id')),
    Column('location_code', Unicode(10)),

    UniqueConstraint('customer_id', 'location_code', name='uix_1')
    )
# or the index, which will ensure uniqueness as well
Index('myindex', mytable.c.customer_id, mytable.c.location_code, unique=True)


# version2: declarative
class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key = True)
    customer_id = Column(Integer, ForeignKey('customers.customer_id'), nullable=False)
    location_code = Column(Unicode(10), nullable=False)
    __table_args__ = (UniqueConstraint('customer_id', 'location_code', name='_customer_location_uc'),
                     )
点击查看英文原文

Extract from the documentation of the Column:

unique – When True, indicates that this column contains a unique constraint, or if index is True as well, indicates that the Index should be created with the unique flag. To specify multiple columns in the constraint/index or to specify an explicit name, use the UniqueConstraint or Index constructs explicitly.

As these belong to a Table and not to a mapped Class, one declares those in the table definition, or if using declarative as in the __table_args__:

# version1: table definition
mytable = Table('mytable', meta,
    # ...
    Column('customer_id', Integer, ForeignKey('customers.customer_id')),
    Column('location_code', Unicode(10)),

    UniqueConstraint('customer_id', 'location_code', name='uix_1')
    )
# or the index, which will ensure uniqueness as well
Index('myindex', mytable.c.customer_id, mytable.c.location_code, unique=True)


# version2: declarative
class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key = True)
    customer_id = Column(Integer, ForeignKey('customers.customer_id'), nullable=False)
    location_code = Column(Unicode(10), nullable=False)
    __table_args__ = (UniqueConstraint('customer_id', 'location_code', name='_customer_location_uc'),
                     )

回答 1

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class Location(Base):
      __table_args__ = (
        # this can be db.PrimaryKeyConstraint if you want it to be a primary key
        db.UniqueConstraint('customer_id', 'location_code'))
      customer_id = Column(Integer,ForeignKey('customers.customer_id')
      location_code = Column(Unicode(10))
点击查看英文原文 
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class Location(Base):
      __table_args__ = (
        # this can be db.PrimaryKeyConstraint if you want it to be a primary key
        db.UniqueConstraint('customer_id', 'location_code'),
      )
      customer_id = Column(Integer,ForeignKey('customers.customer_id')
      location_code = Column(Unicode(10))