goxapi queueing mysql commits - python

"""
trading robot breadboard
"""
import time
import goxapi
import MySQLdb
import decimal
import threading
import Queue

cPrec = 1E5
bPrec = 1E8

class Strategy(goxapi.BaseObject):
    # pylint: disable=C0111,W0613,R0201

    def __init__(self, gox):
        goxapi.BaseObject.__init__(self)
        self.signal_debug.connect(gox.signal_debug)
        gox.signal_keypress.connect(self.slot_keypress)
        gox.signal_strategy_unload.connect(self.slot_before_unload)
        gox.signal_ticker.connect(self.slot_tick)
        gox.signal_depth.connect(self.slot_depth)
        gox.signal_trade.connect(self.slot_trade)
        gox.signal_userorder.connect(self.slot_userorder)
        gox.signal_wallet.connect(self.slot_wallet)
        gox.orderbook.signal_changed.connect(self.slot_obook)
        self.gox = gox
        self.name = "%s.%s" % (__name__, self.__class__.__name__)
        self.debug("%s loaded" % self.name)
        import MySQLdb
        self.conn = MySQLdb.connect(host= "127.0.0.1",
        user="genBTC",
        passwd="genBTC",
        db="bitcoins")
        self.commit_queue = Queue.Queue()
        self._terminate_commits = False
        self._commit_thread = self.start_thread(self.do_commit_queue)
        self.obook_lastcommit = 0

    def __del__(self):
        self.debug("%s unloaded" % self.name)

    def start_thread(self, thread_func):
        """start a new thread to execute the supplied function"""
        thread = threading.Thread(target=thread_func)
        thread.daemon = True
        thread.start()
        return thread

    def slot_wallet(self, gox, _dummy):
        currencies = ['EUR','GBP','USD','AUD','JPY','RUB','PLN','BTC']
        for cur in currencies:
            cdec = cPrec
            if cur == 'BTC':
                cdec = bPrec
            self.commit_queue.put(("UPDATE market_data SET WalletBal=%s WHERE Item_Name=%s",((float(gox.wallet[cur])/cdec),repr(cur))))

    def slot_obook(self, orderbook, _dummy):
        if time.time() - self.obook_lastcommit > 2:
            for y in range(0,10):
                bidprice = float(self.gox.orderbook.bids[y].price)/cPrec
                bidvol = float(self.gox.orderbook.bids[y].volume)/bPrec
                self.commit_queue.put(("INSERT INTO depth_eur (price, volume, type, RecordTime) VALUES (%s,%s,'bids',%s)",(bidprice, bidvol, now)))
            for y in range(0,10):
                askprice = float(self.gox.orderbook.asks[y].price)/cPrec
                askvol = float(self.gox.orderbook.asks[y].volume)/bPrec
                self.commit_queue.put(("INSERT INTO depth_eur (price, volume, type, RecordTime) VALUES (%s,%s,'asks',%s)",(askprice, askvol, now)))
            self.commit_queue.put(("DELETE FROM depth_eur WHERE RecordTime != (SELECT x.RT FROM (SELECT MAX(t.RecordTime) AS RT FROM depth_eur t) x)",()))
            self.obook_lastcommit = time.time()
        else:
            return

    def do_commit_queue(self):
        """send queued requests to the DB"""
        while self._terminate_commits == False:
            success = False
            message,allvalues = self.commit_queue.get(True)
            while success == False:
                try:
                    if allvalues == ():
                        self.conn.cursor().execute(message)
                    else:
                        self.conn.cursor().execute(message,allvalues)
                    success = True
                except (AttributeError, MySQLdb.OperationalError):
                    self.debug("### Error,failure.Reconnecting")
                    self.conn = MySQLdb.connect(host= "127.0.0.1", user="genBTC", passwd="genBTC", db="bitcoins")
            self.commit_queue.task_done()

    def slot_before_unload(self, _sender, _data):
        self.debug("%s before unload" % self.name)
        self.conn.close()

    def slot_keypress(self, gox, (key)):
        self.debug("someone pressed the %s key" % chr(key))

    def slot_tick(self, gox, (bid, ask)):
        now = time.strftime('%Y-%m-%d %H:%M:%S')
        newask = float(ask)/cPrec
        newbid = float(bid)/cPrec
        self.commit_queue.put(("UPDATE market_data SET AskVal=%s, BidVal=%s, TTime=%s WHERE Item_Name='EUR'",(newask, newbid, now)))

    def slot_depth(self, gox, (typ, price, volume, total_volume)):
        pass

    def slot_trade(self, gox, (date, price, volume, typ, own)):
        now = time.strftime('%Y-%m-%d %H:%M:%S')
        goxlag = self.gox.order_lag/1E6
        newprice = float(price)/cPrec
        setVal = {"bid":"BidVal","ask":"AskVal"}
        self.commit_queue.put(("UPDATE market_data SET {0}=%s, TTime=%s WHERE Item_Name='EUR'".format(setVal[typ]),(newprice, now)))
        self.commit_queue.put(("UPDATE market_data SET Value1=%s WHERE Item_Name='GOXLAG'",(goxlag)))

    def slot_userorder(self, gox, (price, volume, typ, oid, status)):
        pass