It will however produce parameter for certain string literals in a valid
query. It may be a minute detail, but since you have that regex for
detecting parameters it might well avoid that (unless there is a case).
>>> from sqlalchemy.sql import text
>>> q = text("SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'")
>>> print(q.compile())
SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'
>>> print(q.compile().params)
{'param1': None, 'A': None}
On Thursday, March 21, 2019 at 4:11:45 PM UTC-4, Mike Bayer wrote:
>
> On Thu, Mar 21, 2019 at 3:33 PM Victor Olex
> <victo...@vtenterprise.com <javascript:>> wrote:
> >
> > 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?
>
> the regex in SQLAlchemy is not a SQL parser, it's just putting bound
> parameters where the user typed them. It does not detect invalid
> SQL, that's the database's job.
>
> >
> > 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> 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.
> >> > To post to this group, send email to sqlal...@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+...@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.
