def q(self, cmd: str) -> pd.DataFrame: """ Execute q command on KDB+ :param cmd: The q command to execute Example: Assume the remote KDB+ has a tbl with time and price as column. Insert 100 random prices into a table :: tbl:([] time:100?.z.p; price:100?10f) Using .curve(...) you could get a slice of that table. However, if instead you want a 10 day moving average. You could always do this. :: ts.q('10 mavg tbl.price') """ with qconnection.QConnection(host=self.host(), port=self.port()) as q: res = q(cmd) return pd.DataFrame.from_records(res)
def add2kdb(data): q = qconnection.QConnection(host="139.224.9.75", port=52800, pandas=True) #q = qconnection.QConnection(host="127.0.0.1", port=5010, pandas=True) q.open() print(data) q('upsert', np.string_("account"), data) q.close()
def get_connection(self): i = random.randint(0,len(self.sessions) - 1) q = qconnection.QConnection( host = self.sessions[i]['host'], port = self.sessions[i]['port'], username = self.sessions[i]['username'], password = self.sessions[i]['password']) return q
def connect(q_host, q_port): spark = SparkSession.builder.appName("ts").getOrCreate() sqlContext = SQLContext(spark) fc = FlintContext(sqlContext) q = qconnection.QConnection(host=q_host, port=q_port, pandas=True) q.open() return q, fc, spark
def db_connect(host="localhost", port=5000): try: q = qconnection.QConnection(host="localhost", port=5000) q.open() return q except Exception as err: print "Database connection failed." print err
def __init__(self, host, port): self.host = host self.port = port self.q = qconnection.QConnection(self.host, self.port, pandas=True) self.q.open() print( f"IPC version: {self.q.protocol_version}. Is connected: {self.q.is_connected()}" )
def rdb(cls, query): """Connect to rdb""" with qconnection.QConnection('kdb.genevatrading.com', 9218, pandas=True) as rdb: return rdb(query)
def kdb(cls, query): """connect to kdb""" with qconnection.QConnection('kdb.genevatrading.com', 8000, pandas=True) as kdb: return kdb(query)
def get_kdb_data(): q = qconnection.QConnection(host='kdb.genevatrading.com', port=9898, pandas=True) q.open() # initialize connection # print('IPC version: %s. Is connected: %s' % (q.protocol_version, q.is_connected())) # simple query execution via: QConnection.sendSync data = q.sendSync('select time,ISIN,bidprice0,askprice0,bidsize0,asksize0 from bookZNOPT_hist where date=2020.04.30, time within 11:00:00.000 12:01:00.000, year=2020, month in (6,7)') q.close() # close connection return data
def run(self): q = qconnection.QConnection( host = self.kdb_host,port = self.kdb_port,username = self.kdb_username, password = self.kdb_password) self.prepare_timers() self.read_secdefs(q) self.handler.on_start_of_day(self) self.read_ticks(q) self.process_ticks() self.handler.on_end_of_day(self)
def __init__(self, host='127.0.0.1', key=None, numeric_type=float, **kwargs): q = qconnection.QConnection(host='localhost', port=5555, pandas=True) q.open() self.key = key if key else self.default_key self.numeric_type = numeric_type
def load_from_kdb(config, read_name): q = qp.QConnection(config['kdb_load']['host'], config['kdb_load']['port'], config['kdb_load']['username'], config['kdb_load']['password']) q.open() query = 'get `:' + config['place_load']['kdb'] + read_name data = q.sync(query) data = pd.DataFrame(data) q.close() return data
def connect(self): host = self.config('HOST') port = self.config('PORT') timeout = self.config('TIMEOUT') q = qconnection.QConnection(host, port, timeout=timeout) try: q.open() except QConnectionException: raise RuntimeError("KDB connection cannot be establish! {}:{}".format(self._host, self._port)) return q
def rdb(cls, query=None): """Connect to rdb""" rdb = qconnection.QConnection('kdb.genevatrading.com', 9218, pandas=True) rdb.open() if query is None: return rdb return rdb(query)
def kdb(cls, query=None): """connect to kdb""" kdb = qconnection.QConnection('kdb.genevatrading.com', 8000, pandas=True) kdb.open() if query is None: return kdb return kdb(query)
def ts(): with qconnection.QConnection(host='localhost', port=5001) as q: for cmd in COMMANDS: try: q(cmd) except QException as msg: print('q error: \'%s' % msg) db = kydb.connect('memory://kdb') db.upload_objdb_config(OBJDB_CONFIG) ts = db.new('KDBTimeSeries', '/Bloomerg/VOD.L') return ts
def connect_to_kdbdb(host="localhost",port="10000"): # Create log handler log_handler = logger.init_logger() # Connection to database and exception handling try: q = qconnection.QConnection(host="localhost", port=10000) q.open() return q except qconnection.QConnectionException as err: log_handler.error(err) except: log_handler.error("Unable to connect KDB")
def update(config, update_kdb, update_csv, last_date_record, name, new_data, reset_index=True, set_index=False, index_name='trade_date'): if update_kdb: data_kdb_save = new_data[ new_data.index > last_date_record['KDB'][name]] if data_kdb_save.shape[0] > 0: if (config['runD']): print( dt.datetime.now(), '开始更新KDB数据:,过去文件最新日期: ' + str(last_date_record['KDB'][name]) + '; 更新开始日期: ' + str(data_kdb_save.index.min())) if (reset_index): data_kdb_save = data_kdb_save.reset_index() q = qp.QConnection(config['kdb_save']['host'], config['kdb_save']['port'], config['kdb_save']['username'], config['kdb_save']['password']) q.open() query = name + ': get`:' + config['place_save']['kdb'] + name + ';' q.sync(query) q('set', np.string_('table_new'), data_kdb_save) query = name + ':' + name + ' upsert table_new;' query += 'save `$("" sv ("' + config['place_save'][ 'kdb'] + '";"' + name + '"))' q.sync(query) q.close() else: pass if update_csv: data_csv_save = new_data[ new_data.index > last_date_record['CSV'][name]] if data_csv_save.shape[0] > 0: if (config['runD']): print( dt.datetime.now(), '开始更新CSV数据:,过去文件最新日期: ' + str(last_date_record['CSV'][name]) + '; 更新开始日期: ' + str(data_csv_save.index.min())) data = pd.read_csv(config['place_load']['csv'] + name + '.csv') data = data.set_index(index_name) data = pd.concat([data, data_csv_save], axis=0, ignore_index=False) data.to_csv(config['place_save']['csv'] + name + '.csv') else: pass
def _queryProc(self, isSync, *args, **kwargs): # "Private" function try: with qconnection.QConnection( host="localhost" if self.host is None else self.host, port=self.port, username=self.username, password=self.password, ) as q: res = (q.sendSync(*args, **kwargs) if isSync else q.sendAsync( *args, **kwargs)) except Exception as e: print(f"Exception querying kdb process: {e}") res = None return res
def read_from_db(path, date_str): q = qconnection.QConnection(host='127.0.0.1', port=2070, pandas=True) q.open() q('\l /home/azureuser/ifs/data/2019/12/30/levels') symb = q('select distinct sym from levels') symbols = '`'.join([x.decode() for x in symb['sym']]) symbol_list = [x.decode() for x in symb['sym']] Date = date_str q("\l " + path + date_str) q('meta levels') df = q('select from levels where sym in `' + symbols) q.close() return df, symbol_list
def sym_vols(dt, host='10.200.101.92', port=9057, username='******', password='******'): q = qconnection.QConnection(host, port, username, password) aa = [] q.open() for tbl in RAW_KDB_TABLES: qstr = 'select sum(size) by sym from %s where date=%s, entryType="2"' % ( tbl, dt.strftime('%Y.%m.%d')) res = q(qstr) for item in res.iteritems(): aa.append((item[0][0], item[1][0], tbl)) q.close() return sorted(aa)
def connect(self, **kwargs): """ Connect :param path: sqlite file to connect """ host = kwargs['host'] port = kwargs['port'] Logger.info(self.__class__.__name__, 'Kdb+ database client is connecting to %s:%d' % (host, port)) self.conn = qconnection.QConnection(host=host, port=port) self.conn.open() if self.conn.is_connected(): Logger.info(self.__class__.__name__, 'Connection to %s:%d is successful.' % (host, port)) else: Logger.info(self.__class__.__name__, 'Connection to %s:%d is failed.' % (host, port)) return self.conn.is_connected()
def __init__(self, date_beg, date_end, **kwargs): self.__date_beg = str(date_beg) self.__date_end = str(date_end) self.__hostname = kwargs["hostname"] self.__portnum = kwargs["portnum"] self.__username = kwargs["username"] self.__password = kwargs["password"] self.__database = kwargs["database"] self.__trade = kwargs["trade"] self.__q = qconn.QConnection(host=self.__hostname, port=self.__portnum, \ username=self.__username, password=self.__password) self.connect()
def __init__(self, **kwargs): self.__hostname = kwargs["hostname"] self.__portnum = kwargs["portnum"] self.__username = kwargs["username"] self.__password = kwargs["password"] self.__nbbo_database = kwargs["nbbo_database"] self.__nbbo_trades = kwargs["nbbo_trades"] self.__nbbo_quotes = kwargs["nbbo_quotes"] self.__itch_database = kwargs["itch_database"] self.__itch_trade = kwargs["itch_trade"] self.__q = qconn.QConnection(host=self.__hostname, port=self.__portnum, \ username=self.__username, password=self.__password, \ numpy_temporals=True) self.connect()
def query(query_cmd): try: logger.info("Getting data with query: {0}".format(query_cmd)) q = qconnection.QConnection(host=QConnection.host, port=QConnection.port) q.open() data = q.sync(query_cmd) q.close() data = df(data) if 't1' in data: data['t1'] = [x.decode('utf-8') for x in data['t1']] data['t2'] = [x.decode('utf-8') for x in data['t2']] if 'ticker' in data: data['ticker'] = [x.decode('utf-8') for x in data['ticker']] return data except: logger.error("Failed in getting data")
def __init__(self, date_beg, date_end, sym, venue, fwd_ticks, **kwargs): self.__date_beg = date_beg self.__date_end = date_end self.__sym = sym self.__venue = venue self.__fwd_ticks = fwd_ticks self.__hostname = kwargs["hostname"] self.__portnum = kwargs["portnum"] self.__username = kwargs["username"] self.__password = kwargs["password"] self.__q = qconn.QConnection(host=self.__hostname, port=self.__portnum, \ username=self.__username, password=self.__password, \ numpy_temporals=True) self.connect()
def get_OHLCV_by_kdb(config_data_query, code=[], frequency='', asset='', start_date='', end_date='', trade_date=''): file_name = 'OHLCV_' + frequency + '_' + asset code = ''.join(list(map(lambda x: '`' + x, code))) if start_date != '': start_date = pd.to_datetime(start_date).strftime('%Y.%m.%d') if end_date != '': end_date = pd.to_datetime(end_date).strftime('%Y.%m.%d') if trade_date != '': trade_date = pd.to_datetime(trade_date).strftime('%Y.%m.%d') q = qp.QConnection(config_data_query['kdb_load']['host'], config_data_query['kdb_load']['port'], config_data_query['kdb_load']['username'], config_data_query['kdb_load']['password']) q.open() if len(code) != 0: if (start_date == '') & (end_date == ''): if trade_date == '': query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where ts_code in ' + code else: query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where ts_code in ' + code+',trade_date in '+trade_date elif (start_date != '') & (end_date == ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where ts_code in ' + code + ', trade_date >= ' + start_date elif (start_date == '') & (end_date != ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where ts_code in ' + code + ', trade_date <= ' + end_date elif (start_date != '') & (end_date != ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where ts_code in ' + code + ', trade_date >= ' + start_date + ', trade_date <= ' + end_date else: if (start_date == '') & (end_date == ''): if trade_date == '': query = '0!data:select from (get`:Local/OHLCV_D_E)' else: query = '0!data:select from (get`:Local/OHLCV_D_E) where trade_date in '+trade_date elif (start_date != '') & (end_date == ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where trade_date >= ' + start_date elif (start_date == '') & (end_date != ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where trade_date <= ' + end_date elif (start_date != '') & (end_date != ''): query = '0!data:select from (get`:' + config_data_query['place_load']['kdb'] + file_name + ') where trade_date >= ' + start_date + ', trade_date <= ' + end_date data = pd.DataFrame(q.sync(query)) q.close() data['trade_date'] += 946684800000000000 data['trade_date'] = pd.to_datetime(data['trade_date']) data = data.set_index('trade_date') data = data.dropna() return data
def save_as_kdb(config, output_name, data, reset_index=True, set_index=True, index_name='utc_datetime'): if (reset_index): data = data.reset_index() q = qp.QConnection(config['kdb_save']['host'], config['kdb_save']['port'], config['kdb_save']['username'], config['kdb_save']['password']) q.open() q('set', np.string_(output_name), data) query = 'save `$("" sv ("' + config['place_save'][ 'kdb'] + '";"' + output_name + '"))' q.sync(query) q.close() if (set_index): data = data.set_index(index_name) else: pass
def tryConnect(self, args): try: host, port = args.qinstance.split(':') if args.qinstance else ( None, None) if not port: port = self.spawnQProcess(args) if not host: host = self.localhost q = qconnection.QConnection(host=host, port=int(port), pandas=True) q.open() except Exception as ex: self.subprocessTerminate(self.proc) logger.warn( 'Warning unable to connect to q; will try again\n%r' % ex) q = None return q
def sec_def(dt, symbol, host='10.200.101.92', port=9057, username='******', password='******'): q = qconnection.QConnection(host, port, username, password) q.open() sym = '`$("%s")' % symbol secdef = {} for tbl in MASTER_KDB_TABLES: qstr = '{[a] select from %s where date=%s, sym in ((),a) } [%s]' % ( tbl, dt.strftime('%Y.%m.%d'), sym) res = q(qstr) if len(res) == 0: continue for i in xrange(len(res.dtype.names)): secdef[res.dtype.names[i]] = res[-1][i] q.close() return secdef