Using raw SQL

Pony allows using raw SQL in your queries. There are two options on how you can use raw SQL:

1. Use the raw_sql() function in order to write only a part of a generator or lambda query using raw SQL.

2. Write a complete SQL query using the Entity.select_by_sql() or Entity.get_by_sql() methods.

Using the raw_sql() function

Let’s explore examples of using the raw_sql() function. Here is the schema and initial data that we’ll use for our examples:

from datetime import date
from pony.orm import *
db = Database('sqlite', ':memory:')
class Person(db.Entity):
    id = PrimaryKey(int)
    name = Required(str)
    age = Required(int)
    dob = Required(date)
db.generate_mapping(create_tables=True)
with db_session:
    Person(id=1, name='John', age=30, dob=date(1986, 1, 1))
    Person(id=2, name='Mike', age=32, dob=date(1984, 5, 20))
    Person(id=3, name='Mary', age=20, dob=date(1996, 2, 15))

The raw_sql() result can be treated as a logical expression:

select(p for p in Person if raw_sql('abs("p"."age") > 25'))

The raw_sql() result can be used for a comparison:

q = Person.select(lambda x: raw_sql('abs("x"."age")') > 25)
print(q.get_sql())
SELECT "x"."id", "x"."name", "x"."age", "x"."dob"
FROM "Person" "x"
WHERE abs("x"."age") > 25

Also, in the example above we use raw_sql() in a lambda query and print out the resulting SQL. As you can see the raw SQL part becomes a part of the whole query.

The raw_sql() can accept $parameters:

x = 25
select(p for p in Person if raw_sql('abs("p"."age") > $x'))

You can change the content of the raw_sql() function dynamically and still use parameters inside:

x = 1
s = 'p.id > $x'
select(p for p in Person if raw_sql(s))

Another way of using dynamic raw SQL content:

x = 1
cond = raw_sql('p.id > $x')
select(p for p in Person if cond)

You can use various types inside the raw SQL query:

x = date(1990, 1, 1)
select(p for p in Person if raw_sql('p.dob < $x'))

Parameters inside the raw SQL part can be combined:

x = 10
y = 15
select(p for p in Person if raw_sql('p.age > $(x + y)'))

You can even call Python functions inside:

select(p for p in Person if raw_sql('p.dob < $date.today()'))

The raw_sql() function can be used not only in the condition part, but also in the part which returns the result of the query:

names = select(raw_sql('UPPER(p.name)') for p in Person)[:]
print(names)
['JOHN', 'MIKE', 'MARY']

But when you return data using the raw_sql() function, you might need to specify the type of the result, because Pony has no idea on what the result type is:

dates = select(raw_sql('(p.dob)') for p in Person)[:]
print(dates)
['1985-01-01', '1983-05-20', '1995-02-15']

If you want to get the result as a list of dates, you need to specify the result_type:

dates = select(raw_sql('(p.dob)', result_type=date) for p in Person)[:]
print(dates)
[datetime.date(1986, 1, 1), datetime.date(1984, 5, 20), datetime.date(1996, 2, 15)]

The raw_sql() function can be used in a Query.filter() too:

x = 25
select(p for p in Person).filter(lambda p: p.age > raw_sql('$x'))

It can be used inside the Query.filter() without lambda. In this case you have to use the first letter of entity name in lower case as the alias:

x = 25
Person.select().filter(raw_sql('p.age > $x'))

You can use several raw_sql() expressions in a single query:

x = '123'
y = 'John'
Person.select(lambda p: raw_sql("UPPER(p.name) || $x")
                        == raw_sql("UPPER($y || '123')"))

The same parameter names can be used several times with different types and values:

x = 10
y = 31
q = select(p for p in Person if p.age > x and p.age < raw_sql('$y'))
x = date(1980, 1, 1)
y = 'j'
q = q.filter(lambda p: p.dob > x and p.name.startswith(raw_sql('UPPER($y)')))
persons = q[:]

You can use raw_sql() in Query.order_by() section:

x = 9
Person.select().order_by(lambda p: raw_sql('SUBSTR(p.dob, $x)'))

Or without lambda, if you use the same alias, that you used in previous filters. In this case we use the default alias - the first letter of the entity name:

x = 9
Person.select().order_by(raw_sql('SUBSTR(p.dob, $x)'))

Using the select_by_sql() and get_by_sql() methods

Although Pony can translate almost any condition written in Python to SQL, sometimes the need arises to use raw SQL, for example - in order to call a stored procedure or to use a dialect feature of a specific database system. In this case, Pony allows the user to write a query in a raw SQL, by placing it inside the function Entity.select_by_sql() or Entity.get_by_sql() as a string:

Product.select_by_sql("SELECT * FROM Products")

Unlike the method Entity.select(), the method Entity.select_by_sql() does not return the Query object, but a list of entity instances.

Parameters are passed using the following syntax: “$name_variable” or “$(expression in Python)”. For example:

x = 1000
y = 500
Product.select_by_sql("SELECT * FROM Product WHERE price > $x OR price = $(y * 2)")

When Pony encounters a parameter within a raw SQL query, it gets the variable value from the current frame (from globals and locals) or from the dictionaries which can be passed as parameters:

Product.select_by_sql("SELECT * FROM Product WHERE price > $x OR price = $(y * 2)",
                       globals={'x': 100}, locals={'y': 200})

Variables and more complex expressions specified after the $ sign, will be automatically calculated and transferred into the query as parameters, which makes SQL-injection impossible. Pony automatically replaces $x in the query string with “?”, “%S” or with other paramstyle, used in your database.

If you need to use the $ sign in the query (for example, in the name of a system table), you have to write two $ signs in succession: $$.