Pokeminer0.2 db.py V0.3

import time
import csv
import os
import json
import time
from datetime import datetime
from datetime import timedelta
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.sql import not_
from pushbullet import Pushbullet

try:
    import config
    DB_ENGINE = config.DB_ENGINE
except ImportError, AttributeError:
    DB_ENGINE = 'sqlite:///db.sqlite'

def get_engine():
    return create_engine(DB_ENGINE)

Base = declarative_base()


class Sighting(Base):
    __tablename__ = 'sightings'

    id = Column(Integer, primary_key=True)
    pokemon_id = Column(Integer)
    spawn_id = Column(String(32))
    expire_timestamp = Column(Integer)
    normalized_timestamp = Column(Integer)
    lat = Column(String(16))
    lon = Column(String(16))


Session = sessionmaker(bind=get_engine())

def pkmn_time_text(time):
		s = (time - datetime.utcnow()).total_seconds()
		(m, s) = divmod(s, 60)
		(h, m) = divmod(m, 60)
		d = timedelta(hours = h, minutes = m, seconds = s)
		disappear_time = datetime.now() + d
		return "Available until %s (%dm %ds)." % (disappear_time.strftime("%H:%M:%S"),m,s)


def normalize_timestamp(timestamp):
    return int(float(timestamp) / 120.0) * 120

def get_pokemon_name(pokemon_id):
    if not hasattr(get_pokemon_name, 'names'):
        file_path = os.path.join(os.getcwd()+'/pokemon.json')

        with open(file_path, 'r') as f:
            get_pokemon_name.names = json.loads(f.read())

    return get_pokemon_name.names[str(pokemon_id)]

def gmaps_link(lat, lng):
		latLon = '{},{}'.format(repr(lat), repr(lng))
		return 'http://maps.google.com/maps?q={}'.format(latLon)


with open('locales/pokemon.en.json') as f:
    pokemon_names = json.load(f)


def add_sighting(session, spawn_id, pokemon):
    obj = Sighting(
        pokemon_id=pokemon['id'],
        spawn_id=spawn_id,
        expire_timestamp=pokemon['disappear_time'],
        normalized_timestamp=normalize_timestamp(pokemon['disappear_time']),
        lat=pokemon['lat'],
        lon=pokemon['lng'],
    )
    # Check if there isn't the same entry already
    existing = session.query(Sighting) \
        .filter(Sighting.pokemon_id == obj.pokemon_id) \
        .filter(Sighting.spawn_id == obj.spawn_id) \
        .filter(Sighting.expire_timestamp > obj.expire_timestamp - 10)     \
        .filter(Sighting.expire_timestamp < obj.expire_timestamp + 10) \
        .filter(Sighting.lat == obj.lat) \
        .filter(Sighting.lon == obj.lon) \
        .first()
    if existing:
        return
    session.add(obj)

    #write to CSV
    dt = pokemon['disappear_time']+7200 # +7200 needed to match my timezone
    if dt > time.time()+7200:           # +7200 needed to match my timezone
        negativetime = ' '
        if pokemon['disappear_time'] < time.time():
            negativetime = 'Negative time bug, dunno exp time'
            dt = time.time()
        f = open(os.getcwd()+'\spawn_locationPminer.csv', "a")
        out = csv.writer(f, delimiter=",")
        out.writerow([str(pokemon['id']),str(pokemon['lat']),str(pokemon['lng']),str(dt)])
        f.close()

    # PUSHBULLET part

 #  1,7,10,11,13,14,15,16,17,18,19,20,21,22,29,30,32,35,39,41,42,43,44,46,47,48,49,50,51,52,60,69,81,82,90,92,93,96,98,99,100,109,113,114,116,118,120,124,129,133
        mydict = {'Bulbasaur':1,'Ivysaur':2,'Venusaur':3,'Charmander':4,'Charmeleon':5,'Charizard':6,
                  'Squirtle':7,'Wartortle':8,'Blastoise':9,'Caterpie':10,'Metapod':11,'Butterfree':12,
                  'Weedle':13,'Kakuna':14,'Beedrill':15,'Pidgey':16,'Pidgeotto':17,'Pidgeot':18,'Rattata':19,
                  'Raticate':20,'Spearow':21,'Fearow':22,'Ekans':23,'Arbok':24,'Pikachu':25,'Raichu':26,
                  'Sandshrew':27,'Sandlash':28,'Nidoran M':29,'Nidorina':30,'Nidoqueen':31,'Nidoran F':32,
                  'Nidorino':33,'Nidoking':34,'Clefairy':35,'Clefable':36,'Vulpix':37,'Ninetales':38,
                  'Jigglypuff':39,'Wigglypuff':40,'Zubat':41,'Golbat':42,'Oddish':43,'Gloom':44,'Vileplume':45,
                  'Paras':46,'Parasect':47,'Venomat':48,'Venomoth':49,'Diglett':50,'Dugtrio':51,'Meowth':52,
                  'Persian':53,'Psyduck':54,'Golduck':55,'Mankey':56,'Primeape':57,'Growlithe':58,'Arcanine':59,
                  'Poliwag':60,'Poliwhirl':61,'Poliwrath':62,'Abra':63,'Kadabra':64,'Alakazam':65,'Machop':66,
                  'Machoke':67,'Machamp':68,'Bellsprout':69,'Weepinbell':70,'Victreebell':71,'Tentacool':72,'Tentacruel':73,
                  'Geodude':74,'Graveler':75,'Golem':76,'Ponyta':77,'Rapidash':78,'Slowpoke':79,'Slowbro':80,'Magnemite':81,
                  'Magneton':82,'FarfetchD':83,'Doduo':84,'Dodrio':85,'Seel':86,'Dewgong':87,'Grimer':88,'Muk':89,'Shellder':90,
                  'Cloyster':91,'GAstly':92,'Haunter':93,'Gengar':94,'Onix':95,'Drowzee':96,'Hypno':97,'Krabby':98,'Kingler':99,
                  'Voltorb':100,'Electrode':101,'Exeggcute':102,'Exeggutor':103,'Cubone':104,'Marowak':105,'Hitmonlee':106,
                  'Hitmonchan':107,'Lickitung':108,'Koffing':109,'Weezing':110,'Rhyhorn':111,'Rhydon':112,'Chansey':113,
                  'Tangela':114,'Kangaskhan':115,'Horsea':116,'Seadra':117,'Goldeen':118,'Seaking':119,'Staryu':120,
                  'Starmie':121,'MrMime':122,'Scyther':123,'Jynx':124,'Electabuzz':125,'Magmar':126,'Pinsir':127,'Tauros':128,
                  'Magikarp':129,'Gyarados':130,'Lapras':131,'Ditto':132,'Eevee':133,'Vaporeon':134,'Jolteon':135,'Flareon':136,
                  'Porygon':137,'Omanyte':138,'Omastar':139,'Kabuto':140,'Kabutops':141,'Aerodactyl':142,'Snorlax':143,'Articuno':144,
                  'Zapdos':145,'Moltres':146,'Dratini':147,'Dragonair':148,'Dragonite':149,'Mewtwo':150,'Mew':151}
        pokename = mydict.keys()[mydict.values().index(pokemon['id'])]

        expsec = int(pokemon['disappear_time'] - time.time())
        m, s = divmod(expsec, 60)
        expat = datetime.fromtimestamp(pokemon['disappear_time']).strftime('%H:%M')

        google_maps_link = gmaps_link(pokemon["lat"], pokemon["lng"])

        try:
            pb = Pushbullet("PUSHBULLET KEY HERE")
        except:
            pass

        #Here you can filter what to show:
        goodlist = [4,5,6,9,23,24,25,26,36,38,53,59,74,75,76,77,78,83,87,88,89,91,94,101,102,103,104,105,
                    110,111,112,115,126,127,128,130,131,134,135,136,137,138,139,140,141,142,143,144,145,
                    146,148,149,150,151]

        if pokemon['id'] in goodlist:
            try:
                print 'PUSHBULLET'
                push = pb.push_link('A wild ' + pokename.title() + ' has appeared!',google_maps_link,  'Expires in: '+str(m)+'m'+str(s)+'s ('+str(expat)+') '+negativetime)
            except:
                print 'INVALIDKEY'
                pass
        else:
            print 'INVALIDKEY'
            pass


    # Here you can filter what not to show:
#        thrashlist = [1,7,10,11,13,14,15,16,17,18,19,20,21,22,29,30,32,35,39,41,42,43,44,46,47,48,49,
#                      50,51,52,60,69,81,82,90,92,93,96,98,99,100,109,113,114,116,118,120,124,129,133]
#        if pokemon['id'] in thrashlist:
#          pass
#        else:
#            try:
#                print 'asd'
#                push = pb.push_link('A wild ' + pokename.title() + ' has appeared!',google_maps_link,  'Expires in: '+str(m)+'m'+str(s)+'s ('+str(expat)+') '+negativetime)
#            except:
#                pass

def get_sightings(session):
    query = session.query(Sighting) \
        .filter(Sighting.expire_timestamp > time.time())
    trash_list = getattr(config, 'TRASH_IDS')
    if trash_list:
        query = query.filter(not_(Sighting.pokemon_id.in_(config.TRASH_IDS)))
    return query.all()