On Wed, Mar 20, 2019 at 7:59 AM mdob <mike.dobrzan...@gmail.com> 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+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.
