Re: [sqlalchemy] LIKE operator and double percent signs
On 06/15/2016 01:37 PM, Mike Bayer wrote: > > > On 06/15/2016 01:34 PM, Mike Bayer wrote: >> >> >> On 06/15/2016 12:40 PM, Jonathan Rogers wrote: >>> 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 >>> > 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. >> >> the escaping is to get around the DBAPI itself (e.g. psycopg2) which >> wishes to apply pyformat substitution to the string. > > 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. 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 -- 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.
Re: [sqlalchemy] LIKE operator and double percent signs
On 06/15/2016 01:34 PM, Mike Bayer wrote:
On 06/15/2016 12:40 PM, Jonathan Rogers wrote:
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
> 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.
the escaping is to get around the DBAPI itself (e.g. psycopg2) which
wishes to apply pyformat substitution to the string.
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.
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+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.
--
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.co
Re: [sqlalchemy] LIKE operator and double percent signs
On 06/15/2016 12:40 PM, Jonathan Rogers wrote:
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
> 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.
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+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.
--
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.
Re: [sqlalchemy] LIKE operator and double percent signs
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 > 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. -- 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.
Re: [sqlalchemy] LIKE operator and double percent signs
On Feb 25, 2011, at 10:20 AM, Jon Nelson wrote: > On Fri, Feb 25, 2011 at 9:15 AM, Michael Bayer > 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 > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] LIKE operator and double percent signs
On Fri, Feb 25, 2011 at 9:15 AM, Michael Bayer 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. > 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 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] LIKE operator and double percent signs
% is significant in DBAPIs like postgresql and mysqldb where pyformat and format: %(foo)s and %s, are allowed, so % must be doubled. 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 > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] LIKE operator and double percent signs
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 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
