raw_sql

[stsouko]

select(x for x in A if raw_sql("x.s LIKE 'some'"))

works fine, but

rs = "x.s LIKE %s'" % 'some'
select(x for x in A if raw_sql(rs))

or

select(x for x in A if raw_sql("x.s LIKE %s'" % 'some'))

don't

[kozlovsky]

Currently raw_sql argument should be constant string. But this string can refer to external variables or expressions using the $ syntax. You can write:

param = 'aaa'
select(x for x in A if raw_sql("x.s LIKE $param"))

or even

select(x for x in A if raw_sql("x.s LIKE $(request.args['arg1'])"))

Each $x variable or $(a+b) expression is translated into query parameter. This way is much better then direct substitution of raw values into SQL command for two reasons: (1) SQL command can be cached and (2) Query parameters API totally excludes the possibility of SQL injection.

[kozlovsky]

We completely rewrote raw_sql() implementation. Now it is possible to do the following:

1. Use $parameters inside a raw_sql string:

s = 'J%'
select(p for p in Person if raw_sql('p.name LIKE $s'))

2. Inline complex Python expressions into raw_sql fragments:

import datetime
select(t for t in Task if raw_sql("t.due_date < date($datetime.date.today(), '+1 day')"))

3. Use dynamically generated strings as a parameter to raw_sql:

x = 100
s = '"p"."price" > $x'
select(p for p in Product if raw_sql(s))

or

x = 100
s = '"p"."price" > $x'
raw = raw_sql(s)
select(p for p in Product if raw)

4. raw_sql as a query result:

select(raw_sql('UPPER(p.name)') for p in Person)

See tests for more examples: https://github.com/ponyorm/pony/blob/orm/pony/orm/tests/test_raw_sql.py