SQLAlchemy smooth queries

October 2, 2009

Suppose we have a table A and a child table B and we need the following queries:

  • Select all records from A where their Title like ‘something’;
  • Select count of records in the result of the previous one;
  • Select all records from A that have children in B and Title like “something”;
  • Select all records from A that don’t have children in B and Title like “something”;
  • Select their counts;
  • you get the idea of set of conditions combined in different ways

I could write queries like “SELECT FROM A WHERE… INNER JOIN …” and then “SELECT COUNT(A.*) FROM…” etc. But in case I need to change conditions I ‘ll have to rewrite  select queries and select count ones. I could also invent some string routine tricks to remove duplication between select query and select count queries. But SQLAlchemy has an ORM which allows  to write queries in the following way:

def getQuery(key):

	Cond1 = A.Title.like("something")
        from sqlalchemy.sql import exists
        from sqlalchemy import and_,desc, or_
	HasChildren = exists().where(B.Parent == A.ID)
	Cond2 = and_(Cond1, HasChildren )
	Cond3  = and_(Cond1, ~ HasChildren )	

	return locals()[key]

def GetQuery(session, conditions):
	return session.query(A).filter(conditions)

def GetByFilter(filterName):
	s = get_session()
		return GetQuery(s, getQuery(filterName)).all()

def GetCount(filterName):
	s = get_session()
		return GetQuery(s, getQuery(filterName)).count()

Now I have:

  • one set of queries both for select and counting;
  • one set of conditions which can be conbined by AND, OR, EXISTS etc.;
  • very-very readable code.

posted in Python by dmitko

Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org