Re: [sqlalchemy] Geo query support in SqlAlchemy

[mike bayer]

well, GeoAlchemy was based off of a simple proof of concept and modern 
SQLAlchemy has a lot more API hooks to build up custom expression 
behavior.This would be dealing mostly with APIs at 
docs.sqlalchemy.org/en/latest/core/custom_types.html and in particular 
defining new operators 
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#redefining-and-creating-new-operators 
as well as 
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#applying-sql-level-bind-result-processing 
where you'd be applying the geometry functions.


If you're just in a hurry, that's fine too.   you can use a one-line 
custom type and just use func. directly.


I don't use geometry at all I just skimmed the MySQL docs, below are 
examples of direct func. usage.   What GeoAlchemy does is build 
operators that allow the func. calls to be transparent; but it's using 
older hacks (that I gave them) to make it work.  The above extensible 
type system can be used to make these functions transparent.



from sqlalchemy import create_engine, \
Column, Integer, func
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import UserDefinedType


class Geometry(UserDefinedType):
def get_col_spec(self, **kw):
return "GEOMETRY"


Base = declarative_base()


class MyGeometry(Base):
__tablename__ = 'my_geometry'

id = Column(Integer, primary_key=True)
data = Column(Geometry)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)
s.add(MyGeometry(data=func.ST_GeomFromText('MULTIPOINT(1 1, 2 2, 3 3)')))

print s.query(func.ST_AsText(MyGeometry.data)).first()


polygon = 'Polygon((3 15000, 31000 15000, 31000 16000, 3 16000, 
3 15000))'


print s.query(MyGeometry).filter(
func.MBRContains(func.ST_GeomFromText(polygon), MyGeometry.data)).all()




On 02/21/2017 08:08 PM, Guy Zehavi wrote:

I am using Python 3.3 with sqlalchemy using a MySql 5.6.33  Database
with SQLAlchemy 1.1.5. I know that mysql supports Geo data types, but I
was unable to find the equivalent in sqlalchemy in the docs. I did see
GeoAlchemy and GeoAlchemy2 but GeoAlchemy is not for Python 3 and I am
not using a PostgresQL for GeoAlchemy2. Does sqlAlchemy support this out
of the box? If not, any recommendations? Thanks very much in advance for
any help!

--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Using CTEs inside union()

[Michael Williamson]

On Wed, 22 Feb 2017 11:15:05 -0500
mike bayer  wrote:

> 
> 
> On 02/22/2017 10:17 AM, Michael Williamson wrote:
> > Using CTEs directly inside union() (or similar functions such as
> > intersect()) causes an error:
> >
> >
> >  query_1 = s.query(Record.id).cte()
> >  query_2 = s.query(Record.id).cte()
> >
> >  select_from = union(query_1, query_2)
> 
> what does the above intend to mean?  I don't know what SQL one would 
> expect from that.  A CTE is specifically a SELECT that goes on top of 
> another SELECT as a sort of "aliased" subquery.   a UNION doesn't
> make any sense on the "outside" of that unless you can show me.
> 
> I can see this instead:
> 
>   q1 = s.query(Record.id)
>   q2 = s.query(Record.id)
> 
>   select_from = union(q1, q2).cte()
> 
> but that's not what the above is saying.

My expectation was as in the working code I wrote: that is, select all
of the rows from the CTE.

select_from = union(
select([query_1.c.id]).select_from(query_1),
select([query_2.c.id]).select_from(query_2),
)

If the answer is "you're doing something weird, don't do that", that's
fine, I just thought I'd point out a situation where the error that was
generated didn't make it immediately clear where I'd make a mistake.

> 
> 
> 
> 
> 
> >  print(s.query(func.count(1)).select_from(select_from).all())
> >
> >
> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError)
> > syntax error at or near "SELECT" LINE 2: SELECT record.id AS id
> > ^
> >  [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
> > \nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
> > count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
> > anon_1'] [parameters: {'count_2': 1}]
> >
> > The way I got things working was to wrap the CTE in a SELECT rather
> > than using it directly:
> >
> > select_from = union(
> >  select([query_1.c.id]).select_from(query_1),
> >  select([query_2.c.id]).select_from(query_2),
> > )
> > print(s.query(func.count(1)).select_from(select_from).all())
> >
> > However, the original error didn't make it especially obvious to me
> > what was going on. Perhaps either the API could be changed to allow
> > using the CTE directly, or a clearer error could be emitted? Or am I
> > just thinking about this all wrong?
> >
> > I also got an error when using subquery() instead of cte(), which
> > was fixed in the same way -- presumably this is the same issue?
> >
> > Thanks
> >
> > Michael
> >
> 

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] self-referential many-to-many with single attribute

[Matthew Brookes]

Hi Mike,

Thanks for the pointers. I take your point about directionality, but it 
feels like this is a special case that intuitively should work the same way 
for a single model that it does for two. However for now, it does what it 
does.

I took at look at using a union @property, and while it does work to return 
both sides of the relationship, it does not, as you say support querying. I 
took a look at 
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper,
 
and I"ll be honest, I didn't understand most of it. Also 
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#building-query-enabled-properties
 
seems to suggest that only predefined queries would be possible?

I'm wondering if instead I should use one-to-many relationships, and use an 
event to create the opposing relationship when a relationship is added? 
Would appreciate your thoughts.

Thanks!
Matt.

On Tuesday, 21 February 2017 23:25:27 UTC, Mike Bayer wrote:
>
> you want "Node.connected" to be the set of all nodes connected in either 
> direction.The problem is that relationally, all graphs are 
> "directed", so we have the "left", "right" aspect of things. 
>
> The easiest way to get ".connected" as the union of both sets (hint!) is 
> to just union them in Python.   Give Node a "left_nodes" and 
> "right_nodes" relationship (example: see 
>
> http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html)
>  
>
> then make a regular Python @property that returns 
> "self.left_nodes.union(self.right_nodes)", easy enough. 
>
> If you want to get into querying this relationally, then you need to do 
> the UNION on the SQL side and you'd need to get into creating a mapping 
> against a UNION and then building a relationship to it.  This is 
> significantly more involved and would be using some of the techniques at 
>
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper.
>  
>
>
> But, a @property that does a Python union of two relationships, quick 
> and easy. 
>
>
>
> On 02/21/2017 04:43 PM, Matthew Brookes wrote: 
> > 
> > I apologize if this is well trodden ground, but having googled, and 
> > stack-overflowed, read the docs, and searched this list, where lots of 
> > people have asked the same (or similar) questions, I couldn't find a 
> > concrete answer... 
> > 
> > I'm trying to set up a Model such that an entry can be connected to one 
> > or more other entries, and that the reverse relationship can be found 
> > from the same attribute. 
> > 
> > Effectively I'm trying to do this: 
> > 
> > ``` 
> > 
> > from sqlalchemy import Integer, ForeignKey, String, Column, Table 
> > from sqlalchemy.ext.declarative import declarative_base 
> > from sqlalchemy.orm import relationship 
> > 
> > Base = declarative_base() 
> > 
> > node_to_node = Table("node_to_node", Base.metadata, 
> > Column("left_node_id", Integer, ForeignKey("node.id"), 
> primary_key=True), 
> > Column("right_node_id", Integer, ForeignKey("node.id"), 
> primary_key=True) 
> > ) 
> > 
> > class Node(Base): 
> > __tablename__ = 'node' 
> > id = Column(Integer, primary_key=True) 
> > label = Column(String) 
> > connected = relationship("Node", 
> > secondary=node_to_node, 
> > primaryjoin=id==node_to_node.c.left_node_id, 
> > secondaryjoin=id==node_to_node.c.right_node_id, 
> > backref="connected" 
> > ) 
> > 
> > ``` 
> > 
> > However, that naturally fails (`Error creating backref 'translations' on 
> > relationship 'Sentence.translations': property of that name exists`) as 
> > there's no magic to figure out that `translations` should be 
> > bi-directional. 
> > 
> > Is there another way to achieve this? 
> > 
> > Thanks! 
> > 
> > Matt. 
> > 
> > -- 
> > 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 post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

Re: [sqlalchemy] Using CTEs inside union()

[mike bayer]

On 02/22/2017 11:25 AM, Michael Williamson wrote:

On Wed, 22 Feb 2017 11:15:05 -0500
mike bayer  wrote:




On 02/22/2017 10:17 AM, Michael Williamson wrote:

Using CTEs directly inside union() (or similar functions such as
intersect()) causes an error:


 query_1 = s.query(Record.id).cte()
 query_2 = s.query(Record.id).cte()

 select_from = union(query_1, query_2)


what does the above intend to mean?  I don't know what SQL one would
expect from that.  A CTE is specifically a SELECT that goes on top of
another SELECT as a sort of "aliased" subquery.   a UNION doesn't
make any sense on the "outside" of that unless you can show me.

I can see this instead:

q1 = s.query(Record.id)
q2 = s.query(Record.id)

select_from = union(q1, q2).cte()

but that's not what the above is saying.


My expectation was as in the working code I wrote: that is, select all
of the rows from the CTE.

select_from = union(
select([query_1.c.id]).select_from(query_1),
select([query_2.c.id]).select_from(query_2),
)

If the answer is "you're doing something weird, don't do that", that's
fine, I just thought I'd point out a situation where the error that was
generated didn't make it immediately clear where I'd make a mistake.


Well, union(cte, cte) is just not a covered use case.  I guess you 
expected it to implicitly generate a SELECT against each CTE first, so 
yeah, that's not what union(x, y) implies; the internal objects are 
taken as is.


An example is to do the same thing just with a straight Table object and 
you can see putting those into UNION produces SQL that isn't valid:


>>> from sqlalchemy import table, union, column
>>> t1 = table('t', column('x'))
>>> t2 = table('s', column('y'))
>>> u = union(t1, t2)
>>> print u
 UNION
>>> print u.select()
SELECT x
FROM (t UNION s)

then to make it "right":

>>> u = union(t1.select(), t2.select())
>>> print u
SELECT t.x
FROM t UNION SELECT s.y
FROM s

for this kind of case SQLAlchemy tends to try to render what you're 
asking for literally.   the approach of having it look for failures 
ahead of time might be appropriate however at the same time it's kind of 
nice (and a little more performant) that it is open ended and doesn't 
assume that what you're asking isn't somehow possible, perhaps the given 
FROM has event attached to it that change its behavior or something.













 print(s.query(func.count(1)).select_from(select_from).all())


sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError)
syntax error at or near "SELECT" LINE 2: SELECT record.id AS id
^
 [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
\nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
anon_1'] [parameters: {'count_2': 1}]

The way I got things working was to wrap the CTE in a SELECT rather
than using it directly:

select_from = union(
 select([query_1.c.id]).select_from(query_1),
 select([query_2.c.id]).select_from(query_2),
)
print(s.query(func.count(1)).select_from(select_from).all())

However, the original error didn't make it especially obvious to me
what was going on. Perhaps either the API could be changed to allow
using the CTE directly, or a clearer error could be emitted? Or am I
just thinking about this all wrong?

I also got an error when using subquery() instead of cte(), which
was fixed in the same way -- presumably this is the same issue?

Thanks

Michael







--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Forcing filters to use same type as field

[mike bayer]

On 02/22/2017 04:23 PM, Chris Frey wrote:

Hi,

We're using MySQL, and we have tables that use a GUID as the ID.
Unfortunately, if the GUID starts with a number, and if you select
using an integer, mysql will helpfully convert for you:

mysql> select id from table where id = 2;
+-+
| id  |
+-+
| 2ceb-d632-f330-4e7c-8490-90b7-5a02-e633 |
+-+
1 row in set, 7 warnings (0.00 sec)


GUID is a string, since that's the only option available, so that is 
insane.  None of the SQL mode options even seem to affect it.




This comes from a query like this in SQLAlchemy:

record = session.query(Table).filter(Table.id == 2).first()

Is there any way to force the resulting SQL to use the type of Table.id
instead of the integer type of 2?


Obviously it's a little odd you are feeding an integer into your query. 
If you want to enforce the type do this:


from sqlalchemy.types import TypeDecorator

class MustBeString(TypeDecorator):
impl = String

def process_bind_param(self, value, dialect):
if not isinstance(value, str):
raise ValueError("value must be string!!!")
return value

since you're using GUID, you can use the recipe at 
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#backend-agnostic-guid-type 
which has a more comprehensive approach, just alter the bind_param 
routine to whatever you want it to do.






Or is there a better way to handle this?

Thanks,
- Chris



--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Forcing filters to use same type as field

[Chris Frey]

Hi,

We're using MySQL, and we have tables that use a GUID as the ID.
Unfortunately, if the GUID starts with a number, and if you select
using an integer, mysql will helpfully convert for you:

mysql> select id from table where id = 2;
+-+
| id  |
+-+
| 2ceb-d632-f330-4e7c-8490-90b7-5a02-e633 |
+-+
1 row in set, 7 warnings (0.00 sec)

This comes from a query like this in SQLAlchemy:

record = session.query(Table).filter(Table.id == 2).first()

Is there any way to force the resulting SQL to use the type of Table.id
instead of the integer type of 2?

Or is there a better way to handle this?

Thanks,
- Chris

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Status of cascading polymorphism, in docs as well

[Shane Carey]

Works great for me

On Tuesday, February 21, 2017 at 2:56:58 PM UTC-6, Shane Carey wrote:
>
> I understand from the docs and several questions both here and on 
> bitbucket that cascading polymorphism is not supported. This
> is what it says on the docs:
>
> Warning
>
> Currently, *only one discriminator column may be set*, typically on the 
> base-most class in the hierarchy. “Cascading” polymorphic columns are not 
> yet supported.
>
>
> However, the following experiment works for me
>
>
> from sqlalchemy import *
>
> from sqlalchemy.orm import *
>
> from sqlalchemy.ext.declarative import declarative_base
>
> import json
>
>
> Base = declarative_base()
>
>
> class Top(Base):
>
> __tablename__ = 'top'
>
>
> id = Column(Integer, primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'id': self.id
>
> }
>
>
> class Primary(Top):
>
> __tablename__ = 'primary'
>
>
> id = Column(None, ForeignKey(Top.id), primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'primary',
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'type': self.type,
>
> **super().dict()
>
> }
>
>
> class One(Primary):
>
> __tablename__ = 'one'
>
>
> id = Column(None, ForeignKey(Primary.id), primary_key=True)
>
> one = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'one'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'one': self.one,
>
> **super().dict()
>
> }
>
>
> class Two(Primary):
>
> __tablename__ = 'two'
>
>
> id = Column(None, ForeignKey(Primary.id), primary_key=True)
>
> two = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'two'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'two': self.two,
>
> **super().dict()
>
> }
>
>
> class Secondary(Top):
>
> __tablename__ = 'secondary'
>
>
> id = Column(None, ForeignKey(Top.id), primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'secondary',
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'type': self.type,
>
> **super().dict()
>
> }
>
>
> class Three(Secondary):
>
> __tablename__ = 'three'
>
>
> id = Column(None, ForeignKey(Secondary.id), primary_key=True)
>
> three = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'three'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'three': self.three,
>
> **super().dict()
>
> }
>
>
> class Four(Secondary):
>
> __tablename__ = 'four'
>
>
> id = Column(None, ForeignKey(Secondary.id), primary_key=True)
>
> four = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'four'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'four': self.four,
>
> **super().dict()
>
> }
>
>
> if __name__ == '__main__':
>
> e = create_engine('sqlite:///poly_casc.db', echo=True)
>
>
> Base.metadata.drop_all(e)
>
> Base.metadata.create_all(e)
>
>
> s = create_session(e)
>
>
> s.begin()
>
>
> s.add_all([One(one='one'), Two(two='two'), Three(three='three'), 
> Four(four='four')])
>
>
> s.commit()
>
>
> for m in s.query(Top).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> s.expunge_all()
>
>
> for m in s.query(Primary).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> s.expunge_all()
>
>
> for m in s.query(One).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> so I am wondering if the docs needed to be updated, or I am missing 
> something regarding the functionality.
>
>
> Thanks
>

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Using CTEs inside union()

[Michael Williamson]

Using CTEs directly inside union() (or similar functions such as
intersect()) causes an error:

 import os

 from sqlalchemy.event import listens_for
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()


 class Record(Base):
  __tablename__ = 'record'
  id = Column(Integer, primary_key=True)


 # On postgres
 e = create_engine(os.environ["TEST_DATABASE"], echo=True)

 Base.metadata.create_all(e)

 s = Session(e)
 s.commit()

 query_1 = s.query(Record.id).cte()
 query_2 = s.query(Record.id).cte()

 select_from = union(query_1, query_2)
 print(s.query(func.count(1)).select_from(select_from).all())


sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax
error at or near "SELECT" LINE 2: SELECT record.id AS id 
^
 [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
\nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
anon_1'] [parameters: {'count_2': 1}]

The way I got things working was to wrap the CTE in a SELECT rather than
using it directly:

select_from = union(
 select([query_1.c.id]).select_from(query_1),
 select([query_2.c.id]).select_from(query_2),
)
print(s.query(func.count(1)).select_from(select_from).all())

However, the original error didn't make it especially obvious to me
what was going on. Perhaps either the API could be changed to allow
using the CTE directly, or a clearer error could be emitted? Or am I
just thinking about this all wrong?

I also got an error when using subquery() instead of cte(), which was
fixed in the same way -- presumably this is the same issue?

Thanks

Michael

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] inspecting attribute_mapped_collection info of RelationshipProperty?

[YKdvd]

Let's say I have a model class Child, with an Integer column "birthOrder" 
and text column "birthSign", and a class Parent which has "children = 
relationship('Child', 
collection_class=attribute_mapped_collection('birthOrder'))".  This gives 
Parent a dictionary "children", keyed by the birth order.  I could do a 
Parent query with 
.join(Child).filter(Child.birthOrder==1).filter(Child.birthSign=="Virgo") 
 to find all the firstborn Virgos.

I'd like to dynamically build up a similar query, given the text 
"children[1].birthOrder" (plus knowledge that the operator and value were 
"==" and "Virgo").  I can split that string on "." to get "children[1]", 
and seeing the brackets I can assume the attribute_mapped_collection and 
split again to get "children" and the value "1",   Using inspect(), I can 
find out that "children" is a RelationshipProperty of the Child class, But 
how can I inspect to find out what column ("birthOrder") was passed in the 
attribute_mapped_collection?  The RelationshipProperty I get from inspect() 
has "collection_class", but this holds a lambda function.  Is knowledge 
that "birthOrder" was what was used lost inside that, or is there some 
other way I can find it?

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] inspecting attribute_mapped_collection info of RelationshipProperty?

[mike bayer]

On 02/22/2017 08:56 PM, YKdvd wrote:

Let's say I have a model class Child, with an Integer column
"birthOrder" and text column "birthSign", and a class Parent which has
"children = relationship('Child',
collection_class=attribute_mapped_collection('birthOrder'))".  This
gives Parent a dictionary "children", keyed by the birth order.  I could
do a Parent query with
.join(Child).filter(Child.birthOrder==1).filter(Child.birthSign=="Virgo")  to
find all the firstborn Virgos.

I'd like to dynamically build up a similar query, given the text
"children[1].birthOrder" (plus knowledge that the operator and value
were "==" and "Virgo").  I can split that string on "." to get
"children[1]", and seeing the brackets I can assume the
attribute_mapped_collection and split again to get "children" and the
value "1",   Using inspect(), I can find out that "children" is a
RelationshipProperty of the Child class, But how can I inspect to find
out what column ("birthOrder") was passed in the
attribute_mapped_collection?  The RelationshipProperty I get from
inspect() has "collection_class", but this holds a lambda function.  Is
knowledge that "birthOrder" was what was used lost inside that, or is
there some other way I can find it?


i think it's just stuck in that lambda.   so you might need to make 
yourself a callable that also has a little memoization stuck on it. 
it's legal to tack .some_variable onto a function if that works.





--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Using CTEs inside union()

[mike bayer]

On 02/22/2017 10:17 AM, Michael Williamson wrote:

Using CTEs directly inside union() (or similar functions such as
intersect()) causes an error:


 query_1 = s.query(Record.id).cte()
 query_2 = s.query(Record.id).cte()

 select_from = union(query_1, query_2)


what does the above intend to mean?  I don't know what SQL one would 
expect from that.  A CTE is specifically a SELECT that goes on top of 
another SELECT as a sort of "aliased" subquery.   a UNION doesn't make 
any sense on the "outside" of that unless you can show me.


I can see this instead:

q1 = s.query(Record.id)
q2 = s.query(Record.id)

select_from = union(q1, q2).cte()

but that's not what the above is saying.







 print(s.query(func.count(1)).select_from(select_from).all())


sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax
error at or near "SELECT" LINE 2: SELECT record.id AS id
^
 [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
\nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
anon_1'] [parameters: {'count_2': 1}]

The way I got things working was to wrap the CTE in a SELECT rather than
using it directly:

select_from = union(
 select([query_1.c.id]).select_from(query_1),
 select([query_2.c.id]).select_from(query_2),
)
print(s.query(func.count(1)).select_from(select_from).all())

However, the original error didn't make it especially obvious to me
what was going on. Perhaps either the API could be changed to allow
using the CTE directly, or a clearer error could be emitted? Or am I
just thinking about this all wrong?

I also got an error when using subquery() instead of cte(), which was
fixed in the same way -- presumably this is the same issue?

Thanks

Michael



--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.