def dt(conn, msg): if len(msg.text.split()) > 2: naam = msg.text.split()[1] error = ' '.join(msg.text.split()[2:]) rowid = mysql.set('INSERT INTO irc_dt (name, error) VALUES (%s, %s)', (naam, error)) conn.send('PRIVMSG %s :DT-fout %i added!\r\n' % (msg.channel, rowid)) else: parts = msg.text.split() if len(parts) == 1: rows, count = mysql.get('SELECT * FROM irc_dt ORDER BY RAND()', '') if count != 0: conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2])) elif parts[1].isdigit(): rows, count = mysql.get('SELECT * FROM irc_dt WHERE id=%s', parts[1]) if count != 0: conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2])) else: rows, count = mysql.get('SELECT * FROM irc_dt WHERE name=%s ORDER BY RAND()', (parts[1])) if count != 0: conn.send('PRIVMSG %s :DT-fout: %s heeft in totaal al %i DT-fouten gemaakt\r\n' % (msg.channel, rows[0][1], count)) else: conn.send('PRIVMSG %s :DT-fout: %s heeft nog geen DT-fouten gemaakt\r\n' % (msg.channel, parts[1]))
def quote(conn, msg): parts = msg.text.split() if len(parts) == 1: # Random quote rows, count = mysql.get('SELECT * FROM irc_quote ORDER BY RAND()', '') elif parts[1].isdigit(): # Haal quote nummer zoveel op rows, count = mysql.get('SELECT * FROM irc_quote WHERE id=%s', parts[1]) else: rows, count = mysql.get('SELECT * FROM irc_quote WHERE quote LIKE %s ORDER BY RAND()', ("%"+parts[1]+"%")) if count != 0: conn.send('PRIVMSG %s :Quote %i: %s\r\n' % (msg.channel, rows[0][0], rows[0][1]))
def init_concept_from_ts(self): table = 'concept' sql = 'create table if not exists %s(c_name varchar(50),code varchar(5000))' % table if table not in self.tables: if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql): raise Exception("create table %s failed" % table) df_concept = ts.get_concept_classified() df_concept = df_concept[['code', 'c_name']] df_concept_dict = {} for index, code_id in df_concept['code'].iteritems(): concept_name = df_concept['c_name'][index] if concept_name in df_concept_dict: if not code_id in df_concept_dict: df_concept_dict[concept_name].append(code_id) else: df_concept_dict[concept_name] = [] df_concept_dict[concept_name].append(code_id) concept_obj = {} with open("concepts/concepts.json") as f: concept_obj = json.load(f) for key, value in concept_obj.items(): if key in df_concept_dict: if value not in df_concept_dict[key]: df_concept_dict[key].extend(value) else: df_concept_dict[key] = value for key in df_concept_dict: df_concept_dict[key] = json.dumps(df_concept_dict[key], ensure_ascii=False) df_concept = DataFrame({ 'c_name': df_concept_dict.keys(), 'code': df_concept_dict.values() }) set(self.engine, df_concept, table) return get(self.engine, SQL % table)
def post(self): fid = self.get_argument('fid', None) password = self.get_argument('password', None) if fid is None or password is None: response = {'status': 'error', 'next': '/faculty-login'} self.finish(json_encode(response)) return fid = json_decode(fid) password = json_decode(password) faculty = mysql.get('faculty', {'fid': fid}) if faculty is None: response = {'status': 'error', 'next': '/faculty-login'} self.finish(json_encode(response)) return if len(faculty) != 1: response = {'status': 'error', 'next': '/faculty-login'} self.finish(json_encode(response)) return row = faculty[0] response = {'status': 'error', 'next': '/faculty-login'} if row: if password == row['password']: role = row['role'] self.set_cookie('role', role) self.set_cookie('fid', fid) response = {'status': 'ok', 'next': '/faculty-home'} self.finish(json_encode(response))
def get_classified_stocks(type_name=MARKET_ALL): """ 获取股票类型 Return -------- DataFrame code :股票代码 name :股票名称 """ table = "info" engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) df = get(engine, SQL % table) df = df[[ 'code', 'name', 'timeToMarket', 'c_name', 'totals', 'outstanding', 'industry', 'area' ]] if type_name == MARKET_SH: df = df.ix[df.code.str[0] == '6'] elif type_name == MARKET_CYB: df = df.ix[df.code.str[0] == '3'] elif type_name == MARKET_SZ: df = df.ix[df.code.str[0] == '0'] else: pass return df.sort_values('code').reset_index(drop=True)
def init_forward_adjusted_price(self): _today = datetime.now().strftime('%Y-%m-%d') start_date = str(datetime.datetime.today().date() + datetime.timedelta(-1)) end_date = str(datetime.datetime.today().date()) stock_infos = get_classified_stocks(market) total_data = None table = 'forward_adjusted_price' sql = 'create table if not exists `%s`(date varchar(10),\ open float,\ high float,\ close float,\ low float,\ volume float,\ code varchar(8))' % table if is_trading_day(_today): for _index, code_id in stock_infos['code'].iteritems(): tmp_data = ts.get_k_data(code_id, start=start_date, end=end_date, autype='qfq') tmp_data['code'] = code_id tmp_data = tmp_data.reset_index(drop=False) total_data = tmp_data if _index == 0 else pd.concat( [total_data, tmp_data], ignore_index=True).drop_duplicates() set(self.engine, total_data, table) return get(self.engine, SQL % table)
def sync_uid(): result = mysql.get('id', {'name': 'uid'}) if len(result) == 0: mysql.insert('id', {'name': 'uid', 'num': 0}) logic.global_uid = 0 else: logic.global_uid = result[0]['num']
def is_trading_day(_date): table = "calendar" engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) stock_dates_df = get(engine, SQL % table) return stock_dates_df.query('calendarDate=="%s"' % _date).isOpen.values[0] == 1
def post(self): old = self.get_argument('old', None) password = self.get_argument('password', None) if old is None or password is None: response = {'status': 'error'} self.write(json_encode(response)) return role = self.get_cookie('role') fid = self.get_cookie('fid') old = json_decode(old) password = json_decode(password) faculty = mysql.get('faculty', { 'fid': fid, 'password': old, 'role': role }) if faculty is None or len(faculty) != 1: response = {'status': 'error'} self.write(json_encode(response)) return sql = 'update faculty set password = "******" where fid = "%s"' % ( password, fid) result = mysql.execute(sql) if result: response = {'status': 'ok'} else: response = {'status': 'error'} self.write(json_encode(response))
def init_stock_basic_info(self): table = 'info' text = 'create table if not exists %s(code int,\ name varchar(20),\ industry varchar(20),\ c_name varchar(100),\ area varchar(20),\ pe float,\ outstanding float,\ totals float,\ totalAssets float,\ fixedAssets float,\ reserved float,\ reservedPerShare float,\ esp float,\ bvps float,\ pb float,\ timeToMarket int,\ undp float,\ perundp float,\ rev float,\ profit float,\ gpr float,\ npr float,\ limit-up-num int,\ continuous-limit-up-num int,\ holders int)' % table if table not in self.tables: if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, text): raise Exception("create table %s failed" % table) df = ts.get_stock_basics() df = df.reset_index().rename_axis({'index': 'code'}, axis="columns") df_concept = ts.get_concept_classified() df_concept = df_concept[['code', 'c_name']] df_concept_dict = {} for index, code_id in df_concept['code'].iteritems(): concept_name = df_concept['c_name'][index] if code_id in df_concept_dict: if not concept_name in df_concept_dict: df_concept_dict[code_id].append(concept_name) else: df_concept_dict[code_id] = [] df_concept_dict[code_id].append(concept_name) for key in df_concept_dict: df_concept_dict[key] = json.dumps(df_concept_dict[key], ensure_ascii=False) df_concept = DataFrame({ 'code': df_concept_dict.keys(), 'c_name': df_concept_dict.values(), 'limit-up-num': 0, 'continuous-limit-up-num': 0 }) df_all = pd.merge(df, df_concept, how='left', on=['code']) df_all.reset_index(drop=True) set(self.engine, df_all, table) return get(self.engine, SQL % table)
def is_code_exists(code_id): table = "info" engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) stock_info = get(engine, SQL % table) stock_info = stock_info[['code', 'timeToMarket']] if len(stock_info[stock_info.code == code_id].index.tolist()) > 0: return True return False
def explain(conn, msg): if len(msg.text.split()) > 1: word = msg.text.split()[1] rows, count = mysql.get('SELECT * FROM irc_assign WHERE word = %s', (word)) if count != 0: conn.send('PRIVMSG %s :%s: %s\r\n' % (msg.channel, word, rows[0][2])) else: usage = 'Gebruik: ? woord' conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def get_stock_volumes(market, data_times): stock_id_frame = get_classified_stocks(market) stock_ids = list() stock_names = list() stock_turnover = list() stock_volume = list() stock_concepts = list() stock_dates = list() stock_pchanges = list() pydate_array = data_times.to_pydatetime() date_only_array = np.vectorize(lambda s: s.strftime('%Y-%m-%d'))( pydate_array) engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) for _date in date_only_array: if is_trading_day(_date): for code_index, code_id in stock_id_frame['code'].iteritems(): if not is_sub_new_stock( str(stock_id_frame['timeToMarket'][code_index])): turnover = 0 volume = 0 stock_name = stock_id_frame['name'][code_index] stock_concept = stock_id_frame['c_name'][code_index] hist_data = get_hist_data(engine, code_id, _date) if hist_data is not None: hist_data = hist_data[hist_data['volume'] > 0] hist_data = hist_data[[ 'turnover', 'volume', 'p_change' ]] if not hist_data.empty: turnover = hist_data['turnover'][0] p_change = hist_data['p_change'][0] if turnover > 5 and p_change > 5: stock_dates.append(_date) stock_ids.append(code_id) stock_names.append(stock_name) volume = hist_data['volume'][0] stock_volume.append(volume) stock_turnover.append(turnover) stock_concepts.append(stock_concept) stock_pchanges.append(p_change) df = DataFrame({ 'date': stock_dates, 'code': stock_ids, 'name': stock_names, 'turnover': stock_turnover, 'volume': stock_volume, 'c_name': stock_concepts, 'p_change': stock_pchanges }) table = 'turnover' sql = 'create table if not exists %s(date varchar(10),code varchar(10),name varchar(20),volume float,p_change float,c_name varchar(5000))' % table engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) set(engine, df, table) return get(engine, SQL % table)
def init_trading_day(self): table = 'calendar' sql = 'create table if not exists `%s`(calendarDate varchar(10),isOpen int)' % table if table not in self.tables: if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql): raise Exception("create table %s failed" % table) trading_day = ts.trade_cal() if trading_day is not None: set(self.engine, trading_day, str(table)) return get(self.engine, SQL % table)
def get(self): #import pdb #pdb.set_trace() fid = self.get_cookie('fid') self.clear_cookie('role') roles = mysql.get('faculty', {'fid': fid}) if len(roles) == 0: return roles = roles[0]['role'] roles = roles.split(',') self.render('faculty-role.html', roles=roles, fid=fid)
def post(self): #import pdb #pdb.set_trace() fid = self.get_argument('fid') passwd = self.get_argument('passwd') result = mysql.get('password', {'fid': fid}) response = {'status': 'error'} if len(result) == 1: if passwd == result[0]['passwd']: self.set_cookie('fid', fid) response = {'status': 'ok'} self.write(json_encode(response))
def get_post_trading_day(_date): table = 'calendar' engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) df = get(engine, SQL % table) _index = df[df.calendarDate == _date].index.tolist()[0] if _index > 0: _tindex = _index while _tindex < len(df): _tindex += 1 if df['isOpen'][_tindex] == 1: return df['calendarDate'][_tindex] raise Exception("can not find post trading day.")
def collect_concept_volume_price(data_times): table = 'concept' engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) concepts = get(engine, SQL % table) stock_infos = get(engine, SQL % 'info') pydate_array = data_times.to_pydatetime() date_only_array = np.vectorize(lambda s: s.strftime('%Y-%m-%d'))( pydate_array) tables = get_all_tables(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME) infos = dict() for _date in date_only_array: if is_trading_day(_date): for index, row in concepts.iterrows(): concept_name = row['c_name'] infos[concept_name] = list() codes = json.loads(row['code']) for code_id in codes: if is_code_exists(code_id): if is_after_release(code_id, _date): if code_id in tables: hist_data = get_hist_data( engine, code_id, _date) rate = hist_data['p_change'] if len(rate) > 0 and rate[0] > 5: volume = hist_data['volume'][0] pre_price = hist_data['close'][ 0] + hist_data['price_change'][0] c_index = stock_infos[ stock_infos.code == code_id].index.tolist()[0] code_name = stock_infos.loc[c_index, 'name'] up_date = get_highest_time( code_id, _date, pre_price) infos[concept_name].append( (code_id, code_name, rate[0], up_date, volume)) return infos
def post(self): fid = self.get_cookie('fid') cookdo = [] result = mysql.get('cook_do', {'fid': fid}) flag = False for one in result: if one['did'] == 'all': flag = True break cookdo.append(one['did']) if flag == True: cookdo = ['all'] response = {'status': 'ok', 'cookdo': cookdo} self.write(json_encode(response))
def get_concepts(type_name): """ 获取题材类型 Return -------- DataFrame code :股票代码 name :股票名称 """ table = "concept" engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) df = get(engine, SQL % table) return df.sort_values('code').reset_index(drop=True)
def post(self): fid = self.get_cookie('fid') passwd1 = self.get_argument('passwd1') passwd2 = self.get_argument('passwd2') result = mysql.get('password', {'fid': fid}) #print result response = {'status': 'failure'} if result and result[0] and result[0]['passwd'] == passwd1: sql = 'update password set passwd = "%s" where fid = "%s"' % (passwd2, fid) #print sql r = mysql.execute(sql) #print r if r: response = {'status': 'success'} self.write(json_encode(response))
def is_after_release(code_id, _date): table = "info" engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) stock_info = get(engine, SQL % table) stock_info = stock_info[['code', 'timeToMarket']] _index = stock_info[stock_info.code == code_id].index.tolist()[0] time2Market = stock_info.loc[_index, 'timeToMarket'] if time2Market: t = time.strptime(str(time2Market), "%Y%m%d") y, m, d = t[0:3] time2Market = datetime(y, m, d) if (datetime.strptime(_date, "%Y-%m-%d") - time2Market).days > 0: return True return False
def post(self): did = int(json_decode(self.get_argument('did'))) res = mysql.get('diet', {'did': did}) if res and res[0]: picture = res[0]['picture'] full_path = os.path.join(os.path.dirname(__file__), 'static/pictures/' + picture) os.remove(full_path) mysql.delete('diet', {'did': did}) data.diet = mysql.get_all('diet') response = {'status': 'ok'} self.finish(json_encode(response)) return response = {'status': 'err'} self.finish(json_encode(response))
def post(self): did = self.get_argument('did') if did in logic.diet: logic.diet.pop(did) result = mysql.get('diet', {'did': did}) if result and result[0]: picture = result[0]['pic'] full_path = os.path.join(logic.data_dir, 'pictures/' + picture) if mysql.delete('diet', {'did': did}) and picture != '': if os.path.isfile(full_path): os.remove(full_path) response = {'status': 'ok'} self.finish(json_encode(response)) return response = {'status': 'error'} self.finish(json_encode(response))
def reassign(conn, msg): if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg): if len(msg.text.split()) > 2: word = msg.text.split()[1] defin = ' '.join(msg.text.split()[2:]) rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word)) if count == 0: conn.send('PRIVMSG %s :%s is not defined yet. Use !assign word def to assign it.\r\n' % (msg.channel, word)) else: rowid = mysql.set('UPDATE irc_assign SET def=%s WHERE word=%s', (defin, word)) conn.send('PRIVMSG %s :%s reassigned to: %s\r\n' % (msg.channel, word, defin)) else: usage = 'Gebruik: !reassign woord definitie' conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage)) else: usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.' conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def assign(conn, msg): if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg): if len(msg.text.split()) > 2: word = msg.text.split()[1] defin = ' '.join(msg.text.split()[2:]) rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word)) if count > 0: conn.send('PRIVMSG %s :%s is already defined: %s\r\n' % (msg.channel, rows[0][1], rows[0][2])) else: rowid = mysql.set('INSERT INTO irc_assign (word, def) VALUES (%s, %s)', (word, defin)) conn.send('PRIVMSG %s :%s added to assign list.\r\n' % (msg.channel, word)) else: usage = 'Gebruik: !assign woord definitie' conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage)) else: usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.' conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def lijst(conn, msg): aantal = 5 if len(msg.text.split()) > 1: getal = msg.text.split()[1] if getal.isdigit(): getal = int(getal) if getal > 0: aantal = getal if getal > 15: aantal = 15 rows, count = mysql.get('SELECT name, COUNT(*) FROM `irc_dt` GROUP BY name ORDER BY COUNT(*) DESC', '') conn.send('PRIVMSG %s :Nr.: Naam - Aantal\r\n' % (msg.channel)) if count < aantal: aantal = count conn.send('PRIVMSG %s :DT-fouten top %i:\r\n' % (msg.channel, aantal)) for row in range(0, aantal): conn.send('PRIVMSG %s : %i: %s - %s\r\n' % (msg.channel, (row+1), rows[row][0], rows[row][1]))
def __init__(self, fid): self.fid = fid self.name = faculty.get(fid)['name'] self.current = None self.byway = [] self.doing = [] self.done = [] #self.deny = [] self.cookdo = [] self.waiters = set() self.stamp = time.time() self.queue = [] #init self.cookdo result = mysql.get('cook_do', {'fid': self.fid}) all = False for one in result: if one['did'] == 'all': all = True self.cookdo.append(one['did']) if all: self.cookdo = ['all']
def lijst(conn, msg): aantal = 10 if len(msg.text.split()) > 1: getal = msg.text.split()[1] if getal.isdigit(): getal = int(getal) if getal > 0: aantal = getal if getal > 30: aantal = 30 rows, count = mysql.get('SELECT * FROM irc_assign ORDER BY id DESC', '') if aantal > count: aantal = count out=[] for row in range(0, aantal): out.append(rows[row][1]) conn.send('PRIVMSG %s :Last %i assigns: %s\r\n' % (msg.channel, aantal, ', '.join(out)))
def __init__(self, table): self.table = table.upper() self.seats = mysql.get('desks', {'desk': table})[0]['num'] self.pid = 0 self.gdemand = '' self.comment = '' self.orders = [] self.left = [] self.doing = [] self.done = [] self.cancel = [] #canceled by customer or waiter self.payed = [] self.delete = [] #deleted by cashier self.submit = 0 self.last = 0 self.stamp = time.time() self.waiters = set() self.history = [] self.power = 0
def active(conn, msg): rows, count = mysql.get('SELECT * FROM irc_tell WHERE `to`=%s', (msg.user)) for row in rows: conn.send('PRIVMSG %s :%s: [%s] %s: %s\r\n' % (msg.channel, row[3], row[1], row[2], row[4])) count = mysql.set('DELETE FROM irc_tell WHERE `id`=%s', row[0])
def get(table, time_from, time_to): if table in ('diet', 'category', 'faculty'): res = mysql.get(table, time_from, time_to) else: res = myredis.get(table, time_from, time_to) return res
def daily_analysis(data_times): engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' % (DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME)) stock_infos = get(engine, SQL % 'info')
def get(table, con): res = mysql.get(table, con) return res