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