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
import pandas as pd
from models import OnlineShoppingReview
from database import SessionLocal
df = pd.read_csv("resource/corpus/online_shopping_10_cats.csv")
review_list = []
for row in df.itertuples():
item_type = getattr(row, "cat")
review_type = getattr(row, "label")
review_type = "好评" if review_type == 1 else "差评"
text = getattr(row, "review")
if not pd.isnull(text):
review = OnlineShoppingReview(item_type=item_type,
review_type=review_type,
text=text)
review_list.append(review)
# 写入数据库
try:
session = SessionLocal()
session.add_all(review_list)
except Exception as e:
print(e)
session.close()
else:
session.commit()
def fetch_stock_data(symbol: str):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.symbol == symbol).first()
yahoo_data = yfinance.Ticker(stock.symbol)
exp_list = list(yahoo_data.options)
strike = {}
contract = {}
price = {}
profit = {}
in_the_money = {}
for i in exp_list:
opt = yahoo_data.option_chain(i)
strike[i] = opt.calls['strike']
strike_value = strike.values()
strike_list = list(strike_value)
strike_price = [element * 100 for element in strike_list]
contract[i] = opt.calls['ask']
contract_value = contract.values()
contract_list = list(contract_value)
contract_price = [element * 10 for element in contract_list]
price = yahoo_data.info['previousClose']
price_total = price * 100
price_to_execute = list(map(add, strike_price, contract_price))
profit = [element - price_total for element in price_to_execute]
profit_large = profit * 100
percent_profit = list(map(truediv, profit_large, price_to_execute))
in_the_money[i] = opt.calls['inTheMoney']
itm_value = in_the_money.values()
itm_list = list(itm_value)
stock.price = price_total
exps = []
for date in exp_list:
exps.append(Expiration(symbol=stock.symbol, exp_list=date))
df = pd.DataFrame(strike_price)
strike_listee = df.values.tolist()
strike_final = [[x for x in y if not np.isnan(x)] for y in strike_listee]
c_df = pd.DataFrame(contract_price)
contract_listee = c_df.values.tolist()
contract_final = [[x for x in y if not np.isnan(x)] for y in contract_listee]
p2e_df = pd.DataFrame(price_to_execute)
p2e_listee = p2e_df.values.tolist()
p2e_final = [[x for x in y if not np.isnan(x)] for y in p2e_listee]
p_profit_df = pd.DataFrame(percent_profit)
p_profit_listee = p_profit_df.values.tolist()
p_profit_final = [[x for x in y if not np.isnan(x)] for y in p_profit_listee]
itm_df = pd.DataFrame(itm_list)
itm_listee = itm_df.values.tolist()
itm_final = [[x for x in y if not np.isnan(x)] for y in itm_listee]
strk = []
for strike_group, contract_group, p2e_group, p_profit_group, itm_group, date in zip(strike_final, contract_final, p2e_final, p_profit_final, itm_final, exp_list):
for strikes, contracts, p2es, p_profits, itms in zip(strike_group, contract_group, p2e_group, p_profit_group, itm_group):
strk.append(Strike(symbol=stock.symbol, exp_list=date, strike_price=strikes, contract_price=contracts, price_to_execute=p2es, percent_profit=p_profits, in_the_money=itms))
instances = [stock]
instances.extend(exps)
instances.extend(strk)
db.add_all(instances)
db.commit()
import pandas as pd
from models import COVID19News
from database import SessionLocal
from datetime import datetime
df = pd.read_csv("resource/corpus/news.csv")
news_list = []
for row in df.itertuples():
title = getattr(row, "title")
publish_date_str = getattr(row, "publish_date")
publish_date = datetime.strptime(publish_date_str, "%Y-%m-%d %H:%M:%S")
text = getattr(row, "text")
news = COVID19News(title=title, publish_date=publish_date, text=text)
news_list.append(news)
# 写入数据库
try:
session = SessionLocal()
session.add_all(news_list)
except Exception as e:
print(e)
session.close()
else:
session.commit()
music_json = json.load(music_file)
except IOError as e:
print('文件读取异常！', e.msg)
else:
song_list = []
for song in music_json:
# 歌手
singer = song["singer"]
# 歌名
song_name = song["song"]
# 专辑名
album = song["album"]
# 歌词
lyric = '\n'.join(song["geci"])
lyric = f"{song_name}\n\t{singer} - {album}\n{lyric}"
song_list.append(
ChineseLyric(singer=singer,
song=song_name,
album=album,
text=lyric))
# 写入数据库
try:
session = SessionLocal()
session.add_all(song_list)
except Exception as e:
print(e)
session.close()
else:
session.commit()
