def all_items_in_game(game):
database = SteamDatabase(False)
results = database.query_database(
f"SELECT * FROM \"Item\" WHERE name='{game}'")
database.shutdown()
return dumps(results)
def check_for_tasks():
"""
Checks if all items have their corresponding tasks, if not, create them
"""
# Creating database
database = SteamDatabase()
tasks = set(
database.query_database("SELECT item, app_id, action FROM task"))
items = database.query_database(
'SELECT market_hash_name, app_id FROM public."Item"')
games = database.query_database('SELECT app_id FROM public."Game"')
# Checking games
for game in games:
# Operation Phoenix Weapon Case is a placeholder to pass foreign key - mostly because I don't want another table of items or to split it
if ("Operation Phoenix Weapon Case", game, "New Items") not in tasks:
# Add actions associated with game
database.add_task_game(game)
# Checking items
for item in items:
if (item[0], item[1], "Official Price") not in tasks:
# Add actions associated with item
database.add_task_item(item[0], item[1], live_price=False)
if (item[0], item[1], "Live Price") not in tasks:
# Add actions associated with item
database.add_task_item(item[0], item[1], official_price=False)
# Finalising database
database.shutdown()
def all_prices_for_item_in_game(app_id, item, type):
database = SteamDatabase(False)
results = database.query_database(
f"SELECT market_hash_name, time, median_price, volume FROM \"{type}\" WHERE market_hash_name='{database.clean_market_hash_name(item)}'"
)
database.shutdown()
return dumps(results, default=str)
def __init__(self):
# Connection to database
self.database = SteamDatabase()
self.database.ping_database("Started scraper")
# Cookie for scraping
try:
self.last_cookie_check = datetime.utcnow()
self.cookie = None
self.get_cookie()
except:
print_issue("Could not get cookie")
def transaction_amount():
"""
Obtain's total volume price of the Steam Market
"""
database = SteamDatabase(False)
results = database.query_database("""
SELECT time::date as time, sum(median_price * volume) as transaction_volume
FROM "PriceDaily"
GROUP BY time
""")
database.shutdown()
return dumps(results, default=str)
def market_seasonality():
"""
Seasonality of the entire market calculated using the earliest date of a
month and the latest date of a month:
(end_price - start_price) / start_price
"""
database = SteamDatabase(False)
results = database.query_database("""
SELECT start_month_prices.month, AVG(((end_month_prices.median_price - start_month_prices.median_price) / start_month_prices.median_price) * 100) as seasonal_percentage_move
FROM (SELECT daily_price_action.month, daily_price_action.year, daily_price_action.median_price, daily_price_action.market_hash_name
FROM (SELECT max(day) as day, month, year, market_hash_name
FROM
(SELECT date_part('day', time) as day, date_part('month', time) as month, date_part('year', time) as year, market_hash_name
FROM "PriceDaily") as split_data
GROUP BY month, year, market_hash_name) as end_month_data
JOIN
(SELECT date_part('day', time) as day, date_part('month', time) as month, date_part('year', time) as year, market_hash_name, median_price
FROM "PriceDaily") as daily_price_action
ON daily_price_action.market_hash_name = end_month_data.market_hash_name
AND daily_price_action.day = end_month_data.day
AND daily_price_action.month = end_month_data.month
AND daily_price_action.year = end_month_data.year
GROUP BY daily_price_action.month, daily_price_action.year, daily_price_action.median_price, daily_price_action.market_hash_name) as end_month_prices
JOIN
(SELECT daily_price_action.month, daily_price_action.year, daily_price_action.median_price, daily_price_action.market_hash_name
FROM (SELECT min(day) as day, month, year, market_hash_name
FROM
(SELECT date_part('day', time) as day, date_part('month', time) as month, date_part('year', time) as year, market_hash_name
FROM "PriceDaily") as split_data
GROUP BY month, year, market_hash_name) as start_month_data
JOIN
(SELECT date_part('day', time) as day, date_part('month', time) as month, date_part('year', time) as year, market_hash_name, median_price
FROM "PriceDaily") as daily_price_action
ON daily_price_action.market_hash_name = start_month_data.market_hash_name
AND daily_price_action.day = start_month_data.day
AND daily_price_action.month = start_month_data.month
AND daily_price_action.year = start_month_data.year
GROUP BY daily_price_action.month, daily_price_action.year, daily_price_action.median_price, daily_price_action.market_hash_name) as start_month_prices
ON start_month_prices.month = end_month_prices.month
AND start_month_prices.year = end_month_prices.year
AND start_month_prices.market_hash_name = end_month_prices.market_hash_name
GROUP BY start_month_prices.month
""")
return dumps(results, default=str)
def priority_of_items():
"""
Checks the database for mistakes in priority and adjusts them accordingly
Finds items which have yet to have a price scan and marks them as urgent
"""
# Creating database
database = SteamDatabase()
# Obtaining all items which do not have a price point
work = database.query_database("""
SELECT distinct market_hash_name, app_id
FROM "Item" where market_hash_name not in
(SELECT distinct market_hash_name FROM "PriceDaily"
INTERSECT select distinct market_hash_name from "PriceHourly")
""")
# Removing timeouts
for item in work:
database.queue_database(
f"UPDATE task SET due_date='{datetime.utcnow() - relativedelta.relativedelta(days=999)}'::timestamp WHERE item='{database.clean_market_hash_name(item[0])}' AND app_id={item[1]} AND action='Official Price'"
)
# Closing session
database.shutdown()
def market_overview():
"""
Obtain's price and volume of the Steam Market
"""
database = SteamDatabase(False)
results = database.query_database("""
SELECT daily_price_action.time as time, daily_price_action.volume as volume, daily_hour_price_action.high as high, daily_hour_price_action.low as low, daily_price_action.median_price as close
FROM (SELECT time::date as time, sum(volume) as volume, sum(median_price) as median_price
FROM "PriceDaily"
GROUP BY time::date
ORDER BY time) as daily_price_action
LEFT JOIN
(SELECT time::date as time, max(median_price) as high, min(median_price) as low
FROM (SELECT time as time, sum(median_price) as median_price
FROM "PriceHourly"
GROUP BY time
ORDER BY time) as hourly_price_action
GROUP BY time::date
ORDER BY time::date) as daily_hour_price_action
ON daily_price_action.time=daily_hour_price_action.time
""")
database.shutdown()
return dumps(results, default=str)
from steam_database import SteamDatabase
if __name__ == "__main__":
# Connecting to database
database = SteamDatabase()
# Checking if workers have recently pinged
pings = database.query_database("""
SELECT distinct name
FROM workers
WHERE last_ping >= (timezone('utc', now()) - INTERVAL '0.1 DAY')::timestamp
""")
# Printing information of workers
for ping in pings:
print(ping)
def all_game():
database = SteamDatabase(False)
results = database.query_database('SELECT name, icon FROM "Game"')
database.shutdown()
return dumps(results)