sqlobject-discuss

[SQLObject] Looking for selection hints

[Frank B. <fb...@fo...>]

Hi all,

I could need a little help with understanding how to build
SQLObject selections dynamically. 

So far, I have a dictionary that describes the criteria to search the
DB. Keys are my Columns, values are lists of search words. Like this: 

 {'title': ['rock'], 'format': ['cd'], 'artist': ['strokes', 'the']}

That should become like this: 

results = Product.select( AND( 
			 LIKE(Product.q.title, "rock"),
			 LIKE(Product.q.format, "cd"),
			 LIKE(Product.q.artist, "strokes"),
			 LIKE(Product.q.artist, "the") ))

I know I could do something like 
  
  eval(""" LIKE(Product.q.%s, "%s" """, (key, value))

but this is so ugly, it just feels very wrong. I also could build the
SQL directly, but this feels still a little bit wrong, although might
be okay. 

My main problem is: How do I do the mapping between attribute names
(like the keys in my dict) and the attributes themselves (like
Product.q.title or Product.title)? Is there some introspection
available? I found the _SO_columnDict but this didn't help much yet.

But then maybe it's just late. 

Anyway I'd love to hear some opinions on this.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Looking for selection hints

[Ian B. <ia...@co...>]

On Sunday, October 26, 2003, at 04:48 PM, Frank Barknecht wrote:
> I could need a little help with understanding how to build
> SQLObject selections dynamically.
>
> So far, I have a dictionary that describes the criteria to search the
> DB. Keys are my Columns, values are lists of search words. Like this:
>
>  {'title': ['rock'], 'format': ['cd'], 'artist': ['strokes', 'the']}

query = None
for colName, ops in above_dict.items():
     for op in ops:
         subquery =  LIKE(getattr(Product.q, colName), op)
         if query:
             query = AND(query, subquery)
         else:
             query = subquery


It might be nice if Product.q allowed dictionary access too.  And if 
AND(anything, None) return anything.  But this will work too.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] Looking for selection hints

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> On Sunday, October 26, 2003, at 04:48 PM, Frank Barknecht wrote:
> > {'title': ['rock'], 'format': ['cd'], 'artist': ['strokes', 'the']}
> 
> query = None
> for colName, ops in above_dict.items():
>     for op in ops:
>         subquery =  LIKE(getattr(Product.q, colName), op)
>         if query:
>             query = AND(query, subquery)
>         else:
>             query = subquery
> 
> 
> It might be nice if Product.q allowed dictionary access too.  And if 
> AND(anything, None) return anything.  But this will work too.

I *knew* it had to be something basic (or built-in). A thousand thanks
for opening my eyes.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Looking for selection hints

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> Hallo,
> Ian Bicking hat gesagt: // Ian Bicking wrote:
> 
> > On Sunday, October 26, 2003, at 04:48 PM, Frank Barknecht wrote:
> > > {'title': ['rock'], 'format': ['cd'], 'artist': ['strokes', 'the']}
> > 
> > query = None
> > for colName, ops in above_dict.items():
> >     for op in ops:
> >         subquery =  LIKE(getattr(Product.q, colName), op)
> >         if query:
> >             query = AND(query, subquery)
> >         else:
> >             query = subquery
> > 
> > 
> > It might be nice if Product.q allowed dictionary access too.  And if 
> > AND(anything, None) return anything.  But this will work too.
> 
> I *knew* it had to be something basic (or built-in). A thousand thanks
> for opening my eyes.

So, and to give something back, here's my QueryBuilder class that's
intended to be used with Webware's extraPathInfo and automates
searching via the extra-URL:

### snip ###

from SQLObject import *

class QueryBuilder(object):

	def __init__(self, table, url=""):
		self.__url = url
		self.__sql = None
		self.__conds = {}
		self._table = table
		self._cols = self._table._SO_columnDict.keys()
		id url:
			self.parse()

	def set_url(self, url):
		self.__url = url
		self.parse()
	
	def get_sql(self):
		query = None
		for colName, ops in self.__conds.items():
			for op in ops:                     
				subquery =  LIKE(getattr(self._table.q, colName), "%%%s%%" % op)
				if colName == "id":
					subquery = self._table.q.id == op
				if query:
					query = AND(query, subquery)
				else:
					query = subquery
		return query

	def parse(self):
		cols = self._cols
		cols.append("id")
		u = self.__url.split("/")
		l = len(u)
		i = 0
		conds = {}
		last = None
		while i < l:
			if last and u[i]:
				conds[last] = conds.get(last, [])
				conds[last].append(u[i])
				last = None
			elif u[i] in cols:
				last = u[i] 
			i += 1	
		self.__conds = conds

	url =  property(None, set_url, None, "URL property, write only")
	sql =  property(get_sql, None, None, "SQL property, read only")

### snip ###

Not that elegant, but working fine for "flat" databases.  It's used
like this: 

# Product is an SQLObject with attrs: name, title, description,...
q = QueryBuilder(Product)
q.url = "name/potter/name/harry/description/book/foo/bar/"
print q.sql
print "Number of results: %d " % len (table.select(q.sql))
for n in list(table.select(q.sql)[0:10]):
	print "%s: %s" % (n.name, n.title)


ciao
-- 
 Frank Barknecht                               _ ______footils.org__