For my final project, I query my database for a the ID of a city whose Name I already have. However, SQL returns no such city even when there is one.

Code (Line with error is surrounded by stars):

@app.route("/clients/create", methods=["GET", "POST"])
def create_clients():
    id = session['user_id']
    introle = int(db.execute("SELECT role FROM users WHERE userId = :id;", id=id)[0]['role'])
    if introle == 1:
        role = 'admin'
    elif introle == '2':
        role = 'engineer'
    if request.method == "GET":
        raw_cities = db.execute("SELECT cityName FROM cities")
        cities = []
        for city in raw_cities:
        return render_template("createclients.html", user_id=role, cities=cities)
    elif request.method == "POST":
        name = request.form.get("name")
        if not name:
            return apology("Missing Client Name")
        tcity = request.form.get("city")
        if not tcity:
            return apology("Missing City")
        **city = int(db.execute("SELECT cityId FROM cities WHERE cityName = :name", name=tcity)[0]['cityId'])**
        province = db.execute("SELECT provinceId FROM cities, provinces WHERE cities.cityId = provinces.provinceId AND cities.cityId = :id", id=city)[0]['provinceId']
        cname = request.form.get("cname")
        if not cname:
            return apology("Missing Conatact Name")
        cemail = request.form.get("cemail")
        if not cemail:
            return apology("Missing Conatact Email")
        cphone = request.form.get("cphone")
        if not cphone:
            return apology("Missing Contact Phone Number")
        inserted = db.execute("INSERT INTO clients (clientName, cityId, provinceId, contactName, contactEmail, contactPhone) VALUES(:name, :city, :province, :cname, :cemail, :cphone);", name=name.lower(), city=city, province=province, cname=cname.lower(), cemail=cemail, cphone=cphone)
        if not inserted:
            return apology("Could not insert client")
        return redirect("/clients/admin")

** I did try to manually do this, even then it did not work **

  • 1
    No offense, I'm inclined to believe the sqlite result. string equality is case sensitive. I can't think of a way to troubleshoot this without seeing the cities data and the result of print(tcity) Dec 18, 2019 at 13:39
  • Thank you so much DinoCoderSaurus! The answer was indeed that the cities shown to the user were in title case, while the cities in the database were in lowercase. Thank you so much, especially as the deadline is nearing :)
    – AK2150
    Dec 18, 2019 at 14:49


You must log in to answer this question.

Browse other questions tagged .