1. Use the 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 methods.

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

    The result can be treated as a logical expression:

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

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

    1. q = Person.select(lambda x: raw_sql('abs("x"."age")') > 25)
    2. print(q.get_sql())
    4. SELECT "x"."id", "x"."name", "x"."age", "x"."dob"
    5. FROM "Person" "x"
    6. 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 can accept $parameters:

    1. x = 25
    2. 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:

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

    You can use various types inside the raw SQL query:

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

    Parameters inside the raw SQL part can be combined:

    You can even call Python functions inside:

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

    1. names = select(raw_sql('UPPER(p.name)') for p in Person)[:]
    2. print(names)
    4. ['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:

    1. dates = select(raw_sql('(p.dob)') for p in Person)[:]
    2. print(dates)
    4. ['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:

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

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

    1. x = 25

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

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

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

    1. x = 10
    2. y = 31
    4. x = date(1980, 1, 1)
    5. y = 'j'
    6. q = q.filter(lambda p: p.dob > x and p.name.startswith(raw_sql('UPPER($y)')))
    7. persons = q[:]

    You can use raw_sql() in section:

    1. x = 9
    2. 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:

    1. x = 9
    2. 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 or Entity.get_by_sql() as a string:

    1. Product.select_by_sql("SELECT * FROM Products")

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

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

    1. x = 1000
    2. y = 500
    3. 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:

    1.                        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.