def many(sqls, filt, disp, codes): rate = parameter.extreme(sqls['rate'], ext=('0', '0.1')) df = pd.DataFrame(columns=['code', 'ndays']) conn = db.conn() pbar = tqdm(total=len(codes)) for code in codes: pbar.set_description('[{}]'.format(code)) pbar.update(1) sql = "SELECT close, low FROM kdata\ WHERE code='{}' AND date>='{}' AND date<='{}'"\ .format() res = db.conn().all(sql) n = 0 for r in res: _rate = (r['close']-r['low']) / r['low'] if rate[0] <= _rate <= rate[1]: n += 1 if n>0: df.append({ 'code': code, 'ndays': n, }) return df
def add(ps): sql = "INSERT OR REPLACE INTO monitor(id, user, date, code, volume, amount, message) VALUES (\ (SELECT id FROM monitor WHERE user=? AND code=?), ?, ?, ?, ?, ?, ?)" db.conn().exec(sql, [ ps.user, ps.code, ps.user, date.today(), ps.code, ps.volume, ps.amount, ps.message ]) print('Add stock monitor info successed.')
def once(sqls, filt, disp): rate = parameter.extreme(sqls['rate'], ext=('0', '0.1'), toFloat=False) sql = "SELECT code, (close-low)/low AS rate FROM kdata WHERE date='{}' AND close<open AND (close-low)/low>={} AND (close-low)/low<={}"\ .format(sqls['date'], rate[0], rate[1]) df = db.conn().df(sql) return df
def main(argString='600300'): code = argString sql = "SELECT code, price, change, volume, amount/10000 as amount, turnover, pe, \ name || ' ' || industry || ' ' || area as extrInfo\ FROM day_all WHERE code=?" df = db.conn().df(sql, [code]) print(df) print('近期价格:') sql = "SELECT date, close, open, high, low, volume FROM kdata WHERE code=? ORDER BY date DESC LIMIT 5" df = db.conn().df(sql, [code]) df.sort_values('date', inplace=True) print(df) exit() sql = "SELECT max(date) FROM kdata" date = db.conn().val(sql) get_tick_data(code, date)
def fetch_k_data(codes): print('Fetching data through function named get_k_data(code)... ') sql = "SELECT code, max(date) FROM kdata GROUP BY code" dates = dict(db.conn().all(sql)) lastTradeDay = tdate.lastTrade(tdate.today()) errorCodes = [] pbar = tqdm(total=len(codes)) for code in codes: pbar.set_description('[{}]'.format(code)) sDate = dates.get(code, '2017-01-01') if sDate < lastTradeDay: try: df = ts.get_k_data(code, start=sDate) df = df.set_index('date') df.to_sql('kdata', db.conn().connect, if_exists='append') except Exception as e: print('[{}] some error raised:'.format(code), e) errorCodes.append(code) pbar.update(1) pbar.close() print('The data fetched and saved to table [kdata] success.')
def main(argString=''): ps = {'page': '1'} # ps.update(paras) offset = (int(ps.get('page', 1)) - 1) * 10 sql = "SELECT code, name from stock_basics LIMIT {offset}, 10"\ .format(offset=offset, **ps) res = db.conn().all(sql) for r in res: print(r) print() print(ps)
def execute(sql): db.conn().exec(sql) print('execute SQL successed.')
def fetch_hist_data(): codes = ['600300', '300347', '0000001', '000881'] codes = ['300347'] today = datetime.date.today() weekday = today.weekday() if weekday == 5: today -= datetime.timedelta(days=1) elif weekday == 6: today -= datetime.timedelta(days=2) pbar = tqdm(total=len(codes)) for code in codes: sql = "SELECT date, close, hfq FROM hist WHERE code=? ORDER BY date DESC LIMIT 1" res = db.conn().one(sql, [code]) p_date, p_close, p_hfq = res if res else ('2017-01-01', 0, 1) startdate = datetime.date( *map(int, p_date.split('-'))) + datetime.timedelta(days=1) if startdate <= today: try: df = ts.get_hist_data(code, start=startdate.strftime('%Y-%m-%d')) except Exception as e: print(e) else: pass if any(df): df = df.sort_index() hfqs, needFQ = [], False if p_close == 0: r0 = df.iloc[0] p_close = round(r0['close'] - r0['price_change'], 2) for idx in df.index: r = df.loc[idx] E = round(r['close'] - r['price_change'], 2) if abs(E - p_close) < 0.02: hfq = p_hfq else: hfq = round(p_hfq * p_close / E, 9) needFQ = True hfqs.append(hfq) p_close, p_hfq = r['close'], hfq df.insert(0, 'code', pd.Series([code], index=df.index)) df.insert(1, 'hfq', pd.Series(hfqs, index=df.index)) df.insert(2, 'qfq', pd.Series([1], index=df.index)) df.to_sql('hist', db.conn().connect, if_exists='append') if needFQ: sql = "UPDATE kdata SET qfq = hfq/? WHERE code=?" db.conn().showsql().exec(sql, [hfq, code]) pbar.update(1) pbar.close() print('fetch data successed.') sql = "SELECT code, date, hfq, qfq FROM kdata ORDER BY date" res = db.conn().all(sql) for r in res: print(r)
#!/usr/bin/env python # -*- coding:utf-8 # author: HuoHongJian # date: 2018-01-05 import os, time import pandas as pd from libs.Sqlite import Sqlite as db from libs import tdate, cache, parameter, dataFrame sql = "SELECT max(date) FROM kdata" maxdate = db.conn().val(sql) sqls = {'ma':'5,10', 'date':maxdate} filt = {} disp = {} def main(argString=''): global sqls, filt, disp sqls, filt, disp = parameter.parse(argString, sqls, filt, disp) key = hash('ycx' + str(sqls)) if cache.dfs.has(key): df = cache.dfs.get(key) else: date = sqls.get('date') if not tdate.isTrade(date): print('The date you input is not trade day, please reset date parameter') return
def fetch_today_all(): print('Fetching today_all... ') df = ts.get_today_all() df.to_sql('today_all', db.conn().connect, if_exists='replace') print('is done! AND saved data to table [today_all] success.')
def fetch_stock_basics(): print('Fetching stock_basics... ') df = ts.get_stock_basics() df.to_sql('stock_basics', db.conn().connect, if_exists='replace') print('is done! And saved data to table [stock_basics] success')
def remove(ps): sql = "DELETE FROM monitor WHERE user=? AND code=?" db.conn().exec(sql, [ps.user, ps.code]) print('code=[{}] stock is deleted success.')
def fetchall(sql): res = db.conn().all(sql) print([r[0] for r in db.cursor.description]) print('-' * 30) for r in res: print(r)
def show(ps): sql = "SELECT * FROM monitor WHERE user=?" for r in db.conn().all(sql, [ps.user]): print(r)
def fetch_day_all(): print('Fetching day_all... ') # 判断是否为当天数据 for N in range(10): df = ts.get_day_all() size = df.shape[0] loop = False for i in range(3): idx = random.randint(0, size - 1) _df = df.ix[idx] sql = "SELECT open, close, high, low FROM kdata WHERE code=? ORDER BY date DESC LIMIT 1" res = db.conn().one(sql, [_df.code]) if not any(res): continue if res[0] == _df.open and res[1] == _df.price and res[ 2] == _df.high and res[3] == _df.low: loop = True break if not loop: break if N > 8: exit() print('网上数据不是最新的,1个小时后,再次下载.') time.sleep(3600) # 处理下载的数据 df = df.loc[df.open > 0] df.to_sql('day_all', db.conn().connect, if_exists='replace') codes = list(df.code) #np.array(df[['code']]).tolist() print('is done! And saved data to table [day_all] success.') # 判断是否append to table [kdata] sql = "SELECT max(date) FROM kdata" maxDate = db.conn().val(sql) or '2018-01-01' today = tdate.today() _time = tdate.time() nextTradeDay = tdate.nextTrade(maxDate) # 数据连续,可一次性下载当天全部交易数据 if (nextTradeDay == today and _time > '15:50:00') or 1: df = df[['code', 'open', 'price', 'high', 'low', 'volume']] df = df.set_index('code') df.insert(0, 'date', pd.Series([nextTradeDay], index=df.index)) df.rename(columns={'price': 'close'}, inplace=True) df.to_sql('kdata', db.conn().connect, if_exists='append') print( 'At the same time, day_all data also saved to table [kdata] success.' ) sql = "INSERT INTO log (operate, result, message) VALUES (?, ?, ?)" db.conn().exec(sql, [ 'fetch_day_all', 'success', 'fetch and saveed the data to tables [day_all, kdata], total=[{}]'. format(len(codes)) ]) print('Starting compute moving average... ') pbar = tqdm(total=len(codes)) for code in codes: pbar.set_description('[{}]'.format(code)) sql = "SELECT id, close, volume FROM kdata WHERE code=? ORDER BY date DESC LIMIT 60" rdf = db.conn().df(sql, [code]).sort_index(axis=0, ascending=False) rdf = fetch.compute_ma(rdf) r = rdf.iloc[-1] sql = '''UPDATE kdata SET ma5=?, ma10=?, ma20=?, ma30=?, ma60=?, va5=?, va10=?, va20=?, va30=?, va60=? WHERE id=?''' db.conn().exec(sql, [*r[3:].round(2), r.id]) pbar.update(1) pbar.close() else: print( 'Save data to table [kdata] failed, Please execute the program at trade day and time > 16:00:00.' )
def buystock(code, price, volume, user, date, remark): sql = "INSERT INTO trade(user, date, deal, code, price, volume, amount, remark) VALUES(?,?,'b',?,?,?,?,?)" db.conn().showsql(False).exec( sql, [user, date, code, price, volume, price * volume, remark]) print('add the info of buy stock is successed.')
def getByCode(code): sql = "SELECT * FROM trade WHERE code=?" rs = db.conn().all(sql, code) render(rs)
def getAll(): sql = "SELECT * FROM trade" rs = db.conn().all(sql) render(rs)
def fetch_trade_calendar(): print('Fetching trade_calendar... ', end='') df = ts.trade_cal() df.to_sql('trade_cal', db.conn().connect, if_exists='replace') print('is done! And saved data to table [trade_cal] success.')
def fetch_concept_classified(): print("fetching concept classified data (概念分类)") df = ts.get_concept_classified() df.to_sql('concept', db.conn().connect, if_exists='replace') print("Concept classified data is saved to [concept] table success.")
def monitor(ps): import tushare as ts from libs.Sqlite import Sqlite as db from colorama import Fore, Back, Style COLORS = ['YELLOW', 'MAGENTA', 'CYAN', 'RED', 'GREEN', 'BLUE'] * 3 header = '{:>3} {:^8} {:^6} {:>6} {:>6} {:>5} {:>6} {:>6} {:>6} {:>6}'.format( 'NO', 'time', 'code', 'price', 'pc ', 'pcr', 'valume', 'amount', 'bid', 'ask') pattern = '{:>3} {:^8} {:^6} {:>6.2f} {:>6.2f} {:>5.1f}% {:>6.0f} {:>6.0f} {:>6.2f} {:>6.2f}' sql = "SELECT code, volume, amount FROM monitor WHERE user=?" res = db.conn().all(sql, [ps.user]) print('Monitoring:', str(res)) i, I, codes, stock = 0, 0, [], {} for code, volume, amount in res: codes.append(code) stock[code] = { 'volume': volume, 'amount': amount, 'color': COLORS[I], 'lastVolume': 0, 'lastAmount': 0 } I += 1 while True: time.sleep(6) t = time.strftime("%H:%M:%S") if (t < '09:28:00' or (t > '11:32:00' and t < '12:58:00') or t > '15:02:00'): time.sleep(54) continue try: df = ts.get_realtime_quotes(codes) except: continue for r in df.values: open, pre_close, price, high, low, bid, ask = float(r[1]), float( r[2]), float(r[3]), float(r[4]), float(r[5]), float( r[6]), float(r[7]) volume, amount = float(r[8]) / 100, float(r[9]) / 10000 # b1_v, b2_v, b3_v, b4_v, b5_v = r[10], r[12], r[14], r[16], r[18] # b1_p, b2_p, b3_p, b4_p, b5_p = r[11], r[13], r[15], r[17], r[19] # a1_v, a2_v, a3_v, a4_v, a5_v = r[20], r[22], r[24], r[26], r[28] # a1_p, a2_p, a3_p, a4_p, a5_p = r[21], r[23], r[25], r[27], r[29] date, times, code = str(r[30]), str(r[31]), str(r[32]) vc = volume - stock[code]['lastVolume'] ac = amount - stock[code]['lastAmount'] stock[code]['lastVolume'] = volume stock[code]['lastAmount'] = amount pc = price - pre_close pcr = pc / pre_close * 100 if vc > stock[code]['volume'] and ac > stock[code]['amount']: i += 1 if i % 10 == 1: print(Back.BLUE, header, '', Style.RESET_ALL) print( getattr(Fore, stock[code]['color']), pattern.format(i, times, code, price, pc, pcr, vc, ac, bid, ask), Style.RESET_ALL) # print('\a') if i % 5 == 0: print()
from libs.Sqlite import Sqlite as db import tushare as ts import threading import time from concurrent import futures from queue import Queue end = object() print('start..........') s = time.time() q = Queue() sql = "select code from day_all limit 100" res = db.conn().all(sql) codes = [r[0] for r in res] def test(): print('this is a function') for i in range(100): print(i) r = q.get() print(r) if r == 'hhj': print('hhj') break pbar = tqdm(total=len(codes))