def getAll(q):
q_ = f"%{q.lower()}%"
query = "select id, category, description, created_date from report where lower(description) like %s order by created_date desc"
results = execute_get(query, (q_, ))
return [{
"id": result[0],
"category": result[1],
"description": result[2],
"created_date": result[3]
} for result in results]
def getAll():
query = """
SELECT id
FROM booking
"""
results = execute_get(query, ())
return [
{
"id": result[0]
} for result in results
]
def getAll():
query = """
SELECT username, created_date, active
FROM users
"""
results = execute_get(query, ())
return [{
"username": result[0],
"created_date": result[1],
"active": result[2]
} for result in results]
def getByUsername(username):
query = """
SELECT username, created_date, active
FROM users
WHERE username = %s
"""
result = execute_get(query, (username, ))[0]
return {
"username": result[0],
"created_date": str(result[1]),
"active": result[2]
}
def getAll():
query = BASE + " ORDER BY created_date desc"
results = execute_get(query, ())
return [
{
"id": result[0],
"dataset_url": result[1],
"dataset_name": result[2],
"created_by": result[3],
"created_date": result[4],
"deleted_by": result[5],
"deleted_date": result[6],
} for result in results
]
def getById(id):
query = BASE + " WHERE id = %s"
result = execute_get(query, (id, ))
if len(result) > 0:
result = result[0]
return {
"id": result[0],
"dataset_url": result[1],
"dataset_name": result[2],
"created_by": result[3],
"created_date": result[4],
"deleted_by": result[5],
"deleted_date": result[6],
}
else:
raise HTTPException(404)
def login(username, password):
query = """
SELECT username, hashed_password
FROM users
WHERE username = %s
"""
result = execute_get(query, (username, ))
if len(result) > 0:
username = result[0][0]
hashed_password = result[0][1].encode()
if password_matches(password, hashed_password):
return _getJwt(username)
else:
raise HTTPException(status_code=403, detail="Unauthorized access")
else:
raise HTTPException(status_code=404, detail="Username not found")
def getAvailable():
# Returning all available (not booked) tasks
query = """
SELECT id, dataset_name
FROM task
EXCEPT
SELECT t.id, t.dataset_name
FROM task t
JOIN booking b
ON t.id = b.task_id
WHERE b.revoked_date iS NULL
"""
results = execute_get(query, ())
return [
{
"id": result[0],
"dataset_name": result[1]
} for result in results
]
def getByUser(username):
query = """
SELECT
b.id,
t.dataset_name,
t.dataset_url,
b.created_date
FROM task t
JOIN booking b
ON t.id = b.task_id
WHERE b.created_by = %s
AND b.revoked_date IS NULL
AND t.deleted_date IS NULL
ORDER BY b.created_date DESC
"""
results = execute_get(query, (username, ))
return [
{
"id": result[0],
"dataset_name": result[1],
"dataset_url": result[2],
"booking_date": result[3]
} for result in results
]