def test_insertTickerData(self): from sql import SQL s = SQL() s.connect() r = s.insertTickerData("TEST", 123.34, 1000, "2017-11-24 13:00:00") s.close() self.assertEqual(r, 1)
def newQuery(): sql = SQL() sql.connect() data = request.get_json() if "currQuery" not in session: session["currQuery"] = "" oldQuery = session["currQuery"] newData = driver.new_tables(sql, oldQuery, data) session["currQuery"] = newData["currentQuery"] return jsonify(newData)
def loadBots(hash): # Load all bots matching hash bots = [] s = SQL() s.connect() rows = s.fetchBots(hash) for r in rows: #print("LOAD bot " + r['name'] + " --> " + r['data']) lb = LongBot(r['name'], r['data']) bots.append(lb) s.close() return bots
def getTable(): sql = SQL() sql.connect() data = request.get_json() print(data) tableName = data["table"] page = int(data['page']) if "currQuery" not in session: session["currQuery"] = "" query = session["currQuery"] tableData = driver.get_table(sql, query, page, tableName) return jsonify(tableData)
def fetch(freq): fCnt = 0 if freq in ("hourly", "daily"): # Do daily stuff try: s = SQL() s.connect() rows = s.fetchFromSource(freq) http = urllib3.PoolManager() #print("Fetch sources" + str(len(rows))) for r in rows: try: cache = None try: cache = LongBot.getData(r['url']) except Exception as e: #print("Not in cache i guess") None if not cache: print("INFO\tNot cached, loading " + r['url']) res = http.request('GET', r['url']) if res.status == 200: Processor = getattr( importlib.import_module("process_" + r['handler']), "Processor") pres = Processor.process(str(res._body)) print(str(pres)) if pres['valid']: LongBot.setData(pres['ticker'], pres['last']) fCnt = fCnt + 1 LongBot.setData(r['url'], True) else: print("WARN\tCould not download from " + r['url']) print(str(res._body)) else: print("INFO\tCached, not loading " + r['url']) fCnt = fCnt + 1 except Exception as e: print("ERROR\t" + r['name'] + "\t" + str(e)) http.clear() except Exception as e: print(str(e)) finally: s.close() return fCnt
#!/usr/bin/env python3 # coding : utf-8 import os; from sql import SQL; sql = SQL(); sql.connect(); s = sql.select_code(96); print(s) sql.close()
from sql import SQL sql = SQL() sql.connect() tablesQuery = sql.ExecuteRaw("SHOW TABLES;") tables = [] for table in tablesQuery: for k,v in table.items(): column = sql.ExecuteRaw("SHOW COLUMNS FROM "+str(v))[1]["Field"] #print("Primary Key: "+str(column[1]["Field"])) try: sql.ExecuteRaw("ALTER TABLE "+str(v)+" ADD PRIMARY KEY("+column+")") except: print("WHOOPS: ")
class Server: def __init__(self, *args): """ address = (address, port) """ signal.signal(signal.SIGCHLD, signal.SIG_IGN) # 处理僵尸进程 self.address = ("0.0.0.0", 4001) if args != None and len(args) == 2: self.address = args def start_server(self): s = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1) s.bind(self.address) s.listen(__BACKLOG__) while True: client, addr = s.accept() client.settimeout(__TIMEOUT__) logging.debug("connect from: {}".format(addr)) t = Thread(target=self.work, args=(client, addr)) t.start() def work(self, client, addr): """ transaction """ iresult = { # 标记事务完成情况 1: False, # first stage -- after first recv, before local transaction 2: False, # first stage -- after local transaction, before first ack 3: False, # first stage and second stage -- after first ack, before second recv 4: False, # second stage -- after recv, before commit or rollback 5: False, # second stage -- after commit or rollback, before second ack 6: False # second stage -- after second ack } logging.info("------ Transaction Start ------") self.cnx = SQL() try: # first stage -- enquire # test point 1 if TEST_POINT_1 == True: logging.info("TEST_POINT_1 == True, exit") sys.exit(0) buf = client.recv(1024).decode("utf-8") iresult[1] = True logging.debug("first stage from coordinaotr: {}".format(buf)) if buf == '': # meas client closed pass else: msg = json.loads(buf) sequence = msg['sequence'] self.task = msg['msg'] result = self.operation(self.task) # 本地事务操作, 不释放资源 logging.debug("local transaction result: {}".format(result)) ack = {'sequence': sequence, 'status': result} payload = json.dumps(ack).encode('utf-8') iresult[2] = True # test point 2 if TEST_POINT_2 == True: logging.info("TEST_POINT_2 == True, exit") sys.exit(0) client.sendall(payload) iresult[3] = True logging.info("{} first stage completes.".format(sequence)) # first stage complete # second stage -- commit or rollback # test point 3 if TEST_POINT_3 == True: logging.info("TEST_POINT_3 == True, exit") sys.exit(0) buf = client.recv(1024).decode('utf-8') iresult[4] = True logging.debug("second stage from coordinator: {}".format(buf)) if buf == '': self.rollback() pass # means client closed else: reply = json.loads(buf) if reply['sequence'] == sequence and reply['status'] == 0: logging.debug("commit") self.commit() else: logging.debug("rollback") self.rollback() iresult[5] = True # test point 4 if TEST_POINT_4 == True: logging.info("TEST_POINT_4 == True, exit") sys.exit(0) client.sendall(payload) iresult[6] = True logging.info("{} second stage completes.".format(sequence)) except Exception as e: # 崩溃,检查完成情况 for i in range(1, 5): if iresult[i] == False: if i == 1: # first recv over -- maybe ... nothing to do pass elif i == 2: # local transaction over -- ... nothing to do pass elif i == 3: # first ack over -- rollback self.rollback() elif i == 4: # second recv over -- .. rollback pass # 二阶段提交无法解决 elif i == 5: # commit or rollback over -- ... code must be some question, or database over ... database will automaticly resume pass # 根据记录的协调者信息重新执行操作 elif i == 6: # second send over ... pass # 重新发送 logging.debug("error: {}".format(e)) finally: self.cnx.close() logging.info("------ Transaction End ------") client.close() def operation(self, task): """ local transaction operation return 1 or 0 means fail or success """ logging.debug("task: {}".format(task)) result = self.cnx.connect() logging.debug("sql connect result: {}".format(result)) if result: # 成功 if task['type'] == '20': if self.deposit(task): return 0 elif task['type'] == '30': if self.withdraw(task): return 0 elif task['type'] == '40': if self.transfer(task): return 0 return 1 # 失败 def commit(self): return self.cnx.commit() def rollback(self): return self.cnx.rollback() def deposit(self, msg): query = "UPDATE account SET amount=amount+{} where bankcard='{}'".format( int(msg['amount']), msg['bankcard']) return self.cnx.execute(query) def withdraw(self, msg): """ 允许账户余额为负数 """ query = "UPDATE account SET amount=amount-{} where bankcard='{}'".format( int(msg['amount']), msg['bankcard']) return self.cnx.execute(query) def transfer(self, msg): query = "UPDATE account SET amount=amount-{} where bankcard='{}'".format( int(msg['amount']), msg['bankcard']) if self.cnx.execute(query): query = "UPDATE account SET amount=amount+{} where bankcard='{}'".format( int(msg['amount']), msg['transferred']) if self.cnx.execute(query): return True return False