On 03/25/2016 09:48 AM, Brian Cherinka wrote:
Hi,
Is there perhaps a bug in the sqlalchemy function lower (func.lower)?
I'm trying to do a simple query that filters on a string keyword (the
label of a header_keyword). I want to bind the parameter so I can
easily update the label after the fact, and I want to use the func.lower
to ensure no funny stuff, and all lowercase. Without using the lower
everything works fine, and when I print the query, the correct variables
are inserted into the query. However, it seems when I use func.lower,
the bindparam no longer works and inserts NULL into the variable. Any
ideas what I'm doing wrong? Is there a proper way to write this such
that it works? Thanks.
*Without Lower*
|
In[59]:tmpq=session.query(Cube).join(HeaderValue,HeaderKeyword).filter(HeaderKeyword.label==bindparam('header_keyword.label','PLATE'))
In[55]:printtmpq
SELECT cube.*
FROM cube JOIN header_value ON cube.pk =header_value.cube_pk JOIN
header_keyword ON header_keyword.pk =header_value.header_keyword_pk
WHERE header_keyword.label =%(header_keyword.label)s
In[56]:printtmpq.statement.compile(compile_kwargs={'literal_binds':True})
SELECT cube.*
FROM cube JOIN header_value ON cube.pk =header_value.cube_pk JOIN
header_keyword ON header_keyword.pk =header_value.header_keyword_pk
WHERE header_keyword.label ='PLATE'
|
*With Lower*
|
In[59]:tmpq=session.query(Cube).join(HeaderValue,HeaderKeyword).filter(func.lower(HeaderKeyword.label)==bindparam('header_keyword.label',func.lower('PLATE')))
the above is not valid:
bindparam(somename, func.lower(something)) # <-- not valid
the argument to bindparam must be a Python literal value, not a SQL
expression. Bound parameters are interpreted by the database driver
and the client API of the database itself and cannot evaluate SQL
expressions.
In this case you just need to put lower on the outside:
func.lower(bindparam(somename))
In[60]:printtmpq
SELECT cube.*
FROM cube JOIN header_value ON cube.pk =header_value.cube_pk JOIN
header_keyword ON header_keyword.pk =header_value.header_keyword_pk
WHERE lower(header_keyword.label)=%(header_keyword.label)s
In[61]:printtmpq.statement.compile(compile_kwargs={'literal_binds':True})
SELECT cube.*
FROM cube JOIN header_value ON cube.pk =header_value.cube_pk JOIN
header_keyword ON header_keyword.pk =header_value.header_keyword_pk
WHERE lower(header_keyword.label)=NULL
|
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit 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+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.
