GeezerVison

peewee

peewee is a small, expressive ORM. Compared to other ORMs, peewee focuses on the principal of minimalism where the API is simple and the library is easy to use and understand.

[shell]
pip install peewee
Downloading/unpacking peewee
Downloading peewee-2.1.7.tar.gz (1.1MB): 1.1MB downloaded
Running setup.py egg_info for package peewee

Installing collected packages: peewee
Running setup.py install for peewee
changing mode of build/scripts-2.7/pwiz.py from 644 to 755

changing mode of /Users/xiaonuogantan/python2-workspace/bin/pwiz.py to 755
Successfully installed peewee
Cleaning up...
[/shell]

To create a database model mapping, we implement a Person class and an Address class that map to the corresponding database tables.

[python]
>>> from peewee import SqliteDatabase, CharField, ForeignKeyField, Model
>>>
>>> db = SqliteDatabase(':memory:')
>>>
>>> class Person(Model):
... name = CharField()
...
... class Meta:
... database = db
...
>>>
>>> class Address(Model):
... address = CharField()
... person = ForeignKeyField(Person)
...
... class Meta:
... database = db
...
>>> Person.create_table()
>>> Address.create_table()
[/python]

To insert objects into the database, we instantiate the objects and call their save() methods. From object creation point of view, peewee is very similar to Django.

[python]
>>> p = Person(name='person')
>>> p.save()
>>> a = Address(address='address', person=p)
>>> a.save()
[/python]

To get or retrieve the objects from the database, we select the objects from their respective classes.

[python]
>>> person = Person.select().where(Person.name == 'person').get()
>>> person
>>>
>>> print '%r, %r' % (person.id, person.name)
1, u'person'
>>> address = Address.select().where(Address.person == person).get()
>>> print '%r, %r' % (address.id, address.address)
1, u'address'
[/python]

PonyORM

PonyORM allows you to query the database using Python generators. These generators are translated into SQL and the results are automatically mapped into Python objects. Writing queries as Python generators makes it easy for programmers to quickly construct certain queries.

For example, let's use PonyORM to query the previous Person and Address models in a SQLite database.

[python]
>>> from pony.orm import Database, Required, Set
>>>
>>> db = Database('sqlite', ':memory:')
>>>
>>>
>>> class Person(db.Entity):
... name = Required(unicode)
... addresses = Set("Address")
...
>>>
>>> class Address(db.Entity):
... address = Required(unicode)
... person = Required(Person)
...
>>> db.generate_mapping(create_tables=True)
[/python]

Now we have a SQLite database in memory and two tables mapped to the db object, we can insert two objects into the database.

[python]
>>> p = Person(name="person")
>>> a = Address(address="address", person=p)
>>> db.commit()
[/python]

The call db.commit() actually commits the new objects p and a into the database. Now we can query the database using the generator syntax.

[python]
>>> from pony.orm import select
>>> select(p for p in Person if p.name == "person")[:]
[Person[1]]
>>> select(p for p in Person if p.name == "person")[:][0].name
u'person'
>>> select(a for a in Address if a.person == p)[:]
[Address[1]]
>>> select(a for a in Address if a.person == p)[:][0].address
u'address'
[/python]

SQLAlchemy

SQLAlchemy is an open source SQL toolkit and ORM for the Python programming language released under the MIT license. It was released initially in February 2006 and written by Michael Bayer. It provides "a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language". It has adopted the data mapper pattern (like Hibernate in Java) rather than the active record pattern (like the one in Ruby on Rails).

SQLAlchemy's unit-of-work principal makes it essential to confine all the database manipulation code to a specific database session that controls the life cycles of every object in that session. Similar to other ORMs, we start by defining subclasses of declarative_base() in order to map tables to Python classes.

[python]
>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> from sqlalchemy.orm import relationship
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>> Base = declarative_base()
>>>
>>>
>>> class Person(Base):
... __tablename__ = 'person'
... id = Column(Integer, primary_key=True)
... name = Column(String)
...
>>>
>>> class Address(Base):
... __tablename__ = 'address'
... id = Column(Integer, primary_key=True)
... address = Column(String)
... person_id = Column(Integer, ForeignKey(Person.id))
... person = relationship(Person)
...
[/python]

Before we write any database code, we need to create an database engine for our db session.

[python]
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
[/python]

Once we have created a database engine, we can proceed to create a database session and create tables for all the database classes previously defined as Person and Address.

[python]
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
[/python]

Now the session object becomes our unit-of-work constructor and all the subsequent database manipulation code and objects will be attached to a db session constructed by calling its __init__() method.

[python]
>>> s = session()
>>> p = Person(name='person')
>>> s.add(p)
>>> a = Address(address='address', person=p)
>>> s.add(a)
[/python]

To get or retrieve the database objects, we call query() and filter() methods from the db session object.

[python]
>>> p = s.query(Person).filter(Person.name == 'person').one()
>>> p

>>> print "%r, %r" % (p.id, p.name)
1, 'person'
>>> a = s.query(Address).filter(Address.person == p).one()
>>> print "%r, %r" % (a.id, a.address)
1, 'address'
[/python]

Notice that so far we haven't committed any changes to the database yet so that the new person and address objects are not actually stored in the database yet. Calling s.commit() will actually commit the changes, i.e., inserting a new person and a new address, into the database.

[python]
>>> s.commit()
>>> s.close()
[/python]

Comparison Between Python ORMs

For each Python ORM presented in this article, we are going to list their pros and cons here:

SQLObject

Pros:

1. Adopted the easy-to-understand ActiveRecord pattern
2. A relatively small codebase

Cons:

1. Naming of methods and classes follow Java's camelCase style
2. Does not support database sessions to isolate unit of work

Storm

Pros:

1. A clean and lightweight API leading to short learning curve and long-term maintainability
2. Does not need special class constructors, nor imperative base classes

Cons:

1. Forcing the programmer to write manual table-creation DDL statements instead of automatically deriving it from the model class
2. Contributors of Storm have to give their contributions' copyrights to Canonical Ltd.

Django's ORM

Pros:

1. Easy-to-use with a short learning curve
2. Tightly integrated with Django to make it the de-factor standard when dealing with databases in Django

Cons:

1. Does not handle complex queries very well; forcing the developer to go back to raw SQL
2. Tightly integrated with Django; making it hard to use outside of a Django context

peewee

Pros:

1. A Django-ish API; making it easy-to-use
2. A lightweight implementation; making it easy to integrate with any web framework

Cons:

1. Does not support automatic schema migrations
2. Many-to-Many queries are not intuitive to write

SQLAlchemy

Pros:

1. Enterprise-level APIs; making the code robust and adaptable
2. Flexible design; making it painless to write complex queries

Cons:

1. The Unit-of-work concept is not common
2. A heavyweight API; leading to a long learning curve

PonyORM

Pros:

1. A very convenient syntax for writing queries
2. Automatic query optimization
3. Simplified setup and usage

Cons:

1. Not designed to process hundreds of thousands or millions of records simultaneously

Summary and Tips

Compared to other ORMs, SQLAlchemy stands out in its focus on the unit-of-work concept which is prevalent whenever you write SQLAlchemy code. The DBSession concept might be hard to understand and use correctly initially, but later you will appreciate the additional complexity which reduces accidental database commit-timing-related bugs to almost zero. Dealing with multiple databases in SQLAlchemy can be tricky since each DB session is confined to one database connection. However, this kind of limitation is actually a good thing since it forces you to think hard about the interaction between multiple databases and make it easier to debug database interaction code.

In the future articles, we are going to fully explore more advanced use cases of SQLAlchemy to truly grasp its immensely powerful APIs.