GQL queries (GAE datastore Python): how to retrieve all entities with same tag and all entities containing a substring?

Question

I have a bilingual dictionary database of 60000 pairs stored in GAE that's look like this:

date, tag, value

date, vis, screw

date, vis, screws

date, vis à bois, wood screw

date, vis à bout pointu, cone point screw

date, vis à braser, braze screw

date, vis à métaux, machine screw

.

Today with the following python script:

def get_value(self, tag):
  entry = db.GqlQuery("SELECT * FROM StoredData where tag = :1", tag).get()
  value = entry.value
  value = unicode(value)
  value = value.encode('ascii','xmlcharrefreplace')

if I ask for "vis" I only get "screw" in response.

Questions:

1. I would like to get also "screws" as a second answer, that is to say retrieve all entities having the same tag.

2. I would like to get also all entities containing the substring "screw" (with a minimum of 3 characters typed by user) :

screw, screws, wood screw, cone point screw, braze screw, machine screw

=> what GQL queries have I to make to show them in a table generated by this script ?

### Show the tags and values as a table.
###def show_stored_data(self):
###  self.response.out.write('''
###    <p><table border=1>
###      <tr>
###         <th>Key</th>
###         <th>Value</th>
###         <th>Created (GMT)</th>
###      </tr>''')
###  entries = db.GqlQuery("          ***GQL query n°1 or n°2***           ")
###  for e in entries:
###    entry_key_string = str(e.key())
###    self.response.out.write('<tr>')
###    self.response.out.write('<td>%s</td>' % e.tag)
###    self.response.out.write('<td>%s</td>' % e.value)
###    self.response.out.write('<td><font size="-1">%s</font></td>\n' % e.date.ctime())
###    self.response.out.write('''
###      <td><form action="/deleteentry" method="post"
###            enctype=application/x-www-form-urlencoded>
###     <input type="hidden" name="entry_key_string" value="%s">
###     <input type="hidden" name="tag" value="%s">
###            <input type="hidden" name="fmt" value="html">
###     <input type="submit" style="background-color: red" value="Delete"></form></td>\n''' %
###                            (entry_key_string, e.tag))
###    self.response.out.write('</tr>')
###  self.response.out.write('</table>')

Many thanks in advance for your help.

Philippe

Answer 1

Getting only one result

Here is your problem--it's that you're using the .get() method on your GqlQuery object. The documentation says:

Call the query's get() method, to obtain the first single matching entity found in the Datastore:

If you do this:

def get_value(self, tag):
  entries = db.GqlQuery("SELECT * FROM StoredData where tag = :1", tag).get()
  for entry in entries:
    value = entry.value
    value = unicode(value)
    value = value.encode('ascii','xmlcharrefreplace')
    print value

I expect that you'd see that you'll be getting both screw and screws.

Substring

As for grabbing all strings with "screw", that's going to be a lot harder than you bargained for. Take a look at this SO question. Some answers recommend that you use SearchableModel.

For examples like vis à <blank>, where you know that the phrase is always starting with the sub-tag in question, you can probably play around with > and < to get what you're looking for, maybe seeing if the tag is between vis and vis zzzzzzz, or something along those lines--I'm not 100% clear on GQL's string sort order, and I can't find documentation on it right now.

Answer 2

Sounds like you want full text search: https://developers.google.com/appengine/docs/python/search/overview