Python 101: An Intro to Pony ORM

Posted by Mike on July 21st, 2014 filed in Cross-Platform, Python

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!

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

Print Friendly

  • Dennis Monsewicz

    Awesome article! Thanks for the post!

  • Patrycja Szabłowska

    Why use PonyORM and not Django ORM or SQLAlchemy? Any thoughts? To be honest, I don’t think there’s a need for yet another Python ORM. There are many others which are quite mature.

  • http://www.blog.pythonlibrary.org/ Mike Driscoll

    Can you use Django’s ORM outside of Django? I don’t think you can, at least not easily. I think the main reason people might want to try Pony or peewee is that they are more lightweight and possibly easier to use than SQLAlchemy.

  • Patrycja Szabłowska

    You’re right, it’s hard to use Django’s ORM outside Django (but possible). What I’m trying to say is that I think it is somehow reinventing the wheel, a wasted effort to copy functionalities of libraries that already exist instead of trying to improve the existing ones.
    And no one can be sure how long such library is going to be maintained – PonyORM has two contributors, so you can’t say for sure.

  • http://www.blog.pythonlibrary.org/ Mike Driscoll

    Yeah, that’s very true. My point is that sometimes people reinvent the wheel in a way that’s better than the original. I don’t know that Pony has done that, but without innovators, there wouldn’t be any fun ORMs in the first place.

  • http://ponyorm.com/ Alexey Malashkevich

    Pony ORM author here.

    You’re right, there are many other ORMs, but the reason we are developing Pony ORM is that we see the need for a simpler and more efficient ORM.

    Pony allows programmers working with objects, which are stored in the database the same way as if they were stored in memory, using the native Python syntax. It makes development easier.

    Also Pony can automatically solve N+1 query problem and optimize SQL queries for better performance.

    Last month I gave a presentation at EuroPython where I shared interesting implementation details. Also there is a little bit of comparison with other ORMs. This is the video http://www.youtube.com/watch?v=xjikvIHjvHs and here are the slides: http://www.slideshare.net/ponyorm/pony-orm-ep2014-slideshare

    P.S. Thank you, Mike, for the Pony tutorial!

  • Patrycja Szabłowska

    Alexey, thank you for your answer. That sounds promising, PonyORM looks clean for sure. I think that showing some benchmarks of PonyORM compared to other ORMs would be even more convincing – e.g. how quick is a certain query when run from PonyORM vs. Django and SQLAlchemy.

  • http://ponyorm.com/ Alexey Malashkevich

    Thanks Patrycja. You are’re right, benchmarks is on our to do list.

  • http://www.ondoheer.com Ondoheer

    Brilliant Article, I just configured a DB which was giving me a bit of a headeache with Flask-SqlAlchemy with Pony in my first try in under 20 mins and it works flawlessly! Cheers to Alexey!

  • http://blog.yegle.net/ yegle

    Hi Alexey,

    What’s the N+1 query problem that Pony solves? I saw it been mentioned in prefetch() document but not sure what it is. Thank you.

  • http://ponyorm.com/ Alexey Malashkevich

    Hi,

    Here you can find a good description of what is the N+1 problem: http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue
    And here you can find more info on how Pony solves it http://youtu.be/xjikvIHjvHs?t=21m49s

    Please let me know if you still have any questions.

  • YF

    I’d like to say that I like Pony ORM very, very much. It works like a charm. Thanks the authors..