def add_item(): conn = get_db() c = conn.cursor() form = NewItemForm() c.execute("SELECT id, name FROM categories") categories = c.fetchall() form.category.choices = categories c.execute( """SELECT id, name FROM subcategories WHERE category_id = ?""", (1, )) subcategories = c.fetchall() form.subcategory.choices = subcategories if form.validate_on_submit() and form.image.validate( form, extra_validators=(FileRequired(), )): filename = save_image_upload(form.image) c.execute( """INSERT INTO items (title, description, price, image, category_id, subcategory_id) VALUES (?, ?, ?, ?, ?, ?)""", (form.title.data, form.description.data, float(form.price.data), filename, form.category.data, form.subcategory.data)) conn.commit() flash(f"Item {form.title.data} has been successfully submitted.", "success") return redirect(url_for("main.home")) return render_template("new_item.html", form=form)
def item(item_id): c = get_db().cursor() c.execute( """SELECT i.id, i.title, i.description, i.price, i.image, c.name, s.name FROM items AS i INNER JOIN categories AS c ON i.category_id = c.id INNER JOIN subcategories AS s ON i.subcategory_id = s.id WHERE i.id = ?""", (item_id, )) row = c.fetchone() try: good = { "id": row[0], "title": row[1], "description": row[2], "price": row[3], "image": row[4], "category": row[5], "subcategory": row[6] } except IndexError: good = {} if good: delete_item_form = DeleteItemForm() return render_template("item.html", item=good, deleteItemForm=delete_item_form) return redirect(url_for('main.home'))
def edit_item(item_id): conn = get_db() c = conn.cursor() c.execute("SELECT * FROM items WHERE id=?", (item_id, )) row = c.fetchone() try: good = { "id": row[0], "title": row[1], "description": row[2], "price": row[3], "image": row[4], } except IndexError: good = {} if good: form = EditItemForm() if form.validate_on_submit(): filename = good["image"] if form.image.data: filename = save_image_upload(form.image) c.execute( """UPDATE items SET title = ?, description = ?, price = ?, image = ? WHERE id = ?""", (form.title.data, form.description.data, float( form.price.data), filename, item_id)) conn.commit() flash(f"Item {form.title.data} has been successfully updated.", "success") return redirect(url_for("item.item", item_id=item_id)) form.title.data = good["title"] form.description.data = good["description"] form.price.data = good["price"] return render_template("edit_item.html", form=form, item=good)
def delete_item(item_id): conn = get_db() c = conn.cursor() c.execute("SELECT * FROM items WHERE id=?", (item_id, )) row = c.fetchone() try: good = { "id": row[0], "title": row[1], } except IndexError: good = {} if good: c.execute("DELETE FROM items WHERE id = ?", (item_id, )) conn.commit() flash(f"Item {good['title']} has been successfully deleted.", "success") else: flash(f"This item does not exist", "danger") return redirect(url_for("main.home"))
def index(): conn = get_db() c = conn.cursor() form = FilterForm(request.args, meta={'csrf': False}) c.execute('SELECT id, name FROM category') categories = c.fetchall() form.category.choices += categories c.execute('SELECT id, name FROM subcategory WHERE category_id = ?', (1, )) subcategories = c.fetchall() form.subcategory.choices += subcategories query = '''SELECT i.id, i.title, i.description, i.price, i.image, c.name, s.name FROM item AS i INNER JOIN category AS c ON i.category_id = c.id INNER JOIN subcategory AS s ON i.subcategory_id = s.id ''' filter_queries = [] parameters = [] if form.title.data.strip(): filter_queries.append('i.title LIKE ?') parameters.append(f'%{form.title.data.strip()}%') if form.description.data.strip(): filter_queries.append('i.description LIKE ?') parameters.append(f'%{form.description.data.strip()}%') if form.category.data: filter_queries.append('i.category_id = ?') parameters.append(form.category.data) if form.subcategory.data: filter_queries.append('i.subcategory_id = ?') parameters.append(form.subcategory.data) if filter_queries: query += ' WHERE ' query += ' AND '.join(filter_queries) if form.price.data: if form.price.data == 1: query += ' ORDER BY i.price DESC' else: query += ' ORDER BY i.price' else: query += ' ORDER BY i.id DESC' items_from_db = c.execute(query, tuple(parameters)) items = [] for row in items_from_db: print(row) item = { 'id': row[0], 'title': row[1], 'description': row[2], 'price': row[3], 'image': row[4], 'category': row[5], 'subcategory': row[6], } items.append(item) print(query) print(items) return render_template('index.html', items=items, form=form)
def home(): conn = get_db() c = conn.cursor() form = FilterForm(request.args, meta={"csrf": False}) c.execute("SELECT id, name FROM categories") categories = c.fetchall() categories.insert(0, (0, "---")) form.category.choices = categories c.execute("SELECT id, name FROM subcategories WHERE category_id = ?", (1, )) subcategories = c.fetchall() subcategories.insert(0, (0, "---")) form.subcategory.choices = subcategories query = """SELECT i.id, i.title, i.description, i.price, i.image, c.name, s.name FROM items AS i INNER JOIN categories AS c ON i.category_id = c.id INNER JOIN subcategories AS s ON i.subcategory_id = s.id """ if form.validate(): filter_queries = [] parameters = [] if form.title.data.strip(): filter_queries.append("i.title LIKE ?") parameters.append(f"%{form.title.data}%") if form.category.data: filter_queries.append("i.category_id = ?") parameters.append(form.category.data) if form.subcategory.data: filter_queries.append("i.subcategory_id = ?") parameters.append(form.subcategory.data) if filter_queries: query += " WHERE " query += " AND ".join(filter_queries) if form.price.data: if form.price.data == 1: query += " ORDER BY i.price DESC" else: query += " ORDER BY i.price" else: query += " ORDER BY i.id DESC" items_from_db = c.execute(query, tuple(parameters)) else: items_from_db = c.execute(query + " ORDER BY i.id DESC") items = [] for row in items_from_db: item = { "id": row[0], "title": row[1], "description": row[2], "price": row[3], "image": row[4], "category": row[5], "subcategory": row[6] } items.append(item) return render_template('home.html', items=items, form=form)