async def assign_post(courier_id: CourierID):
response_ids = []
default_response = JSONResponse(status_code=status.HTTP_400_BAD_REQUEST)
db = Session()
courier_from_db = db.query(CourierSchema).get(courier_id.courier_id)
if not courier_from_db:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Courier not found")
query = db.query(OrderSchema).filter(
and_(OrderSchema.region.in_(courier_from_db.regions)),
(OrderSchema.weight <= courier_from_db.courier_type.weight())).all()
if not query:
return default_response
for order in query:
if check_courier_time_for_order(courier_from_db.working_hours,
order.delivery_hours):
order.courier_id_assigned = courier_from_db.courier_id
response_ids.append(order.order_id)
if response_ids:
db.commit()
return JSONResponse(status_code=status.HTTP_200_OK,
content={
'orders': [{
'id': id_
} for id_ in response_ids],
'assign_time':
datetime.now().isoformat()[:-4] + 'Z'
})
else:
return default_response
async def complete_post(order: OrderDone):
db = Session()
courier_from_db = db.query(CourierSchema).get(order.courier_id)
order_from_db = db.query(OrderSchema).get(order.order_id)
if not courier_from_db or not order_from_db or courier_from_db.courier_id != order.courier_id:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Wrong courier or order id")
# TODO count rating
db.delete(order_from_db)
db.commit()
return JSONResponse(status_code=status.HTTP_200_OK,
content={'order_id': order.order_id})
def today_orders_import(ctx):
"""
read orders from url and write new today orders into database
"""
url = 'https://online.moysklad.ru/api/remap/1.1/entity/customerorder'
login = '******'
password = '******'
resp = get(url, auth=HTTPBasicAuth(login, password))
get_orders = resp.json()
session_orders = Session()
session_clients = Session()
if settings.debug:
today = date(2019, 2, 15)
else:
today = datetime.today().date()
for i in get_orders['rows']:
if datetime.strptime(i['moment'], "%Y-%m-%d %H:%M:%S").date() == today:
if not session_orders.query(DenysOrders).filter(
DenysOrders.id == i['id']).all():
client_data = get(i['agent']['meta']['href'],
auth=HTTPBasicAuth(login, password)).json()
if not session_clients.query(DenysClients).filter(
DenysClients.id == client_data['id']).all():
session_clients.add(
DenysClients(
id=client_data['id'],
name=client_data['name'],
))
session_orders.add(
DenysOrders(
id=i['id'],
name=i['name'],
# description =
moment=i['moment'],
sum=i['sum'],
counterparty_id=client_data['id'],
))
session_clients.commit()
session_orders.commit()
async def get_courier(courier_id: int):
db = Session()
query = db.query(CourierSchema).get(courier_id)
# TODO дополнительные поля и подсчет рейтинга!
if not query:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
detail="Courier not found")
return JSONResponse(query.to_dict())
def create_top_one_train_data(
min_appts: int) -> Tuple[pd.DataFrame, pd.DataFrame]:
session = Session()
all_users = get_all_appointment_users(session, min_appts)
doctor_vectors = DoctorVector()
doctor_vectors.load()
test = []
train = []
for user in progressbar(all_users):
# add test data
last_appt, test_appt = get_two_last_appointment(session, user)
test_doctor_model = session.query(Doctor).filter(
Doctor.id == test_appt.doctor_id).first()
test_doctors = get_town_doctor_list(session, test_doctor_model.town_id,
test_appt.spec_id)
test_appt_id = test_appt.id
for doctor in test_doctors[:100]:
if doctor == test_doctor_model.id:
continue
test.append([0, test_appt_id, *doctor_vectors[doctor]])
test.append([1, test_appt_id, *doctor_vectors[test_doctor_model.id]])
# add train data
all_user_appts = get_all_appointments(
session, user, [last_appt.doctor_id, test_appt.doctor_id])
for appt in all_user_appts:
appt_id = appt.id
train.append([1, appt_id, *doctor_vectors[appt.doctor_id]])
train_doctor_model = session.query(Doctor).filter(
Doctor.id == appt.doctor_id).first()
train_town_doctors = get_town_doctor_list(
session, train_doctor_model.town_id, appt.spec_id)
for doctor in train_town_doctors[:100]:
if doctor == train_doctor_model.id:
continue
train.append([0, appt_id, *doctor_vectors[doctor]])
test_df = pd.DataFrame(test)
train_df = pd.DataFrame(train)
return test_df, train_df
def excel_read(ctx):
"""
read from excel file and fill the database
"""
session = Session()
session.query(DenysOrders).delete()
session.query(DenysClients).delete()
loc = '../task/ПримерМСКонтрагентыSQLAzure2.xlsx'
wb = xlrd.open_workbook(loc)
orders_sheet = wb.sheet_by_index(0)
counterparty_sheet = wb.sheet_by_index(1)
clients_list = []
for i in range(1, counterparty_sheet.nrows):
values = counterparty_sheet.row_values(i)
clients_list.append(DenysClients(
id=values[0],
name=values[1],
))
session.add_all(clients_list)
session.commit()
orders_list = []
for i in range(1, orders_sheet.nrows):
values = orders_sheet.row_values(i)
orders_list.append(
DenysOrders(
id=values[0],
name=values[1],
description=values[2],
moment=values[3],
sum=values[4],
counterparty_id=values[5],
))
session.add_all(orders_list)
session.commit()
def create(self):
session = Session()
data = pd.DataFrame.from_records(session.query(Doctor).order_by(
Doctor.id).all(),
columns=Doctor._fields)
doctor_stats = data.iloc[:, [0, 1, 2]]
matrix = MinMaxScaler().fit_transform(data.iloc[:, 3:])
doctors = {}
progress = progressbar.ProgressBar(max_value=data.shape[0])
for stat, values in zip(doctor_stats.iterrows(), matrix):
doctors[str(int(stat[1][0]))] = values.tolist()
progress.next()
self._vectors = doctors
async def patch_courier(courier_id: int, data: PatchCourier):
db = Session()
query = db.query(CourierSchema).get(courier_id)
if not query:
raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
detail="Courier not found")
else:
if data.courier_type and data.regions and data.working_hours:
query.regions = data.regions
db.commit()
return JSONResponse(query.to_dict(),
status_code=status.HTTP_200_OK)
else:
raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
detail="Wrong fields")
def create(self):
doctor_vectors = DoctorVector()
doctor_vectors.load()
session = Session()
appts = {}
for appt in session.query(Appointment.user_id,
Appointment.doctor_id).all():
appts.setdefault(str(appt.user_id), []).append(int(appt.doctor_id))
users_vectors = {}
progress = progressbar.ProgressBar(max_value=len(appts))
for user_id, doctor_ids in appts.items():
vectors = [doctor_vectors[doctor_id] for doctor_id in doctor_ids]
users_vectors[user_id] = np.sum(vectors, axis=0).tolist()
progress.update(progress.value + 1)
self._vectors = users_vectors
def get_basic_metrics(self):
"""
Get a set of basic metrics related to the Spark application.
:return: dict (metric_name: value)
"""
db = Session()
basic_metrics = {}
runtime = self.duration / 1000.0 # seconds
cpu_time = cast_or_none(
db.query(func.sum(StageEntity.executor_cpu_time) /
1.0e9).filter(StageEntity.app_id == self.app_id).scalar(),
float) # seconds
total_tasks_time = cast_or_none(
db.query(func.sum(TaskEntity.duration) / 1000.0).filter(
and_(TaskEntity.stage_key == StageEntity.stage_key,
StageEntity.app_id == self.app_id)).scalar(), float)
total_gc_time = cast_or_none(
db.query(func.sum(ExecutorEntity.total_gc_time) / 1.0e3).filter(
ExecutorEntity.app_id == self.app_id).scalar(),
float) # seconds
basic_metrics["Duration"] = fmt_time(runtime) # seconds
basic_metrics["CPU Time"] = fmt_time(cpu_time) # seconds
basic_metrics["Total Tasks Time"] = fmt_time(total_tasks_time)
basic_metrics["Spark Mode"] = self.mode
basic_metrics["Jobs Number"] = db.query(func.count(
JobEntity.job_id)).filter(
JobEntity.app_id == self.app_id).scalar()
basic_metrics["Stages Number"] = db.query(
func.count(StageEntity.stage_id)).filter(
StageEntity.app_id == self.app_id).scalar()
basic_metrics["Tasks Number"] = cast_or_none(
db.query(func.sum(StageEntity.num_tasks)).filter(
StageEntity.app_id == self.app_id).scalar(), int)
basic_metrics["Total GC Time"] = fmt_time(total_gc_time) # seconds
return basic_metrics
def __str__(self):
return f'Reaction[id={self.id}, seen_date={self.seen_date}, reaction={self.reaction}, user={self.user}, quote_id={self.quote}]'
if __name__ == '__main__':
Base.metadata.create_all(engine)
session = Session()
user = User('3649028588457703', 'DAILY')
session.add(user)
quote = Quote(
"If you want to achieve greatness stop asking for permission.",
"Anonymous")
session.add(quote)
session.commit()
user_quote = Reaction('3649028588457703', quote.id, 'LIKE')
session.add(user_quote)
session.commit()
session.close()
print("=== Now querying data ===")
session = Session()
users = session.query(User).all()
for user in users:
print(user)
quotes = session.query(Quote).all()
for quote in quotes:
print(quote)
reactions = session.query(Quote.text).join(Reaction) \
.filter(Reaction.reaction == 'LIKE')
for reaction in reactions:
print(reaction)
class Context:
def __init__(self):
Base.metadata.create_all(engine)
self.session = Session()
def getAdById(self, adId):
return self.session.query(Ad).get(adId)
def getAdByTitle(self, title):
return self.session.query(Ad).filter_by(title=title).one_or_none()
def getAdsByTags(self, tags):
return self.session.query(Ad).filter(Ad.hiddenTags.any(Tag.value.in_(tags))).all()
def addAd(self, ad):
self._adTags(ad)
try:
self.session.commit()
self.session.add(ad)
self.session.commit()
return ad
except exc.SQLAlchemyError:
print("Context: cannot create ad: ", ad)
return None
def getAds(self, limit, offset=0):
return self.session.query(Ad).limit(limit).offset(offset).all()
def updateAd(self, adId, updated):
fromDb = self.getAdById(adId)
if fromDb is None:
return None
self._removeTags(fromDb)
self._adTags(updated)
try:
fromDb.update(updated)
self.session.commit()
return fromDb
except exc.SQLAlchemyError:
print("Context: cannot remove ad: ", fromDb)
return None
def _adTags(self, ad):
try:
for tag in ad.tags:
self.session.add(tag)
except exc.SQLAlchemyError:
print("Context: cannot add hidden tags: ", ad.tags)
return None
try:
for tag in ad.hiddenTags:
self.session.add(tag)
except exc.SQLAlchemyError:
print("Context: cannot add hidden tags: ", ad.hiddenTags)
def _removeTags(self, ad):
try:
for tag in ad.tags:
self.session.query(ad_tags_association).filter_by(tag_id=tag.id).delete()
self.session.delete(tag)
self.session.commit()
except exc.SQLAlchemyError:
print("Context: cannot delete tags: ", ad.tags)
return None
try:
for tag in ad.hiddenTags:
self.session.query(hidden_ad_tags_association).filter_by(tag_id=tag.id).delete()
self.session.delete(tag)
self.session.commit()
except exc.SQLAlchemyError:
print("Context: cannot delete hidden tags: ", ad.hiddenTags)
def removeAd(self, adId):
fromDb = self.getAdById(adId)
if fromDb is None:
return False
self._removeTags(fromDb)
try:
self.session.commit()
self.session.delete(fromDb)
self.session.commit()
return True
except exc.SQLAlchemyError:
print("Context: cannot delete ad: ", fromDb)
return False
def getTagById(self, tagId):
return self.session.query(Tag).get(tagId)
def getTagByValue(self, value):
return self.session.query(Tag).filter_by(value=value).first()
def addTag(self, tag):
self.session.add(tag)
self.session.commit()
def __del__(self):
try:
self.session.close()
except AttributeError as err:
print(err)
class PrometeyService():
def __init__(self):
Base.metadata.create_all(engine)
self.session = Session()
self.__downloader = PrometeyDownloader()
self.__amazon = PrometeyAmazon(config['aws'])
@Transactional
def register_user(self, telegram_user_id):
user = User(telegram_user_id)
self.session.add(user)
return user
@Transactional
def get_user(self, telegram_user_id):
user = self.session.query(User).filter(User.telegram_user_id == telegram_user_id).one_or_none()
return user
@Transactional
def create_content(self, user_id, name):
content = Content(name, 'DRAFT')
content.user_id = user_id
self.session.add(content)
self.session.flush()
user = self.session.query(User).filter(User.id == user_id).one()
user.current_content = content
return content
@Transactional
def change_current(self, user_id, new_content_id):
user = self.session.query(User).filter(User.id == user_id).one()
user.current_content_id = new_content_id
@Transactional
def add_url(self, user_id, url):
user = self.session.query(User).filter(User.id == user_id).one()
t = urlparse(url)
if user.current_content and user.current_content.status == 'DRAFT' and t.hostname == 'vm.tiktok.com':
video = Video(url)
user.current_content.videos.append(video)
return video
else:
pass # todo ошибка
@Transactional
def finish_video(self, user_id):
user = self.session.query(User).filter(User.id == user_id).one()
content = user.current_content
if content:
content.status = 'FINISHED'
user.current_content_id = None
return content
# stay
def get_download_list(self):
videos = self.session.query(Video).join(Content). \
filter(Video.status == 'NEW').limit(3)
return videos
@Transactional
def set_video_status_downloaded(self, video):
video.status = 'DOWNLOADED'
@Transactional
def get_content_to_encode(self):
content = self.session.query(Content).join(Video). \
filter(Content.status == "FINISHED"). \
filter(Content.all_downloaded).first()
return content
@Transactional
def get_content_to_send(self):
content = self.session.query(Content).filter(Content.status == "READY").first()
return content
@Transactional
def set_content_status(self, content, status):
content.status = status
return content
def download_videos(self):
videos = self.get_download_list()
for video in videos:
self.__download_video(video)
return videos
@Transactional
def __download_video(self, video):
data = self.__downloader.download_video(video.url)
print(f'{video.url} - скачано из сети.')
key = f'{video.content_id}/{video.id}.mp4'
print(f'Загружаю на s3 {key}')
print(self.__amazon.upload(key, data))
video.status = 'DOWNLOADED'
from Models.ad_tags import ad_tags_association, hidden_ad_tags_association from Models.ad import Ad from Models.tag import Tag from db.base import Base, Session, engine Base.metadata.create_all(engine) session = Session() session.query(ad_tags_association).delete() session.query(hidden_ad_tags_association).delete() session.query(Tag).delete() session.query(Ad).delete() session.commit() session.close()
