On 04/14/2017 03:49 AM, Lele Gaifax wrote:
mike bayer <mike...@zzzcomputing.com> writes:
However, we can improve upon the situation by adding a CAST, which seems to
send psycopg2 a little more information:
row = s.query(
cast(
func.array_agg(tuple_(A.x, A.y)), ARRAY(Unicode)
)
).scalar()
print row
For one new application I'm writing I chose the "new" asyncpg[1] driver.
The major problem I hit was related to this CASTing need ([2] and [3]): due to
the way asyncpg talks to the database, in some cases it cannot guess the right
format because on the PG side things may be ambiguous (e.g. when an operator
is overloaded and works on different types of data, like the @> operator I
used in [3]).
To solve that I wrote a very simple custom PGDialect+PGCompiler duo to handle
a new "paramstyle", basically translating the following SA construct::
oneyearago = date.today() - timedelta(days=365)
q = sa.select([users.c.id]) \
.where(users.c.name == 'test') \
.where(users.c.validity.contains(oneyearago))
into this SQL statement::
SELECT auth.users.id
FROM auth.users
WHERE auth.users.name = $1::VARCHAR(64) AND (auth.users.validity @> $2::DATE)
I'd note that the above seems to *not* be casting "users.id" in the
SELECT. The problem we are having with the array/json/tuple etc. is
that the typing information is not in the result description, so that's
really the only place a CAST is sometimes needed when dealing with
psycopg2.
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.
Additionally, the Python DBAPI already supports a method for dealing
with the general problem of "what types should be applied to bound
parameters?", it's called setinputsizes:
https://www.python.org/dev/peps/pep-0249/#id26 . SQLAlchemy has
generalized support for this for DBAPIs that require it, which currently
includes cx_Oracle.
but that's pep-249, which asyncpg unfortunately simply disregards.
So, what you have above is very easy to accomplish with a simple
@compiles recipe that merely adds CAST to the rendering of bindparam():
from sqlalchemy import *
from sqlalchemy.orm import *
from datetime import date, timedelta
from sqlalchemy.sql.elements import BindParameter
from sqlalchemy.ext.compiler import compiles
@compiles(BindParameter)
def inject_cast(element, compiler, _already_wrapped=False, **kw):
if _already_wrapped:
return compiler.visit_bindparam(element, **kw)
else:
return compiler.process(
cast(element, element.type), _already_wrapped=True, **kw)
m = MetaData()
users = Table(
'users', m,
Column('id', Integer, primary_key=True),
Column('name', String(64)),
Column('validity', DateTime)
)
oneyearago = date.today() - timedelta(days=365)
q = select([users.c.id]) \
.where(users.c.name == 'test') \
.where(users.c.validity.contains(oneyearago))
print q
SELECT users.id
FROM users
WHERE users.name = CAST(:name_1 AS VARCHAR(64)) AND (users.validity LIKE
'%' + CAST(:validity_1 AS DATE) || '%')
Since it eventually boils down to overriding a single "private" method
(_apply_numbered_params), I wonder if it would be a welcome addition to
standard SA, maybe allowing to set "paramstyle" to an user's arbitrary
callable.
What do you think?
Thank you,
ciao, lele.
[1] https://github.com/MagicStack/asyncpg
[2] https://github.com/CanopyTax/asyncpgsa/issues/12
[3] https://github.com/MagicStack/asyncpg/issues/32
--
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.
