raw_sql in function scope can't resolve parameters

[tonycpsu]

Test code:

from pony.orm import *

db = Database('sqlite', ':memory:')

class Foo(db.Entity):
    foo_id = PrimaryKey(int, auto=True)
    name = Required(str)

    def __repr__(self):
        return "<Foo: %s>" %(self.name)


db.generate_mapping(create_tables=True)

def foo():
    with db_session:

        f = Foo(name="aaa")
        f = Foo(name="aab")
        f = Foo(name="abb")
        f = Foo(name="abc")

        search = "ab%"

        results = select(
            f for f in Foo
            if raw_sql("name LIKE $(search)")
        )

        print list(results)

foo()

Results in:

pony.orm.core.ExprEvalError: (search) raises NameError: name 'search' is not defined

Even though search is in scope. Taking the function body out of the foo() function and executing it in the main body of the test script works fine.

[kozlovsky]

Thanks for reporting!

In order to fix this issue as well as several other issues, 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

[tonycpsu]

Looks great to me so far -- thanks!