Working with objects (CRUD)¶
Any object-relational mapper has a basic function set for work with objects, which is usually known under the abbreviation CRUD, which stands for four functions: Create/Read/Update/Delete. It allows to create an object and save it in a database, to get an objects from a database, and to update and delete an object.
Pony, too, has all the CRUD operations, and we will now review their syntax.
Creation of an object (CREATE)¶
Creating an object in Pony is similar to creating a usual object in Python; for example:
customer1 = Customer(login="John", password="***",
name="John", email="john@google.com")
When creating an object in Pony, all the parameters should be specified as keyword arguments. If an attribute has a default value, then upon creation of an object this attribute does not have to be set.
Any instance of an entity created by Pony in an operative memory belongs to the current transaction. At first, an object is created only in the memory of a program, and in order to save it in the database, it is required to complete the transaction with the command commit.
In some object-relational mappers, in order to save an object it is required to call its save() method. This is inconvenient, as a programmer must track which objects he has created or edited, and not to forget to call the save() method on each one. Pony can track which objects were created or edited during the execution of a transaction; and thus, when using Pony - all changes will be saved automatically with a single call to commit() method.
Loading objects from a database (READ)¶
Searching for an object by primary key¶
The simplest case is when we want to retrieve an object by the primary key. To accomplish this in Pony, the user simply needs to put the primary key in square brackets, after the class name. For example, to extract a customer with a primary key value of 123, we can write:
customer1 = Customer[123]
The same syntax also works for objects with composite keys; one just needs to list the elements of the composite primary key, separated by commas, in the same order that the attributes were defined in the entity description:
order_item = OrderItem[order1, product1]
At the same time, if an object is not found in the database, an ObjectNotFound exception is thrown.
Search for an object by the unique combination of attributes¶
If we want to find a unique object not by the primary key, but by another combination of attributes, we can use method get() of an entity. In most cases, it is used when we want to find an object by the secondary unique key, but it can also be used to search by any other combination of attributes. As a parameter of “get ()” method, we state names of the attributes and their values. For example, if we want to receive a product under the name “Product 1”, and we believe that database has only one product under this name, we can write:
product1 = Product.get(name="Product1")
If an object is not found, get() returns None, if multiple objects are found, MultipleObjectsFoundError exception is thrown.
We may want to use the get() method with primary key when we want to get None instead of ObjectNotFound exception if the object does not exists in database.
Method get() can also receive a lambda function as a single positioning argument, similar to the method select(), which we will discuss later; but nevertheless, it will return an instance of an entity, and not an object of the Query type.
Search of several objects by generic condition¶
In order to retrieve several objects from a database, we use function select(), which is present in every entity. Its argument is presented by the lambda function, which carries a single parameter, symbolizing an instance of an object in the database. Inside this function, we write conditions, by which we want to carry out the search. For example, if we want to find all products with the price higher than 100, we can write:
products = Product.select(lambda p: p.price > 100)
This lambda function that is passed as a parameter, will not be executed in Python. Instead, its contents will be analyzed and the conditions inside the lambda function will be translated to SQL. One important advantage of declarative query syntax in Pony is that it offers full protection from SQL-injections, as all the data related to external query parameters is properly escaped.
The result of a request method select() is an object of class Query, which can be used for iteration. For example, we can display a name and a price of the products we are interested in:
for p in Product.select(lambda p: p.price > 100):
print p.name, p.price
In addition to iteration, other useful things that can be done with Query objects, and they are described in the relevant section.
Parameter transfer to the query¶
Inside the lambda function, it is possible to use variables declared previously. In such cases, the query will include a parameter. Pony processes the parameters correctly, preventing SQL injections.
For example, if we want to find products with a price higher than X, then we can simply write:
x = 100
products = Product.select(lambda p: p.price > x)
In this query, X will be processed as a parameter completely protected from SQL-injections.
Sorting of query results¶
If we need to sort objects in a certain order, we can use the method order_by() of the query object. This method accepts the lambda function as well, but this time it describes not a searching condition, but an expression used for sorting. In the case that we need a reverse ordering, an expression is wrapped in a desc() function.
For example, if we want to display names and prices of all products with price higher than 100 in a descending order, we can write it like this:
for p in Product.select(lambda p: p.price > 100) \
.order_by(lambda p: desc(p.price)):
print p.name, p.price
In simple cases, we can indicate an attribute of an object instead of the lambda function. We could rewrite the previous example like this:
for p in Product.select(lambda p: p.price > 100) \
.order_by(desc(Product.price)):
print p.name, p.price
In order to sort by several expressions, the user has to transfer them into the lambda function, separated by commas. According to the syntax rule of Python, this tuple of expressions must be surrounded by an additional set of parentheses. For example, if we want to sort the products by descending price, while displaying products with similar prices alphabetically, we can write:
for p in Product.select(lambda p: p.price > 100) \
.order_by(lambda p: (desc(p.price), p.name)):
print p.name, p.price
Or, without the lambda function, we can write it like this:
for p in Product.select(lambda p: p.price > 100) \
.order_by(desc(Product.price), Product.name):
print p.name, p.price
The short form of order_by() request can be also used, where instead of using the lambda function, we pass the contents of the lambda function as a string. In this case the previous query would look like this:
for p in Product.select(lambda p: p.price > 100) \
.order_by("desc(p.price), p.name"):
print p.name, p.price
The method order_by() belongs not only to the query, but also to the entity. It is handy, if we want to sort all objects, without having to make a selection query:
all_products = Product.order_by(Product.name)
Limiting the number of chosen objects¶
It is possible to limit the number of objects returned by a query by using the query method limit(), or by the more compact “slice” notation. For example, the names of 10 most expensive products can be listed like this:
for p in Product.order_by(lambda p: desc(p.price))[:10]:
print p.name
The result of a slice is not a query object, but a final list of entity instances. In order to convert the query object into a list, we can apply the plain slice operator:
products = Product.select(...).order_by(...)[:]
or to pass the query object to list method:
products = list(Product.select(...).order_by(...))
If we want to receive something other than an object from the query, a tuple of several objects, a product of aggregation, or a separate column for example, then we need to use declarative queries, which are described in a separate section.
Traversing relationship attributes¶
As objects have realtionships, it is possible to traverse from one object to another. If the object we are traversing to already exists in memory, then Pony will not have to load in from a database, and the user will receive a sought-for object. If an object is not in the cache, it will be uploaded from the database automatically.
For example, if object Order is uploaded to the cache, we can use the connection to switch to an Customer object:
order = Order[123]
customer = order.customer
print customer.name
Pony tries to minimize the amount of queries sent to the database. In the given example, if a requested Customer was already uploaded to the cache of the current transaction, Pony will retrieve the object created earlier without sending a query to the database. But, if an object has not yet been created, Pony still will not send a query immediately. Instead, it will create an object containing only the primary key. Pony does not know how this object will be used, and there is always the possibility the only the primary key is needed. In the example above, uploading of an object is executed only in the third line in, when we access the name of a customer. Such an object, which is only known by a primary key, is called “seed”. By using the “seed” concept, Pony achieves high efficiency and fully resolves the “N+1” problem, which is a weakness of many other mappers.
Traversing is possible in the direction “to many” as well. For example, we have a Customer object and we want to loop through its orders:
c = Customer[123]
for order in c.orders:
print order.state, order.price
Further information is available in the chapter about collections.
Updating an object in a database (UPDATE)¶
In order to make an update we must retrieve an object, assign new values to the attributes, and save the object by calling the commit() funciton. Pony tracks, which attributes were changed, and it will only save those changes.
For example, in order to increase the number of products by 10 with a primary key of 123, we can execute the following code:
Product[123].quantity += 10
Commits are not required after each change. It would be more effective to do it only once, when all the changes are completed. If the decorator @db_session is used, the changes will be committed automatically.
If we want to change values of multiple attributes, we can do it separately:
order = Order[123]
order.state = "Shipped"
order.date_shipped = datetime.now()
or in a line, using the set() method:
order = Order[123]
order.set(state = "Shipped", date_shipped = datetime.now())
The set() method can be convenient when you need to update several object attributes at once from a dictionary:
order.set(**dict_with_new_values)
In the future, Pony will be augmented with an option to perform a bulk update (updating multiple objects on the disk without loading them to the cache):
update(p.set(price=price * 1.1) for p in Product
if p.category.name == "T-Shirt")
Deleting an object (DELETE)¶
A method delete() can be called on an object, which will mark an object as deleted and will remove it from the database during the following commit.
For example, to delete an order with number 123, we can write:
Order[123].delete()
In the future, Pony will be augmented with an option to perform bulk deletes. It will allow the deletion of multiple objects without loading them to the cache:
delete(p for p in Product if p.category.name == "Floppy disk")
Raw SQL¶
Although Pony can translate to SQL almost any condition written in Python, sometimes the need arises to use raw SQL, for example - in order to call 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 select_by_sql() or get_by_sql() as a string:
products = Product.select_by_sql("SELECT * FROM Products")
Unlike the method select(), method select_by_sql does not return object Query, but a prepared list of objects. In the query, we should choose columns in the same order in which attributes were declared in the object description. Asterisk can be used only if the order of columns in the database coincides completely with the order of attributes in the entity description.
Parameters can be transferred to the string with SQL using following syntax: “$name_variable” or “$(expression in Python)”. For example:
x = 1000
products = Product.select_by_sql("SELECT * FROM Products WHERE price > $x")
Variables and more complex expressions listed 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 the current database system.
If $ sign is required in the query (for example, in the name of a system table), it is necessary to write two $ signs in succession, and text of the query will thus include a single $ sign.