def make_chart_sql(year, market1, market2):
query1 = db.select([data]).where(
db.and_(data.columns.year == year, data.columns.market == market1))
data1 = connection.execute(query1).fetchall()[0][2:]
query2 = db.select([data]).where(
db.and_(data.columns.year == year, data.columns.market == market2))
data2 = connection.execute(query2).fetchall()[0][2:]
img = io.BytesIO()
fig, ax = plt.subplots(figsize=(6, 4))
pos = list(range(len(seasons)))
width = 0.35
plt.bar(pos, data1, width, color='#58ACB6')
plt.bar([p + width for p in pos], data2, width, color='#7067A0')
plt.title(market1 + ' vs. ' + market2 + ' - ' + year)
plt.legend([market1, market2])
ax.set_xticks([p + 0.5 * width for p in pos])
ax.set_xticklabels(seasons)
fig.savefig(img,
format='png',
transparent=True,
bbox_inches='tight',
pad_inches=0)
img.seek(0)
chart_url = base64.b64encode(img.getvalue()).decode()
plt.close()
return 'data:image/png;base64,{}'.format(chart_url)
def get_all_feature_bytree(cls, whereitem, wherestr):
'''抓取展示目录，全部'''
if whereitem == 'link_project':
return [{
"project_name":
project.name,
"link_features":
[feature.json() for feature in project.features]
} for project in ProjectModel.query.filter_by(id=wherestr).all()]
elif whereitem == 'link_date':
return [{
"project_name":
project.name,
"link_features": [
feature.json() for feature in project.features.filter(
db.and_(
db.extract('month', FeatureModel.create_time) ==
wherestr[5:7],
db.extract('year', FeatureModel.create_time) ==
wherestr[:4])).all()
]
} for project in ProjectModel.query.all()]
else:
return [{
"project_name":
project.name,
"link_features": [
feature.json() for feature in project.features.filter(
db.and_(
FeatureModel.name.like('%' + wherestr + '%'),
FeatureModel.introduce.like('%' + wherestr +
'%'))).all()
]
} for project in ProjectModel.query.all()]
def get_relationship(user1, user2):
if (user1 < user2):
return Friendship.query.filter(
db.and_(Friendship.user_1_id == user1,
Friendship.user_2_id == user2)).first()
else:
return Friendship.query.filter(
db.and_(Friendship.user_1_id == user2,
Friendship.user_2_id == user1)).first()
def get_all_acricles_bytree(cls, whereitem, wherestr):
'''抓取sidebar目录,全部'''
if whereitem == 'link_project':
return [{
"project_name":
project.name,
"link_articles":
[article.json() for article in project.articles]
} for project in ProjectModel.query.filter_by(id=wherestr).all()]
elif whereitem == 'link_date':
return [{
"project_name":
project.name,
"link_articles": [
article.json() for article in project.articles.filter(
db.and_(
db.extract('month', ArticleModel.create_time) ==
wherestr[5:7],
db.extract('year', ArticleModel.create_time) ==
wherestr[:4])).all()
]
} for project in ProjectModel.query.all()]
else:
print(whereitem)
return [{
"project_name":
project.name,
"link_articles": [
article.json() for article in project.articles.filter(
db.or_(
ArticleModel.topic.like('%' + wherestr + '%'),
ArticleModel.introduce.like('%' + wherestr +
'%'))).all()
]
} for project in ProjectModel.query.all()]
def get_by_user_id_vendor_id_start_date(cls, user_id, social_network_id,
start_date):
assert user_id and social_network_id and start_date
return cls.query.filter(
db.and_(Event.user_id == user_id,
Event.social_network_id == social_network_id,
Event.start_datetime >= start_date)).all()
def get_by_user_id_params_type_source_id(cls, user_id, params, type,
source_id):
return cls.query.filter(
db.and_(
Activity.user_id == user_id,
Activity.params == params,
Activity.type == type,
Activity.source_id == source_id,
)).first()
def get_by_social_network_rsvp_id_and_social_network_id(cls,
social_network_rsvp_id,
social_network_id,
):
assert social_network_id and social_network_rsvp_id
return cls.query.filter(
db.and_(
RSVP.social_network_rsvp_id == social_network_rsvp_id,
RSVP.social_network_id == social_network_id
)
).first()
def get_by_vendor_rsvp_id_candidate_id_vendor_id_time(cls, vendor_rsvp_id,
candidate_id,
social_network_id,
added_time):
assert vendor_rsvp_id and candidate_id and social_network_id
return cls.query.filter(
db.and_(
RSVP.social_network_rsvp_id == vendor_rsvp_id,
RSVP.candidate_id == candidate_id,
RSVP.social_network_id == social_network_id,
RSVP.rsvp_datetime == added_time,
)
).first()
def get_by_vendor_rsvp_id_candidate_id_vendor_id_event_id(cls,
vendor_rsvp_id,
candidate_id,
social_network_id,
event_id):
assert vendor_rsvp_id and candidate_id and social_network_id and event_id
return cls.query.filter(
db.and_(
RSVP.social_network_rsvp_id == vendor_rsvp_id,
RSVP.candidate_id == candidate_id,
RSVP.social_network_id == social_network_id,
RSVP.event_id == event_id
)
).first()
def near_flight():
flight_id = request.args.get('flight_id', None, type=int)
if not flight_id:
return jsonify({'error': 'flight_id required'})
flight = Flight.query.filter(
Flight.id == flight_id
).first()
if not flight:
return jsonify({'error': 'Flight not found for id'})
query = Flight.query.filter(
db.and_(
Flight.departure_time <
flight.departure_time + datetime.timedelta(days=1),
Flight.departure_time >
flight.departure_time - datetime.timedelta(days=1),
Flight.id != flight_id
)
)
list = [
format_flight(flight) for flight in query
]
return jsonify({'result': list})
def get_by_name(cls, vendor_name):
assert vendor_name
return cls.query.filter(db.and_(Product.name == vendor_name)).first()
def get_area_of_interest(cls, domain_id, name):
return cls.query.filter(
db.and_(AreaOfInterest.domain_id == domain_id,
AreaOfInterest.name == name)).first()