Thanks Mike, though the question is valid - why does regex in SQLAlchemy
allow for discovering parameter token inside quotes? Have you seen a
legitimate case for that?
On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote:
>
> On Wed, Mar 20, 2019 at 7:59 AM mdob <mike.do...@gmail.com <javascript:>>
> wrote:
> >
> > Hi,
> >
> >
> > Is it correct behavior that parameter placeholders in quotes e.g. SELECT
> * FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid
> parameters?
> >
> >
> > from sqlalchemy.sql import text
> > from sqlalchemy.dialects import sqlite
> > from sqlalchemy import create_engine
> > engine = create_engine('sqlite:////home/mike/Chinook.sqlite', echo=True)
> >
> >
> > s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
> > q = text(s)
> >
> > c = q.compile()
> > print c.params
> >
> > {'pattern': None}
> >
> >
> > If parameter is provided
> > engine.execute(q, {'pattern': 'foo'})
> >
> > engine echo:
> >
> > 2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * FROM
> Artist WHERE Name LIKE '%?'
> > 2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)
> >
> > and error is raised
> >
> >
> > ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of
> bindings supplied. The current statement uses 0, and there are 1 supplied.
> [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)]
> (Background on this error at: http://sqlalche.me/e/f405)
> >
> >
> > When executed without parameters
> >
> > engine.execute(q, {'pattern': 'foo'})
> >
> > different error is raised
> >
> >
> > StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required
> for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE Name LIKE
> "%?%"'] (Background on this error at: http://sqlalche.me/e/cd3x)
> >
> > It feels to me like finding parameters in
> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
>
> should exclude placeholders inside quotes.
>
> This is not how bound parameters work in relational databases. Bound
> parameters are not Python substitution strings, they are more often
> than not processed by the server, or at least have to behave as though
> they were. This means you cannot assume a parameter is substituted
> inside of a value as you are doing here. The correct syntax for what
> you are trying to do is:
>
> s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%'
>
> that is, the concatenation you are doing is explicit in SQL.
>
>
> >
> > Michal
> >
> > --
> > 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 <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
