Using Database object for raw SQL queries

Typically you will work with entities and let Pony interact with the database, but Pony also allows you to work with the database using SQL, or even combine both ways. Of course you can work with the database directly using the DBAPI interface, but using the Database object gives you the following advantages:

• Automatic transaction management using the db_session() decorator or context manager. All data will be stored to the database after the transaction is finished, or rolled back if an exception happened.

• Connection pool. There is no need to keep track of database connections. You have the connection when you need it and when you have finished your transaction the connection will be returned to the pool.

• Unified database exceptions. Each DBAPI module defines its own exceptions. Pony allows you to work with the same set of exceptions when working with any database. This helps you to create applications which can be ported from one database to another.

• Unified way of passing parameters to SQL queries with the protection from injection attacks. Different database drivers use different paramstyles - the DBAPI specification offers 5 different ways of passing parameters to SQL queries. Using the Database object you can use one way of passing parameters for all databases and eliminate the risk of SQL injection.

• Automatic unpacking of single column results when using get() or select() methods of the Database object. If the select() method returns just one column, Pony returns a list of values, not a list of tuples each of which has just one item, as it does DBAPI. If the get() method returns a single column it returns just value, not a tuple consisting of one item. It’s just convenient.

• When the methods select() or get() return more than one column, Pony uses smart tuples which allow accessing items as tuple attributes using column names, not just tuple indices.

In other words the Database object helps you save time completing routine tasks and provides convenience and uniformity.

Using parameters in raw SQL queries

With Pony you can easily pass parameters into SQL queries. In order to specify a parameter you need to put the $ sign before the variable name:

x = "John"
data = db.select("select * from Person where name = $x")

When Pony encounters such a parameter within the SQL query it gets the variable value from the current frame (from globals and locals) or from the dictionary which is passed as the second parameter. In the example above Pony will try to get the value for $x from the variable x and will pass this value as a parameter to the SQL query which eliminates the risk of SQL injection. Below you can see how to pass a dictionary with the parameters:

data = db.select("select * from Person where name = $x", {"x" : "Susan"})

This method of passing parameters to the SQL queries is very flexible and allows using not only single variables, but any Python expression. In order to specify an expression you need to put it in parentheses after the $ sign:

data = db.select("select * from Person where name = $(x.lower()) and age > $(y + 2)")

All the parameters can be passed into the query using the Pony unified way, independently of the DBAPI provider, using the $ sign. In the example above we pass name and age parameters into the query.

It is possible to have a Python expressions inside the query text, for example:

x = 10
a = 20
b = 30
db.execute("SELECT * FROM Table1 WHERE column1 = $x and column2 = $(a + b)")

If you need to use the $ sign as a string literal inside the query, you need to escape it using another $ (put two $ signs in succession: $$).