Python 101: An Intro to Pony ORM

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. When I first started using PonyORM, they were 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. However, as of October 2016, the PonyORM package is under the Apache 2.0 license.

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!

import datetime
import pony.orm as pny

database = pny.Database("sqlite",
                        "music.sqlite",
                        create_db=True)

########################################################################
class Artist(database.Entity):
    """
    Pony ORM model of the Artist table
    """
    name = pny.Required(unicode)
    albums = pny.Set("Album")
    
########################################################################
class Album(database.Entity):
    """
    Pony ORM model of album table
    """
    artist = pny.Required(Artist)
    title = pny.Required(unicode)
    release_date = pny.Required(datetime.date)
    publisher = pny.Required(unicode)
    media_type = pny.Required(unicode)

# turn on debug mode
pny.sql_debug(True)

# map the models to the database 
# and create the tables, if they don't exist
database.generate_mapping(create_tables=True)

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:

GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE TABLE "Artist" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL
)

CREATE TABLE "Album" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "artist" INTEGER NOT NULL REFERENCES "Artist" ("id"),
  "title" TEXT NOT NULL,
  "release_date" DATE NOT NULL,
  "publisher" TEXT NOT NULL,
  "media_type" TEXT NOT NULL
)

CREATE INDEX "idx_album__artist" ON "Album" ("artist")

SELECT "Album"."id", "Album"."artist", "Album"."title", "Album"."release_date", "Album"."publisher", "Album"."media_type"
FROM "Album" "Album"
WHERE 0 = 1

SELECT "Artist"."id", "Artist"."name"
FROM "Artist" "Artist"
WHERE 0 = 1

COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION

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:

import datetime
import pony.orm as pny

from models import Album, Artist

#----------------------------------------------------------------------
@pny.db_session
def add_data():
    """"""
    
    new_artist = Artist(name=u"Newsboys")
    bands = [u"MXPX", u"Kutless", u"Thousand Foot Krutch"]
    for band in bands:
        artist = Artist(name=band)
        
    album = Album(artist=new_artist,
                  title=u"Read All About It",
                  release_date=datetime.date(1988,12,01),
                  publisher=u"Refuge",
                  media_type=u"CD")
    
    albums = [{"artist": new_artist,
               "title": "Hell is for Wimps",
               "release_date": datetime.date(1990,07,31),
               "publisher": "Sparrow",
               "media_type": "CD"
               },
              {"artist": new_artist,
               "title": "Love Liberty Disco", 
               "release_date": datetime.date(1999,11,16),
               "publisher": "Sparrow",
               "media_type": "CD"
              },
              {"artist": new_artist,
               "title": "Thrive",
               "release_date": datetime.date(2002,03,26),
               "publisher": "Sparrow",
               "media_type": "CD"}
              ]
     
    for album in albums:
        a = Album(**album)
        
if __name__ == "__main__":
    add_data()

    # use db_session as a context manager
    with pny.db_session:
        a = Artist(name="Skillet")

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.

import pony.orm as pny

from models import Artist, Album

with pny.db_session:
    band = Artist.get(name="Newsboys")
    print band.name
    
    for record in band.albums:
        print record.title
        
    # update a record
    band_name = Artist.get(name="Kutless")
    band_name.name = "Beach Boys"

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:

result = pny.select(i.name for i in Artist)
result.show()

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:

import pony.orm as pny

from models import Artist

with pny.db_session:
    band = Artist.get(name="MXPX")
    band.delete()

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