如何在SQLAlchemy中创建以Interval作为主键的表？

Question

我正在尝试使用SQLAlchemy ORM创建一个用于处理计费频率的表，但我似乎无法让它满意

以下是博士后的优秀作品：

create table test_interval(
    frequency interval primary key
);


insert into test_interval values ('1 MONTH'), ('1 YEAR');


select * from test_interval;
-- 0 years 1 mons 0 days 0 hours 0 mins 0.00 secs
-- 1 years 0 mons 0 days 0 hours 0 mins 0.00 secs

我现在正试图用这段代码在SQLAlchemy中实现同样的效果

from typing import Any

from sqlalchemy import Column, Interval, PrimaryKeyConstraint
from sqlalchemy.ext.declarative import as_declarative, declared_attr


@as_declarative()
class Base:
    id: Any
    __name__: str

    # Generate __tablename__ automatically
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()



class BillingFrequency(Base):
    __tablename__ = "billing_frequency"
    # I've also tried this
    # __table_args__ = (PrimaryKeyConstraint("frequency"),)
    # frequency: Column(Interval(native=True), index=True, unique=True, nullable=False)
    frequency: Column(Interval(native=True), primary_key=True, nullable=False)


# seed.py
# -- I've not even managed to create the table so this is yet untested --
from sqlalchemy.orm import Session
from sqlalchemy.dialects.postgresql import insert

from app.models import BillingFrequency

def seed_billing(db: Session) -> None:
    # Monthy frequency
    stmt_month = insert(BillingFrequency).values(frequency="1 MONTH")
    stmt_month = stmt_month.on_conflict_do_nothing(
        index_elements=[BillingFrequency.frequency],
    )
    db.add(stmt_month)
    # Year frequency
    stmt_year = insert(BillingFrequency).values(frequency="1 YEAR")
    stmt_year = stmt_year.on_conflict_do_nothing(
        index_elements=[BillingFrequency.frequency],
    )
    db.add(stmt_year)
    db.commit()

这将导致以下错误：

sqlalchemy.exc.ArgumentError: Mapper mapped class BillingFrequency->billing_frequency could not assemble any primary key columns for mapped table 'billing_frequency'

如果我尝试使用__table_args__使用主键，我会得到以下错误

 KeyError: 'frequency'

不知道该怎么处理。在纯SQL中，这是非常琐碎的，但是ORM使它成为一件痛苦的事情

Answer 1

您犯了两个小错误，但不幸的是，对于这种类型的错误，错误消息有点神秘

第一个问题是您使用了...: Column，即作为类型而不是...= Column，分配了一个值。这就是导致sqlalchemy.exc.ArgumentError和KeyError: 'frequency'的原因，SQLAlchemy不知道该列存在，因为它不在类型注释中查找列数据

您犯的第二个错误是对语句使用db.add(…)，而应该使用db.execute(…)。使用db.add会出现以下错误：

AttributeError: 'Insert' object has no attribute '_sa_instance_state'

The above exception was the direct cause of the following exception:
...
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.dialects.postgresql.dml.Insert' is not mapped

通过这些更改，您的代码应该如下所示：

from typing import Any

from sqlalchemy import Column, Interval, PrimaryKeyConstraint
from sqlalchemy.ext.declarative import as_declarative, declared_attr


@as_declarative()
class Base:
    id: Any
    __name__: str

    # Generate __tablename__ automatically
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()



class BillingFrequency(Base):
    __tablename__ = "billing_frequency"
    frequency = Column(Interval(native=True), primary_key=True, nullable=False)


# seed.py
#   I've not even managed to create the table so this is yet untested  
from sqlalchemy.orm import Session
from sqlalchemy.dialects.postgresql import insert

from app.models import BillingFrequency

def seed_billing(db: Session) -> None:
    # Monthy frequency
    stmt_month = insert(BillingFrequency).values(frequency="1 MONTH")
    stmt_month = stmt_month.on_conflict_do_nothing(
        index_elements=[BillingFrequency.frequency],
    )
    db.execute(stmt_month)
    # Year frequency
    stmt_year = insert(BillingFrequency).values(frequency="1 YEAR")
    stmt_year = stmt_year.on_conflict_do_nothing(
        index_elements=[BillingFrequency.frequency],
    )
    db.execute(stmt_year)
    db.commit()