class PlatePERatioCrawler(object):
def __init__(self, dbinfo=ct.DB_INFO, redis_host=None):
self.dbname = self.get_dbname()
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(host=redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis=self.redis)
if not self.mysql_client.create_db(self.dbname):
raise Exception("init pledge database failed")
@staticmethod
def get_dbname():
return "plate_pe"
def create_table(self, table):
import pdb
pdb.set_trace()
sql = 'create table if not exists %s(date varchar(10) not null,\
code varchar(10) not null,\
name varchar(50),\
pledge_counts int,\
unlimited_pledge_stocks float,\
limited_pledge_stocks float,\
total_stocks float,\
pledge_ratio float,\
PRIMARY KEY(date, code))' % table
return True if table in self.mysql_client.get_all_tables(
) else self.mysql_client.create(sql, table)
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.dbname = self.get_dbname()
self.redis_host = redis_host
self.logger = getLogger(__name__)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.get_dbname()): raise Exception("init rstock database failed")
class InvestorCrawler(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table = self.get_table_name()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.dbname): raise Exception("init stock database failed")
if not self.create_table(): raise Exception("init week investor table failed")
@staticmethod
def get_dbname():
return "stock"
@staticmethod
def get_table_name():
return "investor"
def create_table(self):
sql = 'create table if not exists %s(date varchar(10) not null,\
new_investor float,\
final_investor float,\
new_natural_person float,\
new_non_natural_person float,\
final_natural_person float,\
final_non_natural_person float,\
PRIMARY KEY(date))' % self.table
return True if self.table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, self.table)
class CInvestor(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table = self.get_table_name()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
@staticmethod
def get_dbname():
return "stock"
@staticmethod
def get_table_name():
return "month_investor"
def get_data(self, mdate = None):
table_name = self.get_table_name()
if mdate is not None:
sql = "select * from %s where date=\"%s\"" %(table_name, mdate)
else:
sql = "select * from %s" % table_name
return self.mysql_client.get(sql)
def get_data_in_range(self, start_date, end_date):
table_name = self.get_table_name()
sql = "select * from %s where date between \"%s\" and \"%s\"" %(table_name, start_date, end_date)
return self.mysql_client.get(sql)
class PlateValuationCrawler(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table_name = self.get_tablename()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.dbname): raise Exception("create plate database failed")
if not self.create_table(self.table_name): raise Exception("create valuation table failed")
@staticmethod
def get_dbname():
return "plate"
@staticmethod
def get_tablename():
return "valuation"
def create_table(self, table):
sql = 'create table if not exists %s(date varchar(10) not null,\
code varchar(10) not null,\
name varchar(50),\
pe float,\
ttm float,\
pb float,\
dividend float,\
PRIMARY KEY(date, code))' % table
return True if table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, table)
def __init__(self,
market=ct.SH_MARKET_SYMBOL,
dbinfo=ct.DB_INFO,
redis_host=None):
self.market = market
self.dbinfo = dbinfo
self.balcklist = ['2006-07-10'
] if market == ct.SH_MARKET_SYMBOL else list()
self.logger = getLogger(__name__)
self.dbname = self.get_dbname(market)
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(host=redis_host)
self.header = {
"Host":
"query.sse.com.cn",
"Referer":
"http://www.sse.com.cn/market/stockdata/overview/day/",
"User-Agent":
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36"
}
self.mysql_client = CMySQL(self.dbinfo,
dbname=self.dbname,
iredis=self.redis)
if not self.mysql_client.create_db(self.dbname):
raise Exception("create %s failed" % self.dbname)
class ChinaTreasuryRateCrawler(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table_name = self.get_tablename()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.dbname): raise Exception("create china treasury database failed")
if not self.create_table(self.table_name): raise Exception("create rate table failed")
@staticmethod
def get_dbname():
return "china_treasury"
@staticmethod
def get_tablename():
return "yield"
def create_table(self, table):
sql = 'create table if not exists %s(date varchar(10) not null,\
name varchar(50),\
month3 float,\
month6 float,\
year1 float,\
year3 float,\
year5 float,\
year7 float,\
year10 float,\
year30 float,\
PRIMARY KEY(date, name))' % table
return True if table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, table)
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table_name = self.get_tablename()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.dbname): raise Exception("create plate database failed")
if not self.create_table(self.table_name): raise Exception("create valuation table failed")
def __init__(self, dbinfo):
self.redis = create_redis_obj()
self.mysql_client = CMySQL(dbinfo)
self.table = ct.ANIMATION_INFO
self.trigger = ct.SYNC_ANIMATION_2_REDIS
if not self.create(): raise Exception("create animation table %s table failed" % self.table)
if not self.register(): raise Exception("create animation trigger %s failed" % self.trigger)
def __init__(self, dbinfo):
self.table = ct.DELISTED_INFO_TABLE
self.trigger = ct.SYNC_DELISTED_2_REDIS
self.mysql_client = CMySQL(dbinfo)
self.redis = create_redis_obj()
if not self.create(): raise Exception("create delisted table failed")
if not self.init(True): raise Exception("init delisted table failed")
if not self.register(): raise Exception("create delisted trigger failed")
def __init__(self, code, dbname, dbinfo, redis_host):
self.code = code
self._dbname = dbname
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(dbinfo=dbinfo,
dbname=dbname,
iredis=self.redis)
def __init__(self, dbinfo, table):
self.table = table
self.redis = create_redis_obj()
self.mysql_client = CMySQL(dbinfo)
self.trigger = ct.SYNC_HALTED_2_REDIS
if not self.create(): raise Exception("create chalted table failed")
if not self.init(True): raise Exception("init chalted table failed")
if not self.register(): raise Exception("create chalted trigger failed")
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbinfo = dbinfo
self.emotion_table = ct.EMOTION_TABLE
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(self.dbinfo, iredis = self.redis)
self.rstock_client = RIndexStock(dbinfo, redis_host)
self.logger = getLogger(__name__)
if not self.create(): raise Exception("create emotion table failed")
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.table = self.get_table_name()
self.logger = getLogger(__name__)
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(dbinfo, iredis = self.redis)
self.header = {"Host": "home.flashdata2.jrj.com.cn",
"Referer": "http://stock.jrj.com.cn/tzzs/zdtwdj/zdforce.shtml",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36"}
if not self.create(): raise Exception("create stock %s failed" % self.table)
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
for (market_from, market_to) in [(ct.SH_MARKET_SYMBOL, ct.HK_MARKET_SYMBOL), (ct.HK_MARKET_SYMBOL, ct.SH_MARKET_SYMBOL), (ct.SZ_MARKET_SYMBOL, ct.HK_MARKET_SYMBOL), (ct.HK_MARKET_SYMBOL, ct.SZ_MARKET_SYMBOL)]:
dbname = self.get_dbname(market_from, market_to)
self.mysql_client = CMySQL(dbinfo, dbname)
if not self.mysql_client.create_db(dbname): raise Exception("init hkex crawler database failed")
#self.mysql_client.delete(self.get_topten_table(dbname))
if not self.create_topten_table(dbname): raise Exception("init hkex crawler topten table failed")
#self.mysql_client.delete(self.get_capital_table(dbname))
if not self.create_capital_table(dbname): raise Exception("init hkex crawler capital table failed failed")
class HkexCrawler(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
for (market_from, market_to) in [(ct.SH_MARKET_SYMBOL, ct.HK_MARKET_SYMBOL), (ct.HK_MARKET_SYMBOL, ct.SH_MARKET_SYMBOL), (ct.SZ_MARKET_SYMBOL, ct.HK_MARKET_SYMBOL), (ct.HK_MARKET_SYMBOL, ct.SZ_MARKET_SYMBOL)]:
dbname = self.get_dbname(market_from, market_to)
self.mysql_client = CMySQL(dbinfo, dbname)
if not self.mysql_client.create_db(dbname): raise Exception("init hkex crawler database failed")
#self.mysql_client.delete(self.get_topten_table(dbname))
if not self.create_topten_table(dbname): raise Exception("init hkex crawler topten table failed")
#self.mysql_client.delete(self.get_capital_table(dbname))
if not self.create_capital_table(dbname): raise Exception("init hkex crawler capital table failed failed")
@staticmethod
def get_dbname(market_from, market_to):
return "%s2%s" % (market_from, market_to)
@staticmethod
def get_topten_table(dbname):
return "%s_topten" % dbname
@staticmethod
def get_capital_table(dbname):
return "%s_capital_overview" % dbname
def create_topten_table(self, dbname):
table = self.get_topten_table(dbname)
sql = 'create table if not exists %s(date varchar(10) not null,\
code varchar(10) not null,\
name varchar(50),\
rank int,\
total_turnover float,\
buy_turnover float,\
sell_turnover float,\
PRIMARY KEY(date, code))' % table
return True if table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, table)
def create_capital_table(self, dbname):
table = self.get_capital_table(dbname)
sql = 'create table if not exists %s(date varchar(10) not null,\
total_turnover float,\
buy_turnover float,\
sell_turnover float,\
total_trade_count long,\
buy_trade_count long,\
sell_trade_count long,\
PRIMARY KEY(date))' % table
return True if table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, table)
def run(self):
settings = get_project_settings()
myrunner = CrawlerRunner(settings)
myrunner.crawl(HkexSpider)
d = myrunner.join()
d.addBoth(lambda _: reactor.stop())
reactor.run() #the script will block here until the crawling is finished
return True
def __init__(self, dbinfo, code):
self.code = code
self.dbname = self.get_dbname(code)
self.redis = create_redis_obj()
self.name = self.get('name')
self.data_type_dict = {9: "day"}
self.influx_client = CInflux(ct.IN_DB_INFO, self.dbname)
self.mysql_client = CMySQL(dbinfo, self.dbname)
if not self.create():
raise Exception("create stock %s table failed" % self.code)
def __init__(self, index_code, dbinfo = ct.DB_INFO, redis_host = None):
self.dbinfo = dbinfo
self.index_code = index_code
self.index_obj = CIndex(index_code, dbinfo = self.dbinfo, redis_host = redis_host)
self.db_name = self.index_obj.get_dbname(index_code)
self.logger = getLogger(__name__)
self.ris = RIndexStock(dbinfo, redis_host)
self.bull_stock_ratio_table = self.get_table_name()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(self.dbinfo, dbname = self.db_name, iredis = self.redis)
if not self.create(): raise Exception("create emotion table failed")
def __init__(self, dbinfo):
self.dbinfo = dbinfo
self.sdir = '/data/docs/blog/hellobiek.github.io/source/_posts'
self.doc = CDoc(self.sdir)
self.stock_objs = dict()
self.redis = create_redis_obj()
self.mysql_client = CMySQL(self.dbinfo, iredis=self.redis)
self.cal_client = ccalendar.CCalendar(without_init=True)
self.animating = False
self.emotion_table = ct.EMOTION_TABLE
if not self.create_emotion():
raise Exception("create emotion table failed")
def set_market(self, market_from, market_to):
self.market_from = market_from
self.market_to = market_to
self.balcklist = [
"2018-10-17", "2018-09-25", "2018-07-02", "2018-05-22",
"2018-04-02", "2018-03-30"
] if market_from in [ct.SH_MARKET_SYMBOL, ct.SZ_MARKET_SYMBOL
] else list()
self.dbname = self.get_dbname(market_from, market_to)
self.crawler = MCrawl(market_from)
self.mysql_client = CMySQL(self.dbinfo, self.dbname, iredis=self.redis)
return False if not self.mysql_client.create_db(self.dbname) else True
def __init__(self, dbinfo=ct.DB_INFO, redis_host=None):
self.dbinfo = dbinfo
self.logger = getLogger(__name__)
self.tu_client = get_tushare_client()
self.doc = CDoc()
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(dbinfo, iredis=self.redis)
self.margin_client = Margin(dbinfo=dbinfo, redis_host=redis_host)
self.rstock_client = RIndexStock(dbinfo=dbinfo, redis_host=redis_host)
self.sh_market_client = StockExchange(ct.SH_MARKET_SYMBOL)
self.sz_market_client = StockExchange(ct.SZ_MARKET_SYMBOL)
self.emotion_client = Emotion()
def __init__(self, dbinfo):
self.sdir = '/data/docs/blog/hellobiek.github.io/source/_posts'
self.doc = CDoc(self.sdir)
self.redis = create_redis_obj()
self.mysql_client = CMySQL(dbinfo)
self.cal_client = ccalendar.CCalendar(without_init=True)
self.trading_info = None
self.animating = False
self.emotion_table = ct.EMOTION_TABLE
self.industry_table = ct.INDUSTRY_TABLE
if not self.create_industry():
raise Exception("create industry table failed")
if not self.create_emotion():
raise Exception("create emotion table failed")
class MonthInvestorCrawler(object):
def __init__(self, dbinfo = ct.DB_INFO, redis_host = None):
self.dbname = self.get_dbname()
self.table = self.get_table_name()
self.redis = create_redis_obj() if redis_host is None else create_redis_obj(host = redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis = self.redis)
if not self.mysql_client.create_db(self.dbname): raise Exception("init stock database failed")
if not self.create_table(): raise Exception("init month investor table failed")
@staticmethod
def get_dbname():
return "stock"
@staticmethod
def get_table_name():
return "month_investor"
def create_table(self):
sql = 'create table if not exists %s(date varchar(10) not null,\
new_investor float,\
new_natural_person float,\
new_non_natural_person float,\
final_investor float,\
final_natural_person float,\
final_natural_a_person float,\
final_natural_b_person float,\
final_non_natural_person float,\
final_non_natural_a_person float,\
final_non_natural_b_person float,\
final_hold_investor float,\
final_a_hold_investor float,\
final_b_hold_investor float,\
trading_investor float,\
trading_a_investor float,\
trading_b_investor float,\
PRIMARY KEY(date))' % self.table
return True if self.table in self.mysql_client.get_all_tables() else self.mysql_client.create(sql, self.table)
def get_data(self, mdate):
table_name = self.get_table_name()
if date is not None:
sql = "select * from %s where date=\"%s\"" %(table_name, date)
else:
sql = "select * from %s" % table_name
return self.mysql_client.get(sql)
def get_data_in_range(self, start_date, end_date):
table_name = self.get_table_name()
sql = "select * from %s where date between \"%s\" and \"%s\"" %(table_name, start_date, end_date)
return self.mysql_client.get(sql)
class CHalted:
@trace_func(log=logger)
def __init__(self, dbinfo, table):
self.table = table
self.redis = create_redis_obj()
self.mysql_client = CMySQL(dbinfo)
self.trigger = ct.SYNC_HALTED_2_REDIS
if not self.create(): raise Exception("create chalted table failed")
if not self.init(True): raise Exception("init chalted table failed")
if not self.register():
raise Exception("create chalted trigger failed")
@trace_func(log=logger)
def create(self):
sql = 'create table if not exists %s(code varchar(6) not null, name varchar(20), stopTime varchar(20), stopDate varchar(20), showDate varchar(20), market varchar(20), stopReason varchar(100), PRIMARY KEY (code))' % self.table
return True if self.table in self.mysql_client.get_all_tables(
) else self.mysql_client.create(sql, self.table)
@trace_func(log=logger)
def register(self):
sql = "create trigger %s after insert on %s for each row set @set=gman_do_background('%s', json_object('name', NEW.name, 'code', NEW.code, 'market', NEW.market, 'stopReason', NEW.stopReason, 'showDate', NEW.showDate, 'stopDate', NEW.stopDate, 'stopTime', NEW.stopTime));" % (
self.trigger, self.table, self.trigger)
return True if self.trigger in self.mysql_client.get_all_triggers(
) else self.mysql_client.register(sql, self.trigger)
@trace_func(log=logger)
def init(self, status):
df = ts.get_halted()
if df is None: return False
if not self.mysql_client.set(df, self.table): return False
if status: return self.redis.set(ct.HALTED_INFO, _pickle.dumps(df, 2))
@trace_func(log=logger)
def is_halted(self, code_id, _date=None):
if _date is None: _date = datetime.now().strftime('%Y-%m-%d')
df = self.get(_date)
if df is None: raise Exception("get chalted list failed")
if df.empty: return False
return True if code_id in df['code'].tolist() else False
@trace_func(log=logger)
def get(self, _date=None):
df_byte = self.redis.get(ct.HALTED_INFO)
if df_byte is None: return pd.DataFrame()
df = _pickle.loads(df_byte)
if _date is None:
return df
else:
return df.loc[df.date == _date]
def __init__(self,
market_from=ct.SH_MARKET_SYMBOL,
market_to=ct.HK_MARKET_SYMBOL,
dbinfo=ct.DB_INFO,
redis_host=None):
self.market_from = market_from
self.market_to = market_to
self.balcklist = None
self.crawler = None
self.mysql_client = None
self.dbinfo = dbinfo
self.logger = getLogger(__name__)
self.dbname = self.get_dbname(market_from, market_to)
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(host=redis_host)
self.mysql_client = CMySQL(self.dbinfo, self.dbname, iredis=self.redis)
def __init__(self, dbinfo):
self.mysql_client = CMySQL(dbinfo)
self.cal_client = ccalendar.CCalendar(without_init=True)
with open(ct.USER_FILE) as f:
infos = json.load(f)
self.trader = Trader(infos[0]["account"], infos[0]["passwd_encrypted"],
infos[0]["secuids_sh"], infos[0]["secuids_sz"])
self.bnew_succeed_date = ""
def __init__(self, dbinfo, fpath = ct.USER_FILE):
self.mysql_client = CMySQL(dbinfo)
self.cal_client = ccalendar.CCalendar(without_init = True)
with open(fpath) as f: infos = json.load(f)
self.traders = list()
for info in infos:
self.traders.append(Trader(info["account"], info["passwd_encrypted"], info["secuids_sh"], info["secuids_sz"]))
self.buy_succeed_date = ""
def __init__(self, code, dbinfo, redis_host):
self.code = code
self.logger = getLogger(__name__)
self.dbname = self.get_dbname(code)
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(dbinfo,
dbname=self.dbname,
iredis=self.redis)
class StockLimitCrawler(object):
def __init__(self, dbinfo=ct.DB_INFO, redis_host=None):
self.dbname = self.get_dbname()
self.table_name = self.get_tablename()
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(host=redis_host)
self.mysql_client = CMySQL(dbinfo, self.dbname, iredis=self.redis)
if not self.mysql_client.create_db(self.dbname):
raise Exception("create stock limit database failed")
if not self.create_table(self.table_name):
raise Exception("create stock limit table failed")
@staticmethod
def get_dbname():
return "stock"
@staticmethod
def get_tablename():
return "limitup"
def create_table(self, table):
sql = 'create table if not exists %s(date varchar(10) not null,\
code varchar(6) not null,\
price float,\
pchange float,\
prange float,\
concept varchar(200),\
fcb float,\
flb float,\
fdmoney float,\
first_time varchar(20),\
last_time varchar(20),\
open_times varchar(20),\
intensity float,\
PRIMARY KEY (date, code))' % self.table
return True if table in self.mysql_client.get_all_tables(
) else self.mysql_client.create(sql, table)
class CMysqlObj(CClass):
__slots__ = ("code", "redis", "mysql_client")
def __init__(self, code, dbname, dbinfo, redis_host):
self.code = code
self._dbname = dbname
self.redis = create_redis_obj(
) if redis_host is None else create_redis_obj(redis_host)
self.mysql_client = CMySQL(dbinfo=dbinfo,
dbname=dbname,
iredis=self.redis)
def __del__(self):
self.redis = None
self.mysql_client = None
def create_db(self, db_name):
return self.mysql_client.create_db(db_name)
def is_table_exists(self, table_name):
if self.redis.exists(self.dbname):
return table_name in set(
table.decode() for table in self.redis.smembers(self.dbname))
return False
def is_date_exists(self, table_name, cdate):
if self.redis.exists(table_name):
return cdate in set(tdate.decode()
for tdate in self.redis.smembers(table_name))
return False
def get_existed_keys_list(self, table_name):
if self.redis.exists(table_name):
return list(tdate.decode()
for tdate in self.redis.smembers(table_name))
return list()
@property
def dbname(self):
return self._dbname
@dbname.setter
def dbname(self, value):
self._dbname = value
