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
Advertisements

One comment


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s