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
<mik...@zzzcomputing.com <javascript:>> 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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto: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.
