Thread: [SQLObject] Form fields and SQLObject
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <mar...@ge...> - 2004-03-02 22:12:29
|
I am new to Python and SQLObject and I am looking for tips on how to do a search in a web application that uses SQLObject. For testing/learning purposes I am using the Person class from the SQlObject: from SQLObject import * class Person(SQLObject): _connection = MySQLConnection(host='localhost', db='test', user='*', passwd='*', debug=1) firstName = StringCol(length=100) middleInitial = StringCol(length=1, default=None) lastName = StringCol(length=100) So far I have a findPerson method that looks like this: def findPerson(self, id, fn, mi, ln): persons = Person.select( AND(Person.q.firstName.startswith(fn), Person.q.middleInitial.startswith(mi), Person.q.lastName.startswith(ln))) return persons Where id, fn, mi and ln are the values of the fields in my search form - not very impressive and problematic in many ways I know ! What I would like is to implement a findPerson method that, based on the field values incl. any operators - *, <, >, <=, >= and so on, is able to perform a proper search and return all matching Person objects(rows). I have fiddled around with some inelegant string substitution to ugly to post. Especially properties - in this case middleInitial - that defaults to None, are troublesome, since an empty field is not matched by None. Is there a elegant preferably generic solution for the above ? by generic I mean somthing that would work irrespective of the specific class. I am using Python 2.3 on Windows 2000 and SQLObject 0.5.1. Ian Bicking - super cool tool. Regards, Martin ------------------------------------------------- WebMail fra Tele2 http://www.tele2.dk ------------------------------------------------- |
From: Chris G. <ch...@il...> - 2004-03-04 18:11:24
|
mar...@ge... wrote: > So far I have a findPerson method that looks like this: > > def findPerson(self, id, fn, mi, ln): > persons = Person.select( > AND(Person.q.firstName.startswith(fn), > Person.q.middleInitial.startswith(mi), > Person.q.lastName.startswith(ln))) > return persons > [...] > What I would like is to implement a findPerson method that, based on the field values [...] Is there a elegant preferably generic solution for the above ? by generic I mean somthing that would work irrespective of the specific class. The best way to do this is, I believe, is not to use those Person.q methods. "q" is an SQLBuilder object, which creates regular SQL queries when you compare it with strings and stuff. Example: >>> fname = "John" >>> lname = "Doe" >>> AND(Person.q.first_name == fname, person.last_name == lname) (person.first_name = 'John AND person.last_name = 'Doe') ^^-- the comparison returns that string. Any expression involving Person.q's will be converted into a string, which is what the Person.select() method actually uses to do the query... its parameter is a regular SQL expression (everything after the WHERE part of "SELECT whatever FROM table"). So, the best way to do this is to write a clever SQL query that uses the "LIKE" operator, which can do fuzzy matches. Here's how it works: http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1250 It would be something like: Person.select("firstname LIKE %"+fn+"%"). BUT, be careful that the variables you give the SELECT statement (fn, mi, etc) are properly quoted and escaped, otherwise someone could hax0r your database. :) |
From: Ian B. <ia...@co...> - 2004-03-04 18:26:45
|
mar...@ge... wrote: > What I would like is to implement a findPerson method that, based on > the field values incl. any operators - *, <, >, <=, >= and so on, is > able to perform a proper search and return all matching Person > objects(rows). I have fiddled around with some inelegant string > substitution to ugly to post. Especially properties - in this case > middleInitial - that defaults to None, are troublesome, since an > empty field is not matched by None. > > Is there a elegant preferably generic solution for the above ? by > generic I mean somthing that would work irrespective of the specific > class. You might want to look back through the mailing lists for a thread "Looking for selection hints" started by Frank Barknecht around 10/2003. He was doing some similar stuff. > I am using Python 2.3 on Windows 2000 and SQLObject 0.5.1. > > Ian Bicking - super cool tool. Thanks ;) Cheers, Ian |
From: Frank B. <fb...@fo...> - 2004-03-04 18:50:47
|
Hallo, Ian Bicking hat gesagt: // Ian Bicking wrote: > You might want to look back through the mailing lists for a thread > "Looking for selection hints" started by Frank Barknecht around 10/2003. > He was doing some similar stuff. Yes, I posted a QueryBuilder.py, which unfortunatly now grew into quite a custom, application specific monstrosity (I shouldn't have done this, I know), so I don't have it anymore, but it's still in the archives here: http://sourceforge.net/mailarchive/message.php?msg_id=6379871 Also you might want to take a look at another recent archive post, where I talked with myself about using fulltext indexes on Postgres, which showed another use case, namely creating your own "LIKE"-like operators: http://sourceforge.net/mailarchive/message.php?msg_id=7076046 ciao -- Frank Barknecht _ ______footils.org__ |