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
