PG-Ltree/Flask-PtC | Admin42Day

PostgreSQL Ltree into a Flask Website

new: 17-May-2025

Part C: A Basic Ltree/Flask Application

There three sections: app.py and two html files; index.html and layout.html (to provide styling) in the templates folder.
Step One: Create the app.py

sammy@imega:~/flktree$ nano app.py

In the first part of app.py, we set the modules and the helper function.

For the second part of app.py, it comprises of all the routes for a full CRUD ability plus a route to capture exceptions arising from faulty search queries (debug should be set to 'False').

#Part B of app.py

@app.route('/', methods=["GET", "POST"])
def index(): 
	# Connect to the database 
	conn = psycopg2.connect(database="testdb",  
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

# create a cursor 
	cur = conn.cursor() 
	if request.method == "POST":
		path = dict(request.form)
		data = getpath(path["search"])
	else:
		cur.execute('''SELECT t.id, t.path, n.descrip, n.notes, n.tree_id 
       FROM tree t, treenotes n WHERE path  ~ '*' 
       AND t.id=n.tree_id ORDER BY t.id DESC LIMIT 20; ''' )        
	# Fetch the data 
	data = cur.fetchall()

# close the cursor and connection 
	cur.close() 
	conn.close()

return render_template('index.html', data=data)

@app.route('/create', methods=['POST']) 
def create(): 
	conn = psycopg2.connect(database="testdb",  
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

cur = conn.cursor()

# Get the data from the form 
	path = request.form['path']
	descrip = request.form['descrip'] 
	notes = request.form['notes']

# Insert the data into the table tree and that of treenotes  
	cur.execute( 
     '''WITH new_tree AS (
    INSERT INTO tree (path) VALUES (%s)
    RETURNING id
    )
    INSERT INTO treenotes (tree_id, descrip, notes) SELECT id, %s, %s 
    FROM new_tree;''', (path, descrip, notes))

# commit the changes 
	conn.commit()

# Remove the inputting of any possible duplicate paths from the table
	cur.execute( 
	'''DELETE FROM tree WHERE id IN
       (SELECT id
        FROM
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY path
        ORDER BY  id ) AS row_num
        FROM tree ) t
        WHERE t.row_num > 1 )''')

# commit the changes 
	conn.commit()

# close the cursor and connection 
	cur.close() 
	conn.close()

return redirect(url_for('index'))

@app.route('/update', methods=['POST']) 
def update(): 
	conn = psycopg2.connect(database="testdb", 
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

cur = conn.cursor()

# Get the data from the form 
	descrip = request.form['descrip'] 
	notes = request.form['notes'] 
	id = request.form['id']

# Update the data in the table 
	cur.execute( 
		'''UPDATE treenotes SET descrip=%s,	
		notes=%s WHERE id=%s''', (descrip, notes, id))

# commit the changes 
	conn.commit() 
	return redirect(url_for('index'))

@app.route('/delete', methods=['POST']) 
def delete(): 
	conn = psycopg2.connect(database="testdb", 
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

cur = conn.cursor()

# Get the data from the form 
	id = request.form['id']
	# Delete the data from the table 
	cur.execute('''DELETE FROM tree WHERE id=%s''', (id,))

# commit the changes 
	conn.commit()

# close the cursor and connection 
	cur.close() 
	conn.close()

return redirect(url_for('index')) 
  
  # Resolve ltree search input errors
@app.errorhandler(Exception)
def handle_exception(e):
	message = str(e)
	return render_template('index.html', message=message)

if __name__ == '__main__': 
	app.run(host='0.0.0.0', debug=False)

A complete rendering of app.py as a text file: flktree-app.py.txt

sammy@imega:~/flktree$ mkdir templates
sammy@imega:~/flktree$ nano templates/layout.html -see contents below
sammy@imega:~/flktree$ nano templates/index.html -see contents below

Now for the layout.html: flktree-layout.html.txt

And for the layout.html:flktree-index.html.txt

With these files in place, start the application (if no errors, set debug to False)

sammy@imega:~/flktree$ python3 app.py

PgLtree/Flask	PgLtree/Flask-PartB
PgLtree/Flask-Deploy