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"))
