Re: [sqlalchemy] Composite columns and None

[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

-- 
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/2e52030a-8772-4876-8c74-1f607d2af049n%40googlegroups.com.

Re: [sqlalchemy] Composite columns and None

[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 *Point*s to a *Vertex* via that FK 
relationship 樂

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

-- 
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/f3737188-68c0-4eba-9467-a7aaa520df2fn%40googlegroups.com.

Re: [sqlalchemy] Composite columns and None

[Mike Bayer]

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 
>  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?

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
>  
> .

-- 
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.

[sqlalchemy] 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

-- 
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.