On Fri, Mar 22, 2019 at 1:59 PM Victor Olex
<victor.o...@vtenterprise.com> wrote:
>
> 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}
Hi Victor -
yes, colons may appear in string literals, and on the PostgreSQL
backend they are also part of PG's shorthand CAST operator. so there
is a need to be able to render a colon character in text(). This is
achieved by escaping the colon with a backslash, as documented at
https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=text#sqlalchemy.sql.expression.text
which illustrates a colon inside of a literal string. Hope this
helps.
>
>
> 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> 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.
>> > 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+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.
