forked from modrzew/pokeminer
/
db.py
71 lines (54 loc) · 1.95 KB
/
db.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
import time
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_
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 normalize_timestamp(timestamp):
return int(float(timestamp) / 120.0) * 120
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)
def get_sightings(session):
query = session.query(Sighting) \
.filter(Sighting.expire_timestamp > time.time())
trash_list = getattr(config, 'TRASH_IDS', None)
if trash_list:
query = query.filter(not_(Sighting.pokemon_id.in_(config.TRASH_IDS)))
return query.all()