Thread: [SQLObject] Generic "find" & findOne method
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Carlos R. <car...@gm...> - 2004-12-21 16:43:40
|
Hi, I'm writing a class method that finds a row in a table using arbitrary column names & values, as in: person.find({'name':'...', 'address':'...'}) find works like a select, but it takes a dictionary with column names and desired values. It search for records by AND'ing all clauses, and returns a list of rows. person.findOne({'name':'...', 'address':'...'}) findOne returns a single record. It raises an exception if no item is found. If more than one item is found, it returns the first one, and no exception is rased. The goal here is not efficiency, but flexibility. When writing code for web applications it's useful to have this generic query feature, and these methods are more convenient than building a select on the fly. If this code is useful, I can contribute a patch back to SQLObject. It involves simple modifications on dbconnection.py and main.py. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2004-12-21 16:54:21
|
On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote: > findOne returns a single record. It raises an exception if no item is > found. If more than one item is found, it returns the first one Which one? Just random? Is there "orderBy" in findOne? > If this code is useful, I can contribute a patch back to SQLObject. It > involves simple modifications on dbconnection.py and main.py. Why dbconnection? Seems like a trivial addition in main.py - just process the dictionary to generate appropriate WHERE clause, and call self.select(). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ma...> - 2004-12-21 16:58:32
|
On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote: > person.find({'name':'...', 'address':'...'}) What is the difference between person.find({'name':'...', 'address':'...'}) and person.select(AND(person.q.name == '...', person.q.address == '...')) ??? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Carlos R. <car...@gm...> - 2004-12-21 20:13:57
|
I'll reply both messages at once, if you don't mind... On Tue, 21 Dec 2004 19:54:12 +0300, Oleg Broytmann <ph...@ma...> wrote: > On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote: > > findOne returns a single record. It raises an exception if no item is > > found. If more than one item is found, it returns the first one > > Which one? Just random? Is there "orderBy" in findOne? Random. The use case for findOne assumes that only one record should be found, anyway. I just don't want to complicate the code or raise an exception if there is more than one record in the result set. More on this later. > > If this code is useful, I can contribute a patch back to SQLObject. It > > involves simple modifications on dbconnection.py and main.py. > > Why dbconnection? Seems like a trivial addition in main.py - just > process the dictionary to generate appropriate WHERE clause, and call > self.select(). For what I could see, it's discouraged to write SQL code inside the SQLObject class (at main.py). Also, the select & get methods already call stuff that is on dbconnection.py. > > person.find({'name':'...', 'address':'...'}) > > What is the difference between > person.find({'name':'...', 'address':'...'}) > and > person.select(AND(person.q.name == '...', person.q.address == '...')) > ??? Let's assume that you have the result of a web form. You can simply feed the form data to findOne as an argument. One possible example (using a slightly different and improved syntax): person.findOne(**form.getData()) There is a good justification for the entire idea. I'm developing a web application, and in the process, I'm trying to maintain the tiers as decoupled as possible. All communication between the interface code and the application code is done using data-only classes, that support both attribute-style and dict-style access. These intermediate data structures can also be serialized using something similar to JSON (a Javascript notation for data exchange). The advantage of this method is that I can automatically test the application code without the need to invoke the interface code, just by feeding some hard-coded data structures to it. The use case for findOne follows as a consequence. Web forms return a bunch of data, that are the keys to locate a unique record in the database. Instead of calling a table.byField() method (using an alternate key), it's easier and more dynamic just to pass the fields & values to a generic findOne method. It also makes a little bit easier to handle the case of composite unique keys, which aren't support by SQLObject yet. ** As a curiosity, Delphi had such a method (I think it was called locate) as part of its database API. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2004-12-21 20:33:18
|
On Tue, Dec 21, 2004 at 06:12:39PM -0200, Carlos Ribeiro wrote: > Let's assume that you have the result of a web form. You can simply > feed the form data to findOne as an argument. One possible example > (using a slightly different and improved syntax): > > person.findOne(**form.getData()) Are you passing form data to SQL without validation?! Wow!! Isn't there a security risk? Well, I am against such addition to the SQLObject. It is too specific. You can easily do it in your own project by creating a parent class like this: class Finding(SQLObject): def findOne(self, dict): ... and use it as the base for all your tables: class Person(Finding): ... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Carlos R. <car...@gm...> - 2004-12-21 21:40:06
|
On Tue, 21 Dec 2004 23:33:13 +0300, Oleg Broytmann <ph...@ma...> wrote: > On Tue, Dec 21, 2004 at 06:12:39PM -0200, Carlos Ribeiro wrote: > > Let's assume that you have the result of a web form. You can simply > > feed the form data to findOne as an argument. One possible example > > (using a slightly different and improved syntax): > > > > person.findOne(**form.getData()) > > Are you passing form data to SQL without validation?! Wow!! Isn't > there a security risk? Don't worry, validation is being done. That's the problem of simplifications -- the example was intentionally simple just to illustrate the use case. As I've told you, I'm trying to keep modules as decoupled as possible. I'm also aggressively using TDD, and this approach makes writing unit tests really easy. Data validity is checked at *every* step. The form itself does some validation, using client-side Javascript code. When the HTTP request is handled, the form data is also passed to a validation object, using the same intermediate representation for a slightly stricter validation (it also avoids the problem that would occur if data was sent directly without passing by the client-side validation; for example, if urllib were used to bypass it). Also, code would only get that far after passing several security checks (login, session management, etc.). In the end, it's something along these lines (pseudo code, untested, not optimized): # builds a validator object using the data fetched from the form validator = MyValidator(form.getData()) # warning and errors are lists of objects; if empty, everything is ok if not validator.warnings and not validator.errors: person.findOne(**form.getData()) At this point, please bear in mind that I am just *locating* a record, not filling it with data. So most of the security issues that you point out are not really valid. Also, please note that the code on the findOne() method iterates over the keys, and only reads values if the key is a valid column name, which avoids the risk of a security exploit. > Well, I am against such addition to the SQLObject. It is too > specific. You can easily do it in your own project by creating a parent > class like this: > > class Finding(SQLObject): > def findOne(self, dict): > ... > > and use it as the base for all your tables: > > class Person(Finding): > ... While I respect you opinion, I still think that the proposed function is useful enough. However, I think that I may not have presented it clearly enough. I'll try to write a less verbose (and cleaner) explanation and present it again. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2004-12-22 09:14:39
|
On Tue, Dec 21, 2004 at 07:39:58PM -0200, Carlos Ribeiro wrote: > At this point, please bear in mind that I am just *locating* a record, > not filling it with data. So most of the security issues that you > point out are not really valid. There is a huge security risk even in locating data. Does the term "SQL injection" appeal to you?! > While I respect you opinion, I still think that the proposed function > is useful enough. However, I think that I may not have presented it > clearly enough. I'll try to write a less verbose (and cleaner) > explanation and present it again. Please do. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Carlos R. <car...@gm...> - 2004-12-22 18:29:16
|
Well, It turns out that Ian's time machine is almost as good as Guido's one. There *is* a find() method on the current SQLObject; it's called selectBy and it's not documented anywhere else. I only found it today while looking for something else. BTW, I admit being ashamed about it :-( Now, just out of curiosity: is selectBy undocumented just for lack of time, or is it because it's not recommended? -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2004-12-22 19:02:59
|
On Wed, Dec 22, 2004 at 04:29:07PM -0200, Carlos Ribeiro wrote: > It turns out that Ian's time machine is almost as good as Guido's one. > There *is* a find() method on the current SQLObject; it's called > selectBy and it's not documented anywhere else. I only found it today > while looking for something else. BTW, I admit being ashamed about it > :-( Oops, me too. I seldom use it, and have forgotten about it. > Now, just out of curiosity: is selectBy undocumented just for lack of > time, or is it because it's not recommended? There is not much documentation for SQLObject, alas! :( Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Ian B. <ia...@co...> - 2004-12-29 16:58:48
|
Carlos Ribeiro wrote: > It turns out that Ian's time machine is almost as good as Guido's one. > There *is* a find() method on the current SQLObject; it's called > selectBy and it's not documented anywhere else. I only found it today > while looking for something else. BTW, I admit being ashamed about it > :-( > > Now, just out of curiosity: is selectBy undocumented just for lack of > time, or is it because it's not recommended? Forgetfulness. I've added a note to the svn docs. With reference to the rest of the discussion -- I don't like the findOne function, because it throws things away. I'd rather it raise an error when more than one record was found; I don't think there's a use case for throwing rows away, it's just easy to implement. In most cases alternateID fills the role of select-one, though another way of doing this might be useful. Actually, I think it's mostly useful because of the error issue. If you implement ad hoc findOne-like methods, you'll probably throw away extra columns. Maybe it could be a method for SelectResult methods, like: inst = MyTable.selectBy(...).getOne() But I don't like "getOne" as a method name. SQL injection won't be a problem, since we do all the necessary quoting -- that's part of the point of SQLObject and SQLBuilder, after all. You only have to be sure that all your columns are "public", more or less, since a person might be able to inject their own form variables. But it's also easy to test that your keys are limited to a specific set of columns. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: <pk...@gm...> - 2004-12-22 13:19:54
|
Oleg Broytmann wrote: >On Tue, Dec 21, 2004 at 02:43:38PM -0200, Carlos Ribeiro wrote: > > >>person.find({'name':'...', 'address':'...'}) >> >> > > What is the difference between >person.find({'name':'...', 'address':'...'}) > and >person.select(AND(person.q.name == '...', person.q.address == '...')) > ?? > > I really like the interface of the first version. IMO it's much more "pythonic" and you can easily write generic (i.e not bound to colum names) queries without hardcoding any column names in the body of the method. ATM I can't think of a way to do this using the select(AND(... approach -- besides, it looks very sqlish... just a thought Paul |