LIKE operator and double percent signs

[Jon Nelson]

I've been wondering something about sqlalchemy - let's say I have a
text column "foo". Being able to do foo.startswith(some_value),
foo.endswith, foo.like and so on is really nice. However, I've noticed
that the SQL that is emitted contains two percent signs. However, I
thought only one was necessary. Why is sqlalchemy emitting two?

--
Jon

[Michael Bayer]

% is significant in DBAPIs like postgresql and mysqldb where pyformat and format: %(foo)s and %s, are allowed, so % must be doubled.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Jon Nelson]

On Fri, Feb 25, 2011 at 9:15 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> % is significant in DBAPIs like postgresql and mysqldb where pyformat and format:  %(foo)s and %s, are allowed, so % must be doubled.

So does psycopg2 send '%' or '%%' ? It seems to me that if the
strings are held as atoms (individual arguments rather than a single,
concatenated string) then '%%' is unnecessary.

[Michael Bayer]

On Feb 25, 2011, at 10:20 AM, Jon Nelson wrote:

> On Fri, Feb 25, 2011 at 9:15 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> % is significant in DBAPIs like postgresql and mysqldb where pyformat and format: %(foo)s and %s, are allowed, so % must be doubled.
>
> So does psycopg2 send '%' or '%%' ?

psycopg2 is the DBAPI here, % is significant so the client of DBAPI must escape % that is not part of a string format.

> It seems to me that if the
> strings are held as atoms (individual arguments rather than a single,

sorry, I don't know what you mean by "individual arguments", do you mean bind params, i.e. :p1 + :p2 + :p3 ?

> concatenated string) then '%%' is unnecessary.

The compiler most certainly needs to escape literal-rendered % signs across the board on those DBAPIs where the symbol has other meanings, since a user might use literal_column() with a '%' sign in it, and would like this symbol to behave the same way on all backends.

>
>> On Feb 25, 2011, at 8:53 AM, Jon Nelson wrote:
>>
>>> I've been wondering something about sqlalchemy - let's say I have a
>>> text column "foo". Being able to do foo.startswith(some_value),
>>> foo.endswith, foo.like and so on is really nice. However, I've noticed
>>> that the SQL that is emitted contains two percent signs. However, I
>>> thought only one was necessary. Why is sqlalchemy emitting two?
>
>
> --
> Jon
>

[Jonathan Rogers]

On Friday, February 25, 2011 at 10:27:34 AM UTC-5, Michael Bayer wrote:

On Feb 25, 2011, at 10:20 AM, Jon Nelson wrote:

> On Fri, Feb 25, 2011 at 9:15 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> % is significant in DBAPIs like postgresql and mysqldb where pyformat and format:  %(foo)s and %s, are allowed, so % must be doubled.
>
> So does psycopg2 send '%' or '%%' ?  

psycopg2 is the DBAPI here, % is significant so the client of DBAPI must escape % that is not part of a string format.

> It seems to me that if the
> strings are held as atoms (individual arguments rather than a single,

sorry, I don't know what you mean by "individual arguments", do you mean bind params, i.e. :p1 + :p2 + :p3  ?    

> concatenated string) then '%%' is unnecessary.

The compiler most certainly needs to escape literal-rendered % signs across the board on those DBAPIs where the symbol has other meanings, since a user might use literal_column() with a '%' sign in it, and would like this symbol to behave the same way on all backends.

I can understand why a '%' needs to be doubled in a typical statement such as a select. However, I also see a '%' doubled inside the definition of a CheckConstraint when I compile and print a Table containing the CheckConstraint object for the purpose of generating a DDL script. The constraint expression should be left alone. In Postgres, '%%' means the same as '%' when used with LIKE, but I'd still like to avoid the unnecessary doubling.

[Mike Bayer]

the escaping is to get around the DBAPI itself (e.g. psycopg2) which
wishes to apply pyformat substitution to the string.


example:

from sqlalchemy import *

m = MetaData()

t = Table('t', m,
Column('x', String(50)),
CheckConstraint("x != 'foo%'")
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.create_all(e)


output:

2016-06-15 13:33:41,255 INFO sqlalchemy.engine.base.Engine select version()
2016-06-15 13:33:41,256 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,257 INFO sqlalchemy.engine.base.Engine select
current_schema()
2016-06-15 13:33:41,257 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,258 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2016-06-15 13:33:41,259 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,259 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2016-06-15 13:33:41,259 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,260 INFO sqlalchemy.engine.base.Engine show
standard_conforming_strings
2016-06-15 13:33:41,260 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,261 INFO sqlalchemy.engine.base.Engine select
relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2016-06-15 13:33:41,262 INFO sqlalchemy.engine.base.Engine {'name': u't'}
2016-06-15 13:33:41,263 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t (
x VARCHAR(50),
CHECK (x != 'foo%%')
)


2016-06-15 13:33:41,263 INFO sqlalchemy.engine.base.Engine {}
2016-06-15 13:33:41,265 INFO sqlalchemy.engine.base.Engine COMMIT

end result:

[classic@photon2 sqlalchemy]$ psql -U scott test
psql (9.5.3)
Type "help" for help.

test=# \d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target |
Description
--------+-----------------------+-----------+----------+--------------+-------------
x | character varying(50) | | extended | |
Check constraints:
"t_x_check" CHECK (x::text <> 'foo%'::text)

test=#


only one % sign is present.

>
> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

also note, using the non-DBAPI level dialect (in this case PGDialect),
you don't get the percent signs:

from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateTable
print CreateTable(t).compile(dialect=postgresql.base.PGDialect())

CREATE TABLE t (
x VARCHAR(50),

CHECK (x != 'foo%')
)


so I'd use that for DDL rendering as scripts.

[Jonathan Rogers]

That's exactly what I needed to know. I had been using
sqlalchemy.dialects.postgresql.dialect(). Thanks for the swift reply as
usual.

--
Jonathan Rogers