def latestshelters(count=1):
"""
Retrieves latest shelters (updates to existing shelters also count)
:param count: number of latest shelters to return
:type count: int
"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
Supercategory = db.aliased(Category)
subquery = db.session.query(Shelter).filter(
Shelter.is_published == True).order_by(desc(
Shelter.updated_at)).limit(count).subquery()
querybase = db.session.query(subquery.c.id.label("shelter_id"), Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid,func.string_agg(Value.name,';').label("value"))\
.join(Property, subquery.c.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.order_by(desc(subquery.c.updated_at))\
.group_by(subquery.c.updated_at,subquery.c.id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(
Category.name).filter(Category.section_id != None)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
# if shelter_id:
# shelter_properties = shelter_properties.filter(Property.shelter_id==shelter_id)
# shelter_pictures = shelter_pictures.filter(ShelterPicture.shelter_id==shelter_id)
#
# if request.args.getlist('attribute'):
# attribute = request.args.getlist('attribute')
#
# subquery = db.session.query(Property.shelter_id)\
# .join(Attribute, Attribute.id==Property.attribute_id)\
# .filter(Attribute.uniqueid.in_(attribute))\
# .group_by(Property.shelter_id)
#
# shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
# shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
#
# if request.args.getlist('value'):
# value = request.args.getlist('value')
# if not request.args.getlist('attribute'):
# subquery = db.session.query(Property.shelter_id)\
# .join(Attribute, Attribute.id==Property.attribute_id)\
# .filter(Property.values.any(Value.name.in_(value)))\
# .group_by(Property.shelter_id)
# else:
# subquery = subquery.filter(Property.values.any(Value.name.in_(value)))
#
# shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
# shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
#
# if request.args.get('q'):
# attribute = request.args.get('q')
#
# shelter_properties = shelter_properties.join(Tsvector, Property.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
# shelter_pictures = shelter_pictures.join(Tsvector, ShelterPicture.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
for shelter_property in shelter_properties:
if not result[shelter_property.shelter_id]:
for category in catquery:
if category.name == "Identification":
result[shelter_property.shelter_id][category.name]["Cover"]
result[shelter_property.shelter_id][
category.name]["Attributes"]
result[shelter_property.shelter_id][category.name]["Pictures"]
if request.args.get('format') == 'prettytext':
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.name] = shelter_property.value
else:
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.uniqueid] = shelter_property.value
for picture in shelter_pictures:
if picture.shelter_id in result:
if picture.is_main_picture == True:
result[picture.shelter_id]["Identification"]["Cover"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
elif not result[picture.shelter_id][picture.name]["Pictures"]:
result[picture.shelter_id][picture.name]["Pictures"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id][picture.name]["Pictures"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
return jsonify(result)
def allshelters(shelter_id=None):
"""
Retrieves shelters with all their attributes and pictures.
**Example requests**:
.. sourcecode:: html
# get all shelters
GET http://0.0.0.0:5000/api/v0.2/shelters
# get shelter whith shelter ID 11
GET http://0.0.0.0:5000/api/v0.2/shelters/11
# get all shelters which have attribute 'storeys'
GET http://0.0.0.0:5000/api/v0.2/shelters?attribute=storeys
# get all shelters which have 2 storeys
GET http://0.0.0.0:5000/api/v0.2/shelters?attribute=storeys&value=2
:param shelter_id: a unique shelter ID generated by the server
:type shelter_id: int
:query format:
if set to ``prettytext``,
attribute names are retrieved as nicely formatted text
(Capital letters, special characters and spaces allowed)
:query attribute:
attribute name
:query value:
attribute value
:query q:
Full text search. Works in English language only.
"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
docpath = os.path.relpath(conf.SHELTERS_DOCUMENTS_SITE_PATH)
Supercategory = db.aliased(Category)
querybase = db.session.query(Property.shelter_id, Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid,func.string_agg(Value.name,';').label("value"))\
.join(Shelter, Shelter.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.group_by(Property.shelter_id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(
Category.name).filter(Category.section_id != None)
docquerybase = db.session.query(ShelterDocument.shelter_id, ShelterDocument.file_name.label("filename"), ShelterDocument.category_id, Category.name)\
.join(Category, Category.id == ShelterDocument.category_id)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
shelter_documents = docquerybase
if shelter_id:
shelter_properties = shelter_properties.filter(
Property.shelter_id == shelter_id)
shelter_pictures = shelter_pictures.filter(
ShelterPicture.shelter_id == shelter_id)
shelter_documents = shelter_documents.filter(
ShelterDocument.shelter_id == shelter_id)
else:
#only query published shelters if no shelter_id supplied
shelter_properties = shelter_properties.filter(
Shelter.is_published == True)
if request.args.getlist('attribute'):
attribute = request.args.getlist('attribute')
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Attribute.uniqueid.in_(attribute))\
.group_by(Property.shelter_id)
shelter_properties = shelter_properties.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = shelter_pictures.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(
subquery.subquery().c.shelter_id == ShelterDocument.shelter_id)
if request.args.getlist('value'):
value = request.args.getlist('value')
if not request.args.getlist('attribute'):
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Property.values.any(Value.name.in_(value)))\
.group_by(Property.shelter_id)
else:
subquery = subquery.filter(
Property.values.any(Value.name.in_(value)))
shelter_properties = shelter_properties.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = shelter_pictures.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(
subquery.subquery().c.shelter_id == ShelterDocument.shelter_id)
if request.args.get('q'):
attribute = request.args.get('q')
shelter_properties = shelter_properties.join(
Tsvector, Property.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
shelter_pictures = shelter_pictures.join(
Tsvector, ShelterPicture.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
shelter_documents = shelter_documents.join(
Tsvector,
ShelterDocument.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
#print(shelter_properties)
#print(shelter_pictures)
for shelter_property in shelter_properties:
if not result[shelter_property.shelter_id]:
for category in catquery:
if category.name == "Identification":
result[shelter_property.shelter_id][category.name]["Cover"]
result[shelter_property.shelter_id][
category.name]["Attributes"]
result[shelter_property.shelter_id][category.name]["Pictures"]
result[shelter_property.shelter_id][category.name]["Documents"]
if request.args.get('format') == 'prettytext':
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.name] = shelter_property.value
else:
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.uniqueid] = shelter_property.value
for picture in shelter_pictures:
if picture.shelter_id in result:
if picture.is_main_picture == True:
if not result[picture.shelter_id]["Identification"]["Cover"]:
result[picture.shelter_id]["Identification"]["Cover"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[
picture.shelter_id]["Identification"]["Cover"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
elif not result[picture.shelter_id][picture.name]["Pictures"]:
result[picture.shelter_id][picture.name]["Pictures"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id][picture.name]["Pictures"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
for document in shelter_documents:
if document.shelter_id in result:
if not result[document.shelter_id][document.name]["Documents"]:
result[document.shelter_id][document.name]["Documents"] = [
"{}/{}/{}".format(docpath, document.shelter_id,
document.filename)
]
else:
result[document.shelter_id][document.name]["Documents"].append(
"{}/{}/{}".format(docpath, document.shelter_id,
document.filename))
return jsonify(result)
def allshelters(shelter_id=None):
"""
Retrieves shelters with all of their attributes and pictures.
:param shelter_id: a unique shelter ID generated by the server
:type shelter_id: int
:query format:
if set to ``prettytext``,
attribute names are retrieved as nicely formatted text
(Capital letters, special characters and spaces allowed)
:query attribute:
attribute name
:query value:
attribute value
:query q:
Full text search. Works in English language only.
**Example requests**:
.. sourcecode:: html
# get all shelters
GET {0}/api/v0.2/shelters
# get shelter whith shelter ID 11
GET {0}/api/v0.2/shelters/11
# get all shelters which have attribute 'storeys'
GET {0}/api/v0.2/shelters?attribute=storeys
# get all shelters which have 2 storeys
GET {0}/api/v0.2/shelters?attribute=storeys&value=2
"""
result = tree()
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
docpath = os.path.relpath(conf.SHELTERS_DOCUMENTS_SITE_PATH)
pretty = False
Supercategory = db.aliased(Category)
querybase = db.session.query(Property.shelter_id, Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid, Attribute.type, func.string_agg(Value.name,';').label("value"))\
.join(Shelter, Shelter.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.group_by(Property.shelter_id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid, Attribute.type)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(Category.name).filter(Category.section_id != None)
docquerybase = db.session.query(ShelterDocument.shelter_id, ShelterDocument.file_name.label("filename"), ShelterDocument.category_id, Category.name)\
.join(Category, Category.id == ShelterDocument.category_id)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
shelter_documents = docquerybase
if shelter_id:
shelter_properties = querybase.filter(Property.shelter_id==shelter_id)
shelter_pictures = picquerybase.filter(ShelterPicture.shelter_id==shelter_id)
shelter_documents = docquerybase.filter(ShelterDocument.shelter_id==shelter_id)
else:
#only query published shelters if no shelter_id supplied
shelter_properties = shelter_properties.filter(Shelter.is_published == True)
if request.args.getlist('attribute'):
attribute = request.args.getlist('attribute')
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Attribute.uniqueid.in_(attribute))\
.group_by(Property.shelter_id)
shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(subquery.subquery().c.shelter_id==ShelterDocument.shelter_id)
if request.args.getlist('value'):
value = request.args.getlist('value')
if not request.args.getlist('attribute'):
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Property.values.any(Value.name.in_(value)))\
.group_by(Property.shelter_id)
else:
subquery = subquery.filter(Property.values.any(Value.name.in_(value)))
shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(subquery.subquery().c.shelter_id==ShelterDocument.shelter_id)
if request.args.get('q'):
attribute = request.args.get('q')
shelter_properties = shelter_properties.join(Tsvector, Property.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
shelter_pictures = shelter_pictures.join(Tsvector, ShelterPicture.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
shelter_documents = shelter_documents.join(Tsvector, ShelterDocument.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
if request.args.get('format') == 'prettytext':
pretty = True
result = populate_dictree(shelter_properties, catquery, result, prettytext=pretty)
populate_pictures(shelter_pictures, result, picpath)
populate_documents(shelter_documents, result, docpath)
return jsonify(result)
def latestshelters(count=1):
"""
Retrieves latest shelters (updates to existing shelters also count).
Only retrieves shelters that have pictures. If no count parameter is supplied, the API
retrieves the latest shelter.
:param count: number of latest shelters to return
:type count: int
**Example requests**:
.. sourcecode:: html
# get latest shelter
GET {0}/api/v0.2/shelters/latest
# get the 3 latest shelters
GET {0}/api/v0.2/shelters/latest/3
"""
result = tree()
pretty = False
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
Supercategory = db.aliased(Category)
subsubquery = db.session.query(ShelterPicture.shelter_id).filter(ShelterPicture.is_main_picture == True).subquery()
subquery= db.session.query(Shelter)\
.filter(Shelter.is_published == True)\
.filter(Shelter.id.in_(subsubquery))\
.order_by(desc(Shelter.updated_at))\
.limit(count).subquery()
querybase = db.session.query(subquery.c.id.label("shelter_id"), Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid, Attribute.type, func.string_agg(Value.name,';').label("value"))\
.join(Property, subquery.c.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.order_by(desc(subquery.c.updated_at))\
.group_by(subquery.c.updated_at,subquery.c.id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid,Attribute.type)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(Category.name).filter(Category.section_id != None)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
if request.args.get('format') == 'prettytext':
pretty = True
result = populate_dictree(shelter_properties, catquery, result, prettytext=pretty)
populate_pictures(shelter_pictures, result, picpath)
return jsonify(result)
def export_shelters(dump_file, truncate=''):
if truncate == 'overwrite':
pass
elif os.path.isfile(dump_file):
print("Export aborted: dump file already exists. Choose another filename or enable overwriting")
sys.exit(1)
else:
pass
Subcategory = db.aliased(Category)
headersquery = db.session.query(Section.name.label("section"), Category.name.label("category"), Subcategory.name.label("subcategory"), Attribute.name.label("attribute"))\
.join(Category, Category.section_id==Section.id)\
.join(Subcategory, Subcategory.parent_id==Category.id)\
.join(Attribute, Attribute.category_id==Subcategory.id)
dataquery = db.session.query(Property.attribute_id.label('att_id'), Property.shelter_id, Value.name.label('value'))\
.join(Association, Association.property_id==Property.id)\
.join(Value, Value.id==Association.value_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Shelter, Shelter.id==Property.shelter_id)\
.order_by(Property.shelter_id,Property.attribute_id)
#print(dataquery)
array=[]
for index, row in enumerate(headersquery):
section = row.section
category = row.category
# insert blanks instead of duplicate sections / categories
if index != 0:
if row.section == prev_row.section:
section = ''
if row.category == prev_row.category:
category = ''
array += [[section,category,row.subcategory, row.attribute]]
prev_row = row
columns_length = len(array)
###dump published shelters
pubdataquery = dataquery.filter(Shelter.is_published==True)
pubfilename = dump_file + "shelterdump_published.csv"
with open(pubfilename, mode='wt',newline='', encoding='utf-8') as csvfile:
shelters = csv.writer(csvfile, delimiter=',')
#transpose array before write
for cursor in zip(*array):
shelters.writerow(cursor)
r = [''] * columns_length
for index, cursor in enumerate(pubdataquery):
#remove any newline and carriage return if string
if isinstance(cursor.value, str):
value = cursor.value.replace('\n',' ').replace('\r',' ')
else:
value = cursor.value
if index != 0:
if cursor.shelter_id == prev_row.shelter_id:
r[cursor.att_id-1] = value
else:
shelters.writerow(r)
r = [''] * columns_length
r[cursor.att_id-1] = value
else:
r[cursor.att_id-1] = value
prev_row = cursor
shelters.writerow(r)
### dump unpublished shelters
unpubdataquery = dataquery.filter(Shelter.is_published==False)
unpubfilename = dump_file + "shelterdump_unpublished.csv"
with open(unpubfilename, mode='wt',newline='', encoding='utf-8') as csvfile:
shelters = csv.writer(csvfile, delimiter=',')
#transpose array before write
for cursor in zip(*array):
shelters.writerow(cursor)
r = [''] * columns_length
for index, cursor in enumerate(unpubdataquery):
#remove any newline and carriage return if string
if isinstance(cursor.value, str):
value = cursor.value.replace('\n',' ').replace('\r',' ')
else:
value = cursor.value
if index != 0:
if cursor.shelter_id == prev_row.shelter_id:
r[cursor.att_id-1] = cursor.value
else:
shelters.writerow(r)
r = [''] * columns_length
r[cursor.att_id-1] = cursor.value
else:
r[cursor.att_id-1] = cursor.value
prev_row = cursor
shelters.writerow(r)