On Tue, Nov 14, 2023, at 6:02 PM, jens.t...@gmail.com wrote: > Hello everyone, > > I wanted to follow up on the examples on Composite Column Types > <https://docs.sqlalchemy.org/en/20/orm/composites.html> 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 > <https://docs.sqlalchemy.org/en/20/orm/examples.html> 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?
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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e0a3f019-11d0-4156-9c13-0a0e493dc40an%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e0a3f019-11d0-4156-9c13-0a0e493dc40an%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8da01a71-032d-4c95-82fb-b27970be111e%40app.fastmail.com.