Re: [sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?

[Mike Bayer]

On Fri, Sep 16, 2022, at 12:10 PM, mkmo...@gmail.com wrote:
> I use the following pattern in my REST APIs, building up the select, joins, 
> where conditions, group bys, order bys, depending on the query parameters 
> passed in by the user:
> 
> selects = [Foo]
> joins = [(Bar, Foo.c.id == Bar.c.foo_id)]
> where_conditions = [Foo.c.id == request.args['pk']]
> 
> if request.args.get('include_baz'):
> selects.append(Baz)
> joins.append((Baz, Bar.c.id == Baz.c.bar_id))
> 
> What I would like to do is the following:
> 
> sel = select(
> *selects
> ).join(
> *joins  # doesn't work
> ).where(
> *where_conditions
> )
> 
> This works for everything except for `join` and `outerjoin`. So I have to 
> write it like this:
> 
> sel = select(*selects)
> for table, condition in joins:
> sel = sel.join(table, condition)
> sel = se.where(*where_conditions)
> 
> Is there some way to perform a join by passing an array of (table, 
> conditions) so I can write the SQL without all of the `sel = sel. ` noise?

if you have explicit join conditions like that, you might be able to make them 
into join objects:

from sqlalchemy.orm import join
sel.join(*[join(left, right, onclause) for right, onclause in conditions])

IMO that's not really any better, or you can make a def like this:

def join(stmt, conditions):
for table, condition in conditions:
   stmt = stmt.join(table, condition)
return stmt

then you use it as:

sel = join(sel, *joins)

the form where we used to accept multiple join conditions inside of one join() 
method is part of legacy Query and is being removed.  There are too many 
different argument forms for join() as it is for it to be appropriate for it to 
accept *args.

personally I think "stmt = stmt.modifier(thing)" is the cleanest, including for 
the WHERE clause too.


> 
> What I've been doing is using a function like the following:
> 
> def collection_query(selects, joins, where_conditions, ...)
> 
> But this has other problems and I would like to go back to raw sqlalchemy.
> 
> Thanks and best regards,
> 
> Matthew
> 
> 
> 
> -- 
> 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/84058464-5b92-4305-a348-d5a65fba441fn%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/66aa5b23-940f-4450-b1f1-cecd25b1cad0%40www.fastmail.com.

[sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?

[mkmo...@gmail.com]

I use the following pattern in my REST APIs, building up the select, joins, 
where conditions, group bys, order bys, depending on the query parameters 
passed in by the user:

selects = [Foo]
joins = [(Bar, Foo.c.id == Bar.c.foo_id)]
where_conditions = [Foo.c.id == request.args['pk']]

if request.args.get('include_baz'):
selects.append(Baz)
joins.append((Baz, Bar.c.id == Baz.c.bar_id))

What I would like to do is the following:

sel = select(
*selects
).join(
*joins  # doesn't work
).where(
*where_conditions
)

This works for everything except for `join` and `outerjoin`. So I have to 
write it like this:

sel = select(*selects)
for table, condition in joins:
sel = sel.join(table, condition)
sel = se.where(*where_conditions)

Is there some way to perform a join by passing an array of (table, 
conditions) so I can write the SQL without all of the `sel = sel. ` noise?

What I've been doing is using a function like the following:

def collection_query(selects, joins, where_conditions, ...)

But this has other problems and I would like to go back to raw sqlalchemy.

Thanks and best regards,

Matthew

-- 
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/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com.

Re: [sqlalchemy] Can’t make the Composite Comparator work…

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

Thank you Simon!

That worked indeed and makes a lot of sense. I think I misinterpreted the 
documentation a bit in that sense; it did puzzle that SQLA would patch all 
these mapped objects…

Based on your example:

*>>> dbsession.query(Vertex).filter(Vertex.start > Point(2, 2)).all()*
*[<__main__.Vertex object at 0x1065f5690>]*

Cheers,
Jens


On Friday, September 16, 2022 at 7:20:40 PM UTC+10 Simon King wrote:

> (I haven't used any of these features, so the following is just a guess)
>
> In your assertion, you are comparing two *Point instances*. The SQLAlchemy 
> comparator_factory mechanism has not made any changes to the Point class 
> itself, and Point doesn't define __gt__, hence your TypeError.
>
> The point of the comparator_factory is to add class-level behaviour when 
> you are building SQL expressions. In this case, you could write a query 
> like this:
>
> query = dbsession.query(Vertex).filter(Vertex.start > Point(2, 2))
>
> Hope that helps,
>
> Simon
>

-- 
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/0d2ee17a-9132-43f7-99c7-6ec9803600bdn%40googlegroups.com.

Re: [sqlalchemy] Can’t make the Composite Comparator work…

[Simon King]

(I haven't used any of these features, so the following is just a guess)

In your assertion, you are comparing two *Point instances*. The SQLAlchemy
comparator_factory mechanism has not made any changes to the Point class
itself, and Point doesn't define __gt__, hence your TypeError.

The point of the comparator_factory is to add class-level behaviour when
you are building SQL expressions. In this case, you could write a query
like this:

query = dbsession.query(Vertex).filter(Vertex.start > Point(2, 2))

Hope that helps,

Simon

On Fri, Sep 16, 2022 at 9:38 AM jens.t...@gmail.com 
wrote:

> Hello,
>
> I’m noodling through the Composite Column Types
>  examples, and
> can’t make it work. Based on the code on that page I put together a minimal,
> reproducible example
>  (attached)
> which fails with
>
>
>
>
>
> *Traceback (most recent call last):  File "/path/to/test.py", line 75, in
> assert v1.start > v2.startTypeError: '>' not supported between
> instances of 'Point' and 'Point'*
>
> Why is that? What am I missing? I expected to see SQL generated that
> implements the “greater than” between two *Point* instances (or perhaps
> *Vertex* instance, not sure, probably not).
>
> 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/149c1ae1-94af-4a4e-884c-171f72eb010bn%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/CAFHwexeqBnAUrWJbrOyKS3aL5FsZvnCGOoU-VuOGeSOHh41aEQ%40mail.gmail.com.

[sqlalchemy] Can’t make the Composite Comparator work…

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

Hello,

I’m noodling through the Composite Column Types 
 examples, and can’t 
make it work. Based on the code on that page I put together a minimal, 
reproducible example 
 (attached) 
which fails with





*Traceback (most recent call last):  File "/path/to/test.py", line 75, in 
assert v1.start > v2.startTypeError: '>' not supported between 
instances of 'Point' and 'Point'*

Why is that? What am I missing? I expected to see SQL generated that 
implements the “greater than” between two *Point* instances (or perhaps 
*Vertex* instance, not sure, probably not).

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/149c1ae1-94af-4a4e-884c-171f72eb010bn%40googlegroups.com.
import logging

from sqlalchemy import Column, Integer, engine_from_config, sql
from sqlalchemy.orm import composite, declarative_base, sessionmaker
from sqlalchemy.orm.properties import CompositeProperty

logging.basicConfig(level=logging.DEBUG, force=True)
logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)


class PointComparator(CompositeProperty.Comparator):
def __gt__(self, other):
"""redefine the 'greater than' operation"""

return sql.and_(
*[
a > b
for a, b in zip(
self.__clause_element__().clauses,
other.__composite_values__(),
)
]
)


class Point:
def __init__(self, x, y):
self.x = x
self.y = y

def __composite_values__(self):
return self.x, self.y

def __repr__(self):
return f"Point(x={self.x!r}, y={self.y!r})"

def __eq__(self, other):
return isinstance(other, Point) and other.x == self.x and other.y == self.y

def __ne__(self, other):
return not self.__eq__(other)


Base = declarative_base()


class Vertex(Base):
__tablename__ = "vertices"

id = Column(Integer, primary_key=True)
x1 = Column(Integer)
y1 = Column(Integer)
x2 = Column(Integer)
y2 = Column(Integer)

start = composite(Point, x1, y1, comparator_factory=PointComparator)
end = composite(Point, x2, y2, comparator_factory=PointComparator)


engine = engine_from_config({"sqlalchemy.url": "postgresql+psycopg2://postgres:postgres@localhost/testdb"})
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

connection = engine.connect()
make_session = sessionmaker(bind=connection)
dbsession = make_session()

v1 = Vertex(start=Point(1, 2), end=Point(3, 4))
dbsession.add(v1)
v2 = Vertex(start=Point(4, 4), end=Point(9, 9))
dbsession.add(v2)
dbsession.flush()

# assert v1 > v2
assert v1.start > v2.start

dbsession.commit()

connection.close()
engine.dispose()