នៅពេលដែលទិន្នន័យត្រូវបានបញ្ចូលកាន់តែច្រើនឡើងៗ ទៅក្នុងមូលដ្ឋានទិន្នន័យ ដើម្បីស្រង់យកទិន្នន័យណាមួយ យើងចាំបាច់ត្រូវតែស្វែងរកទិន្នន័យនោះ ដោយវិធី «Search» ។ អាស្រ័យហេតុ គ្រប់កម្មវិធីមូលដ្ឋានទិន្នន័យទាំងឡាយ ចាំបាច់ត្រូវបំពាក់ដោយមុខងារ search មួយជាដរាប។ ដោយឡែក ចំពោះកម្មវិធី Rental Bicycle App យើងអាចបំពាក់មុខងារ Search អោយវា ដោយធ្វើដូចខាងក្រោមនេះ៖
#\controllers\bikeform.py
import config, re, json
from bottle import template, route, request, redirect
from models import bicycledb
@route("/bikeform")
def renderForm():
if 'rowedit' in config.kargs:
del config.kargs['rowedit']
return template('bikeform', data=config.kargs)
@route("/bikeform/edit/<id>")
def editForm(id):
config.kargs['rowedit'] = bicycledb.edit(id)
config.kargs['id'] = id
return template('bikeform', data=config.kargs)
@route("/bikeform/delete/<id>")
def deleteForm(id):
config.kargs['id'] = id
bicycledb.delete(id)
redirect('/')
@route("/bicycle/<brand>/<sortIndex>")
def sortBicycle(brand,sortIndex):
config.kargs['bicycles'] = json.dumps(bicycledb.sort(brand))
config.kargs['sortIndex'] = sortIndex
return template('index', data=config.kargs)
@route("/search/bicycle/<query>")
def searchBicycle(query):
config.kargs['bicycles'] = json.dumps(bicycledb.search(query))
return template('index', data=config.kargs)
@route("/bikeform", method="POST")
def getFormData():
brand = request.forms.get("fbrand")
country = request.forms.get("fcountry")
year = request.forms.get("fyear")
amount = request.forms.get("famount")
price = request.forms.get("fprice")
if not re.findall("[a-zA-Z]", country):
config.kargs['message'] = "Country name could contain only letter."
return template('bikeform', data=config.kargs)
elif not (re.findall("[0-9]", year) and re.findall("[0-9]", amount)):
config.kargs['message'] = "Year and amount must be whole number."
return template('bikeform', data=config.kargs)
elif not (re.findall(r"[-+]?\d*\.\d+|\d+", price)):
config.kargs['message'] = "Price must be a number."
return template('bikeform', data=config.kargs)
else:
if 'rowedit' in config.kargs:
bicycledb.update(brand, country, int(year), int(amount), float(price), config.kargs['id'])
del config.kargs['rowedit']
redirect('/')
else:
bicycledb.insert(brand, country, int(year), int(amount), float(price))
return template('bikeform', data=config.kargs)
#\models\bicycledb.py
import sqlite3
def insert(*bicycle):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
sql ='''CREATE TABLE IF NOT EXISTS BICYCLE(
BRAND TEXT,
COUNTRY TEXT,
YEAR INT,
AMOUNT INT,
PRICE FLOAT
)
'''
cursor.execute(sql)
cursor.execute("INSERT INTO BICYCLE VALUES (?, ?, ?, ?, ?)", bicycle)
conn.commit()
conn.close()
def select():
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
sql ='''CREATE TABLE IF NOT EXISTS BICYCLE(
BRAND TEXT,
COUNTRY TEXT,
YEAR INT,
AMOUNT INT,
PRICE FLOAT
)
'''
cursor.execute(sql)
cursor.execute("SELECT ROWID, * from BICYCLE")
bicycles = cursor.fetchall()
conn.commit()
conn.close()
return bicycles
def edit(id):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
cursor.execute("SELECT ROWID, * from BICYCLE WHERE ROWID = " + str(id))
bicycle = cursor.fetchone()
conn.commit()
conn.close()
return bicycle
def update(*args):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
sql = "UPDATE BICYCLE SET BRAND=?, COUNTRY=?, YEAR=?, AMOUNT=?, PRICE=? WHERE ROWID=?"
cursor.execute(sql, args)
conn.commit()
conn.close()
def delete(id):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
cursor.execute("DELETE FROM BICYCLE WHERE ROWID=?", (id,))
conn.commit()
conn.close()
def sort(brand):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
cursor.execute("SELECT ROWID, * from BICYCLE ORDER BY " + brand)
bicycles = cursor.fetchall()
conn.commit()
conn.close()
return bicycles
def search(query):
conn = sqlite3.connect('sqlite.db')
cursor = conn.cursor()
sql = "SELECT ROWID, * from BICYCLE WHERE BRAND LIKE '%"+query+"%'"
sql += " OR COUNTRY LIKE '%"+query+"%'"
sql += " OR YEAR LIKE '%"+query+"%'"
sql += " OR AMOUNT LIKE '%"+query+"%'"
sql += " OR PRICE LIKE '%"+query+"%'"
cursor.execute(sql)
bicycles = cursor.fetchall()
conn.commit()
conn.close()
return bicycles
GitHub: https://github.com/Sokhavuth/Rental-Bicycle-App
Heroku: https://khmerweb-rba.herokuapp.com/
