How to create a table with Interval as primary key in SQLAlchemy?

Question

I'm trying to create a table for handling billing frequencies using the SQLAlchemy ORM and I can't seem to get it to be happy

The following works great in 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

And I'm now trying to achieve the same thing in SQLAlchemy with this code

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()

Which results in the following error:

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

And if I try to use primary-key using __table_args__ I get the following error.

 KeyError: 'frequency'

Not sure how to handle this. It's quite trivial in pure SQL but the ORM makes it a pain.

user_274752 · Answer

You've made two minor errors, but the error messages are unfortunately a bit cryptic for this type of error.

The first problem is that you've used ...: Column i.e. as a type instead of ...= Column, assigning a value. This is what causes the sqlalchemy.exc.ArgumentError and the KeyError: 'frequency', SQLAlchemy doesn't know that the column exists since it doesn't look in the type annotations for Column data.

The second error you've made is to use db.add(…) for a statement, you should instead use db.execute(…). You would get this following error with 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

With these changes your code should look like this:


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()

How to create a table with Interval as primary key in SQLAlchemy?

1 Answers