[sqlalchemy] Re: About CASTing query parameters

[Lele Gaifax]

mike bayer  writes:

> yeah IMO this is a bug in asyncpg but of course your custom dialect can work
> around that.

Ok, thank you.
ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] Re: About CASTing query parameters

[mike bayer]

yeah IMO this is a bug in asyncpg but of course your custom dialect can 
work around that.




On 04/14/2017 04:31 PM, Lele Gaifax wrote:

Lele Gaifax  writes:


I bet because it does want to fiddle with the SQL statement parsing, or with
the formatting of parameter's values.


ops: ... it does *not* want...

ciao, lele.



--
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: About CASTing query parameters

[Lele Gaifax]

Lele Gaifax  writes:

> I bet because it does want to fiddle with the SQL statement parsing, or with
> the formatting of parameter's values.

ops: ... it does *not* want...

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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: About CASTing query parameters

[Lele Gaifax]

Mike Bayer  writes:

> Right.  So why can't asyncpg do that.

I bet because it does want to fiddle with the SQL statement parsing, or with
the formatting of parameter's values.

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] Re: About CASTing query parameters

[Mike Bayer]

Right.  So why can't asyncpg do that.

On Apr 14, 2017 12:45 PM, "Lele Gaifax"  wrote:

> Mike Bayer  writes:
>
> > I know that, however (not at my computer at the moment) how does this
> case
> > work w psycopg2 ?
>
> I think that psycopg2 does that on its own, that is, using a debug build, I
> can see that the effective statement it sends to the engine is
>
>   SELECT users.id
>   FROM users
>   WHERE users.name = 'myself' AND (users.active @> '2017-04-14'::date)
>
> ciao, lele.
> --
> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
> l...@metapensiero.it  | -- Fortunato Depero, 1929.
>
> --
> 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.

[sqlalchemy] Re: About CASTing query parameters

[Lele Gaifax]

Mike Bayer  writes:

> I know that, however (not at my computer at the moment) how does this case
> work w psycopg2 ?

I think that psycopg2 does that on its own, that is, using a debug build, I
can see that the effective statement it sends to the engine is

  SELECT users.id 
  FROM users 
  WHERE users.name = 'myself' AND (users.active @> '2017-04-14'::date)

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] Re: About CASTing query parameters

[Mike Bayer]

On Apr 14, 2017 11:40 AM, "Lele Gaifax"  wrote:

Mike Bayer  writes:

> Can you show me the model and some sample data ?  Is this the JSONB
> datatype ?

Sure, here it is:

  import datetime

  from sqlalchemy import create_engine, select, Table, Column, Integer,
UnicodeText, MetaData
  import sqlalchemy.dialects.postgresql as sapg

  metadata = MetaData()
  users = Table('users', metadata,
  Column('id', Integer(), primary_key=True),
  Column('name', UnicodeText()),
  Column('active', sapg.DATERANGE()),
  )

  e = create_engine('postgresql://localhost/test')

  metadata.create_all(e)

  i = users.insert().values(name='myself', active='[2016-10-21,2020-10-22)')

  e.execute(i)

  q = select([users.c.id]) \
  .where(users.c.name == 'myself') \
  .where(users.c.active.contains(datetime.date.today()))

  print(e.execute(q).fetchall())

This works as is with psycopg2 (maybe it was not clear that I was suggesting
an enhancement, not a fix :-).


I know that, however (not at my computer at the moment) how does this case
work w psycopg2 ?




ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

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

[sqlalchemy] Re: About CASTing query parameters

[Lele Gaifax]

Mike Bayer  writes:

> Can you show me the model and some sample data ?  Is this the JSONB
> datatype ?

Sure, here it is:

  import datetime

  from sqlalchemy import create_engine, select, Table, Column, Integer, 
UnicodeText, MetaData
  import sqlalchemy.dialects.postgresql as sapg

  metadata = MetaData()
  users = Table('users', metadata,
  Column('id', Integer(), primary_key=True),
  Column('name', UnicodeText()),
  Column('active', sapg.DATERANGE()),
  )

  e = create_engine('postgresql://localhost/test')

  metadata.create_all(e)

  i = users.insert().values(name='myself', active='[2016-10-21,2020-10-22)')

  e.execute(i)

  q = select([users.c.id]) \
  .where(users.c.name == 'myself') \
  .where(users.c.active.contains(datetime.date.today()))

  print(e.execute(q).fetchall())

This works as is with psycopg2 (maybe it was not clear that I was suggesting
an enhancement, not a fix :-).

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] Re: About CASTing query parameters

[Mike Bayer]

Can you show me the model and some sample data ?  Is this the JSONB
datatype ?

On Apr 14, 2017 11:02 AM, "Lele Gaifax"  wrote:

> mike bayer  writes:
>
> > I'm not sure what asyncpgs' issue is with input types, there should not
> be a
> > reason to parse the SQL statement and look at operators, you have the
> Python
> > type of object coming in, you use that to determine the general behavior
> to
> > take with basic types like strings and dates.
>
> asyncpg does not parse the SQL statement, and it tries hard to not have to
> parse incoming data from the database, even, leaving all that to the PG
> engine.
>
> In my example, the column contains a daterange value, and the query I tried
> used the @> operator (what the SA .contains() on such type renders to):
> but as
> said in https://github.com/MagicStack/asyncpg/issues/32#
> issuecomment-254208760
> PG has not enough data (you can read that as "has a bug" if you prefer) to
> guess the right type, as the @> implements both "daterange contains scalar
> date" and "daterange is a superset of another daterange". Explicitly
> casting
> the input value solves the ambiguity.
>
> > So, what you have above is very easy to accomplish with a simple
> @compiles recipe that merely
> > adds CAST to the rendering of bindparam():
>
> Yes, I know, but since I need to tweak the representation of positional
> parameters (that is, $1 instead of :1), I had to touch the BIND_TEMPLATES
> content. That's why I thought a more generic approach of allowing an
> arbitrary, user controlled way to render the placeholders could be better.
>
> ciao, lele.
> --
> nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
> real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
> l...@metapensiero.it  | -- Fortunato Depero, 1929.
>
> --
> 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.

[sqlalchemy] Re: About CASTing query parameters

[Lele Gaifax]

mike bayer  writes:

> I'm not sure what asyncpgs' issue is with input types, there should not be a
> reason to parse the SQL statement and look at operators, you have the Python
> type of object coming in, you use that to determine the general behavior to
> take with basic types like strings and dates.

asyncpg does not parse the SQL statement, and it tries hard to not have to
parse incoming data from the database, even, leaving all that to the PG
engine.

In my example, the column contains a daterange value, and the query I tried
used the @> operator (what the SA .contains() on such type renders to): but as
said in https://github.com/MagicStack/asyncpg/issues/32#issuecomment-254208760
PG has not enough data (you can read that as "has a bug" if you prefer) to
guess the right type, as the @> implements both "daterange contains scalar
date" and "daterange is a superset of another daterange". Explicitly casting
the input value solves the ambiguity.

> So, what you have above is very easy to accomplish with a simple @compiles 
> recipe that merely
> adds CAST to the rendering of bindparam():

Yes, I know, but since I need to tweak the representation of positional
parameters (that is, $1 instead of :1), I had to touch the BIND_TEMPLATES
content. That's why I thought a more generic approach of allowing an
arbitrary, user controlled way to render the placeholders could be better.

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

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