def test_sql_write_read_session(self):
print(_data_sql)
with get_session() as session:
# Note: There is no commit operation occuring here, so this data won't be written to the database
session.execute(_data_sql)
test_result = session.execute(
'SELECT * FROM productraw p;').fetchone()
self.assertNotEqual(test_result, None)
def products():
routelogger.info("Running Vaccines Query")
with get_session(context=True) as session:
meds = (
session.query(TrialRaw)
.filter(TrialRaw.intervention.like("%vaccine%"))
.all()
)
return jsonify([med.to_json() for med in meds])
def get_product_milestones(context=True):
""" Get all product_milestones currently in DB"""
with get_session(context) as session:
milesone_info = session.query(
ProductMilestone.link_id, ProductMilestone.product_id,
ProductMilestone.milestone_id, ProductMilestone.date,
ProductMilestone.status, Milestone.name,
Milestone.category).join(ProductMilestone).all()
return pd.DataFrame(milesone_info,
columns=[
'link_id', 'product_id', 'milestone_id', 'date',
'status', 'milestone_name', 'category'
])
def execute(self, **params):
writelogger.info(
f'Starting write execution. Processing stack of: {len(self.data)}')
with get_session(context=False) as session:
for record in self.dataframe_to_dict(self.data):
self.make_or_update(
model=self.model,
record=record,
session=session,
primary_key=self._primary_keys,
)
session.commit()
writelogger.info('Stack comitted.')
def treatments():
routelogger.info("Running Treatments Query")
with get_session(context=True) as session:
meds = (
session.query(TrialRaw)
.filter(
or_(
TrialRaw.intervention_type.like("%traditional%"),
TrialRaw.intervention_type.like("%drug%"),
)
)
.all()
)
return jsonify([med.to_json() for med in meds])
def alternatives():
routelogger.info("Running Alternatives Query")
with get_session(context=True) as session:
meds = (
session.query(TrialRaw)
.filter(
and_(
~TrialRaw.intervention_type.like("%traditional%"),
~TrialRaw.intervention_type.like("%drug%"),
~TrialRaw.intervention.like("%vaccine%"),
)
)
.all()
)
return jsonify([med.to_json() for med in meds])
def get_product_locations(context=True):
with get_session(context) as session:
location_info = session.query(
ProductSiteLocation.link_id, ProductSiteLocation.product_id,
ProductSiteLocation.site_location_id, SiteLocation.name,
SiteLocation.city, SiteLocation.state, SiteLocation.country,
SiteLocation.lat,
SiteLocation.lng).join(ProductSiteLocation).all()
return pd.DataFrame(location_info,
columns=[
'link_id',
'product_id',
'site_location_id',
'location_name',
'city',
'state',
'country',
'lat',
'lng',
])
def assets():
routelogger.info("Running Products Query")
with get_session(context=True) as session:
assets = session.query(ProductRaw).all()
# Serialize the assets
serialized_assets = [ccase_serializer.transform(item.json) for item in assets]
# Get related data
sponsors = condense_sponsors(get_product_sponsors())
milestones = condense_milestones(get_product_milestones())
sitelocations = condense_locations(get_product_locations())
for asset in serialized_assets:
asset['sponsors'] = fetch_value(sponsors, asset['productId'])
asset['milestones'] = fetch_value(milestones, asset['productId'])
asset['siteLocations'] = fetch_value(sitelocations, asset['productId'])
asset['sources'] = asset['sources'].split(',')
asset['countries'] = asset['countries'].split(',')
asset['countryCodes'] = asset['countryCodes'].split(',')
return jsonify(serialized_assets)
def get_site_locations(context=True):
with get_session() as session:
return session.query(SiteLocation).all()
def get_product_sponsors(context=True):
with get_session(context) as session:
sponsors_info = session.query(
ProductSponsor.product_id, Sponsor.sponsor_id,
Sponsor.sponsor_name).join(ProductSponsor).all()
return sponsors_info
def get_sponsors(context=True):
""" Get all sponsors currently in DB """
with get_session(context) as session:
sponsors = session.query(Sponsor.sponsor_id,
Sponsor.sponsor_name).all()
return pd.DataFrame(sponsors, columns=['sponsor_id', 'sponsor_name'])
def get_product_names(context=True):
""" Get all product preferred names currently in raw """
with get_session(context) as session:
prod_names = session.query(ProductRaw.preferred_name).all()
return [x[0] for x in prod_names]
def test_session_context(self):
with get_session() as session:
session.execute('SELECT 1')