The Pony ORM project is another object relational mapper package for Python. They allow you to query a database using generators. They also have an online ER Diagram Editor that is supposed to help you create a model. They are also one of the only Python packages I’ve seen with a multi-licensing scheme where you can develop using a GNU license or purchase a license for non-open source work. See their website for additional details.
In this article, we will spend some time learning the basics of this package.
Getting Started
Since this project is not included with Python, you will need to download and install it. If you have pip, then you can just do this:
pip install pony
Otherwise you’ll have to download the source and install it via its setup.py script.
Creating the Database
We will start out by creating a database to hold some music. We will need two tables: Artist and Album. Let’s get started!
Pony ORM will create our primary key for us automatically if we don’t specify one. To create a foreign key, all you need to do is pass the model class into a different table, as we did in the Album class. Each Required field takes a Python type. Most of our fields are unicode, with one being a datatime object. Next we turn on debug mode, which will output the SQL that Pony generates when it creates the tables in the last statement. Note that if you run this code multiple times, you won’t recreate the table. Pony will check to see if the tables exist before creating them.
If you run the code above, you should see something like this get generated as output:
Wasn’t that neat? Now we’re ready to learn how to add data to our database.
How to Insert / Add Data to Your Tables
Pony makes adding data to your tables pretty painless. Let’s take a look at how easy it is:
You will note that we need to use a decorator caled db_session to work with the database. It takes care of opening a connection, committing the data and closing the connection. You can also use it as a context manager, which is demonstrated at the very end of this piece of code.
Using Basic Queries to Modify Records with Pony ORM
In this section, we will learn how to make some basic queries and modify a few entries in our database.
Here we use the db_session as a context manager. We make a query to get an artist object from the database and print its name. Then we loop over the artist’s albums that are also contained in the returned object. Finally, we change one of the artist’s names.
Let’s try querying the database using a generator:
If you run this code, you should see something like the following:
i.name
--------------------
Newsboys
MXPX
Beach Boys
Thousand Foot Krutch
The documentation has several other examples that are worth checking out. Note that Pony also supports using SQL itself via its select_by_sql and get_by_sql methods.
How to Delete Records in Pony ORM
Deleting records with Pony is also pretty easy. Let’s remove one of the bands from the database:
Once more we use db_session to access the database and commit our changes. We use the band object’s delete method to remove the record. You will need to dig to find out if Pony supports cascading deletes where if you delete the Artist, it will also delete all the Albums that are connected to it. According to the docs, if the field is Required, then cascade is enabled.
Wrapping Up
Now you know the basics of using the Pony ORM package. I personally think the documentation needs a little work as you have to dig a lot to find some of the functionality that I felt should have been in the tutorials. Overall though, the documentation is still a lot better than most projects. Give it a go and see what you think!
Additional Resources
Pony ORM’s website
Pony documentation
SQLAlchemy Tutorial
An Intro to peewee