Thread: [SQLObject] Search getting 0 results
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: \Jonathan H. http://JonathansCorner.com\ <jon...@po...> - 2005-11-17 17:46:04
|
I am trying to make a query that will search for documents where the document_keyword reflects one or more occurrences of the search terms. Database_document and database_document_keywords are declared: class database_document(sqlobject.SQLObject): date_last_modified =3D sqlobject.DateTimeCol() date_last_viewed =3D sqlobject.DateTimeCol() #excerpt =3D sqlobject.StringCol() field_permanent_id =3D sqlobject.StringCol() field_text =3D sqlobject.StringCol() field_title =3D sqlobject.StringCol() field_version =3D sqlobject.StringCol() relative_filename =3D sqlobject.StringCol() section =3D sqlobject.StringCol() text =3D sqlobject.StringCol() tokenized =3D sqlobject.StringCol() def _set_tokenized(self, token_list): value =3D " ".join(token_list) self._SO_set_tokenized(value) class database_document_keyword(sqlobject.SQLObject): document_filename =3D sqlobject.StringCol() document_id =3D sqlobject.ForeignKey('database_document') word =3D sqlobject.StringCol() frequency =3D sqlobject.IntCol() total =3D sqlobject.IntCol() def _get_proportion(self): return float(self.frequency) / float(max(1, self.total)) The database_document_keyword(s), along with the database_document row, are (attemptedly) created at the end of the relevant object's constructor, which calls: def to_database(self): creation_time =3D datetime.datetime.now() database_document_id =3D database_document(date_last_modified =3D \ creation_time, date_last_viewed =3D creation_time, field_permanen= t_id \ =3D self.get_field("PermanentID"), field_text =3D \ self.get_field("Text"), field_title =3D \ self.get_field("Title"), field_version =3D self.get_field("Versio= n"), \ relative_filename =3D self.relative_filename, section =3D \ self.section.get_html_name(), \ text =3D self.get_file_contents(), tokenized =3D \ self.get_all_tokens()).id for current_word in self.histogram.get_words(): #debug_log("read_file: " + current_word) database_document_keyword(document_filename =3D \ self.get_relative_filename(), document_id =3D \ database_document_id, word =3D current_word, frequency =3D \ self.histogram.get_occurrences(current_word), total =3D self.histogram.get_total_occurrences()) And last, here is the code to retrieve database_document rows corresponding to a match: #query =3D sqlobject.sqlbuilder.Select(database_document, \ #((database_document.q.section =3D=3D restricted_section) | \ #(restricted_section =3D=3D None))) query =3D None debug_log(tokenize(string.lower(get_cgi("query")))) for keyword in tokenize(string.lower(get_cgi("query"))): debug_log(keyword) subquery =3D sqlobject.sqlbuilder.EXISTS(sqlobject.sqlbuilder.Select(database_document_k= eyword.q.id, \ (database_document.q.id =3D=3D \ database_document_keyword.q.document_id) & \ (database_document_keyword.q.word =3D=3D keyword))) if query: query =3D sqlobject.AND(query, subquery) else: query =3D subquery if get_cgi("format_choice") =3D=3D "timestamp": to_order_by =3D database_document.q.date_last_modified elif get_cgi("format_choice") =3D=3D "viewing_timestamp": to_order_by =3D database_document.q.date_last_viewed else: to_order_by =3D database_document.q.date_last_viewed result =3D database_matches_to_list(database_document.select(query,= \ orderBy=3Dto_order_by)) database_matches_to_list creates a list of row objects, and is always returning 0 results (I created documents containing the word "test", and then searched for "test"). Is there a reason I should be getting 0 results? -- ++ Jonathan Hayward, jon...@po... ** To see an award-winning website with stories, essays, artwork, ** games, and a four-dimensional maze, why not visit my home page? ** All of this is waiting for you at http://JonathansCorner.com ** If you'd like a Google Mail (gmail.com) account, please tell me! |
From: Ian B. <ia...@co...> - 2005-11-18 00:09:10
|
"Jonathan Hayward http://JonathansCorner.com" wrote: > database_matches_to_list creates a list of row objects, and is always > returning 0 results (I created documents containing the word "test", > and then searched for "test"). > > Is there a reason I should be getting 0 results? When this sort of thing happens, it's usually useful to see the SQL queries leading up. Append ?debug=t to your database URI, or just do ._connection.debug = True, and it will start printing out the SQL queries as it runs them. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: \Jonathan H. http://JonathansCorner.com\ <jon...@po...> - 2005-11-18 13:27:50
|
On 11/17/05, Ian Bicking <ia...@co...> wrote: > > "Jonathan Hayward http://JonathansCorner.com" wrote: > > database_matches_to_list creates a list of row objects, and is always > > returning 0 results (I created documents containing the word "test", > > and then searched for "test"). > > > > Is there a reason I should be getting 0 results? > > When this sort of thing happens, it's usually useful to see the SQL > queries leading up. Append ?debug=3Dt to your database URI, or just do > ._connection.debug =3D True, and it will start printing out the SQL > queries as it runs them. When I did this, it appeared that no SQL was being executed from the search= . I had debugging output like: debug_log(tokenize(string.lower(get_cgi("query")))) This is the tail end of the debugging output after it initialized and ran two queries for the word "test": [SQLObject debug output] 1/QueryOne: SELECT document_filename, document_id, word, frequency, total FROM database_document_keyword WHERE id =3D 10152 1/QueryR : SELECT document_filename, document_id, word, frequency, total FROM database_document_keyword WHERE id =3D 10152 1/COMMIT : auto ['test'] at 1132301976.82 test at 1132301976.82 Search results: [] at 1132301976.82 ['test'] at 1132302040.52 test at 1132302040.52 Search results: [] at 1132302040.53 The top SQLObject-generated lines appear to be from initialization; the bottom six lines are my own debugging output, and I would have expected tha= t they be interspersed with SQL lines. Does the following code need something else to actually look up the values? #query =3D sqlobject.sqlbuilder.Select(database_document, \ #((database_document.q.section =3D=3D restricted_section) | \ #(restricted_section =3D=3D None))) query =3D None debug_log(tokenize(string.lower(get_cgi("query")))) for keyword in tokenize(string.lower(get_cgi("query"))): debug_log(keyword) subquery =3D sqlobject.sqlbuilder.EXISTS(sqlobject.sqlbuilder.Select(database_document_k= eyword.q .id, \ (database_document.q.id =3D=3D \ database_document_keyword.q.document_id) & \ (database_document_keyword.q.word =3D=3D keyword))) if query: query =3D sqlobject.AND(query, subquery) else: query =3D subquery if get_cgi("format_choice") =3D=3D "timestamp": to_order_by =3D database_document.q.date_last_modified elif get_cgi("format_choice") =3D=3D "viewing_timestamp": to_order_by =3D database_document.q.date_last_viewed else: to_order_by =3D database_document.q.date_last_viewed result =3D database_matches_to_list(database_document.select(query, \ orderBy=3Dto_order_by)) -- > Ian Bicking / ia...@co... / http://blog.ianbicking.org > -- ++ Jonathan Hayward, jon...@po... ** To see an award-winning website with stories, essays, artwork, ** games, and a four-dimensional maze, why not visit my home page? ** All of this is waiting for you at http://JonathansCorner.com ** If you'd like a Google Mail (gmail.com <http://gmail.com>) account, please tell me! |
From: \Jonathan H. http://JonathansCorner.com\ <jon...@po...> - 2005-11-18 21:54:50
|
I found a possible clue. I expanded result =3D database_matches_to_list(database_document.select(query, \ orderBy=3Dto_order_by)) to: database_matches =3D database_document.select(query, orderBy =3D \ to_order_by) debug_log("Raw search results: " + str(database_matches)) result =3D database_matches_to_list(database_matches) debug_log("Search results: " + str(result)) The output suggests that database_matches is not a database match but a built-up SQL query: Raw search results: SELECT database_document.id, database_document.date_last_modified, database_document.date_last_viewed, database_document.field_permanent_id, database_document.field_text, database_document.field_title, database_document.field_version, database_document.relative_filename, database_document.section, database_document.text, database_document.tokenized FROM database_document WHERE EXISTS (SELECT database_document_keyword.id FROM database_document, database_document_keyword WHERE ((database_document.id =3D database_document_keyword.document_id) AND (database_document_keyword.word = =3D 'test'))) ORDER BY database_document.date_last_viewed at 1132326661.36 Search results: [] at 1132326661.38 Is there anything I should be adding to get the results of the query I've built up? On 11/18/05, Jonathan Hayward http://JonathansCorner.com < jon...@po...> wrote: > > > > On 11/17/05, Ian Bicking <ia...@co...> wrote: > > > > "Jonathan Hayward http://JonathansCorner.com" wrote: > > > database_matches_to_list creates a list of row objects, and is always > > > returning 0 results (I created documents containing the word "test", > > > and then searched for "test"). > > > > > > Is there a reason I should be getting 0 results? > > > > When this sort of thing happens, it's usually useful to see the SQL > > queries leading up. Append ?debug=3Dt to your database URI, or just do > > ._connection.debug =3D True, and it will start printing out the SQL > > queries as it runs them. > > > When I did this, it appeared that no SQL was being executed from the > search. I had debugging output like: > > debug_log(tokenize(string.lower(get_cgi("query")))) > > This is the tail end of the debugging output after it initialized and ran > two queries for the word "test": > > [SQLObject debug output] > 1/QueryOne: SELECT document_filename, document_id, word, frequency, total > FROM database_document_keyword WHERE id =3D 10152 > 1/QueryR : SELECT document_filename, document_id, word, frequency, total > FROM database_document_keyword WHERE id =3D 10152 > 1/COMMIT : auto > ['test'] at 1132301976.82 > test at 1132301976.82 > Search results: [] at 1132301976.82 > ['test'] at 1132302040.52 > test at 1132302040.52 > Search results: [] at 1132302040.53 > > The top SQLObject-generated lines appear to be from initialization; the > bottom six lines are my own debugging output, and I would have expected t= hat > they be interspersed with SQL lines. > > Does the following code need something else to actually look up the > values? > > #query =3D sqlobject.sqlbuilder.Select(database_document, \ > #((database_document.q.section =3D=3D restricted_section) | \ > #(restricted_section =3D=3D None))) > query =3D None > debug_log(tokenize(string.lower(get_cgi("query")))) > for keyword in tokenize(string.lower(get_cgi("query"))): > debug_log(keyword) > subquery =3D > sqlobject.sqlbuilder.EXISTS(sqlobject.sqlbuilder.Select(database_document= _keyword.q > .id, > \ > (database_document.q.id =3D=3D \ > database_document_keyword.q.document_id) & \ > (database_document_keyword.q.word =3D=3D keyword))) > if query: > query =3D sqlobject.AND(query, subquery) > else: > query =3D subquery > if get_cgi("format_choice") =3D=3D "timestamp": > to_order_by =3D database_document.q.date_last_modified > elif get_cgi("format_choice") =3D=3D "viewing_timestamp": > to_order_by =3D database_document.q.date_last_viewed > else: > to_order_by =3D database_document.q.date_last_viewed > result =3D database_matches_to_list(database_document.select(query, \ > orderBy=3Dto_order_by)) > > > -- > > Ian Bicking / ia...@co... / http://blog.ianbicking.org > > > > > > -- > ++ Jonathan Hayward, jon...@po... > ** To see an award-winning website with stories, essays, artwork, > ** games, and a four-dimensional maze, why not visit my home page? > ** All of this is waiting for you at http://JonathansCorner.com > > ** If you'd like a Google Mail ( gmail.com <http://gmail.com>) account, > please tell me! > -- ++ Jonathan Hayward, jon...@po... ** To see an award-winning website with stories, essays, artwork, ** games, and a four-dimensional maze, why not visit my home page? ** All of this is waiting for you at http://JonathansCorner.com ** If you'd like a Google Mail (gmail.com <http://gmail.com>) account, please tell me! |
From: Alan F. <ala...@gm...> - 2005-11-20 21:09:35
|
"Jonathan Hayward http://JonathansCorner.com" on gmane.comp.python.sqlobject said: > database_matches_to_list creates a list of row objects, and is always > returning 0 results (I created documents containing the word "test", > and then searched for "test"). I'm a bit tricked by all your code (it's kind of hard to read), couldn't you write a stripped-down version (less lines, less functions) sharing the same issue? Also... I can't understand what you mean: Are you getting an empty list? Or does list index 0 (at least) contain anything? And have you tried fetching actual results from the DB before checking debug output? -- Alan Franzoni <ala...@gm...> -- Togli .xyz dalla mia email per contattarmi Remove .xyz from my email in order to contact me. |
From: \Jonathan H. http://JonathansCorner.com\ <jon...@po...> - 2005-11-21 00:21:03
|
On 11/18/05, Alan Franzoni <ala...@gm...> wrote: > > "Jonathan Hayward http://JonathansCorner.com" on > gmane.comp.python.sqlobject said: > > > database_matches_to_list creates a list of row objects, and is always > > returning 0 results (I created documents containing the word "test", > > and then searched for "test"). > > I'm a bit tricked by all your code (it's kind of hard to read), couldn't > you write a stripped-down version (less lines, less functions) sharing th= e > same issue? I can't do that right now because my computer is in the middle of the upgrade. But there is a specific part I think I can excerpt: database_document extends SQLObject and adds text and datetime columns. query and to_order_by have been built up. There is something specific that surprises me in this code: database_matches =3D database_document.select(query, orderBy =3D \ to_order_by) debug_log("Raw search results: " + str(database_matches)) result =3D database_matches_to_list(database_matches) I want database_matches to have the database_document rows matching query and ordered by to_order_by. The debug_log says that database_matches is a string: Raw search results: SELECT database_document.id, database_document.date_last_modified, database_document.date_last_viewed, database_document.field_permanent_id, database_document.field_text, database_document.field_title, database_document.field_version, database_document.relative_filename, database_document.section, database_document.text, database_document.tokenized FROM database_document WHERE EXISTS (SELECT database_document_keyword.id FROM database_document, database_document_keyword WHERE ((database_document.id =3D database_document_keyword.document_id) AND (database_document_keyword.word = =3D 'test'))) ORDER BY database_document.date_last_viewed at 1132326661.36 What I'd like to know is what how to take what's in database_matches now, and execute the query so that I can access results.get(0), results.get(1). If a stripped-down version would help, I can probably get to work on that, but what's puzzling me now is that database_matches is assigned a SQL statement instead of being a result set. How do I get a result set now that I have the query I want? Thanks, Also... I can't understand what you mean: Are you getting an empty list? Or > does list index 0 (at least) contain anything? And have you tried fetchin= g > actual results from the DB before checking debug output? > > -- > Alan Franzoni <ala...@gm...> > -- > Togli .xyz dalla mia email per contattarmi > Remove .xyz from my email in order to contact me. > > > > ------------------------------------------------------- > This SF.Net email is sponsored by the JBoss Inc. Get Certified Today > Register for a JBoss Training Course. Free Certification Exam > for All Training Attendees Through End of 2005. For more info visit: > http://ads.osdn.com/?ad_id=3D7628&alloc_id=3D16845&op=3Dclick > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- ++ Jonathan Hayward, jon...@po... ** To see an award-winning website with stories, essays, artwork, ** games, and a four-dimensional maze, why not visit my home page? ** All of this is waiting for you at http://JonathansCorner.com ** If you'd like a Google Mail (gmail.com <http://gmail.com>) account, please tell me! |