예제 #1
0
    def create(self, table, columns, types, is_ifnotexists=True):
        """
        Create table in the database
        :param table: Table name
        :param columns: Column array
        :param types: Type array
        :param is_ifnotexists: Create table if not exists keyword
        """
        file_path = os.path.join(self.file_directory, table + ".csv")
        columns = [e.split(' ')[0] for e in columns]

        if len(columns) != len(types):
            return False

        self.lock.acquire()

        if os.path.isfile(file_path):
            Logger.info(self.__class__.__name__,
                        "File (%s) has been created already." % file_path)
        else:
            with open(file_path, 'w+') as csvfile:
                csvfile.write(','.join(["\"" + e + "\""
                                        for e in columns]) + '\n')

        self.lock.release()
        return True
예제 #2
0
    def on_open_handler(self, instmt, ws):
        """
        Socket on open handler
        :param instmt: Instrument
        :param ws: Web socket
        """
        Logger.info(self.__class__.__name__, "Instrument %s is subscribed in channel %s" % \
                  (instmt.get_instmt_code(), instmt.get_exchange_name()))
        if not instmt.get_subscribed():
            instmt_code_split = instmt.get_instmt_code().split('_')
            if len(instmt_code_split) == 3:
                # Future instruments
                instmt.set_order_book_channel_id("ok_sub_%s_%s_depth_%s_20" % \
                                                 (instmt_code_split[0],
                                                  instmt_code_split[1],
                                                  instmt_code_split[2]))
                instmt.set_trades_channel_id("ok_sub_%s_%s_trade_%s" % \
                                               (instmt_code_split[0],
                                                instmt_code_split[1],
                                                instmt_code_split[2]))
            else:
                # Spot instruments
                instmt.set_order_book_channel_id("ok_sub_%s_depth_20" %
                                                 instmt.get_instmt_code())
                instmt.set_trades_channel_id("ok_sub_%s_trades" %
                                             instmt.get_instmt_code())

            ws.send(self.api_socket.get_order_book_subscription_string(instmt))
            ws.send(self.api_socket.get_trades_subscription_string(instmt))
            instmt.set_subscribed(True)
예제 #3
0
    def insert(self, table, columns, types, values, primary_key_index=[], is_orreplace=False, is_commit=True):
        """
        Insert into the table
        :param table: Table name
        :param columns: Column array
        :param types: Type array
        :param values: Value array
        :param primary_key_index: An array of indices of primary keys in columns,
                          e.g. [0] means the first column is the primary key
        :param is_orreplace: Indicate if the query is "INSERT OR REPLACE"
        """
        if len(columns) != len(values):
            return False

        column_names = ','.join(columns)
        value_string = ','.join([SqlStorage.convert_str(e) for e in values])
        
        if is_orreplace:
            sql = "%s %s (%s) values (%s)" % (self.replace_keyword(), table, column_names, value_string)
        else:
            sql = "insert into %s (%s) values (%s)" % (table, column_names, value_string)

        self.lock.acquire()
        
        try:
            self.execute(sql)
            if is_commit:
                self.commit()
        except Exception as e:
            Logger.info(self.__class__.__name__, "SQL error: %s\nSQL: %s" % (e, sql))
        
        self.lock.release()
        return True
예제 #4
0
 def on_close_handler(self, instmt, ws):
     """
     Socket on close handler
     :param instmt: Instrument
     :param ws: Web socket
     """
     Logger.info(self.__class__.__name__, "Instrument %s is unsubscribed in channel %s" % \
               (instmt.get_instmt_code(), instmt.get_exchange_name()))
     instmt.set_subscribed(False)
예제 #5
0
 def connect(self, **kwargs):
     """
     Connect
     :param path: sqlite file to connect
     """
     addr = kwargs['addr']
     Logger.info(self.__class__.__name__,
                 'Zmq storage is connecting to %s' % addr)
     self.conn.bind(addr)
     return self.conn is not None
예제 #6
0
 def on_open_handler(self, instmt, ws):
     """
     Socket on open handler
     :param instmt: Instrument
     :param ws: Web socket
     """
     Logger.info(self.__class__.__name__, "Instrument %s is subscribed in channel %s" % \
               (instmt.get_instmt_code(), instmt.get_exchange_name()))
     if not instmt.get_subscribed():
         ws.send(self.api_socket.get_trades_subscription_string(instmt))
         instmt.set_subscribed(True)
예제 #7
0
 def on_message_handler(self, instmt, message):
     """
     Incoming message handler
     :param instmt: Instrument
     :param message: Message
     """
     keys = message.keys()
     if 'info' in keys:
         Logger.info(self.__class__.__name__, message['info'])
     elif 'subscribe' in keys:
         Logger.info(self.__class__.__name__, 'Subscription of %s is %s' % \
                     (message['request']['args'], \
                      'successful' if message['success'] else 'failed'))
     elif 'table' in keys:
         if message['table'] == 'trade':
             for trade_raw in message['data']:
                 if trade_raw["symbol"] == instmt.get_instmt_code():
                     # Filter out the initial subscriptions
                     trade = self.api_socket.parse_trade(instmt, trade_raw)
                     if trade.trade_id != instmt.get_exch_trade_id():
                         instmt.incr_trade_id()
                         instmt.set_exch_trade_id(trade.trade_id)
                         self.insert_trade(instmt, trade)
         elif message['table'] == 'orderBookL2':
             l2_depth = self.api_socket.parse_l2_depth(instmt, message)
             if l2_depth is not None and l2_depth.is_diff(instmt.get_l2_depth()):
                 instmt.set_prev_l2_depth(instmt.get_l2_depth())
                 instmt.set_l2_depth(l2_depth)
                 instmt.incr_order_book_id()
                 self.insert_order_book(instmt)
         else:
             Logger.info(self.__class__.__name__, json.dumps(message,indent=2))
     else:
         Logger.error(self.__class__.__name__, "Unrecognised message:\n" + json.dumps(message))
예제 #8
0
    def on_message_handler(self, instmt, messages):
        """
        Incoming message handler
        :param instmt: Instrument
        :param message: Message
        """
        for message in messages:
            keys = message.keys()
            if 'channel' in keys:
                if 'data' in keys:
                    if message['channel'] == instmt.get_order_book_channel_id(
                    ):
                        data = message['data']
                        instmt.set_prev_l2_depth(instmt.get_l2_depth().copy())
                        self.api_socket.parse_l2_depth(instmt, data)

                        # Insert only if the first 5 levels are different
                        if instmt.get_l2_depth().is_diff(
                                instmt.get_prev_l2_depth()):
                            instmt.incr_order_book_id()
                            self.insert_order_book(instmt)

                    elif message['channel'] == instmt.get_trades_channel_id():
                        for trade_raw in message['data']:
                            trade = self.api_socket.parse_trade(
                                instmt, trade_raw)
                            if trade.trade_id != instmt.get_exch_trade_id():
                                instmt.incr_trade_id()
                                instmt.set_exch_trade_id(trade.trade_id)
                                self.insert_trade(instmt, trade)

                elif 'success' in keys:
                    Logger.info(self.__class__.__name__, "Subscription to channel %s is %s" \
                        % (message['channel'], message['success']))
            else:
                Logger.info(self.__class__.__name__,
                            ' - ' + json.dumps(message))
예제 #9
0
    def connect(self, **kwargs):
        """
        Connect
        :param path: sqlite file to connect
        """
        host = kwargs['host']
        port = kwargs['port']
        Logger.info(
            self.__class__.__name__,
            'Kdb+ database storage 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()
예제 #10
0
def main():
    parser = argparse.ArgumentParser(
        description='Bitcoin exchange market data feed handler.')
    parser.add_argument('-instmts',
                        action='store',
                        help='Instrument subscription file.',
                        default='subscriptions.ini')
    parser.add_argument('-exchtime',
                        action='store_true',
                        help='Use exchange timestamp.')
    parser.add_argument('-kdb',
                        action='store_true',
                        help='Use Kdb+ as database.')
    parser.add_argument('-csv',
                        action='store_true',
                        help='Use csv file as database.')
    parser.add_argument('-sqlite',
                        action='store_true',
                        help='Use SQLite database.')
    parser.add_argument('-mysql', action='store_true', help='Use MySQL.')
    parser.add_argument('-zmq', action='store_true', help='Use zmq publisher.')
    parser.add_argument(
        '-mysqldest',
        action='store',
        dest='mysqldest',
        help='MySQL destination. Formatted as <name:pwd@host:port>',
        default='')
    parser.add_argument('-mysqlschema',
                        action='store',
                        dest='mysqlschema',
                        help='MySQL schema.',
                        default='')
    parser.add_argument('-kdbdest',
                        action='store',
                        dest='kdbdest',
                        help='Kdb+ destination. Formatted as <host:port>',
                        default='')
    parser.add_argument(
        '-zmqdest',
        action='store',
        dest='zmqdest',
        help='Zmq destination. For example \"tcp://127.0.0.1:3306\"',
        default='')
    parser.add_argument('-sqlitepath',
                        action='store',
                        dest='sqlitepath',
                        help='SQLite database path',
                        default='')
    parser.add_argument('-csvpath',
                        action='store',
                        dest='csvpath',
                        help='Csv file path',
                        default='')
    parser.add_argument('-output',
                        action='store',
                        dest='output',
                        help='Verbose output file path')
    args = parser.parse_args()

    Logger.init_log(args.output)

    storages = []
    is_database_defined = False

    if args.sqlite:
        storage = SqliteStorage()
        storage.connect(path=args.sqlitepath)
        storages.append(storage)
        is_database_defined = True

    if args.mysql:
        storage = MysqlStorage()
        mysqldest = args.mysqldest
        logon_credential = mysqldest.split('@')[0]
        connection = mysqldest.split('@')[1]

        storage.connect(host=connection.split(':')[0],
                        port=int(connection.split(':')[1]),
                        user=logon_credential.split(':')[0],
                        pwd=logon_credential.split(':')[1],
                        schema=args.mysqlschema)

        storages.append(storage)
        is_database_defined = True

    if args.csv:
        if args.csvpath != '':
            storage = FileStorage(dir=args.csvpath)
        else:
            storage = FileStorage()

        storages.append(storage)
        is_database_defined = True

    if args.kdb:
        storage = KdbPlusStorage()
        storage.connect(host=args.kdbdest.split(':')[0],
                        port=int(args.kdbdest.split(':')[1]))
        storages.append(storage)
        is_database_defined = True

    if args.zmq:
        storage = ZmqStorage()
        storage.connect(addr=args.zmqdest)
        storages.append(storage)
        is_database_defined = True

    if not is_database_defined:
        print('Error: Please define which database is used.')
        parser.print_help()
        sys.exit(1)

    # Subscription instruments
    if args.instmts is None or len(args.instmts) == 0:
        print(
            'Error: Please define the instrument subscription list. You can refer to subscriptions.ini.'
        )
        parser.print_help()
        sys.exit(1)

    # Use exchange timestamp rather than local timestamp
    if args.exchtime:
        ExchangeGateway.is_local_timestamp = False

    # Initialize subscriptions
    subscription_instmts = SubscriptionManager(
        args.instmts).get_subscriptions()

    if len(subscription_instmts) == 0:
        print(
            'Error: No instrument is found in the subscription file. ' +
            'Please check the file path and the content of the subscription file.'
        )

        parser.print_help()
        sys.exit(1)

    # Initialize snapshot destination
    ExchangeGateway.init_snapshot_table(storages)
    Logger.info(__name__, 'Subscription file = %s' % args.instmts)
    log_str = 'Exchange/Instrument/InstrumentCode:\n'

    for instmt in subscription_instmts:
        log_str += '%s/%s/%s\n' % (instmt.exchange_name, instmt.instmt_name,
                                   instmt.instmt_code)

    Logger.info(__name__, log_str)

    gateways = []
    # gateways.append(ExchGwBtccSpot(storages))
    # gateways.append(ExchGwBtccFuture(storages))
    # gateways.append(ExchGwBitmex(storages))
    # gateways.append(ExchGwBitfinex(storages))
    # gateways.append(ExchGwOkCoin(storages))
    # gateways.append(ExchGwKraken(storages))
    # gateways.append(ExchGwGdax(storages))
    gateways.append(BitstampGateway(storages))
    # gateways.append(ExchGwGatecoin(storages))
    # gateways.append(ExchGwQuoine(storages))
    # gateways.append(ExchGwPoloniex(storages))
    # gateways.append(ExchGwBittrex(storages))
    # gateways.append(ExchGwYunbi(storages))
    # gateways.append(ExchGwLiqui(storages))
    gateways.append(BinanceGateway(storages))
    # gateways.append(ExchGwCryptopia(storages))

    threads = []

    for item in gateways:
        for instmt in subscription_instmts:
            if instmt.get_exchange_name() == item.get_exchange_name():
                Logger.info(__name__, "Starting instrument %s-%s..." % \
                    (instmt.get_exchange_name(), instmt.get_instmt_name()))
                threads += item.start(instmt)
예제 #11
0
    def parse_l2_depth(cls, instmt, raw):
        """
        Parse raw data to L2 depth
        :param instmt: Instrument
        :param raw: Raw data in JSON
        """
        # No order book mapping from config. Need to decode here.
        l2_depth = instmt.get_l2_depth()
        l2_depth.date_time = datetime.utcnow().strftime("%Y%m%d %H:%M:%S.%f")
        if isinstance(raw[0], list):
            # Start subscription
            for i in range(0, 25):
                bid = raw[i]
                ask = raw[25 + i]

                l2_depth.bids[i] = L2Depth.Depth(price=bid[0],
                                                 count=bid[1],
                                                 volume=bid[2])
                l2_depth.asks[i] = L2Depth.Depth(price=ask[0],
                                                 count=ask[1],
                                                 volume=-ask[2])

        else:
            price = raw[1]
            count = raw[2]
            volume = raw[3]
            found = False

            if count == 0:
                # Deletion
                if volume > 0:
                    for i in range(0, len(l2_depth.bids)):
                        if price == l2_depth.bids[i].price:
                            found = True
                            del l2_depth.bids[i]
                            break
                else:
                    for i in range(0, len(l2_depth.asks)):
                        if price == l2_depth.asks[i].price:
                            found = True
                            del l2_depth.asks[i]
                            break

                if not found:
                    depth_text = ""
                    for i in range(0, l2_depth.depth):
                        if i < len(l2_depth.bids):
                            depth_text += "%.4f,%d,%.4f" % \
                              (l2_depth.bids[i].volume, \
                               l2_depth.bids[i].count, \
                               l2_depth.bids[i].price)
                        else:
                            depth_text += "                   "
                        depth_text += "<--->"
                        if i < len(l2_depth.asks):
                            depth_text += "%.4f,%d,%.4f" % \
                                          (l2_depth.asks[i].volume, \
                                           l2_depth.asks[i].count, \
                                           l2_depth.asks[i].price)
                        else:
                            depth_text += "                   "
                        depth_text += "\n"
                    Logger.info(cls.__name__, "Cannot find the deletion of the message: %s\nDepth:\n%s\n" % \
                              (raw, depth_text))
            else:
                # Insertion/Update
                if volume > 0:
                    # Update
                    for i in range(0, len(l2_depth.bids)):
                        if price == l2_depth.bids[i].price:
                            l2_depth.bids[i].count = count
                            l2_depth.bids[i].volume = volume
                            found = True
                            break

                    if not found:
                        # Insertion
                        l2_depth.bids.append(
                            L2Depth.Depth(price=price,
                                          count=count,
                                          volume=volume))
                        l2_depth.sort_bids()

                        if len(l2_depth.bids) > l2_depth.depth * 2:
                            del l2_depth.bids[l2_depth.depth:]
                else:
                    for i in range(0, len(l2_depth.asks)):
                        # Update
                        if price == l2_depth.asks[i].price:
                            l2_depth.asks[i].count = count
                            l2_depth.asks[i].volume = -volume
                            found = True
                            break

                    if not found:
                        # Insertion
                        l2_depth.asks.append(
                            L2Depth.Depth(price=price,
                                          count=count,
                                          volume=-volume))
                        l2_depth.sort_asks()

                        if len(l2_depth.asks) > l2_depth.depth * 2:
                            del l2_depth.asks[l2_depth.depth:]

        return l2_depth
예제 #12
0
    def on_message_handler(self, instmt, message):
        """
        Incoming message handler
        :param instmt: Instrument
        :param message: Message
        """
        if isinstance(message, dict):
            keys = message.keys()
            if 'event' in keys and message[
                    'event'] == 'info' and 'version' in keys:
                Logger.info(self.__class__.__name__,
                            "Bitfinex version: %s" % message['version'])
            elif 'event' in keys and message['event'] == 'subscribed':
                if instmt.get_instmt_code() == message['pair']:
                    if message['channel'] == 'book':
                        instmt.set_order_book_channel_id(message['chanId'])
                    elif message['channel'] == 'trades':
                        instmt.set_trades_channel_id(message['chanId'])
                    else:
                        raise Exception("Unknown channel %s : <%s>" %
                                        (message['channel'], message))

                    Logger.info(self.__class__.__name__, 'Subscription: %s, pair: %s, channel Id: %s' % \
                              (message['channel'], instmt.get_instmt_code(), message['chanId']))
        elif isinstance(message, list):
            if message[0] == instmt.get_order_book_channel_id():
                if isinstance(message[1], list):
                    self.api_socket.parse_l2_depth(instmt, message[1])
                elif len(message) != 2:
                    instmt.set_prev_l2_depth(instmt.get_l2_depth().copy())
                    self.api_socket.parse_l2_depth(instmt, message)
                else:
                    return

                if instmt.get_l2_depth().is_diff(instmt.get_prev_l2_depth()):
                    instmt.incr_order_book_id()
                    self.insert_order_book(instmt)

            elif message[0] == instmt.get_trades_channel_id():
                # No recovery trade

                # if isinstance(message[1], list):
                #     raw_trades = message[1]
                #     raw_trades.sort(key=lambda x:x[0])
                #     for raw in raw_trades:
                #         trade = self.api_socket.parse_trade(instmt, raw)
                #         try:
                #             if int(trade.trade_id) > int(instmt.get_exch_trade_id()):
                #                 instmt.incr_trade_id()
                #                 instmt.set_exch_trade_id(trade.trade_id)
                #                 self.insert_trade(instmt, trade)
                #         except Exception as e:
                #             Logger.info('test', "trade.trade_id(%s):%s" % (type(trade.trade_id), trade.trade_id))
                #             Logger.info('test', "instmt.get_exch_trade_id()(%s):%s" % (type(instmt.get_exch_trade_id()), instmt.get_exch_trade_id()))
                #             raise e

                if message[1] == 'tu':
                    trade = self.api_socket.parse_trade(instmt, message[3:])
                    if int(trade.trade_id) > int(instmt.get_exch_trade_id()):
                        instmt.incr_trade_id()
                        instmt.set_exch_trade_id(trade.trade_id)
                        self.insert_trade(instmt, trade)
예제 #13
0
    def create(self,
               table,
               columns,
               types,
               primary_key_index=[],
               is_ifnotexists=True):
        """
        Create table in the database.
        Caveat - Assign the first few column as the keys!!!
        :param table: Table name
        :param columns: Column array
        :param types: Type array
        :param is_ifnotexists: Create table if not exists keyword
        """
        if len(columns) != len(types):
            raise Exception("Incorrect create statement. Number of columns and that of types are different.\n%s\n%s" % \
                (columns, types))

        if is_ifnotexists:
            ret = self.conn("\\v")
            if ret is not None:
                for t in ret:
                    if table == self.decode_qtypes(t):
                        Logger.info(self.__class__.__name__,
                                    "Table %s has been created." % table)
                        return True

            Logger.info(self.__class__.__name__,
                        "Table %s is going to be created." % table)

        c = columns[:]

        for i in range(0, len(types)):
            t = self.convert_type(types[i])
            if t is str:
                if columns[i].find('date_time') > -1:
                    c[i] += ":`timestamp$()"
                else:
                    c[i] += ":`symbol$()"
            elif t is float:
                c[i] += ":`float$()"
            elif t is int:
                c[i] += ":`long$()"

        keys = []
        for i in primary_key_index:
            keys.append(c[i])

        for i in sorted(primary_key_index, reverse=True):
            del c[i]

        if len(keys) > 0:
            command = '%s:([%s] %s)' % (table, '; '.join(keys), '; '.join(c))
        else:
            command = '%s:(%s)' % (table, '; '.join(c))

        self.lock.acquire()
        try:
            self.conn.sync(command)
        except Exception as e:
            Logger.error(self.__class__.__name__,
                         "Error in creat statement(%s).\n%s" % (command, e))
        finally:
            self.lock.release()

        return True
예제 #14
0
        :param table: Table name
        :param condition: Where condition
        """
        if condition == '1==1':
            statement = 'delete from `%s' % (table)
        else:
            statement = 'delete from `%s where %s' % (
                table, self.parse_condition(condition))
        self.conn.sync(statement)
        return True


if __name__ == '__main__':
    Logger.init_log()
    db_storage = KdbPlusClient()
    db_storage.connect(host='localhost', port=5000)
    db_storage.create('test', ['c1', 'c2', 'c3', 'c4'],
                      ['varchar(20)', 'int', 'decimal(8, 20)', 'int'], [0],
                      False)
    db_storage.insert('test', ['c1', 'c2', 'c3', 'c4'], ['abc', 1, 1.1, 5])
    db_storage.insert('test', ['c1', 'c2', 'c3', 'c4'], ['efg', 2, 2.2, 6])
    db_storage.insert('test', ['c1', 'c2', 'c3', 'c4'], ['hij', 3, 3.3, 7])
    # Logger.info('test', db_storage.select('test', columns=['*']))
    Logger.info(
        'test',
        db_storage.select('test',
                          columns=['c2', 'c3'],
                          condition='c1 >= "abc" and c2 > 1'))
    # Logger.info('test', db_storage.select('test', columns=['*'], orderby='c1 desc', limit=1))
    db_storage.delete('test', 'c1="abc"')