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

Question

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

以下在 Postgres 中效果很好：

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'

如果我尝试使用主键 using__table_args__我会收到以下错误。

 KeyError: 'frequency'

不知道如何处理。在纯 SQL 中这很简单，但 ORM 让它变得很痛苦。

标签： pythonpostgresqlsqlalchemy

Answer 1

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

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

您犯的第二个错误是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()