On Tue, Nov 14, 2023, at 6:02 PM, [email protected] 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8da01a71-032d-4c95-82fb-b27970be111e%40app.fastmail.com.
