def apply(self, args, query):
if self.key not in args:
return query
WKT_shape_text = args[self.key]
WKT_query = "SRID=4326;" + WKT_shape_text
db = app_context().database
sample = db.model.sample
model_name = create_model_name_string(self.model)
if hasattr(self.model, "sample_collection") and self.model != sample:
return query.join(self.model.sample_collection).filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(sample.location, 4326)))
path = nested_collection_path(model_name, "sample")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
return db_query.filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(sample.location, 4326)))
return query.filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(self.model.location, 4326)))
def apply(self, args, query):
if self.key not in args:
return query
model_name = create_model_name_string(self.model)
db = app_context().database
Session = db.model.session
format = "%Y-%m-%d"
start, end = args[self.key]
start = datetime.datetime.strptime(start, format)
end = datetime.datetime.strptime(end, format)
if hasattr(self.model, "session_collection"):
return query.join(self.model.session_collection).filter(
and_(Session.date > start, Session.date < end))
path = nested_collection_path(model_name, "session")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
return db_query.filter(
and_(Session.date > start, Session.date < end))
return query.filter(
and_(self.model.date > start, self.model.date < end))
def apply(self, args, query):
if self.key not in args:
return query
model_name = create_model_name_string(self.model)
db = app_context().database
points = args[
"coordinates"] # should be an array [minLong, minLat, maxLong, maxLat]
pnts = [int(pnt) for pnt in points]
bounding_shape = create_bound_shape(pnts)
path = nested_collection_path(model_name, "sample")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
sample = db.model.sample
return db_query.filter(
bounding_shape.ST_Contains(
func.ST_Transform(sample.location, 4326)))
# Create issue about SRID (4326)
return query.filter(
bounding_shape.ST_Contains(
func.ST_Transform(self.model.location, 4326)))
def check_if_exists(self, json_list):
"""
Check if sample exists in the database and if it does add the additional metadata
"""
db = app_context().database
Sample = db.model.sample
samples_that_exist = []
for inx, row in enumerate(json_list):
name = row['name']
res = db.session.query(Sample).filter_by(name=name).all()
material_check(db, row['material'])
if len(res) > 0:
## sample already exists
samples_that_exist.append(inx)
existing = res[0]
for k, v in row.items():
setattr(existing, k, v)
assert len(db.session.dirty) > 0
try:
print("Added data to existing sample")
db.session.commit()
except:
print("Error")
db.session.rollback()
for i in reversed(samples_that_exist
): # reverse so indexes stay in correct place
del json_list[i]
return json_list
def should_apply(self):
db = app_context().database
apply = False
for model in self.possible_models:
if self.model == getattr(db.model, model):
apply = True
break
return apply
def apply(self, args, query):
if self.key not in args:
return query
if hasattr(self.model, "tags_tag_collection"):
db = app_context().database
tag = db.model.tags_tag
ids = args[self.key]
return query.join(self.model.tags_tag_collection).filter(
tag.id.in_([*ids]))
return query
async def put(self, request):
db = app_context().database
try:
model_name = request.path_params["model_name"]
req = await request.json()
data = req["data"]
log.debug(data)
res = db.load_data(model_name, data)
return JSONResponse({
"status": "success",
"model": f"{model_name}",
"id": res.id
})
except Exception as err:
return construct_error_response(err, 400)
def apply(self, args, query):
if self.key not in args:
return query
search_query = args[self.key]
search_query = " & ".join(
search_query.split()) ## breaks sentences into words
db = app_context().database
self.check_document_tables(db)
document_tables = [
self.project_document,
self.sample_document,
self.session_document,
]
return construct_query(db, self.model, search_query, document_tables)
async def put(self, request):
"""
Adds a tag model relationship through sqlalchemy orm
"""
db = app_context().database
model_name = request.path_params["model"]
model = getattr(db.model, model_name)
tags = db.model.tags_tag
data = await request.json()
with db.session_scope():
tag_ids = data["tag_ids"]
model_id = data["model_id"]
# get current model and that models tag collection
current_model = db.session.query(model).get(model_id)
current_collection = current_model.tags_tag_collection
for tag_id in tag_ids:
tag = db.session.query(tags).get(tag_id)
current_model.tags_tag_collection = [*current_collection, tag]
try:
db.session.commit()
except:
db.session.rollback()
return JSONResponse(
{
"Status": "Error",
"message": f"cannot insert tag {tag_id}"
},
status_code=404,
)
return JSONResponse({
"Status": "Success",
"tag_ids": f"{tag_ids}",
"model": f"{model_name}",
"model_id": f"{model_id}",
})
def iterfiles(self, filename):
"""
Read in csv and perform some data cleaning
"""
db = app_context().database
here = Path(__file__).parent
fn = here / filename
insert_sample = here / 'insert-sample.sql' # db.exec_sql
df = pd.read_csv(fn)
df = df[df['Sample ID'].notna(
)] ## gets rid of any row that doesn't have a sample name
# format long/lat columns
df['Longitude'] = df['Longitude'].apply(self.clean_long_lat_to_float)
df['Latitude'] = df['Latitude'].apply(self.clean_long_lat_to_float)
# remove the rest of the unparseable coordinates
df = self.drop_unparseable_coord(df)
# create json list for the eventual load data
json_list = self.create_sample_dict(df)
# check if exsits
json_list = self.check_if_exists(json_list)
# load in all the rest of the data
for ele in json_list:
params = {
'name': ele['name'],
'material': ele['material'],
'location': ele['location']
}
try:
db.exec_sql(insert_sample, params)
print(f"Inserting {ele['name']}")
except:
embed()
print('Something went very wrong')
def apply(self, args, query):
if self.key not in args:
return query
model_name = create_model_name_string(self.model)
db = app_context().database
age = float(args[self.key])
if hasattr(self.model, "datum_collection"):
datum = db.model.datum
return query.join(
self.model.datum_collection).filter(datum.value < age)
path = nested_collection_path(model_name, "datum")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
datum = db.model.datum
return db_query.filter(datum.value < age)
return query.filter(self.model.value < age)
def apply(self, args, query):
if self.key not in args:
return query
model_name = create_model_name_string(self.model)
doi_string = args[self.key]
db = app_context().database
publication = db.model.publication
if hasattr(self.model, "publication_collection"):
return query.join(self.model.publication_collection).filter(
publication.doi.like(f"%{doi_string}%"))
path = nested_collection_path(model_name, "publication")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
return db_query.filter(publication.doi.like(f"%{doi_string}%"))
## this allows for fuzzy searching
return query.filter(self.model.doi.like(f"%{doi_string}%"))