def update_ticker(self): try: ticker = self.get_ticker() if ticker is None: logging.warning('update ticker failed - None received') return self.vwap = float(ticker.get('vwap', [])) self.volume = float(ticker.get('volume', [])) self.value = float(ticker.get('last', [])) self.date = datetime.fromtimestamp(float(ticker.get('timestamp', []))) utils.sql_exec( 'INSERT INTO ticker (pair, date, value, volume, vwap) ' 'VALUES ("{pair}", "{date}", "{value}", "{volume}", "{vwap}")' .format(pair=self.market, date=self.date, value=self.value, volume=self.volume, vwap=self.vwap) ) logging.info('ticker updated') except (requests.exceptions.RequestException, sqlite3.Error, AttributeError): logging.exception('update ticker failed') self.market_data_working_smoothly = False
def update_order_book(self): try: if self.order_cnt == settings.UPDATE_ORDER_FREQ: order_book = self.get_order_book() self.bids = order_book.get('bids', []) self.asks = order_book.get('asks', []) if not self.bids: logging.warning('order book not updating, no bids found') return if not self.asks: logging.warning('order book not updating, no asks found') sql_bid = 'INSERT INTO order_book (id, type, pair, price, amount) VALUES' for bid in self.bids: sql_bid = '{sql_bid} (NULL, "bid", "{pair}", "{bid}", "{amount}"),'\ .format(sql_bid=sql_bid, pair=self.market, bid=bid[0], amount=bid[1]) sql_bid = '{};'.format(sql_bid[:-1]) utils.sql_exec(sql_bid) sql_ask = 'INSERT INTO order_book (id, type, pair, price, amount) VALUES' for ask in self.asks: sql_ask = '{sql_ask} (NULL, "ask", "{pair}", "{ask}", "{amount}"),'\ .format(sql_ask=sql_ask, pair=self.market, ask=ask[0], amount=ask[1]) sql_ask = '{};'.format(sql_ask[:-1]) utils.sql_exec(sql_ask) logging.info('order book updated') self.order_cnt = 0 else: self.order_cnt += 1 except (requests.exceptions.RequestException, sqlite3.Error): logging.exception('update order book failed') self.market_data_working_smoothly = False
def statdata_by_properties(browser, stattype, statdate, datetype, arrange, interval): print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), stattype, statdate, datetype, arrange, interval) projects = get_projects() table_suffix = arrange if arrange else 'resume' global start_time # 清空重复数据 if arrange: sqli = 'DELETE FROM `dataset_%s` WHERE `stattype`="%s" AND `statdate`="%s" AND `datetype`="%s" AND `intervals`="%s"' % ( table_suffix, stattype, statdate, datetype, interval) else: sqli = 'DELETE FROM `dataset_%s` WHERE `stattype`="%s" AND `statdate`="%s" AND `datetype`="%s"' % ( table_suffix, stattype, statdate, datetype) sql_exec(sqli) # 按物业类型统计 for item in handle_locator(browser, '#TenementMore > div:nth-child(2)').find_elements_by_tag_name('a'): if time.clock() - start_time > random.randint(2400, 3000): print("sleeping.............................") time.sleep(random.randint(900, 1000)) start_time = time.clock() properties = handle_attribute(item, 'innerText', 'span > span') handle_click(browser, '#TenementOne') handle_click( browser, '#TenementMore > div.butbox > span:nth-child(1)') handle_click( browser, '#TenementMore > div.butbox > span:nth-child(2)') item.click() handle_click(browser, '#TenementMore > div.butbox > a.but_confirm') # 加载数据 loaddata = loaddata_by_stattype(browser, stattype) print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), properties, len(loaddata) if loaddata else 0) if loaddata: data = [] for item in loaddata: # 新增项目 if item[0] not in projects: sqli = 'INSERT INTO `projects`(`pro_uuid`, `pro_name`) VALUES ("%s", "%s")' % ( uuid1().hex, item[0]) sql_exec(sqli) projects = get_projects() data.append([uuid1().hex, projects[item[0]], "西安市", item[1], statdate, datetype, properties, interval, stattype, item[2], item[3], item[4] if stattype == '成交情况' else 0]) sqli = 'INSERT INTO `dataset_' + table_suffix + \ '`(`uuid`, `pro_uuid`, `city`, `scope`, `statdate`, `datetype`, `property`, `intervals`, `stattype`, `number`, `area`, `amount`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' sql_exec(sqli, executemany=data)
spiderman_frame['property'].extend( list(set(properties).difference(set(spiderman_frame['property'])))) sqli = 'SELECT DISTINCT `intervals` FROM `dataset_room`' result = sql_query(sqli) arrange_room = [item[0] for item in result] spiderman_frame['arrange']['room'].extend( list( set(arrange_room).difference( set(spiderman_frame['arrange']['room'])))) set_option('spiderman_frame', json.dumps(spiderman_frame, ensure_ascii=False)) # 更新项目位置 sqli = 'SELECT `pro_name` FROM `projects` WHERE `pro_address` IS NULL' result = sql_query(sqli) for item in result: time.sleep(3) re = requests.get( 'http://api.map.baidu.com/place/v2/search?query=' + item[0] + '&tag=%E6%88%BF%E5%9C%B0%E4%BA%A7®ion=%E8%A5%BF%E5%AE%89%E5%B8%82&city_limit=true&output=json&ak=sUh3OWHYfFpcZoQqa0qN5g7x' ) jsondata = re.json() if jsondata['results']: for data in jsondata['results']: if difflib.SequenceMatcher(None, item[0], data['name']).quick_ratio() > 0.7: sqli = 'UPDATE `projects` SET `pro_address`="%s",`pro_lng`="%s",`pro_lat`="%s" WHERE `pro_name`="%s"' % ( data['address'], data['location']['lng'], data['location']['lat'], item[0]) sql_exec(sqli) break