Tutorial on deploying an ORM library on Python's Django framework

Python ORM Overview

As a wonderful language, Python has many ORM libraries besides SQLAlchemy. In this article, we'll look at several popular optional ORM libraries in the future to get a better glimpse of the python ORM situation. By writing a script to read and write 2 tables, person and address to a simple database, we can better understand the pros and cons of each ORM library.

Sqlobject

Sqlobject is a Python ORM that maps objects between SQL database and Python. Thanks to its activerecord pattern like Ruby on Rails, the programming community is becoming more and more popular. The first Sqlobject was released in October 2002. It follows the LGPL license.

In Sqlobject, database concepts are mapped to Python in a way that is very similar to slqalchemy, with tables mapped into classes, rows as instances, and fields as attributes. It also provides a query language based on Python objects, which makes SQL more abstract, thus providing database agnostic for Applications (application and database separation)

$ pip Install sqlobjectdownloading/unpacking sqlobjectdownloading sqlobject-1.5.1.tar.gz (276kB): 276kB  Downloadedrunning setup.py egg_info for package sqlobject warning:no files found matching ' *.html ' warning:no files found Matching ' *.css ' warning:no files found matching ' docs/*.html ' warning:no files found matching ' *.py ' under directory ' Te STS ' requirement already satisfied (use--upgrade to upgrade): formencode>=1.1.1 in/users/xiaonuogantan/ Python2-workspace/lib/python2.7/site-packages (from Sqlobject) installing collected packages:sqlobjectrunning setup.py Install for sqlobjectchanging mode of build/scripts-2.7/sqlobject-admin from 644 to 755changing mode of BUILD/SCR  Ipts-2.7/sqlobject-convertolduri from 644 to 755 warning:no files found matching ' *.html ' warning:no files found matching ' *.css ' warning:no files found matching ' docs/*.html ' warning:no files found matching ' *.py ' under directory ' tests ' Chang ing mode Of/users/xiaonuogantan/python2-workspace/bin/sqlobject-admiN to 755changing mode Of/users/xiaonuogantan/python2-workspace/bin/sqlobject-convertolduri to 755Successfully Installed sqlobjectcleaning up ... >>> from sqlobject import Stringcol, Sqlobject, ForeignKey, Sqlhub, connection foruri>>> sqlhub.processconnection = Connectionforuri (' sqlite:/:memory: ') >>>>>> class Person (sqlobject): ... name = Stringcol () ...>>> class address (Sqlobject): ... Address = Stringcol () ... person = Fo Reignkey (' person ') ...>>> person.createtable () []>>> address.createtable () []

The code above creates 2 simple tables: person and address. To create and insert records into these 2 tables, we simply instantiate a person instance and an address instance:

To get or retrieve new records, we use the Magic Q object to associate to the person and Address classes:

Storm

Storm is a Python ORM that maps objects between single or multiple databases and Python. To support dynamic storage and retrieval of object information, it allows developers to build complex queries across data tables. It was developed in Python by the company canonical company behind Ubuntu, used in Launchpad and Landscape applications, and later released as free software in 2007. This project is released under LGPL license and the code contributor must be copyrighted to canonical company.

Like SQLAlchemy and Sqlobject, Storm also maps tables to classes, rows to instances, and fields to properties. The table class in Stom does not need to be a subclass of a framework-specific base class relative to the other 2 libraries. In SQLAlchemy, each table class is a subclass of Sqlalchemy.ext.declarative.declarative_bas. In Sqlojbect, each table class is sqlobject. The subclass of the Sqlobject.

Like SQLAlchemy, Storm's Store object is like a proxy for the back-end database, all operations are cached in memory, and commits to the database when the commit method is called on the Store. Each store holds its own collection of Python database object mappings, like a SQLAlchemy session holding a different collection of Python objects.

The specified version of Storm can be downloaded from the download page. In this article, the sample code is written using the 0.20 version of Storm.

>>> from storm.locals import Int, Reference, Unicode, Create_database, store>>>>>>>> > db = Create_database (' SQLite: ') >>> store = store (db) >>>>>>>>> class person ( Object): ... __storm_table__ = ' person ' ... id = Int (primary=true) ... name = Unicode () ...>>>>>> class Add Ress (object): ... __storm_table__ = ' address ' ... id = int (primary=true) ... address = Unicode () ... person_id = Int () ... pers On = Reference (person_id, person.id) ...

The code above creates a SQLite memory database and then uses the store to reference the database object. A storm store is a sqlalchemy-like Dbsession object that manages the life cycle of an instance object that is attached to it. For example, the following code creates a person and an address, and then inserts a record by refreshing the store.

>>> Store.execute ("CREATE TABLE person" ...) "(ID INTEGER PRIMARY KEY, name VARCHAR)") >>> Store.execute ("CREATE TABLE address" ...) "(Id integer PRIMARY KEY, Address VARCHAR, person_id integer," ...) "FOREIGN KEY (person_id) REFERENCES person (ID))") >>> person = person () >>> person.name = U ' Person ' >&  gt;> Print person >>> print "%r,%r"% (person.id, person.name) None, u ' person ' # Notice that Person.id is None  Since the person instance are not attached to a valid database store yet.>>> store.add (person) >>> Print "%r,%r"% (person.id, person.name) None, u ' person ' # Since the store hasn ' t flushed the person instance into the SQLite D Atabase yet, Person.id is still none.>>> store.flush () >>> print "%r,%r"% (Person.id, person.name) 1, u ' Person ' # Now the store had flushed the person instance, we got a ID value for person.>>> address = address () ;>> Address.person = person>>> Address. Address = ' address ' >>> print '%r,%r,%r '% (address.id, Address.person, address.address) None,, ' address ' >&G t;> Address.person = = persontrue>>> Store.add (address) >>> Store.flush () >>> print "%r,%r ,%r "% (Address.id, Address.person, address.address) 1,, ' address '

To get or retrieve the inserted person and Address objects, we call Store.find () to query:

>>> person = store.find (person, Person.name = = U ' person '). One () >>> print "%r,%r"% (Person.id, PERSON.N AME) 1, u ' person ' >>> store.find (address, Address.person = = person). One () >>> Address = Store.find ( Address, Address.person = = person). One () >>> print "%r,%r"% (Address.id, address.address) 1, u ' Address '

Django's ORM

Django is a free open source, tightly embedded ORM to its system's Web application framework. Since its first release, Django has become increasingly popular thanks to its easy-to-use web-enabled features. It was released under the BSD license in July 2005. Because the Django ORM is tightly embedded into the web framework, it is not recommended to use its ORM in a standalone non-Django Python project.

Django, one of the most popular Python web frameworks, has its own ORM. In contrast to SQLAlchemy, Django's ORM is more consistent with manipulating SQL objects directly, exposing SQL objects that simply map data tables and Python classes.

$ django-admin.py startproject demo$ cd demo$ python manage.py syncdbcreating tables ... Creating table django_admin_logcreating table auth_permissioncreating table auth_group_permissionscreating table Auth_ groupcreating table auth_user_groupscreating table auth_user_user_permissionscreating table auth_usercreating table django_content_typecreating table django_session You just installed Django's auth system, which means you don ' t has any s Uperusers defined. Would to create one now? (yes/no): noinstalling custom SQL ... Installing indexes ... Installed 0 object (s) from 0 fixture (s) $ python manage.py shell

Because we were not able to execute the Django code without first building a project, we created a Django demo project in the previous shell and then went into the Django Shell to test the ORM example we wrote.

# demo/models.py>>> from django.db Import models>>>>>>>>> class person (models. Model): .... name = models. TextField () ... class Meta: ... App_label = ' demo ' ...>>>>>> class Address (models. Model): ... address = models. TextField () ... person = models. ForeignKey (person) ... class Meta: ... App_label = ' demo ' ...

The above code declares 2 python classes, person and Address, each mapped to a database table. Before executing any database operation code, we need to create a table in the local SQLite database.

Python manage.py syncdbcreating tables ... Creating table demo_personcreating table demo_addressinstalling Custom SQL ... Installing indexes ... Installed 0 object (s) from 0 fixture (s)

In order to insert a person and an address into the database, we instantiate the object and invoke the Save () method of those objects.

>>> from demo.models import person, address>>> P = person (name= ' person ') >>> P.save () >> > Print "%r,%r"% (P.id, p.name) 1, ' person ' >>> a = Address (person=p, address= ' address ') >>> A.save () & gt;>> print "%r,%r"% (a.ID, a.address) 1, ' address '

To get or retrieve the person and address objects, we use the amazing object properties of the model class to get objects from the database.

>>> persons = Person.objects.filter (name= ' person ') >>> persons[]>>> p = persons[0]>> > Print "%r,%r"% (P.id, p.name) 1, u ' person ' >>> addresses = Address.objects.filter (person=p) >>> add resses[]>>> a = addresses[0]>>> print "%r,%r"% (a.ID, a.address) 1, u ' address '

PeeWee

PeeWee is a small, expression-type ORM. Compared to other Orm,peewee, the main focus is on minimalism, its API is simple, and its library is easy to use and understand.

Pip Install peeweedownloading/unpacking peeweedownloading peewee-2.1.7.tar.gz (1.1MB): 1.1MB downloadedrunning  setup.py Egg_info for package PeeWee Installing collected packages:peeweerunning setup.py install for peeweechanging mode of build/scripts-2.7/pwiz.py from 644 to 755 changing mode of/users/xiaonuogantan/python2-workspace/bin/pwiz.py to 755Su Ccessfully installed peeweecleaning up ...

To create a database model mapping, we implemented a person class and an address class to map the corresponding database tables.

>>> 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 ()

To insert objects into the database, we instantiate the objects and invoke their Save () methods. From the View object creation point, PeeWee is similar to Django.

>>> p = person (name= ' person ') >>> P.save () >>> a = Address (address= ' address ', person=p) > >> A.save ()

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

>>> 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 () & gt;>> print '%r,%r '% (address.id, address.address) 1, u ' address '

SQLAlchemy

SQLAlchemy is a Python programming language, an open source tool and SQL ORM published under the MIT License. It was first published in February 2006 and written by Michael Bayer. It offers "a well-known enterprise-class persistence model, designed for efficient and high-performance database access, adapted as a complete suite of simple Python domain languages." It takes the data mapping pattern (like hibernate in Java) instead of the active record pattern (like the ORM on the Ruby on Rails).

The work unit of SQLAlchemy mainly makes it necessary to restrict all database operation code to a specific database session, and to control the life cycle of each object in the session. Similar to other ORM, we begin by defining subclasses of Declarative_base () to map tables to Python classes.

>>> from SQLAlchemy import Column, String, Integer, foreignkey>>> from sqlalchemy.orm import Relationsh Ip>>> 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) ...

Before we write any database code, we need to create a database engine for the database session.

>>> from SQLAlchemy import create_engine>>> engine = Create_engine (' sqlite:///')

Once we have created the database engine, we can continue to create a database session and create a database table for all of the previously defined person and address classes.

>>> from Sqlalchemy.orm import sessionmaker>>> session = Sessionmaker () >>> Session.configure (bind=engine) >>> Base.metadata.create_all (engine)

Now, the Session object object becomes the constructor of our work cell, and all subsequent database operation code and objects are associated to a database session built by calling its __init__ () method.

>>> s = Session () >>> p = person (name= ' person ') >>> S.add (p) >>> a = Address (address= ' Address ', person=p) >>> S.add (a)

To obtain or retrieve objects in the database, we invoke the query () and filter () methods on the database session object.

>>> 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 '

Please note that we have not submitted any changes to the database so far, so the new person and address objects are not actually stored in the database. Calling S.commit () will commit the changes, such as inserting a new person and a new address into the database.

>>> s.commit () >>> s.close ()

Python ORM Comparison

For each of the Python ORM mentioned in the article, let's make a list of their pros and cons:
Sqlobject

Advantages:

• Easy-to-use ActiveRecord mode
• A relatively small code base

Disadvantages:

• Methods and classes are named following Java's little hump style
• Database session Isolation Work Unit is not supported

Storm

Advantages:

• Refreshing lightweight API, short learning curve and long-term maintainability
• No special class constructors are required, and there is no necessary base class

Disadvantages:

• Forcing programmers to manually write DDL statements created by tables instead of automatically deriving from model classes
• Storm's contributors must put their contributions to the copyright canonical company

Django ' s ORM

Advantages:

• Easy to use, short learning curve
• And Django tightly set up, using Django to use the conventional method to manipulate the database

Disadvantages:

• Difficult to handle complex queries, forcing developers back to native SQL
• Tight and Django integration makes it hard to use outside of the Django environment

PeeWee

Advantages:

• Django-style API that makes it easy to use
• Lightweight implementation, easy integration with any web framework

Disadvantages:

• Automatic schema migration is not supported
• Many-to-many queries are not intuitive to write.

SQLAlchemy

Advantages:

• Enterprise-class APIs that make code robust and adaptable
• Flexible design makes it easy to write complex queries

Disadvantages:

• Work cell concept is not common
• Heavyweight API, leading to long learning curve

Summary and Tips

In contrast to other Orm, SQLAlchemy means that whenever you write SQLAlchemy code, you focus on the cutting-edge concept of the unit of work. The concept of the DB Session may initially be difficult to understand and use correctly, but later you will appreciate the additional complexity, which reduces the accidental time-to-commit related database bugs to 0. Working with multiple databases in SQLAlchemy is tricky because each DB session qualifies a database connection. However, this type of restriction is actually a good thing because it forces you to think about interactions between multiple databases, making it easy to debug your database interaction code.

In future articles, we will fully disclose the higher-order sqlalchemy use cases and truly understand the infinite and powerful APIs.

