Created
July 13, 2016 19:03
-
-
Save fuzzysteve/da6aea99659741150a6d63fb7c32aee2 to your computer and use it in GitHub Desktop.
Sqlite loader
This file contains 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,ordersarray): | |
| 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']: | |
| ordersarray.append([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}) | |
| orderarray = [] | |
| # 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,orderarray) | |
| 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) | |
| dbapi_conn = connection.connection | |
| dbapi_conn.executemany("insert into orders values (?,?,?,?,?,?,?,?,?,?,?,?)", orderarray) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment