How to query the last element in a database class in Gql (Google Query Language)?

Question

I have this database in GAE:

class Contract(db.Model): # I renamed "Act" as "Contract"
    book_number = db.IntegerProperty(required = True)
    initial_page = db.IntegerProperty(required = True)
    final_page = db.IntegerProperty(required = True)
    date = db.DateProperty(auto_now_add=True)

And I want to query this db to pick the value of book_number, initial_page and final_page of last added element in this db, puting each value in one variable. In MySQL, I'd do this:

SELECT MAX(book_number) AS Last FROM Contracts;

But how I do that in GAE (GglQuery)? Where can I find all commands in Gql? (I didn't find it here or here)

I'm using Python 2.7 GAE.

Here my entire code:

# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
#!/usr/bin/env python
#
# Copyright 2007 Google Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

import os

import webapp2

import jinja2

jinja_environment = jinja2.Environment(autoescape=True,
    loader=jinja2.FileSystemLoader(os.path.join(os.path.dirname(__file__), 'templates')))

import re

from google.appengine.ext import db

USER_RE = re.compile(r"^[a-zA-Z0-9_ -]{3,20}$")
def valid_person(person):
    return USER_RE.match(person)

PASS_RE = re.compile(r"^.{3,20}$")
def valid_SSN(SSN):
    return PASS_RE.match(SSN)

EMAIL_RE = re.compile(r"^[\S]+@[\S]+\.[\S]+$")
def valid_email(email):
    return EMAIL_RE.match(email)

import time

import datetime


##def getNumBookPg():
##    doc = open("numbookpg.txt")
##    numBookPg = []
##    numbers = doc.read().split()
##    for n in numbers:
##        numBookPg.append(n)
##    return numBookPg
##
##numBookPage = getNumBookPg()

##def updateNumBookPg(b, fp):
##    newNumBookPg = b + ' ' + str(int(fp) + 1) + ' ' + str(int(fp) + 1)
##    doc = open("numbookpg.txt", "w")
##    doc.write(newNumBookPg)

def dateToday():
    today = datetime.datetime.today()
    todayDay = str(today.day)
    todayMonth = str(today.month)
    monthExt = {'1':' January ', '2':'February', '3':' March ', '4':'April', '5':'May', '6':'June', '7':' July ', '8':'August', '9':'September', '10':'October', '11':'November ', '12':'December'}
    todayYear = str(today.year)
    return(todayDay + ' of  ' + monthExt[todayMonth] + ' of ' + todayYear)

class MainHandler(webapp2.RequestHandler):
    def get(self):
        q = db.GqlQuery("SELECT * FROM Contract ORDER BY date DESC")
        contract = q.get() # return only the first entity
        numBook = contract.book_number
        numInitialPage = contract.initial_page
        numFinalPage = contract.final_page
        template_values = {"person": "",
                                       "nacionality": "",
                                       "SSN": "",
                                       "driverLicense": "",
                                       "email":"",
                                       "person_error": "",
                                       "SSN_error": "",
                                       "driverLicense_error": "",
                                       "address": "",
                                       "email_error": "",
                                       "numBook": numBook,
                                       "numInitialPage": numInitialPage,
                                       "numFinalPage": numFinalPage,
                                       }
        template = jinja_environment.get_template('index.html')
        self.response.out.write(template.render(template_values))

##    def write_form(self, text_write):
##        self.response.out.write(text_write)

    def post(self):
        person_name = self.request.get("person")
        user_nacionality = self.request.get('nacionality')
        user_profession = self.request.get('profession')
        user_maritalStatus = self.request.get('maritalStatus')
        user_SSN = self.request.get('SSN')
        user_email = self.request.get('email')
        user_driverLicense = self.request.get('driverLicense')
        person_error = ""
        SSN_error = ""
        driverLicense_error = ""
        geted_email_error = ""
        address = self.request.get('address')
        contractType = self.request.get("contractType")
        owner = self.request.get("owner")
        witness = self.request.get("witness")
        numBook = self.request.get("numBook")
        numInitialPage = self.request.get("numInitialPage")
        numFinalPage = self.request.get("numFinalPage")

        if (person_name and valid_person(person_name)) and (user_SSN and valid_SSN(user_SSN)) and ((not user_email) or (user_email and valid_email(user_email))):
            a = Person(firstName = person_name,
                                   nacionality = user_nacionality,
                                   maritalStatus = user_maritalStatus,
                                   profession = user_profession,
                                   SSN = int(user_SSN),
                                   driverLicense = int(user_driverLicense)
                                   #address = user_address
                                   )
            a.put()
            self.redirect('/your_contract?person=%s&nacionality=%s&profession=%s&maritalStatus=%s&SSN=%s&driverLicense=%s&email=%s&witness=%s&owner=%s&contractType=%s&address=%s&numBook=%s&numInitialPage=%s&numFinalPage=%s' % (person_name, user_nacionality, user_profession, user_maritalStatus, user_SSN, user_driverLicense, user_email,
witness, owner, contractType, address, numBook, numInitialPage, numFinalPage))

        else:
            if not person_name or not valid_person(person_name):
                person_error = "Oh no!!! this person name isn't valid!"
            if not user_SSN or not valid_SSN(user_SSN):
                SSN_error = "Oh no!!! SSN isn't valid!"
            if user_email and not valid_email(user_email):
                geted_email_error = "Oh no!!! e-mail isn't valid!"
            template_values = {"person": person_name,
                                "nacionality": user_nacionality,
                                "maritalStatus": user_maritalStatus,
                                "profession": user_profession,
                                "SSN": user_SSN,
                                "driverLicense": user_driverLicense,
                                "email": user_email,
                                "person_error": person_error,
                                "SSN_error": SSN_error,
                                "driverLicense_error": user_driverLicense,
                                "address": address,
                                "email_error": geted_email_error}
            template = jinja_environment.get_template('index.html')
            self.response.out.write(template.render(template_values))

class your_contractHandler(webapp2.RequestHandler):
    def get(self):
        geted_person_name = self.request.get('person')
        geted_user_nacionality = self.request.get("nacionality")
        geted_user_profession = self.request.get("profession")
        geted_user_maritalStatus = self.request.get("maritalStatus")
        geted_user_SSN = self.request.get('SSN')
        geted_user_email = self.request.get('email')
        geted_user_driverLicense = self.request.get('driverLicense')
        geted_person_error = ""
        geted_SSN_error = ""
        geted_driverLicense_error = ""
        geted_address = self.request.get('address')
        geted_owner = self.request.get("owner")
        geted_witness = self.request.get("witness")
        geted_contractType = self.request.get("contractType")
        geted_dateToday = dateToday()
        your_contract = jinja_environment.get_template('your_contract.html')
        geted_numBook = self.request.get('numBook')
        geted_numInitialPage = self.request.get('numInitialPage')
        geted_numFinalPage = self.request.get('numFinalPage')

        contract = Contract(book_number = geted_numBook, initial_page = geted_numInitialPage, final_page = geted_numFinalPage)
        contract.put()

        your_contract_values = {"person":geted_person_name,
                                "nacionality":geted_user_nacionality,
                                "maritalStatus": geted_user_maritalStatus,
                                "profession": geted_user_profession,
                                "SSN":geted_user_SSN,
                                "driverLicense":geted_user_driverLicense,
                                "address":geted_address,
                                "email":geted_user_email,
                                "contractType":geted_contractType,
                                "dateContract":geted_dateToday,
                                "numBook":geted_numBook,
                                "numInitialPage":geted_numInitialPage,
                                "numFinalPage":geted_numInitialPage,
                                }
        template = jinja_environment.get_template('index.html')
        self.response.out.write(your_contract.render(your_contract_values))

class Person(db.Model):
    firstName = db.StringProperty(required = True)
#    lastName = db.StringProperty(required = True)
    nacionality = db.StringProperty(required = True)
    maritalStatus = db.StringProperty(required = True)
    profession = db.StringProperty(required = True)
    SSN = db.IntegerProperty(required = True)
    driverLicense = db.IntegerProperty(required = True)
#    address = db.PostalAdressProperty(required = True)
#    condition = db.StringProperty(required = False, choices=set(["buyer", "seller", "renter", "owner", "witness"]))

class Contract(db.Model):
    book_number = db.IntegerProperty(required = True)
    initial_page = db.IntegerProperty(required = True)
    final_page = db.IntegerProperty(required = True)
    date = db.DateTimeProperty(auto_now_add=True)
#    firstPart = db.StringProperty(required = True)
#   secondPart = db.StringProperty(required = True)
#    contractDraft = db.TextProperty(required = True)

app = webapp2.WSGIApplication([('/', MainHandler), ('/your_contract', your_contractHandler)],
                              debug=True)

score 1 · Accepted Answer · answered Jul 05 '12 at 23:51

1

the gql reference is here and the query docs here

try this:

q = db.GqlQuery("SELECT * FROM Act ORDER BY date DESC")
act = q.get() # return only the first entity
a = act.book_number
b = act.initial_page
c = act.final_page

answered Jul 05 '12 at 23:51

Gianni Di Noia

1,577
10
25

Thanks, @presveva, but now I'm getting this error message: " File "C:\Users\CG\Documents\udacity\contract\main.py", line 77, in get numBook = contract.book_number AttributeError: 'NoneType' object has no attribute 'book_number'" Do you know why? ("I've replaced the class name Act by Contract) – craftApprentice Jul 06 '12 at 00:37
1

Use DateTimeProperty rather than DateProperty. If you have more than one item added on the same day you may not get the latest. Also you example SQL doesn't reflect your question. You SQL example gets min and max value for column1 the whole table, but you asked for " book_number, initial_page and final_page of last added element in this db" – Tim Hoffman Jul 06 '12 at 00:59
And you didn't look to hard in the appengine docs for the GQL reference https://developers.google.com/appengine/docs/python/datastore/gqlreference - its listed in the Python docs index for appengine. – Tim Hoffman Jul 06 '12 at 01:02
Thanks for your critics, @Tim, I'll make effort to improve my questions. THe SQL example was to show that I've know how to perform the query in SQL. I search a list of command in GQL reference (as those available for SQL, with lists of commands). But you're right, I didn't look too hard. Despite this, I don't think my question deserved a -1. – craftApprentice Jul 06 '12 at 01:34
Quite a few (nearly all) of your questions in the last few days would have been easily answered if you read the basic overview of appengine. Things like "your question about being unable to write to the filesystem". As I have mentioned before you would save a lot of time if you did spend some time looking at the introduction to appengine. I would also suggest you have a read of all of Nick Johnsons appengine blog articles. http://blog.notdot.net/tag/app-engine He worked for the appengine team and provided great insight on how to do things. – Tim Hoffman Jul 06 '12 at 03:01

How to query the last element in a database class in Gql (Google Query Language)?

1 Answers1