def hw_remove_aeroporto(id):
session = SessionLocal()
aeroporto = session.query(Aeroporto).filter_by(id=id).first()
session.delete(aeroporto)
session.commit()
session.close()
return {"message": f"Aeroporto {aeroporto.id} was deleted"}
def hw_remove_reserva(id):
session = SessionLocal()
reserva = session.query(Reserva).filter_by(id=id).first()
session.delete(reserva)
session.commit()
session.close()
return {"message": f"Reserva {reserva.id} was deleted"}
def make_prediction(id: int):
"""
This is what make the predictions
"""
db = SessionLocal()
sms_table = db.query(FraudSMS).filter(FraudSMS.id == id).first()
model_path = "./lib/xgb_model.pkl"
xgb_model = joblib.load(model_path)
try:
raw = sms_table.sms_text
processor = custom_processor.InputTransformer()
data = processor.transform(raw)
print(data)
except ValueError:
print("Request has no data or is not a valid json object")
predictions = xgb_model.predict(data)
probability = xgb_model.predict_proba(data)
results = dict()
sms_table.probability = list(probability)[0][1]
# sms_table.prediction = list(predictions)[0]
db.add(sms_table)
db.commit()
def hw_remove_voo(id):
session = SessionLocal()
voo = session.query(Voo).filter_by(id=id).first()
session.delete(voo)
session.commit()
session.close()
return {"message": f"Voo {voo.id} was deleted"}
def send_response():
message = request.form['response']
db_wiz = SessionLocal()
global user_utter
global wizard_utter
# User and Wizard utterance from last record
user_utterance = db_wiz.query(models.UserQuery).order_by(models.UserQuery.id.desc()).first().user_request
wizard_utterance = db_wiz.query(models.UserQuery).order_by(models.UserQuery.id.desc()).first().wizard_response
if (user_utterance == user_utter and user_utterance != "Listening..." and wizard_utterance == "No Response"):
# Setting global variable
wizard_utter = message
# Updating last query wizard response
db_wiz.query(models.UserQuery).order_by(models.UserQuery.id.desc()).first().wizard_response = message
db_wiz.commit()
db_wiz.close()
user_utter="Listening..."
# project_id = os.getenv('DIALOGFLOW_PROJECT_ID')
# fulfillment_text = detect_intent_texts(project_id, "unique", "wizard says "+message, 'en')
# response_text = { "message": fulfillment_text }
# return jsonify(response_text)
return message
def fetch_stock_data(id: int):
# Create a new db session in the background
db = SessionLocal()
# Query the db for the matching stock record using this id
stock = db.query(Stock).filter(Stock.id == id).first()
# Test that our function even works before fetching from YF
# by manually updating 'forward_pe' field
# stock.forward_pe = 10
# Get data from YF and map to our table columns
yahoo_data = yfinance.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info["twoHundredDayAverage"]
stock.ma50 = yahoo_data.info["fiftyDayAverage"]
stock.price = yahoo_data.info["previousClose"]
stock.forward_pe = yahoo_data.info["forwardPE"]
stock.forward_eps = yahoo_data.info["forwardEps"]
# Some stocks don't give dividend_yield, so add conditional
if yahoo_data.info["dividendYield"] is not None:
stock.dividend_yield = yahoo_data.info["dividendYield"] * 100
# Update the record
db.add(stock)
db.commit()
def hw_add_aeroporto(request):
session = SessionLocal()
aeroporto = Aeroporto(nome=request["nome"], cidade=request["cidade"])
# aeroporto_json = populate_aeroporto([aeroporto])
session.add(aeroporto)
session.commit()
aeroporto_json = hw_get_aeroporto(aeroporto.id)
session.close()
return aeroporto_json
async def delete(id: int):
"""
tenteando deleta
"""
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
db.remove(stock)
db.commit()
def fetch_the_data(id: int):
db = SessionLocal()
house = db.query(house_price).filter(house_price.id == id)
house.MedInc = house['MedInc']
db.add(house)
db.commit()
def create_favorite_item(item_id: None, owner: None):
attributes = oac.search_by_imdb_id(item_id, sanitize=True)
db_favorite_item = models.FavoriteMovie(id=str(uuid.uuid1()),
imdb_id=item_id,
attributes=attributes,
owner_id=owner.id)
db = SessionLocal()
db.add(db_favorite_item)
db.commit()
db.refresh(db_favorite_item)
db.close()
return db_favorite_item
def yahoo_data_fetch(id: int):
db = SessionLocal()
stock_data = db.query(StockData).filter(StockData.id == id).first()
yahoo_stock_data = yf.Ticker(stock_data.stockname)
stock_data.ma200 = yahoo_stock_data.info["twoHundredDayAverage"]
stock_data.ma50 = yahoo_stock_data.info["fiftyDayAverage"]
stock_data.price = yahoo_stock_data.info["previousClose"]
stock_data.forward_pe = yahoo_stock_data.info["forwardPE"]
stock_data.forward_eps = yahoo_stock_data.info["forwardEps"]
if stock_data.dividend_yield is not None:
stock_data.dividend_yield = yahoo_stock_data.info["dividendYield"] * 100
db.add(stock_data)
db.commit()
def hw_update_aeroporto(request):
session = SessionLocal()
aeroporto_old = session.query(Aeroporto).filter_by(
id=request["id"]).first()
session.delete(aeroporto_old)
aeroporto = Aeroporto(id=request["id"],
nome=request["nome"],
cidade=request["cidade"])
session.add(aeroporto)
session.commit()
aeroporto_json = hw_get_aeroporto(aeroporto.id)
session.close()
return aeroporto_json
def fetch_stock_data(id: int):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yf.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_eps = yahoo_data.info['forwardEps']
stock.dividend_yield = (yahoo_data.info['dividendYield'] or 1) * 100
db.add(stock)
db.commit()
def hw_update_reserva(request):
session = SessionLocal()
reserva_old = session.query(Reserva).filter_by(id=request["id"]).first()
session.delete(reserva_old)
reserva = Reserva(
id_voo=request["id_voo"],
id_cadastro=request["id_cadastro"],
e_ticket=request["e_ticket"],
)
session.add(reserva)
session.commit()
reserva_json = hw_get_reserva(reserva.id)
session.close()
return reserva_json
def fetch_stock_data(id: int):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yf.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info["twoHundredDayAverage"]
stock.ma50 = yahoo_data.info["fiftyDayAverage"]
stock.price = yahoo_data.info["previousClose"]
stock.forward_pe = yahoo_data.info["forwardPE"]
stock.forward_eps = yahoo_data.info["forwardEps"]
if yahoo_data.info["dividendYield"]:
stock.dividend_yield = yahoo_data.info["dividendYield"] * 100
db.add(stock)
db.commit()
def hw_add_reserva(request):
session = SessionLocal()
reserva = Reserva(id_voo=request["id_voo"],
id_cadastro=request["id_cadastro"])
session.add(reserva)
session.commit()
hash_obj = hashlib.md5(f"{reserva.id}".encode())
md5_value = hash_obj.hexdigest()
reserva.e_ticket = md5_value
session.add(reserva)
session.commit()
reserva_json = hw_get_reserva(reserva.id)
session.close()
return reserva_json
def populate_data():
db = SessionLocal()
countries = covid.getLocations(rank_by='deaths')
for country in countries:
db_record = models.State(id=country['id'],
country=country['country'],
country_code=country['country_code'],
population=country['country_population'],
last_updated=country['last_updated'],
confirmed=((country['latest'])['confirmed']),
deaths=((country['latest'])['deaths']),
recovered=((country['latest'])['recovered']))
db.add(db_record)
db.commit()
def fetch_stock_data(_id: int):
db = SessionLocal()
stock: Stock = db.query(Stock).filter(Stock.id == _id).first()
yfinance_data = yf.Ticker(stock.symbol)
if yfinance_data.info['dividendYield'] is not None:
stock.dividend_yield = yfinance_data.info['dividendYield'] * 100
stock.forward_eps = yfinance_data.info['forwardEps']
stock.forward_pe = yfinance_data.info['forwardPE']
stock.price = yfinance_data.info['previousClose']
stock.ma50 = yfinance_data.info['fiftyDayAverage']
stock.ma200 = yfinance_data.info['twoHundredDayAverage']
db.add(stock)
db.commit()
def fetch_stock_data(id: int):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yfinance.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_eps = yahoo_data.info['forwardEps']
dividend = yahoo_data.info['dividendYield']
stock.dividend_yield = 0 if dividend == None else dividend * 100
db.add(stock)
db.commit()
print(' Data fetched from Yahoo!Finance and saved for', stock.symbol)
def hw_add_voo(request):
session = SessionLocal()
voo = Voo(
data=datetime.strptime(request["data"], "%d/%m/%Y %H:%M:%S"),
destino=request["destino"],
companhia=request["companhia"],
capacidade=request["capacidade"],
ocupacao=request["ocupacao"],
preco=request["preco"],
id_aeroporto=request["id_aeroporto"],
)
session.add(voo)
session.commit()
voo_json = hw_get_voo(voo.id)
session.close()
return voo_json
def is_video_data_exists(oid):
"""
查询bilibiliVideos表中是否已存在该视频
"""
try:
session = SessionLocal()
# 查询是否存在
count = session.query(BilibiliVideo).filter(
BilibiliVideo.oid == oid).count()
except Exception as e:
print(e)
session.close()
return False
else:
session.commit()
return count > 0
def delete_video_by_oid(oid):
"""
查询bilibiliVideos表中是否已存在该视频
"""
try:
session = SessionLocal()
# 查询是否存在
del_video = session.query(BilibiliVideo).filter(
BilibiliVideo.oid == oid).first()
session.delete(del_video)
except Exception as e:
print(e)
session.close()
return False
else:
session.commit()
return True
def hw_update_voo(request):
session = SessionLocal()
voo_old = session.query(Voo).filter_by(id=request["id"]).first()
session.delete(voo_old)
voo = Voo(
id=request["id"],
data=datetime.strptime(request["data"], "%d/%m/%Y %H:%M:%S"),
destino=request["destino"],
companhia=request["companhia"],
capacidade=request["capacidade"],
ocupacao=request["ocupacao"],
preco=request["preco"],
)
session.add(voo)
session.commit()
voo_json = hw_get_voo(voo.id)
session.close()
return voo_json
def fetch_stock_data(id: int):
"""
hold the id and after getting data insert it to database
async in background task
"""
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yf.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_EPS = yahoo_data.info['forwardEps']
db.add(stock)
db.commit()
def fetch_stock_data(stock_id: int):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == stock_id).first()
yahoo_data = yf.Ticker(stock.symbol)
print(yahoo_data.info)
stock.name = yahoo_data.info['longName']
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_eps = yahoo_data.info['forwardEps']
if yahoo_data.info['dividendYield'] is not None:
stock.dividend = yahoo_data.info['dividendYield'] * 100
else:
stock.dividend = 0
db.add(stock)
db.commit()
async def create_stock(stock_request: StockRequest,
background_tasks: BackgroundTasks,
db: SessionLocal = Depends(get_db)):
"""
Create new stock entry and store it in database.
Run background task in queue to update db entry
with fetched stock details
:return:
"""
# Instantiate model with input data
stock = Stock()
stock.symbol = stock_request.symbol
# Save to db
db.add(stock)
db.commit()
background_tasks.add_task(fetch_stock_data, stock.id)
return {"code": "success", "message": "Stock was added to database"}
def write_video_comments_sql(isVideoExists, video_info, comments_json_array):
"""
将评论数据写入数据库
"""
if isVideoExists:
result = delete_video_by_oid(video_info['oid'])
if not result:
return False
try:
session = SessionLocal()
# 写入bilibiliVideos
video_record = BilibiliVideo(oid=video_info['oid'],
url=video_info['url'],
type=video_info['type'],
title=video_info['title'])
session.add(video_record)
session.flush()
# 写入bilibiliVideoComments
vid = video_record.id
commentRecords = []
for page_json in comments_json_array:
page_comment = json.loads(page_json)
top_level_replies = page_comment['data']['replies']
for reply in top_level_replies:
# 去除表情符号
text = re.sub(r'\[\S+\]', '', reply['content']['message'])
comment = BilibiliVideoComment(vid=vid, text=text)
commentRecords.append(comment)
session.add_all(commentRecords)
except Exception as e:
print(e)
session.close()
return False
else:
session.commit()
return True
def fetch_stock_data(id: int):
"""
Import stocks data from yfinance API
"""
db = SessionLocal()
stock = db.query(StockItem).filter(StockItem.id == id).first()
yahoo_data = yf.Ticker(stock.ticker)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.shortname = yahoo_data.info['shortName']
try:
stock.forwardpe = yahoo_data.info['forwardPE']
except:
pass
db.add(stock)
db.commit()
def fetch_stock_data(id: int):
"""
Fetch the stock information from symbol given.
"""
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yfinance.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_eps = yahoo_data.info['forwardEps']
if yahoo_data.info['dividendYield'] is not None:
stock.dividend_yield = yahoo_data.info['dividendYield'] * 100
db.add(stock)
db.commit()
def fetch_data(id: str):
db = SessionLocal()
country = db.query(State).filter(State.id == id).first()
state_data = covid.get_status_by_country_name(country.state)
country.id = state_data['id']
country.state = state_data['country'].capitalize()
country.confirmed = state_data['confirmed']
country.active = state_data['active']
country.deaths = state_data['deaths']
country.recovered = state_data['recovered']
db.add(country)
db.commit()
