Composite columns and None

[jens.t...@gmail.com]

Hello everyone,

I wanted to follow up on the examples on Composite Column Types and in particular setting the mapped composite value in Python-land to None.

For instance, this class

@dataclasses.dataclass
class Point:
    x: int
    y: int

is used in the following composite mapping and I’d like the two mapped properties to be optional (for the sake of the argument, whether it makes sense or not):

class Vertex(Base):
    start: Mapped[Point] | None = composite(mapped_column("x1"), mapped_column("y1"))
    end: Mapped[Point] | None = composite(mapped_column("x2"), mapped_column("y2"))The optional would then have to map to nullable=True for both mapped columns, which in turn would need to be checked to ensure integrity — either both or neither of the two mapped columns for a Point can be NULL at the same time.

I wasn’t able to find a recipe for this use case. What’s the recommendation to implement this? Will I have to roll most of this manually, or does SQLA provide support?

Much thanks!

Jens

[jens.t...@gmail.com]

Come to think of it, mapping to None doesn’t make much sense because then there’s no mapper.

However, it probably would make sense to define an “invalid” mapped Point if all mapped columns are NULL. In that case, I’d still consider adding check constraints to mildly improve integrity, although if any one mapped properties is NULL then by definition to Point would be invalid.

Hmm 🤔

[Mike Bayer]

you can create this using a custom callable for the actual composite type, where you would be bypassing the new-style annotations and dataclass detection part of things and relying on the older style of declaration.   You'd add a composite_values to your dataclass as well and just use the old style

import dataclasses

from sqlalchemy import create_engine

from sqlalchemy import Integer

from sqlalchemy import select

from sqlalchemy.orm import composite

from sqlalchemy.orm import DeclarativeBase

from sqlalchemy.orm import Mapped

from sqlalchemy.orm import mapped_column

from sqlalchemy.orm import Session

@dataclasses.dataclass

class Point:

    x: int | None

    y: int | None

    def __composite_values__(self):

        return (self.x, self.y)

class Base(DeclarativeBase):

    pass

def nullable_point(x, y):

    if x is None and y is None:

        return None

    else:

        return Point(x, y)

class Vertex(Base):

    __tablename__ = "vertices"

    id: Mapped[int] = mapped_column(primary_key=True)

    start: Mapped[Point | None] = composite(

        nullable_point,

        mapped_column("x1", Integer, nullable=True),

        mapped_column("y1", Integer, nullable=True),

    )

    end: Mapped[Point | None] = composite(

        nullable_point,

        mapped_column("x2", Integer, nullable=True),

        mapped_column("y2", Integer, nullable=True),

    )

    def __repr__(self):

        return f"Vertex(start={self.start}, end={self.end})"

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)

s.add_all(

    [

        Vertex(start=Point(5, 10)),

        Vertex(start=Point(10, None), end=Point(25, 17)),

    ]

)

s.commit()

for v in s.scalars(select(Vertex)):

    print(f"{v.start} {v.end}")

Much thanks!

Jens

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e0a3f019-11d0-4156-9c13-0a0e493dc40an%40googlegroups.com.

[jens.t...@gmail.com]

Thank you, Mike, for the suggestion!

Another thought I mulled over was to create a new point table and then use a nullable FK from the vertex table. That way, the Point class can map to its own dedicated table, and a Vertex class would then use a relationship that’s able to map optional Points to a Vertex via that FK relationship 🤔

Once we get to implementing this, I’ll let you know what we did…

[jens.t...@gmail.com]

I wanted to follow up on this thread.

Mike’s suggestion above worked, and considering the related discussion Dataclasses and Composites with init-only variables I adjusted the code somewhat: instead of using the nullable_point() function (from above) I extended the as_composite() class method (from the other thread) as suggested:

@classmethod

def as_composite(cls, x: int | None, y: int | None) -> Point | None:

    if x is None and y is None:

        return None

    return Point(x, y)

Then adjust the mapper types accordingly, and that’s it 👍🏼

Thanks!

Jens