Created
July 14, 2016 16:28
-
-
Save fuzzysteve/0376fd965a7a3cdcfcd0545fb4a98f5a to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from sqlalchemy import create_engine, Column, MetaData, Table, Index | |
| from sqlalchemy import Integer, String, Text, Float, Boolean, BigInteger, Numeric, SmallInteger, DateTime | |
| import time | |
| import requests | |
| from requests_futures.sessions import FuturesSession | |
| import requests_futures | |
| from concurrent.futures import as_completed | |
| import datetime | |
| import csv | |
| import time | |
| import sys | |
| import re | |
| import logging | |
| logging.basicConfig(filename='csvloader.log',level=logging.WARN,format='%(asctime)s %(levelname)s %(message)s') | |
| def RateLimited(maxPerSecond): | |
| minInterval = 1.0 / float(maxPerSecond) | |
| def decorate(func): | |
| lastTimeCalled = [0.0] | |
| def rateLimitedFunction(*args,**kargs): | |
| elapsed = time.clock() - lastTimeCalled[0] | |
| leftToWait = minInterval - elapsed | |
| if leftToWait>0: | |
| time.sleep(leftToWait) | |
| ret = func(*args,**kargs) | |
| lastTimeCalled[0] = time.clock() | |
| return ret | |
| return rateLimitedFunction | |
| return decorate | |
| def processData(result,orderwriter): | |
| try: | |
| m=re.search('/(\d+)/',result.url) | |
| regionid=m.group(1) | |
| resp=result.result() | |
| logging.info('Process {} {} {}'.format(resp.status_code,result.url,result.retry)) | |
| if resp.status_code==200: | |
| orders=resp.json() | |
| logging.info('{} orders on page {}'.format(len(orders['items']),result.url)) | |
| for order in orders['items']: | |
| orderwriter.writerow([order['id'], | |
| order['type'], | |
| order['issued'], | |
| int(order['buy']), | |
| order['volume'], | |
| order['volumeEntered'], | |
| order['minVolume'], | |
| order['price'], | |
| order['stationID'], | |
| order['range'], | |
| order['duration'], | |
| regionid, | |
| ] | |
| ) | |
| logging.info('{}: next page {}'.format(result.url,orders.get('next',{}).get('href',None))) | |
| return {'retry':0,'url':orders.get('next',{}).get('href',None)} | |
| else: | |
| logging.error("Non 200 status. {} returned {}".format(resp.url,resp.status_code)) | |
| return {'retry':result.retry+1,'url':result.url} | |
| except requests.exceptions.ConnectionError as e: | |
| logging.error(e) | |
| return {'retry':result.retry+1,'url':result.url} | |
| @RateLimited(150) | |
| def getData(requestsConnection,url,retry): | |
| future=requestsConnection.get(url) | |
| future.url=url | |
| future.retry=retry | |
| return future | |
| if __name__ == "__main__": | |
| engine = create_engine('sqlite+pysqlite:///marketcsv.db', echo=False) | |
| metadata = MetaData() | |
| connection = engine.connect() | |
| reqs_num_workers = 20 | |
| session = FuturesSession(max_workers=reqs_num_workers) | |
| session.headers.update({'UserAgent':'Fuzzwork All Region Download'}); | |
| orderTable = Table('orders',metadata, | |
| Column('orderID',BigInteger, primary_key=False,autoincrement=False), | |
| Column('typeID',Integer), | |
| Column('issued',DateTime), | |
| Column('buy',Boolean), | |
| Column('volume',BigInteger), | |
| Column('volumeEntered',BigInteger), | |
| Column('minVolume',BigInteger), | |
| Column('price',Numeric(scale=4,precision=19)), | |
| Column('stationID',Integer), | |
| Column('range',String(12)), | |
| Column('duration',Integer), | |
| Column('region',Integer), | |
| ) | |
| Index("orders_1",orderTable.c.typeID) | |
| Index("orders_2",orderTable.c.typeID,orderTable.c.buy) | |
| Index("orders_5",orderTable.c.region,orderTable.c.typeID,orderTable.c.buy) | |
| Index("orders_6",orderTable.c.region) | |
| metadata.drop_all(engine,checkfirst=True) | |
| metadata.create_all(engine,checkfirst=True) | |
| urls=[] | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000001/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000002/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000003/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000004/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000005/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000006/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000007/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000008/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000009/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000010/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000011/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000012/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000013/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000014/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000015/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000016/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000017/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000018/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000019/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000020/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000021/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000022/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000023/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000025/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000027/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000028/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000029/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000030/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000031/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000032/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000033/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000034/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000035/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000036/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000037/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000038/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000039/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000040/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000041/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000042/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000043/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000044/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000045/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000046/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000047/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000048/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000049/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000050/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000051/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000052/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000053/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000054/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000055/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000056/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000057/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000058/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000059/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000060/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000061/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000062/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000063/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000064/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000065/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000066/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000067/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000068/orders/all/",'retry':0}) | |
| urls.append({'url':"https://crest-tq.eveonline.com/market/10000069/orders/all/",'retry':0}) | |
| trans = connection.begin() | |
| with open('orderset.csv', 'wb') as csvfile: | |
| orderwriter = csv.writer(csvfile,quoting=csv.QUOTE_MINIMAL,delimiter=",") | |
| # Loop through the urls in batches | |
| while len(urls)>0: | |
| futures=[] | |
| logging.warn("Loop restarting"); | |
| for url in urls: | |
| logging.info('URL:{} Retry:{}'.format(url['url'],url['retry'])); | |
| futures.append(getData(session,url['url'],url['retry'])) | |
| urls=[] | |
| for result in as_completed(futures): | |
| presult=processData(result,orderwriter) | |
| if presult['retry']==1: | |
| urls.append(presult) | |
| logging.info("adding {} to retry {}".format(presult.url,presult.retry)) | |
| if presult['retry'] == 0 and presult['url'] is not None: | |
| logging.info('{} has more pages. {}'.format(result.url,presult['retry'])) | |
| urls.append(presult) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment