Comparing Django ORM, SQLAlchemy & SuRF

The query interface is the part you see most of your favourite ORM, I believe. So here’s an overview on how three ORMs for Python offer querying: Django ORM, SQLAlchemy and SuRF. The former two are well known for SQL, the latter is a relatively new interface to RDF data (queried foremost by SPARQL).

My goal is to see what methods are offered to query data and to compare those to each other. Here I’ll be coming from the Django side, comparing equivalent methods to SQLAlchemy and also to SuRF. Don’t get me wrong, SQLAlchemy users probably come from a different angle but then I don’t use it often enough to know the full ORM details. For my usecase this suffices so far.

The real goal here is to see what needs to and can yet be done for SuRF. I already started developing Django style complex Q queries and slicing and want to see how SQLAlchemy does it. And also what else I need to take care of.

This list is far from complete, nor, as said, does it present an unbiased view. I might extend this list in the future. Feel free to note errors and other important differences.

Sources

On SQLAlchemy

I personally don’t have much experience with the SQLAlchemy ORM, so take the examples for SQLAlchemy with a grain of salt. I’ll use the query_property here (see http://www.sqlalchemy.org/docs/05/reference/orm/sessions.html) so that it compares more easily to Django. However I don’t know if this way is generally accepted in the SQLAlchemy world.

Here is the way the tutorial of SQLAlchemy puts it:

session.query(User).all()

Using the query_property it boils down to:

class MyClass(object):
    query = db_session.query_property()

MyClass.query.all()

On SuRF

SuRF being an Object Relational Mapper for RDF data does many things differently. Most importantly a property has always a list of values – it might be empty, have one value or several. Also some functionality like aggregates was only specified in SPARQL 1.1 and has yet to be implemented by many backend stores. SuRF knows namespaces and thus a property is referenced by its namespace, here “myns” for property “prop”.

The comparison

Django ORM SQLAlchemy SuRF Description
MyClass.objects.all() MyClass.query.all() MyClass.all() All elements
MyClass.objects.filter(prop=10) MyClass.query.filter(MyClass.prop==10)
MyClass.query.filter_by(prop==10)
MyClass.get_by(myns_prop=10) Query by parameter
MyClass.objects.get(pk=10) MyClass.query.get(10) MyClass.get_by(myns_pk=10).one() Unique key
MyClass.objects.get(prop=10) MyClass.query.filter(MyClass.prop==10).one() MyClass.get_by(myns_prop=10).one() One exact result
? MyClass.query.filter(MyClass.prop==10).first() MyClass.get_by(myns_prop=10).first() First result
MyClass.objects.filter(prop__gt=10) MyClass.query.filter(MyClass.prop > 10).all() MyClass.all().filter(myns_prop=”(%s > 10)”) Greater than filtering
MyClass.objects.filter(prop__in=[1, 2]) MyClass.query.filter(MyClass.prop.in_([1, 2])) MyClass.get_by(myns_prop=[1, 2]) In list
MyClass.objects.filter(
prop__startswith=’somethin’)

MyClass.query.filter(
MyClass.prop.like=’somethin%’)

MyClass.all().filter(
myns_prop=”regex(%s,”^somethin”,”i”)”)
Substring
MyClass.objects.exclude(prop=10) MyClass.objects.filter(~MyClass.prop == 10) Negative search
MyClass.objects.all().count() MyClass.query.all().count() len(MyClass.all()) Result count
MyClass.objects.all().delete() MyClass.query.all().delete() Batch removal
MyClass.objects.all().exist() ? Boolean exist
MyClass.objects.all().order_by(“-prop”) MyClass.query.all().order_by(
desc(MyClass.prop))
MyClass.all().order(ns.MYNS.prop).desc() Descending ordering
default default MyClass.all().full() Preload properties
MyClass.objects.all().select_related(prop) session.query(MyClass)
.options(joinedload(‘prop’)).all()
Eagerly load relations
MyClass.objects.aggregate(Avg(‘prop’)) session.query(func.avg(MyClass.prop)).all() Aggregates
MyClass.objects.all()[1:10] MyClass.query.all()[1:10] MyClass.all().offset(1).limit(10) Slicing
MyClass.objects.all().only(“prop”) session.query(MyClass.prop).all() Performance
MyClass.prop.remove(i) MyClass.prop.remove(i) MyClass.myns_prop.remove(i) Remove from one-to-many relationship
MyClass.objects.filter(
Q(prop=’x’) | Q(prop=’y’))
MyClass.query.filter(or_(MyClass.prop==’x’, MyClass.prop==’y’)) Complex expression

Using Selenium to validate XHTML markup using lettuce

I am currently getting started on providing unit tests for Deniz with Selenium. While deniz is pure Javascript (with HTML & CSS) I am using Python with Lettuce (clone of Ruby’s cucumber) to test the application. Lettuce is a behaviour driven development (BDD) tool and makes testing clean and fun.

In this fashion I wanted to check that the W3C XHTML button is placed correctly, i.e. that deniz actually is valid XHTML. So automatic testing comes into play.

Here’s a small receipt to formulate the validation test. There are some quirks when using Firefox that needed a workaround, so I thought I share:

https://gist.github.com/924043

So I found out that while "static" deniz.html is valid XHTML the components that get embedded once Javascript is run breaks the document’s validity.

Now that brings me to a new question: Should AJAX-style webpages stricly adhere to W3C standards while operating, i.e. going through various states of the application, each changing the underlying HTML code? I guess so.