How to make use of bindparam() in a custom Compiled expression?

[Юрий Пайков]

I based my code on the Michaeil Bayer's answer to this Stack Overflow question . I extended it a little so it takes into account NULLs and ARRAY for Postgresql.

class values(FromClause):
    named_with_column = True

    def __init__(self, columns, *args, **kw):
        self._column_args = columns
        self.list = args
        self.alias_name = self.name = kw.pop('alias_name', None)

    def _populate_column_collection(self):
        # self._columns.update((col.name, col) for col in self._column_args)
        for c in self._column_args:
        c._make_proxy(self, c.name)


@compiles(values)
def compile_values(element, compiler, asfrom=False, **kw):
    columns = element.columns
    v = "VALUES %s" % ", ".join(
    "(%s)" % ", ".join(
        ((compiler.visit_array(elem)+'::'+str(column.type)) if isinstance(column.type, ARRAY) else
         compiler.render_literal_value(elem, column.type))
        if elem is not None else compiler.render_literal_value(elem, NULLTYPE)
        for elem, column in zip(tup, columns))
    for tup in element.list
    )
    if asfrom:
        if element.alias_name:
            v = "(%s) AS %s (%s)" % (v, element.alias_name, (", ".join(c.name for c in element.columns)))
        else:
            v = "(%s)" % v
    return v

Everything worked fine until it turned out I couldn't insert values with "%"-sign to this VALUES clause - they get inlined in a resulting statement and this seems to cause binding problems

I guess if instead of render_literal_value() we used bindparam() we could avoid such an error. But Everything under @compiles should return plain text, am I right? 

How could I amend this to get a query which contains bind parfms along with it?

[Mike Bayer]

On 11/19/2015 05:29 AM, Юрий Пайков wrote:
> I based my code on the Michaeil Bayer's answer to this Stack Overflow
> question

> <http://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy>.

> I extended it a little so it takes into account NULLs and ARRAY for
> Postgresql.
>
> |classvalues(FromClause):named_with_column
> =Truedef__init__(self,columns,*args,**kw):self._column_args =columns
> self.list =args self.alias_name =self.name

> =kw.pop('alias_name',None)def_populate_column_collection(self):#

> self._columns.update((col.name, col) for col in self._column_args)forc
> inself._column_args:c._make_proxy(self,c.name)@compiles(values)defcompile_values(element,compiler,asfrom=False,**kw):columns

> =element.columns v ="VALUES %s"%", ".join("(%s)"%",

> ".join(((compiler.visit_array(elem)+'::'+str(column.type))ifisinstance(column.type,ARRAY)elsecompiler.render_literal_value(elem,column.type))ifelem
> isnotNoneelsecompiler.render_literal_value(elem,NULLTYPE)forelem,column
> inzip(tup,columns))fortup inelement.list

> )ifasfrom:ifelement.alias_name:v ="(%s) AS %s

> (%s)"%(v,element.alias_name,(", ".join(c.name forc
> inelement.columns)))else:v ="(%s)"%v returnv|
>
> Everything worked fine until it turned out I couldn't insert values with
> "%"-sign to this VALUES clause - they get inlined in a resulting
> statement and this seems to cause binding problems
>
> I guess if instead of |render_literal_value()| we used |bindparam()| we
> could avoid such an error. But Everything under |@compiles| should
> return plain text, am I right?
>
>
> How could I amend this to get a query which contains bind parfms along
> with it?

if the issue is percent signs, this is a driver limitation and you need
to double them up as %%, so that they aren't confused as format/pyformat
indicators. psycopg2 uses format/pyformat style for bound parameters.



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

[Юрий Пайков]

But if I used bound parameters in a query - wouldn't it be a solution? 

I mean, if a value for a bindparam is not inlined in a query and rather carried along - there is no need to quote it for the driver, is there? Like done for any other literal comparison, for example.

The driver will insert it as is from some object/field  and won't parse the resulting query for params any further...

четверг, 19 ноября 2015 г., 19:43:39 UTC+5 пользователь Michael Bayer написал:

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Mike Bayer]

On 11/19/2015 10:07 AM, Юрий Пайков wrote:
> But if I used bound parameters in a query - wouldn't it be a solution?
> I mean, if a value for a bindparam is not inlined in a query and rather
> carried along - there is no need to quote it for the driver, is there?
> Like done for any other literal comparison, for example.
> The driver will insert it as is from some object/field and won't parse
> the resulting query for params any further...

you can try a bindparam(), sure. the original question, everything
under @compiles should return plain text, yes, those functions all need
to return text that is appended to the SQL statement.

>
> четверг, 19 ноября 2015 г., 19:43:39 UTC+5 пользователь Michael Bayer
> написал:
>
>
>
> On 11/19/2015 05:29 AM, Юрий Пайков wrote:
> > I based my code on the Michaeil Bayer's answer to this Stack Overflow
> > question
> >
> <http://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy
> <http://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy>>.
>
> > I extended it a little so it takes into account NULLs and ARRAY for
> > Postgresql.
> >
> > |classvalues(FromClause):named_with_column
> > =Truedef__init__(self,columns,*args,**kw):self._column_args =columns

> > self.list =args self.alias_name =self.name <http://self.name>
> > =kw.pop('alias_name',None)def_populate_column_collection(self):#
> > self._columns.update((col.name <http://col.name>, col) for col in
> self._column_args)forc
> > inself._column_args:c._make_proxy(self,c.name
> <http://c.name>)@compiles(values)defcompile_values(element,compiler,asfrom=False,**kw):columns

>
> > =element.columns v ="VALUES %s"%", ".join("(%s)"%",
> >
> ".join(((compiler.visit_array(elem)+'::'+str(column.type))ifisinstance(column.type,ARRAY)elsecompiler.render_literal_value(elem,column.type))ifelem
>
> >
> isnotNoneelsecompiler.render_literal_value(elem,NULLTYPE)forelem,column
> > inzip(tup,columns))fortup inelement.list
> > )ifasfrom:ifelement.alias_name:v ="(%s) AS %s

> > (%s)"%(v,element.alias_name,(", ".join(c.name <http://c.name> forc

> > inelement.columns)))else:v ="(%s)"%v returnv|
> >
> > Everything worked fine until it turned out I couldn't insert
> values with
> > "%"-sign to this VALUES clause - they get inlined in a resulting
> > statement and this seems to cause binding problems
> >
> > I guess if instead of |render_literal_value()| we used
> |bindparam()| we
> > could avoid such an error. But Everything under |@compiles| should
> > return plain text, am I right?
> >
> >
> > How could I amend this to get a query which contains bind parfms
> along
> > with it?
>
> if the issue is percent signs, this is a driver limitation and you need
> to double them up as %%, so that they aren't confused as
> format/pyformat
> indicators. psycopg2 uses format/pyformat style for bound parameters.
>
>
>
> >
> > --
> > 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 <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

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

> <mailto:sqlalchemy+...@googlegroups.com>.

[Юрий Пайков]

The problem is  - I can't wrap my head round using it... Could you direct me to same compiled clause which uses bindparams?
Or perhaps maybe to some documentation on SQLA internals, because it is quite difficult to track the whole query compilation process for me
четверг, 19 ноября 2015 г., 21:14:59 UTC+5 пользователь Michael Bayer написал:

[Mike Bayer]

well what is the ARRAY data you're passing into it? can you provide a
complete example?

[Юрий Пайков]

Oh, man I got it. Cannot imagine what kind of an insight hit me when I figured it out!

Here is a test case and final version of VALUES clause 

Btw, why there is no _type_api mapping for a list() python type (https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/sqltypes.py#L1960) , at least as for version 1.0.9, which I use? We have to explicitly use `array` literal everywhere insted of just specifying python lists like [1,2,3]

пятница, 20 ноября 2015 г., 0:30:15 UTC+5 пользователь Michael Bayer написал:

well what is the ARRAY data you're passing into it?  can you provide a
complete example?

On 11/19/2015 02:04 PM, Юрий Пайков wrote:
> The problem is  - I can't wrap my head round using it... Could you
> direct me to same compiled clause which uses bindparams?
> Or perhaps maybe to some documentation on SQLA internals, because it is
> quite difficult to track the whole query compilation process for me
> четверг, 19 ноября 2015 г., 21:14:59 UTC+5 пользователь Michael Bayer
> написал:
>
>
>
>     you can try a bindparam(), sure.    the original question, everything
>     under @compiles should return plain text, yes, those functions all need
>     to return text that is appended to the SQL statement.
>
> --
> 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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Mike Bayer]

On 11/19/2015 05:23 PM, Юрий Пайков wrote:
> Oh, man I got it. Cannot imagine what kind of an insight hit me when I
> figured it out!

> Here <https://gist.github.com/ojomio/d449abffe588a2abd32b> is a test

> case and final version of VALUES clause
>
> Btw, why there is no _type_api mapping for a list() python type
> (https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/sqltypes.py#L1960)
> , at least as for version 1.0.9, which I use? We have to explicitly use
> `array` literal everywhere insted of just specifying python lists like
> [1,2,3]

ARRAY isn't a SQL standard type so there's no straightforward way to
have the Core pull in a PG specific type when there is otherwise no
ARRAY type present.

that said, in most cases when you're dealing with ARRAY there should
already be an ARRAY type in play somewhere (e.g. the column you're
comparing to, or whatever), so that would handle your list values in
place. The _type_map is just a convenience. If you're doing
bindparam(), say bindparam('foo', ['some', 'list'], type_=ARRAY(String)).

>
> пятница, 20 ноября 2015 г., 0:30:15 UTC+5 пользователь Michael Bayer
> написал:
>
> well what is the ARRAY data you're passing into it? can you provide a
> complete example?
>
> On 11/19/2015 02:04 PM, Юрий Пайков wrote:
> > The problem is - I can't wrap my head round using it... Could you
> > direct me to same compiled clause which uses bindparams?
> > Or perhaps maybe to some documentation on SQLA internals, because
> it is
> > quite difficult to track the whole query compilation process for me
> > четверг, 19 ноября 2015 г., 21:14:59 UTC+5 пользователь Michael Bayer
> > написал:
> >
> >
> >
> > you can try a bindparam(), sure. the original question,
> everything
> > under @compiles should return plain text, yes, those functions
> all need
> > to return text that is appended to the SQL statement.
> >
> > --
> > 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 <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

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

> <mailto:sqlalchemy+...@googlegroups.com>.