Example of what SQLAlchemy can do, and Django ORM cannot -

i've been doing lot of research lately using pyramid sqlalchemy versus keeping current application in django. entire debate, i'm not here discuss that.

what want know is, why sqlalchemy universally considered better django orm? every, if not every, comparison i've found between 2 favors sqlalchemy. assume performance big one, structure of sqlalchemy lets translate sql more smoothly.

but, i've heard harder tasks, django orm impossible use. want scope out how huge of issue can be. i've been reading 1 of reasons switch sqlalchemy when django orm no longer suiting needs.

so, in short, provide query (doesn't have actual sql syntax) sqlalchemy can do, django orm cannot possibly without adding in additional raw sql?

update:

i've been noticing question getting quite attention since first asked, i'd throw in 2 cents.

in end ended using sqlalchemy , must i'm happy decision.

i'm revisiting question provide additional feature of sqlalchemy that, far, i've not been able replicate in django orm. if can provide example of how i'll gladly eat words.

let's want use postgresql function, such similarity(), provides fuzzy comparison (see: finding similar strings postgresql quickly - tl;dr input 2 strings percent similarity).

i've done searching on how using django orm , have found nothing other using raw sql seems apparent documentation: https://docs.djangoproject.com/en/dev/topics/db/sql/.

i.e.

model.objects.raw('select * app_model order \ similarity(name, %s) desc;', [input_name]) 

sqlalchemy, however, has func(), described here: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func

from sqlalchemy import desc, func session.query(model).order_by(func.similarity(model.name, input_name)) 

this allows generate sql defined sql/postgresql/etc function , not require raw sql.

this dangerously close being non-constructive, i'll bite.

suppose need maintain inventories of items number of different, let's say, accounts. ddl follows:

create table account (     id serial primary key,     ... );  create table item (     id serial primary key,     name text not null,     ... );  create table inventory (     account_id integer not null references account(id),     item_id integer not null references item(id),     amount integer not null default 0 check (amount >= 0),     primary key (account_id, item_id) ); 

first of all, django orm can't work composite primary keys. yes, can add surrogate key , unique constraint, that's 1 more column , 1 more index need. big table small number of columns add noticeable size , performance overhead. also, orms have trouble identity mapping using other primary key.

now, let's want query each item in inventory of given account accompanied quantity, include items not present there quantity set 0. , sort in descending order quantity. corresponding sql:

select item.id, item.name, ..., coalesce(inventory.amount, 0) amount     item left outer join inventory         on item.id = inventory.item_id , inventory.team_id = ?     order amount desc; 

there no way express outer join custom condition in django orm. yes, can make 2 simple separate queries , perform join hand in python loop. , performance won't suffer in particular case. that's beside point because results of every query reproduced on application side using basic selects.

with sqlalchemy:

class account(base):     __tablename__ = 'account'     id = column(integer, primary_key=true)     ...  class item(base):     __tablename__ = 'item'     id = column(integer, primary_key=true)     name = column(string, nullable=false)     ...  class inventory(base):     __tablename__ = 'inventory'     account_id = column(integer, foreignkey('account.id'), primary_key=true,             nullable=false)     account = relationship(account)     item_id = column(integer, foreignkey('item.id'), primary_key=true,             nullable=false)     item = relationship(item)     amount = column(integer, checkconstraint('amount >= 0'), nullable=false,             default=0)  account = session.query(account).get(some_id) result = (session     .query(item, func.coalesce(inventory.amount, 0).label('amount'))     .outerjoin(inventory,         and_(item.id==inventory.item_id, inventory.account==account))     .order_by(desc('amount'))     .all()) 

as side note, sqlalchemy makes dictionary based collections easy. addition of following code account model make relationship inventory appear is: mapping items quantity.

items = relationship('inventory',     collection_class=attribute_mapped_collection('item_id')) inventory = association_proxy('items', 'amount',     creator=lambda k, v: inventory(item_id=k, amount=v)) 

this enables write code such as:

account.inventory[item_id] += added_value 

that transparently inserts or updates entries in inventory table.

complex joins, subqueries, window aggregates — django orm fails deal of without falling raw sql.