Gardening App: Creating APIs for Database Connections

Trying to get Buildozer, SQLALchemy, and a database driver to work on Android has become a dead end. There were several issues including that an essential module for Postgres (psycopg2) was for some reason not compatible with Buildozer. Instead, I’m creating my own APIs using Flask so that my Kivy app just has to send a web request rather than interacting directly with a database. I will be using Heroku Postgres to host the database and currently I’m testing my APIs using Postman. I have created separate APIs for the following:

• Searching plants — This is used twice: the explicit plant search and the garden pages. Each time the user views a specific garden, they will see a list of plants in that garden.
• Generating a list of the gardens — for the main page where the user can see all their gardens.
• Adding a plant — Accessed in two places: the main page and the plant search page.
• Adding a garden — Accessed from the main page.
• Updating a plant — Accessed when viewing the individual plant’s full information.
• Updating a garden — Accessed from the garden’s page.
• Deleting a garden — Accessed from the garden’s page.
• Deleting a plant — Accessed when viewing the individual plant’s full information.

Here is the code:

from flask import Flask, request
from sqlalchemy import MetaData, create_engine, Table, Column, String, Integer, and_
from sqlalchemy.sql.expression import Update
import json

app = Flask(__name__)

meta = MetaData()
engine = create_engine("postgresql+psycopg2:// , echo=True)
connection = engine.connect()
my_plants = Table(
    "plants", meta,
    Column("id", Integer, primary_key=True),
    Column("name", String(length=50), ),
    Column("common_name", String(length=50)),
    Column("category", String(length=50)),
    Column("location", String(length=50)),
    Column("year", Integer),
    Column("notes", String(length=500)),
)
my_gardens = Table(
    "gardens", meta,
    Column("id", Integer, primary_key=True),
    Column("name", String(length=50)),
    Column("notes", String(length=250)),
)
meta.create_all(engine)

@app.route("/gardens/create", methods=["POST"])
def create_garden():
    name = request.form['name']
    notes = request.form['notes']
    new_garden = [
        {"name": name,
         "notes": notes}
    ]
    connection.execute(my_gardens.insert(), new_garden)
    return ''  # --- I needed a return statement, even though this API doesn't return anything to the app --- #

@app.route("/plants/create", methods=["POST"])
def create_plant():
    name = request.form["name"]
    common_name = request.form["common_name"]
    category = request.form["category"]
    location = request.form["location"]
    year = request.form["year"]
    notes = request.form["notes"]
    new_plant = [
        {"name": name,
         "common_name": common_name,
         "category": category,
         "location": location,
         "year": year,
         "notes": notes}
    ]
    connection.execute(my_plants.insert(), new_plant)
    return ""

@app.route("/gardens/update/<int:garden_id>", methods=["UPDATE"])
def update_garden(garden_id):
    updated_name = request.form['updated_name']
    updated_notes = request.form['updated_notes']
    updated_garden_info = Update(my_gardens).where(my_gardens.columns.id == garden_id).values(name=updated_name, notes=updated_notes)
    connection.execute(updated_garden_info)
    return ""

@app.route("/plants/update/<int:plant_id>", methods=["UPDATE"])
def update_plant(plant_id):
    updated_name = request.form["updated_name"]
    updated_common_name = request.form["updated_common_name"]
    updated_category = request.form["updated_category"]
    updated_location = request.form["updated_location"]
    updated_year = request.form["updated_year"]
    updated_notes = request.form["updated_notes"]
    updated_plant_info = Update(my_plants).where(my_plants.columns.id == plant_id).values(name=updated_name, common_name=updated_common_name, category=updated_category, location=updated_location, year=updated_year, notes=updated_notes)
    connection.execute(updated_plant_info)
    return ""

@app.route("/plants/delete/<int:plant_id>", methods=["DELETE"])
def delete_plant(plant_id):
    deletion = my_plants.delete().where(my_plants.columns.ids == plant_id)
    connection.execute(deletion)
    return ""

@app.route("/gardens/delete/<int:garden_id>", methods=["DELETE"])
def delete_garden(garden_id):
    deletion = my_gardens.delete().where(my_gardens.columns.ids == garden_id)
    connection.execute(deletion)
    return ""

@app.route("/plants/search", methods=["POST"])
def search_plants():
    name = request.form["name"]
    common_name = request.form["common_name"]
    category = request.form["category"]
    location = request.form["location"]
    year = request.form["year"]
    notes = request.form["notes"]
    filters = []
    if name != "":
        filters.append(my_plants.columns.name.contains(name))
    if common_name != "":
        filters.append(my_plants.columns.common_name.contains(common_name))
    if category != "":
        filters.append(my_plants.columns.category == category)
    if location != "":
        filters.append(my_plants.columns.location == location)
    if year != "":
        filters.append(my_plants.columns.year == year)
    if notes != "":
        filters.append(my_plants.columns.notes.contains(notes))
    search = my_plants.select().where(and_(*filters))
    results = connection.execute(search).fetchall()
    return json.dumps([list(x) for x in results])

@app.route("/gardens", methods=["GET"])
def all_gardens():
    info = my_gardens.select()
    results = connection.execute(info).fetchall()
    return json.dumps([dict(x) for x in results])