class MarketQuestions(object): def __init__(self): self.connQ = psycopg2.connect("dbname='eveclient' user='******'") self.currQ = self.connQ.cursor() expire_after = datetime.timedelta(hours = 6) self.s = requests_cache.CachedSession(cache_name="first_transaction_cache", expire_after=expire_after) self.s.hooks = {'response': self.make_throttle_hook(0.1)} self.queries = SDEQueries() self.markets = EVECrest() def populate_corp_assets_table(self, assets): #curr.executescript('drop table if exists corpassets;') """currQ.execute("CREATE TABLE corpassets " "(itemid INT PRIMARY KEY NOT NULL," "locationid INT NOT NULL," "typeid INT NOT NULL," "quantity INT NOT NULL," "flag INT NOT NULL," "singleton INT NOT NULL" ");") """ self.currQ.execute("DELETE FROM corpassets") for x in assets: '''conn.execute("INSERT INTO corpassets " "(itemid, locationid, typeid, quantity, flags, singleton) " "VALUES (", x["itemID"],", "x['locationID'],", ",x['typeID'],", ",x['quantity']","," ""x['flags']", ",x['singleton']);"" )''' self.currQ.execute("INSERT INTO corpassets ({ii}, {li}, {ti}, {q}, {f}, {s}) VALUES ({vii}, {vli}, {vti}, {vq}, {vf}, {vs})". format(ii="itemid", li="locationid", ti="typeid", q="quantity", f="flag", s="singleton", vii=x["itemID"], vli=x["locationID"], vti=x["typeID"], vq=x["quantity"], vf=x["flag"], vs=x["singleton"])) self.connQ.commit() def make_throttle_hook(self, timeout=1.0): # for eve market api calls logger.debug("HELLO LOGGER") """ Returns a response hook function which sleeps for `timeout` seconds if response is not cached """ def hook(response, **kwargs): if not getattr(response, 'from_cache', False): # ('sleeping') time.sleep(timeout) return response return hook def now_value(self, systemID, interestingItem): marketStatUrl = "http://api.eve-central.com/api/marketstat/json?usesystem=" + str(systemID) + "&typeid=" + str(interestingItem) resp = self.s.get(url=marketStatUrl) # print resp.text data = json.loads(resp.text)[0] #print ("data", data["buy"]["generated"]) return data # pprint.pprint(data) # print ("They Buy ", data[0]['buy']['max']) # print ("They Sell ", data[0]['sell']['min']) def insert_market_price(self, data): logger.debug("HELLO LOGGER") """ insert into the psql table 'marketprices' the data given as an input the input data shouls usually be a json lookup on the eve-central API """ logger.debug("attempting to insert_market_price {dt}". format(dt = data)) # buy orders - wehere we sell an item x = data["buy"] if x["forQuery"]["regions"]: pass else: x["forQuery"]["regions"].append(0) if x["forQuery"]["systems"]: pass else: x["forQuery"]["systems"].append(0) try: self.currQ.execute("INSERT INTO marketprices " "(direction, " "item, " "region, " "system, " "avg, " "fivepercent, " "generated, " "hightolow, " "max, " "median, " "min, " "stddev, " "variance, " "volume, " "wavg, " "source) " "VALUES ( " "{dir}, " "{itm}, " "{rgn}, " "{sys}, " "{avg}, " "{pcn}, " "{gnr}, " "{htl}, " "{max}, " "{mdn}, " "{min}, " "{dev}, " "{vrn}, " "{vol}, " "{wvg}, " "{src})". format( dir = "'they_buy'", itm = x["forQuery"]["types"][0], rgn = x["forQuery"]["regions"][0], sys = x["forQuery"]["systems"][0], avg = x["avg"], pcn = x["fivePercent"], gnr = x["generated"], htl = x["highToLow"], max = x["max"], mdn = x["median"], min = x["min"], dev = x["stdDev"], vrn = x["variance"], vol = x["volume"], wvg = x["wavg"], src = "'eve-market'") ) except psycopg2.IntegrityError: self.connQ.rollback() logger.debug('Duplicate market data, connQ.execute rolled back!') else: self.connQ.commit() #sell orders, where we buy something from them x = data["sell"] if x["forQuery"]["regions"]: pass else: x["forQuery"]["regions"].append(0) if x["forQuery"]["systems"]: pass else: x["forQuery"]["systems"].append(0) try: self.currQ.execute("INSERT INTO marketprices " "(direction, " "item, " "region, " "system, " "avg, " "fivepercent, " "generated, " "hightolow, " "max, " "median, " "min, " "stddev, " "variance, " "volume, " "wavg, " "source) " "VALUES ( " "{dir}, " "{itm}, " "{rgn}, " "{sys}, " "{avg}, " "{pcn}, " "{gnr}, " "{htl}, " "{max}, " "{mdn}, " "{min}, " "{dev}, " "{vrn}, " "{vol}, " "{wvg}, " "{src})". format( dir = "'they_sell'", itm = x["forQuery"]["types"][0], rgn = x["forQuery"]["regions"][0], sys = x["forQuery"]["systems"][0], avg = x["avg"], pcn = x["fivePercent"], gnr = x["generated"], htl = x["highToLow"], max = x["max"], mdn = x["median"], min = x["min"], dev = x["stdDev"], vrn = x["variance"], vol = x["volume"], wvg = x["wavg"], src = "'eve-market'") ) except psycopg2.IntegrityError: self.connQ.rollback() logger.debug('Duplicate market data, connQ.execute rolled back!') else: self.connQ.commit() #all orders - given as an average of both x = data["all"] if x["forQuery"]["regions"]: pass else: x["forQuery"]["regions"].append(0) if x["forQuery"]["systems"]: pass else: x["forQuery"]["systems"].append(0) try: self.currQ.execute("INSERT INTO marketprices " "(direction, " "item, " "region, " "system, " "avg, " "fivepercent, " "generated, " "hightolow, " "max, " "median, " "min, " "stddev, " "variance, " "volume, " "wavg, " "source) " "VALUES ( " "{dir}, " "{itm}, " "{rgn}, " "{sys}, " "{avg}, " "{pcn}, " "{gnr}, " "{htl}, " "{max}, " "{mdn}, " "{min}, " "{dev}, " "{vrn}, " "{vol}, " "{wvg}, " "{src})". format( dir = "'they_all'", itm = x["forQuery"]["types"][0], rgn = x["forQuery"]["regions"][0], sys = x["forQuery"]["systems"][0], avg = x["avg"], pcn = x["fivePercent"], gnr = x["generated"], htl = x["highToLow"], max = x["max"], mdn = x["median"], min = x["min"], dev = x["stdDev"], vrn = x["variance"], vol = x["volume"], wvg = x["wavg"], src = "'eve-market'") ) except psycopg2.IntegrityError: self.connQ.rollback() logger.debug('Duplicate market data, connQ.execute rolled back!') else: self.connQ.commit() #get Stored Sell values def get_stored_sale_price(self, item, system): logger.debug("HELLO LOGGER") self.currQ.execute("SELECT min FROM marketprices WHERE (direction = \'they_sell\' AND " "item = {it} AND system = {sys}) " "ORDER BY generated DESC " "LIMIT 1". format(it = item, sys = system)) #print ("item {id} system {sys}".format(id = item, sys = system)) #print ("db returns.. {db}".format(db = self.currQ.fetchone()[0])) x = self.currQ.fetchone() return (round(x[0], 2)) #itemID, locationID, typeID, quantity, flag, singleto def find_cheapest(self, item): logger.debug("HELLO LOGGER") hubs = ["Rens", "Jita", "Amarr"] cheapest_location = None cheapest_price = -1 for hub in hubs: #self.now_value(hub, item) try: if (cheapest_price == -1) and (self.get_stored_sale_price(item, self.queries.get_system_id(hub)) > 0): #print ("cheapest price = -1") cheapest_location = hub cheapest_price = self.get_stored_sale_price(item, self.queries.get_system_id(hub)) elif self.get_stored_sale_price(item, self.queries.get_system_id(hub)) < cheapest_price: cheapest_location = hub cheapest_price = self.get_stored_sale_price(item, self.queries.get_system_id(hub)) except: self.insert_market_price(self.now_value(self.queries.get_system_id(hub), item)) self.get_stored_sale_price(item, self.queries.get_system_id(hub)) return (cheapest_location, cheapest_price) def get_none_sold_dict(self, items, system): logger.debug("HELLO LOGGER") these_arent_sold = [] for item in items: if self.get_stored_sale_price(item, system) == 0: these_arent_sold.append(item) return these_arent_sold def get_profit_on_items(self, items, from_system, to_system): profit_from_item = {} for item in items: profit = ([self.get_stored_sale_price(item, self.queries.get_system_id(from_system)), self.get_stored_sale_price(item, self.queries.get_system_id(to_system)), (round(self.get_stored_sale_price(item, self.queries.get_system_id(to_system)) - self.get_stored_sale_price(item, self.queries.get_system_id(from_system)), 2))]) profit_from_item[self.queries.get_item_name(item)] = profit return profit_from_item #find items which are cheaper in hek than they are in rens def cheaper_between_two_locations(self, items, location1 = "Hek", location2 = "Rens"): sell_these = [] for theItems in items: diff = (self.get_stored_sale_price(theItems, self.queries.get_system_id(location1)) - self.get_stored_sale_price(theItems, self.queries.get_system_id(location2))) if (diff > 0): #print (queries.get_item_name(theItems), diff, theItems) sell_this = [self.queries.get_item_name(theItems), round(diff, 2)] sell_these.append(sell_this) else: pass headers = ["item", "profit"] sell_these.sort(key=lambda x: x[1], reverse=True) return headers, sell_these # finds items unsold in a system and finds the price in two other hubs def unsold_prices_elsewhere(self, items, location = "Lustrevik", location1 = "Jita", location2 = "Rens"): sell_these = self.get_none_sold_dict(items, self.queries.get_system_id(location)) sell_these = self.get_profit_on_items(sell_these, location1, location2) sell_these.sorted(sell_these.items(), key=lambda e: e[1][2], reverse = True) text = [[aa, bb, cc, dd] for aa, (bb, cc, dd) in sell_these] headers = ["item", "from price", "to price", "profit"] print (tabulate(text, headers, tablefmt = "simple", numalign= "right", floatfmt = ".2f"))