def user_is_exist(db: DBController, user_name: str):
sql = "select name from users where name = ?"
with db.open():
db.execute(sql, [user_name])
result = db.fetchall()
return len(result) != 0
def main():
db = DBController("/home/pi/pyconapac-rpi/db/pycon2015.db.nofacebook")
#cursor = db.conn.cursor()
#cursor.execute("UPDATE regist SET regist_wtime=? WHERE reg_no=?", ('1', '274',))
#cursor.execute("UPDATE regist SET scan_status=? WHERE reg_no=?", (0, '274',))
#info = db.getInfoByReg('274')
#info.scan_status = False
#print info.scan_status
#print info
web = Web()
needUpdateList = db.getNeedUpdate()
print needUpdateList
for info in needUpdateList:
uid = info.uid
reg_no = info.reg_no
time = info.regist_wtime
try:
print uid, reg_no, time
payload = {'action':'regist-update', 'reg_no':reg_no, 'uid':uid, 'regist_wtime':time}
result = requests.post(web.url, data=payload)
return json.loads(result.text)
except requests.exceptions.RequestException as e:
print e
return self.err()
def edit_user(db: DBController):
user_name = input('User name > ')
find_sql = 'select * from users where name = ?'
edit_sql = 'update users set name = ?, age = ? where name = ?'
with db.open():
db.execute(find_sql, (user_name, ))
user_records = db.fetchall()
if len(user_records) == 0:
print(f'Sorry, {user_name} is not found')
return
age = user_records[0][1]
new_user_name = input_user_name(user_name)
if new_user_name is None:
return
new_age = input_user_age(str(age))
if new_age is None:
return
with db.open():
db.execute(edit_sql, [new_user_name, new_age, user_name])
db.commit()
print(f'Update user: {new_user_name}')
def initialize():
"""Method for initializing a youtube object, connection to the DB and load comments
get the comments for the youtube videos ids listed in the DB, store the comments in the DB,
train the classifier and print the classifier metrics
Args:
config: dicitionary containing the DB parameters
"""
with app.app_context():
g.config = get_config()
g.yt = Youtube(api_key= g.config ['youtube']['API_KEY'], api_version = g.config ['youtube']['API_VERSION'], service_name=g.config['youtube']['API_SERVICE_NAME'])
video_ids = g.config['youtube_videos_id']
g.comments = []
for video_id in video_ids:
g.comments.extend(g.yt.get_video_comments(video_id))
#db_params = {"user":g.config['db']['user'], "host":g.config['db']["host"], "port":g.config['db']["port"],"database": g.config['db']["database"]}
g.db = DBController(**g.config['db'])
# create table if it does not exist:
g.db.create_comments_table()
# train classifier
g.classifier = Classifier()
g.classifier.train()
# predication of the comments
for comment_item in g.comments:
text = comment_item.text
predication = g.classifier.predict(comment_text)
comment_item.sentiment = predication
# insert the comment in DB
g.db.insert_comments(comment_item)
def __init__(self, master):
Tab.__init__(self, master, u"新增".encode('utf-8'))
self.web = Web()
self.db = DBController("/home/pi/pyconapac-rpi/db/pycon2015.db")
self.reg_no = StringVar()
self.nickname = StringVar()
self.fullname = StringVar()
self.uid = StringVar()
self.pair_status = StringVar()
self.is_reg_no = False
self.tab3_fm1 = Frame(self)
self.txt3_fm1 = Text(self.tab3_fm1, width=3, height=1, font=('Arial', 12))
self.txt3_fm1.pack(side=TOP, fill=X, expand=YES)
self.txt3_fm1.focus()
self.tab3_btn1 = Button(self.tab3_fm1, text='OK')
self.tab3_btn1.pack(side=TOP, anchor=W, fill=X, expand=YES)
self.tab3_btn1.bind('<Button-1>', self.load_profile)
self.tab3_fm1.pack(side=LEFT, fill=BOTH, expand=YES)
self.tab3_fm2 = Frame(self)
Label(self.tab3_fm2, bg="red", fg="white", width=12, font=('Arial', 16), textvariable=self.reg_no).pack(side=TOP, fill=BOTH, expand=YES)
Label(self.tab3_fm2, bg="green", fg="black", width=12, font=('Arial', 10), textvariable=self.nickname).pack(side=TOP, fill=BOTH, expand=YES)
Label(self.tab3_fm2, bg="blue", fg="white", width=12, font=('Arial', 16), textvariable=self.fullname).pack(side=TOP, fill=BOTH, expand=YES)
self.tab3_fm2.pack(side=LEFT, padx=10)
self.tab3_fm3 = Frame(self)
self.tab3_lbl_uid = Label(self.tab3_fm3, bg="white", fg="black", width=10, font=('Arial', 12), textvariable=sv_uid).pack(side=TOP, anchor=W, fill=X, expand=YES)
self.tab3_btn1 = Button(self.tab3_fm3, text='Pair', font=('Arial', 10))
self.tab3_btn1.pack(side=LEFT, anchor=W)
self.tab3_btn1.bind('<Button-1>', self.pair_uid)
self.tab3_lbl_pair_status = Label(self.tab3_fm3, bg="yellow", fg="black", width=4, font=('Arial', 24), textvariable=self.pair_status).pack(side=LEFT, fill=BOTH, expand=YES)
self.tab3_fm3.pack(side=LEFT, fill=BOTH, expand=YES)
def main():
db = DBController('data.db')
check_db(db) # データベースが初期化されてなかったら初期化
display_welcome_message()
while True:
cmd = input('Your command > ')
cmd = cmd.upper()
if cmd == 'S':
show_all_users_info(db)
print()
elif cmd == 'A':
add_new_user(db)
print()
elif cmd == 'F':
find_user(db)
print()
elif cmd == 'D':
delete_user(db)
print()
elif cmd == 'E':
edit_user(db)
print()
elif cmd == 'Q':
break
else:
display_help()
print()
print('Bye!')
def setUp(self):
subprocess.call(['db/mkdb.sh'])
conn = sqlite3.connect(DB_NAME)
conn.isolation_level = None
cursor = conn.cursor()
cursor.execute("INSERT INTO regist (reg_no, fullname, nickname, uid, tshirt) VALUES ('01', 'John Dow', 'johndow', 'A1 B2', 'L')")
cursor.execute("INSERT INTO regist (reg_no, fullname, nickname, uid, tshirt) VALUES ('02', 'Jane Dow', 'janedow', 'C3 D4', 'S')")
conn.commit()
self.db = DBController(DB_NAME)
def execute(n=0):
gages = load_gages()
print("All gauges: {}".format(len(gages)))
db = DBController()
todo_gages = []
for g in gages:
status = db.data_check(g["SOURCE_FEA"], g["FLComID"])
if not all(status):
todo_gages.append(g)
todo_n = len(todo_gages)
if todo_n == 0:
print("Completed downloading data for all {} gauge catchments".format(
len(gages)))
return
print("Completed gauges: {}".format(len(gages) - todo_n))
print("TODO gauges: {}".format(todo_n))
print("Cycle: {}".format(n))
i = 1
for g in todo_gages:
t0 = time.time()
comid = g["FLComID"]
gageid = g["SOURCE_FEA"]
print("Retrieving data for COMID: {}, GAGEID: {}".format(
comid, gageid))
print("# in session: {}, # remaining: {}".format(i, todo_n + 1 - i))
print("Execution time: {}".format(datetime.datetime.now()))
try:
catchment = get_data(gageid, comid)
except Exception as e:
print(
"Error attempting to download data for COMID: {}, GAGEID: {}".
format(comid, gageid))
print("Error: {}".format(e))
print("Waiting {} seconds before continuing".format(
EXCEPTION_TIMEOUT))
time.sleep(EXCEPTION_TIMEOUT)
continue
t1 = time.time()
try:
db.save(catchment, close=False)
except Exception as e:
print("Error attempting to save data for COMID: {}, GAGEID: {}".
format(comid, gageid))
print("Error: {}".format(e))
print("Waiting {} seconds before continuing".format(
EXCEPTION_TIMEOUT))
time.sleep(EXCEPTION_TIMEOUT)
continue
t2 = time.time()
print("Catchment Data saved for COMID: {}, GAGEID: {}".format(
comid, gageid))
print("Runtime: {} sec".format(round(t2 - t0, 4)))
print("Data Retrieval time: {} sec".format(round(t1 - t0, 4)))
print("DB Processing time: {} sec".format(round(t2 - t1, 4)))
i += 1
db.close()
execute(n + 1)
def __init__(self, master):
Tab.__init__(self, master, u"報到".encode('utf-8'))
self.web = Web()
self.db = DBController("/home/pi/pyconapac-rpi/db/pycon2015.db")
self.reg_no = StringVar()
self.nickname = StringVar()
self.uid = StringVar()
self.tshirt_wtime = StringVar()
self.tab1_lbl_reg_no = Label(self, bg="red", fg="white", font=('Arial', 16), textvariable=sv_reg_no).pack(fill=X)
self.tab1_lbl_nickname = Label(self, bg="green", fg="black", font=('Arial', 22), textvariable=sv_nickname).pack(fill=X)
self.tab1_lbl_uid = Label(self, bg="blue", fg="white", font=('Arial', 20), textvariable=sv_uid).pack(fill=X)
self.tab1_lbl_status = Label(self, bg="yellow", fg="black", font=('Arial', 22), textvariable=sv_regist_status).pack(side=LEFT, fill=BOTH, expand=YES)
def getOneWays(self, start_airport, end_airport, date):
conn = DBController().getConnection()
c = conn.cursor()
responses = []
try:
c.execute("SELECT * FROM {} WHERE inbound_date='None' AND \
outbound_date='{}' AND \
start_airport='{}' AND \
end_airport='{}' \
ORDER BY price" \
.format(self.table, date, start_airport, end_airport))
for row in c.fetchall():
responses.append(
CrawlerResponse(
row[2],
datetime.strptime(row[3], "%Y-%m-%d").date(), None,
row[0], row[1], row[5]))
except Exception as e:
print "Error getting getting one way flights from DB: " + str(e)
return responses
def __init__(self, data, name, pred_data):
self.ticker = name
self.data = data
self.pred_data = pred_data
if (len(data) > 0):
self.stocks_open = pd.to_numeric(data["Open"], downcast="float")
self.stocks_open = self.stocks_open.round(decimals=2) #State aggr
if (len(pred_data) > 0):
self.stocks_pred = pred_data[
"prediction"] #No round on this as we dont use true vals
self.index = 2 #start at 2 so LSTM input is shape (3,4)
self.funds = self.STARTING_FUNDS
self.held_shares = 0
self.volume_traded = 0
self.past_volumes_traded = []
self.shares = []
self.controller = DBController()
class Tab1(Tab):
def __init__(self, master):
Tab.__init__(self, master, u"報到".encode('utf-8'))
self.web = Web()
self.db = DBController("/home/pi/pyconapac-rpi/db/pycon2015.db.nofacebook")
self.reg_no = StringVar()
self.nickname = StringVar()
self.uid = StringVar()
self.tshirt_wtime = StringVar()
self.tab1_lbl_reg_no = Label(self, bg="red", fg="white", font=('Arial', 16), textvariable=sv_reg_no).pack(fill=X)
self.tab1_lbl_nickname = Label(self, bg="green", fg="black", font=('Arial', 22), textvariable=sv_nickname).pack(fill=X)
self.tab1_lbl_uid = Label(self, bg="blue", fg="white", font=('Arial', 20), textvariable=sv_uid).pack(fill=X)
self.tab1_lbl_status = Label(self, bg="yellow", fg="black", font=('Arial', 22), textvariable=sv_regist_status).pack(side=LEFT, fill=BOTH, expand=YES)
def load_profile(self, uid):
print type(self).__name__ + "/" + inspect.stack()[0][3]
try:
result = self.web.infoQuery(uid)
status = result['result']['regist_wtime'] if result['result']['regist_wtime'] != '0' else 'First Check-in'
reg_no = result['result']['reg_no']
nickname = result['result']['nickname']
user_info = self.db.getInfoByReg(reg_no)
sv_reg_no.set(reg_no + "/" + user_info.ticket_type.split(' ')[-1])
sv_nickname.set(nickname)
sv_uid.set(uid)
sv_regist_status.set(status)
result = self.web.registerUpdate(reg_no, uid)
# user_info = self.web.infoQuery(uid)
# sv_reg_no.set(str(user_info['result']['reg_no']))
# sv_nickname.set(str(user_info['result']['nickname']))
# sv_uid.set(str(user_info['result']['uid']))
# result = self.web.registerUpdate(user_info['result']['reg_no'], uid)
print result
# if user_info.regist_wtime == None:
# self.db.checkIn(uid)
except ValueError:
sv_reg_no.set(VALUE_ERROR)
sv_nickname.set(VALUE_ERROR)
sv_uid.set(uid)
def putCrawlerResponse(self, response):
conn = DBController().getConnection()
c = conn.cursor()
try:
c.execute("INSERT INTO {} ({}) VALUES ('{}', '{}', {}, '{}', '{}', '{}')".\
format(
self.table,
self.config.deals_columns,
response.start_airport,
response.end_airport,
response.price,
response.outbound,
response.inbound,
response.website
))
except Exception as e:
print('ERROR: Failed to insert a crawler response:\n' + str(e))
conn.commit()
conn.close()
from datetime import datetime as dt
from hashlib import sha256
from flask import Blueprint, request, render_template, flash, redirect, url_for
from flask_login import UserMixin, LoginManager, login_user, login_required, logout_user
from FirstOfficersLog import app
from blog_utils.md_parser import convert_to_html
from common.InvalidCredentialsException import InvalidCredentialsException
from common.UserNotInDBException import UserNotInDBException
from db_controller import DBController
from logger import logger
auth_view = Blueprint('auth_view', __name__, url_prefix='/auth')
db_ctl = DBController()
login_manager = LoginManager()
login_manager.init_app(app)
class User(UserMixin):
pass
@login_manager.user_loader
def user_loader(username):
user = User()
user.id = username
return user
class TestDBAccess(unittest.TestCase):
def setUp(self):
subprocess.call(['db/mkdb.sh'])
conn = sqlite3.connect(DB_NAME)
conn.isolation_level = None
cursor = conn.cursor()
cursor.execute("INSERT INTO regist (reg_no, fullname, nickname, uid, tshirt) VALUES ('01', 'John Dow', 'johndow', 'A1 B2', 'L')")
cursor.execute("INSERT INTO regist (reg_no, fullname, nickname, uid, tshirt) VALUES ('02', 'Jane Dow', 'janedow', 'C3 D4', 'S')")
conn.commit()
self.db = DBController(DB_NAME)
def tearDown(self):
os.remove(DB_NAME)
def test_checkIn(self):
info = self.db.checkIn("A1 B2")
self.assertIsNotNone(info.data)
self.assertEqual("01", info.reg_no)
self.assertEqual("A1 B2", info.uid)
self.assertEqual("John Dow", info.fullname)
self.assertEqual("johndow", info.nickname)
self.assertIsNotNone(info.regist_wtime)
def test_replace(self):
info = self.db.getInfoByReg("02")
self.assertIsNotNone(info.data)
self.assertNotEqual("11 22", info.uid)
info = self.db.pairUidByReg(info.reg_no, "11 22")
self.assertEqual("11 22", info.uid)
self.assertIsNotNone(info.regist_wtime)
def test_tshirt(self):
info = self.db.getInfoByUid("C3 D4")
self.assertIsNotNone(info.data)
self.assertIsNone(info.tshirt_wtime)
info = self.db.setTshirtTimeByUid(info.uid)
self.assertIsNotNone(info.tshirt_wtime)
def test_needUpdate(self):
self.db.checkIn("A1 B2")
self.db.checkIn("C3 D4")
infoList = self.db.getNeedUpdate()
self.assertNotEqual(0, len(infoList))
self.assertEqual("01", infoList[0].reg_no)
self.assertEqual("A1 B2", infoList[0].uid)
self.assertEqual("John Dow", infoList[0].fullname)
self.assertEqual("johndow", infoList[0].nickname)
self.assertFalse(infoList[0].update_status)
self.assertEqual("02", infoList[1].reg_no)
self.assertEqual("C3 D4", infoList[1].uid)
self.assertEqual("Jane Dow", infoList[1].fullname)
self.assertEqual("janedow", infoList[1].nickname)
self.assertFalse(infoList[1].update_status)
infoList = self.db.setUpdated(infoList)
self.assertNotEqual(0, len(infoList))
self.assertTrue(infoList[0].update_status)
self.assertTrue(infoList[1].update_status)
def test_getInfoByUids(self):
uidList = ["A1 B2", "C3 D4"]
infoList = self.db.getInfoByUids(uidList)
self.assertNotEqual(0, len(infoList))
self.assertEqual("A1 B2", infoList[0].uid)
self.assertEqual("C3 D4", infoList[1].uid)
max_text_length: 5000
mode: 'rectangle'
multiline: False
size_hint: 1, None
height: '50dp'
pos_hint: {'center_x': 0.5, 'center_y': 0.5}
on_text_validate: root.send_message()
Widget:
size_hint: 0.01, 1
MDIconButton:
icon: 'arrow-up-circle'
pos_hint: {'center_x': 0.5, 'center_y': 0.5}
on_press: root.send_message()
'''
db = DBController() # создание класа для работы с бд
_id_instance_button = dict() # хранит id кнопок и их объект
_id_button = [0] # хранит id активной кнопки (по той что перешли в чат)
ws = WSConstroller(db=db) # создает класс для работы в вебсокетами
theme_cls = ThemeManager() # менеджер тем, хранит цвета и разметку
# класс управления всеми экранами (Layout'ами)
class ScreenController(ScreenManager):
# главный экран (Layout), экран чатов
class MainWindow(MDScreen):
dialog = None
def on_kv_post(
self, base_widget
): # выполняет в момент парсинга kv-кода (kivy front-end)
class Tab3(Tab):
def __init__(self, master):
Tab.__init__(self, master, u"新增".encode('utf-8'))
self.web = Web()
self.db = DBController("/home/pi/pyconapac-rpi/db/pycon2015.db")
self.reg_no = StringVar()
self.nickname = StringVar()
self.fullname = StringVar()
self.uid = StringVar()
self.pair_status = StringVar()
self.is_reg_no = False
self.tab3_fm1 = Frame(self)
self.txt3_fm1 = Text(self.tab3_fm1, width=3, height=1, font=('Arial', 12))
self.txt3_fm1.pack(side=TOP, fill=X, expand=YES)
self.txt3_fm1.focus()
self.tab3_btn1 = Button(self.tab3_fm1, text='OK')
self.tab3_btn1.pack(side=TOP, anchor=W, fill=X, expand=YES)
self.tab3_btn1.bind('<Button-1>', self.load_profile)
self.tab3_fm1.pack(side=LEFT, fill=BOTH, expand=YES)
self.tab3_fm2 = Frame(self)
Label(self.tab3_fm2, bg="red", fg="white", width=12, font=('Arial', 16), textvariable=self.reg_no).pack(side=TOP, fill=BOTH, expand=YES)
Label(self.tab3_fm2, bg="green", fg="black", width=12, font=('Arial', 10), textvariable=self.nickname).pack(side=TOP, fill=BOTH, expand=YES)
Label(self.tab3_fm2, bg="blue", fg="white", width=12, font=('Arial', 16), textvariable=self.fullname).pack(side=TOP, fill=BOTH, expand=YES)
self.tab3_fm2.pack(side=LEFT, padx=10)
self.tab3_fm3 = Frame(self)
self.tab3_lbl_uid = Label(self.tab3_fm3, bg="white", fg="black", width=10, font=('Arial', 12), textvariable=sv_uid).pack(side=TOP, anchor=W, fill=X, expand=YES)
self.tab3_btn1 = Button(self.tab3_fm3, text='Pair', font=('Arial', 10))
self.tab3_btn1.pack(side=LEFT, anchor=W)
self.tab3_btn1.bind('<Button-1>', self.pair_uid)
self.tab3_lbl_pair_status = Label(self.tab3_fm3, bg="yellow", fg="black", width=4, font=('Arial', 24), textvariable=self.pair_status).pack(side=LEFT, fill=BOTH, expand=YES)
self.tab3_fm3.pack(side=LEFT, fill=BOTH, expand=YES)
def clear_profile(self):
self.reg_no.set("")
self.nickname.set("")
self.fullname.set("")
self.txt3_fm1.delete("1.0", "end")
def clear_pair(self):
sv_uid.set("")
self.pair_status.set("")
def load_profile(self, button):
print type(self).__name__ + "/" + inspect.stack()[0][3]
global reg_no
reg_no = self.txt3_fm1.get("1.0", "end")
user_info = self.db.getInfoByReg(int(reg_no))
reg_no = user_info.reg_no
self.reg_no.set(str(user_info.reg_no) + "/" + user_info.ticket_type.split(' ')[-1])
self.nickname.set(user_info.nickname)
self.fullname.set(user_info.fullname)
self.clear_pair()
self.is_reg_no = True
def pair_uid(self, button):
print type(self).__name__ + "/" + inspect.stack()[0][3]
# from nfc import NFC
# NFC('/home/pi/3rd/libnfc-1.7.0-rc7/examples/nfc-poll').read()
try :
if self.is_reg_no != True:
return
result = self.web.registerUpdate(reg_no, uid)
print result
self.pair_status.set(result["status"])
self.is_reg_no = False
except NameError:
print "NameError"
self.pair_status.set("NameError")
finally:
self.clear_profile()
sv_uid.set("")
class TestHistoricalSimulation(unittest.TestCase):
TEST_TICKER_IDX = 42 #Arbitrary index from the historical sim to pull from and test
#Pre-test simulation init for each test
def setUp(self):
data = pd.read_csv(TEST_DATA)
pred = pd.read_csv(TEST_PRED)
self.db = DBController()
self.sim = HistoricalSimulation(self.db)
#Test that variables for training are initialized properly
def test_init(self):
print("testing historical simulation init.")
controller = self.sim.controller
self.assertTrue(isinstance(controller, DBController))
print("Success")
#Test buy shares reward function.
def test_buy_stocks(self):
print("testing historical sim for regular buy")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds = self.db.get_user(TEST_NAME).bank[0]
self.assertTrue(pre_share_num + 1 == post_share_num)
self.assertTrue(user_funds < 5000)
print("Success")
print("testing historical sim for regular buy W/ no money.")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds = self.db.get_user(TEST_NAME).bank[0]
self.assertTrue(pre_share_num == post_share_num)
print("Success")
#Test sell shares reward function.
def test_sell_stocks(self):
print("testing historical sim for regular sell")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds_before = self.db.get_user(TEST_NAME).bank[0]
self.sim.buy_shares(1, np.float32(TEST_USER_ID),
np.float32(user_funds_before), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
user_funds_buy = self.db.get_user(TEST_NAME).bank[0]
self.sim.sell_shares(1, 1, np.float32(TEST_USER_ID),
np.float32(user_funds_buy), "v", TEST_SESSION_ID,
self.TEST_TICKER_IDX)
user_funds_after = self.db.get_user(TEST_NAME).bank[0]
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(
pre_share_num == post_share_num
) #buy then sell, no change in vol. Buy is already proven to add +1
self.assertTrue(user_funds_after == user_funds_before)
print("Success")
print("testing historical sim for regular sell W/ no shares.")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.sell_shares(
1, 0, np.float32(TEST_USER_ID), np.float32(5000.0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX
) #aapl test stock has no purchases made ever for test session.
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(pre_share_num == post_share_num)
print("Success")
#Test sell shares reward function.
def test_sell_all_stocks(self):
print("testing historical sim for regular sell all")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(
pre_share_num + 3 == post_share_num
) #buy then sell, no change in vol. Buy is already proven to add +1
self.sim.sell_all(3, np.float32(TEST_USER_ID), np.float32(5000.0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(post_share_num == pre_share_num)
print("Success")
#Test the state used as input for the RL Agent
def test_get_state(self):
print("testing historical get state")
state = self.sim.get_state(TEST_SESSION_ID,
self.db.get_user(TEST_NAME).bank[0], "v",
self.TEST_TICKER_IDX)
price = state[0]
pred = state[1]
funds = state[2]
shares = state[3]
self.assertTrue(isinstance(price, float))
self.assertTrue(isinstance(pred, float))
self.assertTrue(isinstance(funds, float))
self.assertTrue(isinstance(shares, float))
self.assertTrue(price == self.sim.get_price("v", self.TEST_TICKER_IDX))
self.assertTrue(
pred == self.sim.get_predicted_price("v", self.TEST_TICKER_IDX))
self.assertTrue(funds == self.db.get_user(TEST_NAME).bank[0])
self.assertTrue(shares == self.db.get_held_shares(TEST_SESSION_ID))
print("Success")
#Test get price
def test_get_price(self):
print("testing historical get price for all stocks")
for tick in stocks:
price = self.sim.get_price(tick, self.TEST_TICKER_IDX)
self.assertTrue(isinstance(price, float))
self.assertTrue(price > 0)
print("Success")
#Test get pred, statement coverage of pred is obtained here.
def test_get_pred_price(self):
print("testing historical get pred price")
pred1 = self.sim.get_predicted_price("v", self.TEST_TICKER_IDX)
self.assertTrue(isinstance(pred1, int))
self.assertTrue(pred1 == 1)
#I manually found this ticker idx that returns pred of 0. Dont change please.
pred2 = self.sim.get_predicted_price("v", self.TEST_TICKER_IDX + 119)
self.assertTrue(isinstance(pred2, int))
self.assertTrue(pred2 == 0)
print("Success")
def calculate_streamflow_metrics():
db = DBController()
gages = load_gages()
columns = ["streamflow"]
metrics_std = {}
metrics_mean = {}
first_metric = True
for c in columns:
metrics_mean[c] = None
metrics_std[c] = None
print("Calculating Mean and STD for {} gages".format(len(gages)))
i = 0
for g in gages:
print("{}/{} Completed, GAGEID: {}".format(i, len(gages),
g["SOURCE_FEA"]))
query = "SELECT value FROM CatchmentData WHERE ComID=? AND parameter='AreaSqKM'"
values = (g["FLComID"], )
c = db.conn.cursor()
c.execute(query, values)
area = float(c.fetchone()[0])
query = "SELECT * FROM StreamflowData WHERE gageID='{}'".format(
g["SOURCE_FEA"])
df = pd.read_sql_query(query, db.conn)
dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" +
df.day.map(str))
df['date'] = pd.to_datetime(dates, format="%Y/%m/%d")
date_mask = (df.date >= START_DATE) & (df.date <= END_DATE)
df = df.loc[date_mask]
dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" +
df.day.map(str))
df['date'] = pd.to_datetime(dates, format="%Y/%m/%d")
for c in columns:
df[c] = pd.to_numeric(df[c])
agg_df = pd.DataFrame()
# agg_df['streamflow'] = df[['streamflow']].groupby([df["date"].dt.year, df["date"].dt.month, df["date"].dt.day]).sum().streamflow
agg_df['streamflow'] = (df['streamflow'] * 86400) / (area * 10**6)
agg_df['streamflow'] = df['streamflow']
# dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" + df.day.map(str)).unique()
# agg_df.index = pd.to_datetime(dates, format="%Y/%m/%d")
for c in columns:
c_mean = agg_df[c].mean()
c_std = agg_df[c].std()
if first_metric:
metrics_mean[c] = c_mean
metrics_std[c] = c_std
else:
if not np.isnan(c_mean):
metrics_mean[c] = (metrics_mean[c] + c_mean) / 2.0
if not np.isnan(c_std):
metrics_std[c] = (metrics_std[c] + c_std) / 2.0
first_metric = False
i += 1
print("All gage metrics calculated.")
print("STD: {}".format(metrics_std))
print("MEAN: {}".format(metrics_mean))
def create_table(db: DBController):
with db.open():
db.execute('create table users(name text, age integer)')
db.commit()
def setUp(self):
data = pd.read_csv(TEST_DATA)
pred = pd.read_csv(TEST_PRED)
self.db = DBController()
self.sim = LiveSimulation(self.db)
import requests # also https://pypi.org/project/selenium-wire/
from util import *
# from pyvirtualdisplay import Display
# from xvfbwrapper import Xvfb
logger = get_logger('cdn')
LOGGER_LINE_NO = 0
def custlog(line):
global LOGGER_LINE_NO
LOGGER_LINE_NO += 1
logger.debug(line)
dbc = DBController()
class D2O:
def __init__(self, **entries):
self.__dict__.update(entries)
def found_success(new_url):
print(f"***************** FOUND URL: {new_url}")
custlog(f"FOUND DESIRED URL: {new_url}")
with open(output_file, 'a') as of:
of.write(f"{new_url}\n")
of.flush()
if filtered_results == desired_count:
print(f"Found desired count of urls: {desired_count}")
def calculate_metrics():
db = DBController()
gages = load_gages()
columns = ["prcp", "srad", "lrad", "swe", "tmax", "tmin", "vp", "et"]
metrics_std = {}
metrics_mean = {}
first_metric = True
for c in columns:
metrics_mean[c] = None
metrics_std[c] = None
print("Calculating Mean and STD for {} gages".format(len(gages)))
i = 0
for g in gages:
print("{}/{} Completed, GAGEID: {}".format(i, len(gages),
g["SOURCE_FEA"]))
query = "SELECT * FROM ForcingData WHERE gageID='{}'".format(
g["SOURCE_FEA"])
df = pd.read_sql_query(query, db.conn)
dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" +
df.day.map(str))
df['date'] = pd.to_datetime(dates, format="%Y/%m/%d")
date_mask = (df.date >= START_DATE) & (df.date <= END_DATE)
df = df.loc[date_mask]
dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" +
df.day.map(str))
df['date'] = pd.to_datetime(dates, format="%Y/%m/%d")
for c in columns:
df[c] = pd.to_numeric(df[c])
df_temp = df.copy()
agg_df = pd.DataFrame()
agg_df['prcp'] = df[['prcp']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).sum().prcp
agg_df['srad'] = df[['srad']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).mean().srad
agg_df['lrad'] = df[['lrad']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).mean().lrad
agg_df['swe'] = df[['swe']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).sum().swe
agg_df['tmax'] = df[['tmax']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).max().tmax
agg_df['tmin'] = df_temp[['tmax']].groupby([
df_temp["date"].dt.year, df_temp["date"].dt.month,
df_temp["date"].dt.day
]).min().tmax
agg_df['vp'] = df[['vp']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).mean().vp
agg_df['et'] = df[['et']].groupby(
[df["date"].dt.year, df["date"].dt.month,
df["date"].dt.day]).sum().et
dates = (df.year.map(str) + "/" + df.mnth.map(str) + "/" +
df.day.map(str)).unique()
agg_df.index = pd.to_datetime(dates, format="%Y/%m/%d")
for c in columns:
c_mean = agg_df[c].mean()
c_std = agg_df[c].std()
if first_metric:
metrics_mean[c] = c_mean
metrics_std[c] = c_std
else:
if not np.isnan(c_mean):
metrics_mean[c] = (metrics_mean[c] + c_mean) / 2.0
if not np.isnan(c_std):
metrics_std[c] = (metrics_std[c] + c_std) / 2.0
first_metric = False
i += 1
print("All gage metrics calculated.")
print("STD: {}".format(metrics_std))
print("MEAN: {}".format(metrics_mean))
def exec(self):
self.db_ctrl = DBController()
self.scriptView = App(self.insert, self.load, self.read_and_play, self.load_and_play)
self.scriptView.exec()
class TestDBController(unittest.TestCase):
db = DBController()
def test_get_historical_price(self):
#Assert all tickers get historical vals
print("Testing get historical price for all tickers..")
for ticker in stocks:
price = self.db.get_stock_price(ticker, TEST_STOCK_IDX)[0]
self.assertTrue(price >= 0) #Assert price is valid.
self.assertTrue(isinstance(price,
float)) #RL State price is of float
print("Success")
#Testing framework will unfortunately not close sockets properly for these tests.
def test_get_live_stock_price(self):
import warnings #Disable resource warnings for this test
warnings.filterwarnings("ignore", category=ResourceWarning)
print("Testing get live price for all tickers (This may be slow)..")
for ticker in stocks:
tick = Ticker(ticker)
cur_price = tick.price[ticker]["regularMarketPrice"]
price = self.db.get_live_stock_price(ticker)
self.assertTrue(price >= 0)
self.assertTrue(abs(cur_price - price) <=
0.50) #Could be minor fluctuation between calls
self.assertTrue(isinstance(price,
float)) #RL State price is of float
print("Success")
#Test get prediction from DB
def test_get_historical_pred(self):
print("Testing get historical prediction for all tickers..")
for ticker in stocks:
pred = self.db.get_stock_prediction(
ticker, TEST_STOCK_IDX)[0][0] #Get next pred
self.assertTrue(pred >= 0) #Assert price is valid.
self.assertTrue(isinstance(pred, float)) #float expected
print("Success")
#Get live prediction
def test_get_live_stock_pred(self):
print("Testing get live prediction for all tickers..")
import warnings #Disable resource warnings for this test
warnings.filterwarnings("ignore", category=ResourceWarning)
for ticker in stocks:
pred = self.db.get_live_stock_pred(ticker)[0][0] #Get next pred
price_now = self.db.get_live_stock_price(ticker)
self.assertTrue(pred >= 0) #Assert price is valid.
self.assertTrue(
abs(price_now - pred) <= 10.00
) #Cannot guarantee actual range, we just check it is close
self.assertTrue(isinstance(pred, float)) #float expected
print("Success")
#Test get shares for a user
def test_get_held_shares(self):
shares = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(shares >= 0) #Negative shares are impossible
self.assertTrue(isinstance(shares, int)) #RL State price is of float
pass
#Equivalence partitioning for updating funds.
def test_update_user_funds(self):
print("Testing add user funds")
user = self.db.get_user(TEST_NAME)
before_funds = user.bank[0]
self.db.update_user_funds(user.id[0], before_funds + 100)
user_new = self.db.get_user(TEST_NAME)
self.assertTrue(user_new.username[0] == TEST_NAME)
self.assertTrue(user_new.id[0] == TEST_USER_ID)
self.assertTrue(user_new.bank[0] == before_funds + 100)
print("Success")
print("Testing subtract user funds")
user = self.db.get_user(TEST_NAME)
before_funds = user.bank[0]
self.db.update_user_funds(user.id[0], before_funds - 100)
user_new = self.db.get_user(TEST_NAME)
self.assertTrue(user_new.username[0] == TEST_NAME)
self.assertTrue(user_new.id[0] == TEST_USER_ID)
self.assertTrue(user_new.bank[0] == before_funds - 100)
print("Success")
print("Testing add no user funds")
user = self.db.get_user(TEST_NAME)
before_funds = user.bank[0]
self.db.update_user_funds(user.id[0], before_funds)
user_new = self.db.get_user(TEST_NAME)
self.assertTrue(user_new.username[0] == TEST_NAME)
self.assertTrue(user_new.id[0] == TEST_USER_ID)
self.assertTrue(user_new.bank[0] == before_funds)
self.assertTrue(isinstance(before_funds, float))
print("Success")
pass
#Equivalence tests for adding trades of different types, and verification via share count
def test_add_session_trade(self):
print("Testing add session trade BUY")
shares_before = self.db.get_held_shares(TEST_SESSION_ID)
self.db.add_session_trade(10, "BUY", 1, TEST_SESSION_ID)
shares_after = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(shares_before < shares_after)
self.assertTrue(shares_before + 1 == shares_after)
print("Success")
print("Testing add session trade SELL")
shares_before = self.db.get_held_shares(TEST_SESSION_ID)
self.db.add_session_trade(10, "SELL", 1, TEST_SESSION_ID)
shares_after = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(shares_before > shares_after)
self.assertTrue(shares_before - 1 == shares_after)
print("Success")
print("Testing add session trade Broken Trade Type")
shares_before = self.db.get_held_shares(TEST_SESSION_ID)
self.db.add_session_trade(10, "ABCDE", 1, TEST_SESSION_ID)
shares_after = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(shares_before == shares_after)
print("Success")
#Test get a user from DB
def test_get_user(self):
print("testing get user")
user = self.db.get_user(TEST_NAME)
self.assertTrue(user.username[0] == TEST_NAME)
self.assertTrue(user.id[0] == TEST_USER_ID)
#Assert that the proper sessions are grabbed by the query.
def test_get_active_trades(self):
print("testing get sessions")
trades = self.db.get_active_trades()
for sess in trades.iterrows(
): #Test that sessions are not finished or paused.
self.assertTrue(not sess[1].is_finished)
self.assertTrue(not sess[1].is_paused)
print("Success")
#Statement coverage of get_price
def test_failure_get_price(self):
print("Testing failure of get_price")
try:
price = self.db.get_stock_price("v", -1)[0] #Broken stock idx
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of get_live_price
def test_failure_get_live_price(self):
print("Testing failure of get_live_price")
try:
price = self.db.get_stock_price(999)[0] #Broken stock ticker val
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of get_live_pred
def test_failure_get_live_pred(self):
print("Testing failure of get_live_price")
try:
pred = self.db.get_live_stock_pred(999)[
0] #Broken stock ticker val
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of get_pricediction
def test_failure_get_pred(self):
print("Testing failure of get pred")
try:
pred = self.db.get_stock_prediction(
999, 999)[0] #Broken stock ticker val
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of update funds
def test_failure_update_funds(self):
print("Testing failure of update funds")
try:
self.db.update_user_funds(-1, 999) #Broken user_id
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of add_trade
def test_failure_add_trade(self):
print("Testing failure of add trade")
try:
self.db.add_session_trade(-1, "", -1, -1)[0] #Broken inputs
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of update time
def test_failure_update_time(self):
print("Testing failure of update time")
try:
self.db.update_start_time(-1, "") #Broken inputs
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of update trades
def test_failure_update_trades(self):
print("Testing failure of update time")
try:
self.db.update_start_time(-1, -1) #Broken inputs
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
#Statement coverage of get_user
def test_failure_get_user(self):
print("Testing failure of get user")
try:
self.db.get_user(-1) #Broken inputs
self.assertTrue(False) #Shouldnt get here
except:
print("Success")
class TestLiveSimulation(unittest.TestCase):
TEST_TICKER_IDX = 0 #Doesnt matter here, but we need the input regardless
#Pre-test simulation init for each test
def setUp(self):
data = pd.read_csv(TEST_DATA)
pred = pd.read_csv(TEST_PRED)
self.db = DBController()
self.sim = LiveSimulation(self.db)
#Test that variables for training are initialized properly
def test_init(self):
print("testing live simulation init.")
controller = self.sim.controller
self.assertTrue(isinstance(controller, DBController))
print("Success")
#Test buy shares reward function.
def test_buy_stocks(self):
print("testing historical sim for regular buy")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds = self.db.get_user(TEST_NAME).bank[0]
self.assertTrue(pre_share_num + 1 == post_share_num)
self.assertTrue(user_funds < 5000)
print("Success")
print("testing historical sim for regular buy W/ no money.")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds = self.db.get_user(TEST_NAME).bank[0]
self.assertTrue(pre_share_num == post_share_num)
print("Success")
#Test sell shares reward function.
def test_sell_stocks(self):
print("testing historical sim for regular sell")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
user_funds_before = self.db.get_user(TEST_NAME).bank[0]
self.sim.buy_shares(1, np.float32(TEST_USER_ID),
np.float32(user_funds_before), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
user_funds_buy = self.db.get_user(TEST_NAME).bank[0]
self.sim.sell_shares(1, 1, np.float32(TEST_USER_ID),
np.float32(user_funds_buy), "v", TEST_SESSION_ID,
self.TEST_TICKER_IDX)
user_funds_after = self.db.get_user(TEST_NAME).bank[0]
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(
pre_share_num == post_share_num
) #buy then sell, no change in vol. Buy is already proven to add +1
self.assertTrue(user_funds_after == user_funds_before)
print("Success")
print("testing historical sim for regular sell W/ no shares.")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.sell_shares(
1, 0, np.float32(TEST_USER_ID), np.float32(5000.0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX
) #aapl test stock has no purchases made ever for test session.
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(pre_share_num == post_share_num)
print("Success")
#Test sell shares reward function.
def test_sell_all_stocks(self):
print("testing historical sim for regular sell all")
pre_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
self.sim.buy_shares(1, np.float32(TEST_USER_ID), np.float32(5000.0),
"v", TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(
pre_share_num + 3 == post_share_num
) #buy then sell, no change in vol. Buy is already proven to add +1
self.sim.sell_all(3, np.float32(TEST_USER_ID), np.float32(5000.0), "v",
TEST_SESSION_ID, self.TEST_TICKER_IDX)
post_share_num = self.db.get_held_shares(TEST_SESSION_ID)
self.assertTrue(post_share_num == pre_share_num)
print("Success")
#Test the state used as input for the RL Agent
def test_get_state(self):
print("testing historical get state")
state = self.sim.get_state(TEST_SESSION_ID,
self.db.get_user(TEST_NAME).bank[0], "v",
self.TEST_TICKER_IDX)
price = state[0]
pred = state[1]
funds = state[2]
shares = state[3]
self.assertTrue(isinstance(price, float))
self.assertTrue(isinstance(pred, float))
self.assertTrue(isinstance(funds, float))
self.assertTrue(isinstance(shares, float))
self.assertTrue(
abs(price - self.sim.get_price("v", self.TEST_TICKER_IDX)) <=
0.50) #Could fluctuate in live
self.assertTrue(
pred == self.sim.get_predicted_price("v", self.TEST_TICKER_IDX))
self.assertTrue(funds == self.db.get_user(TEST_NAME).bank[0])
self.assertTrue(shares == self.db.get_held_shares(TEST_SESSION_ID))
print("Success")
#Test get price
def test_get_price(self):
print("testing historical get price for all stocks")
for tick in stocks:
price = self.sim.get_price(tick, self.TEST_TICKER_IDX)
ticker = Ticker(tick)
cur_price = ticker.price[tick]["regularMarketPrice"]
self.assertTrue(
abs(price - cur_price) <= 0.50) #Could fluctuate in live
self.assertTrue(isinstance(price, float))
self.assertTrue(price > 0)
print("Success")
#Test get pred, statement coverage of pred is obtained here.
def test_get_pred_price(self):
print("testing historical get pred price")
pred = self.sim.get_predicted_price("v", self.TEST_TICKER_IDX)
self.assertTrue(isinstance(pred, int))
self.assertTrue(
pred == 1 or pred == 0
) #In live we cant guarantee statement coverage in our testing since it is dependent on our ML output. And at any given time we cannot have Both a trend up and a trend down.
import time
import urllib
from pathlib import Path
import tldextract
from googlesearch import search
from selenium import webdriver, common
from db_controller import DBController
logger = logging.getLogger("cdn")
logger.setLevel(logging.DEBUG)
logging.basicConfig(filemode="w",
filename="scdn.log",
format="[%(asctime)s][%(levelname)s]: %(message)s")
dbc = DBController()
class Stats:
processed = 0
stats = Stats()
class D2O:
def __init__(self, **entries):
self.__dict__.update(entries)
def found_success(original_url, urls):
class Content(BoxLayout):
pass
class Dialog(BoxLayout):
def get_text_username(self):
return db.get_username()
# sm = ScreenManager()
# sm.add_widget(MenuScreen(name='menu'))
# sm.add_widget(SpinnerScreen(name='spinner'))
db = DBController()
ws = WSConstroller(db)
theme_cls = ThemeManager()
class Chat(MDApp):
dialog_1 = None
dialog_2 = None
chats = dict()
nickname = 'Guest'
length_chats = 0
press = 0
active_dialog = ''
chat_id = 1