Skip to content

Instantly share code, notes, and snippets.

@rsvargas

rsvargas/poke.json

Last active November 4, 2016 16:59
Show Gist options
  • Save rsvargas/d4c07d6281a1e9296f4de8e811417498 to your computer and use it in GitHub Desktop.
Save rsvargas/d4c07d6281a1e9296f4de8e811417498 to your computer and use it in GitHub Desktop.
Download ZIP
Telegram bot, DB schema and inserter PHP script for a Pokemon Go notifier bot!(based on FastPokeMap)
Raw
poke.json
{
"telegram-token": "TELEGRAM TOKEN",
"log-file": "poke.log",
"database": {
"__example": "MySQL",
"driver": "mysql",
"user": "pokemongo",
"password": "pokemongo",
"host": "localhost",
"database": "pokemongo"
}
}
Raw
poke.php
<?php
header('Access-Control-Allow-Origin: https://fastpokemap.se');
header('Content-Type: application/json');
$db = new mysqli("localhost", "pokemongo", "pokemongo", "pokemongo");
if($db->connect_error) {
die("Connect error: " . $db->connect_errno . " - ". $db->connect_error );
}
$query = 'INSERT INTO spawns(encounter_id, expiration_timestamp, latitude,
longitude, name, spawn_point_id)
VALUES ( ?, ?, ?, ?, ?, ? )
ON DUPLICATE KEY UPDATE name=name';
if( $insert = $db->prepare($query) )
{
$insert->bind_param( "siddss", $_GET['encounter_id'], $_GET['expiration_timestamp'],
$_GET['latitude'], $_GET['longitude'], $_GET['name'], $_GET['spawn_point_id']);
$insert->execute();
if($insert->error) {
die("Insert error: ". $insert->error);
}
$insert->close();
}
$db->close();
echo '{"result": []}';
Raw
pokebot.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# The MIT License (MIT)
# Copyright (c) 2016 Rafael Vargas (rsvargas AT gmail)
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software
# and associated documentation files (the "Software"), to deal in the Software without restriction,
# including without limitation the rights to use, copy, modify, merge, publish, distribute,
# sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all copies or
# substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT
# NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
from telegram.ext import * #Updater, CommandHandler, MessageHandler, Filters, InlineQueryHandler
from telegram import *
import logging
import logging.handlers
import sqlite3
from pokedb import *
from datetime import *
import math
import json
FFORMAT='%(levelname)1.1s|%(asctime)s| %(message)s'
logger = logging.getLogger('poke.telegram')
emoji = {
"map": '\U0001f5fa',
"keyboard": '\u2328',
"enabled": '\u2714',
"disabled": '\u2716',
"ruler": '\U0001F4CF'
}
def config_log(config):
filename = "poke.log";
if "log-file" in config:
filename = config["log-file"]
handler = logging.handlers.RotatingFileHandler(filename,
maxBytes=100*1024*1024, backupCount=5)
handler.setFormatter(logging.Formatter(FFORMAT))
logger.addHandler(handler)
handler.setLevel(logging.DEBUG)
logger.setLevel(logging.DEBUG)
def get_user(update):
return User.find(update.message.chat_id)
def distance(pA, pB):
R = 6371000.0 #raio da terra em metros
aLat = math.radians(pA.latitude)
aLng = math.radians(pA.longitude)
bLat = math.radians(pB.latitude)
bLng = math.radians(pB.longitude)
distLat = bLat - aLat
distLng = (bLng - aLng) * math.cos(0.5*(bLat+aLat))
dist = R * math.sqrt(distLat*distLat + distLng*distLng)
return dist
def cmd_help(bot, update):
chat_id = update.message.chat_id
bot.sendMessage(chat_id,text=
'''/help
/start
/add <pokemon_name>
/add <pokemon_name1> <pokemon_name2>...
/rem <pokemon_name>
/rem <pokemon_name1> <pokemon_name2>...
/list - List configured pokemons
/keyboard - updates the keyboard
Send <location> - Update location
''')
def cmd_start(bot, update):
chat_id = update.message.chat_id
user = get_user(update)
if user:
user.chat_id = chat_id
user.save()
bot.sendMessage(chat_id, text="Welcome back {}, where are you? Please send me your updated position.".format(user.first_name))
else:
msgfrom = update.message.from_user
user = User.new(msgfrom.first_name, msgfrom.last_name, msgfrom.username, chat_id)
user.save()
update.message.reply_text("""I'm the PokeBot, I'll let you know when monsters are nearby.
Please send your localization and a /distance""")
logger.info('New User: {} {} (@{}-{})'.format(user.first_name, user.last_name,
user.username, chat_id))
cmd_keyboard(bot, update)
def cmd_list(bot,update):
chat_id = update.message.chat_id
user = get_user(update)
resp = 'You will receive notifications for: '
for f in user.filters():
resp += f.name + ' '
bot.sendMessage( chat_id, text=resp )
def cmd_add(bot, update, args):
chat_id = update.message.chat_id
user = get_user(update)
try:
for p in args:
poke = Pokemon.by_name(p)
logger.debug("{}({}) add notify: {}".format(user.first_name, chat_id, poke))
user.add_filter(poke.id)
cmd_list(bot, update)
except Exception as e:
logger.error('{}({}) add error: {}'.format(user.username, chat_id, e))
bot.sendMessage(chat_id,text="/add NAME or /add NAME1 NAME2")
def cmd_rem(bot, update, args):
chat_id = update.message.chat_id
user = get_user(update)
try:
for p in args:
poke = Pokemon.by_name(p)
logger.debug("{}({}) rem notify: {}".format(user.first_name, chat_id, poke))
user.del_filter(poke.id)
except Exception as e:
logger.error('{}({}) rem Error: {}'.format(user.username, chat_id, e))
bot.sendMessage(chat_id,text="/rem NAME or /rem NAME1 NAME2")
cmd_list(bot,update)
def cmd_distance(bot, update, args):
chat_id = update.message.chat_id
user = get_user(update)
if len(args) < 1:
bot.sendMessage(chat_id, text="Current distance is {}m".format(user.distance))
return
user.distance = int(args[0])
user.save()
bot.sendMessage(chat_id, text="Distance set to {}m".format(user.distance))
logger.debug("{}({}) Distance: {}m".format(user.first_name, chat_id, user.distance))
def cmd_location(bot, update):
chat_id = update.message.chat_id
user = get_user(update)
try:
loc = update.message.location
user.update_position(loc.latitude, loc.longitude)
bot.sendMessage(chat_id, text='Position set' )
logger.debug("{}({}) Position set: lat={}, lng={}".format(user.first_name, chat_id,
loc.latitude, loc.longitude))
except Exception as e:
bot.sendMessage(chat_id, text="Error setting position")
logger.error("{}({}) error setting position (msg={}) - {}".format(
user.first_name, update.message, e ))
def callback_periodic_check(bot, job):
#print('.', end='', flush=True)
all_users = User.all()
all_spawns = list(Spawn.all_active())
now = datetime.now()
for u in all_users:
if not u.position():
continue
filters = list(u.filters()) #must convert to list, maps only iterate once
notified = False
for s in all_spawns:
exp = datetime.fromtimestamp(s.expiration_timestamp)
secs = (exp - now).total_seconds()
#print(" {:10s} - {:30s} - {:30s} - {}".format(s.name, str(now), str(exp), secs))
if secs < 0:
continue
for f in filters:
if s.name == f.internal_name:
if u.notify(s.encounter_id):
dist = distance(s, u.position())
if dist < u.distance:
if not notified:
logger.debug( "{}({}) Notifying:".format(u.first_name, u.chat_id))
notified = True
logger.debug( " spawn: {} dist: {:1.1f}m - exp in {:02d}m{:02d}s".format(
f.name, dist, int(secs/60), int(secs%60)))
bot.sendVenue(u.chat_id, s.latitude, s.longitude,
"{}".format(s.name),
"{:02d}m{:02d}s left ({:02d}:{:02d}) {:1.1f}m away".format(
int(secs/60), int(secs%60), exp.hour, exp.minute, dist) )
break
if not notified:
pass
def cmd_text(bot, update):
chat_id = update.message.chat_id
user = get_user(update)
cmd = update.message.text[0]
if cmd == emoji["map"]:
cmd_location(bot, update)
elif cmd == emoji["keyboard"]:
bot.sendMessage(chat_id=chat_id, text='Keyboard hidden', reply_markup=ReplyKeyboardHide() )
elif cmd == emoji["enabled"]:
pokename = update.message.text[2:]
poke = Pokemon.by_name(pokename)
user.del_filter(poke.id)
logger.debug("{}({}) disable notify: {}".format(user.first_name, chat_id, pokename))
bot.sendMessage(chat_id=chat_id, text='Disabled notifications for ' + pokename,
reply_markup=get_keyboard(user))
elif cmd == emoji["disabled"]:
pokename = update.message.text[2:]
poke = Pokemon.by_name(pokename)
user.add_filter(poke.id)
logger.debug("{}({}) enable notify: {}".format(user.first_name, chat_id, pokename))
bot.sendMessage(chat_id=chat_id, text='Enabled notifications for ' + pokename,
reply_markup=get_keyboard(user))
elif cmd == emoji["ruler"]:
dist = update.message.text[1:-1]
cmd_distance(bot, update, [dist])
#bot.sendMessage(chat_id=chat_id, text="int? = {}".format(dist) )
def cmd_keyboard(bot, update):
chat_id = update.message.chat_id
user = get_user(update)
bot.sendMessage(chat_id=chat_id, text="Use keyboard to enable/disable notifications", reply_markup=get_keyboard(user))
def get_keyboard(user):
filters = [f.name for f in user.filters() ]
r = emoji["ruler"]
custom_keyboard = [ [ KeyboardButton(text=emoji["map"] + ' Location', request_location=True)],
[r+'100m', r+'300m'], [r+'500m', r+'1000m'] ]
pokes = Pokemon.all()
counter = 0
row = []
for p in pokes:
name = emoji["enabled"] if (p.name in filters) else emoji["disabled"]
name += ' ' + p.name
row.append( name)
if len(row) == 2:
custom_keyboard.append(row)
row = []
if len(row) > 0:
custom_keyboard.append(row)
custom_keyboard.append([KeyboardButton(text=emoji["keyboard"] + ' Hide Keyboard')])
return ReplyKeyboardMarkup(custom_keyboard)
def error(bot, update, error):
logger.error('Update "{}" caused error"{}"'.format( update, error))
def main():
config = {}
with open('poke.json') as config_file:
config = json.load(config_file)
config_log(config)
if 'telegram-token' not in config:
print("Configuration file lacks Telegram Token")
logger.error("Configuration file lacks Telegram Token")
exit()
try:
updater = Updater(config['telegram-token'])
dp = updater.dispatcher
dp.add_handler(CommandHandler('help', cmd_help))
dp.add_handler(CommandHandler('start', cmd_start))
dp.add_handler(CommandHandler('add', cmd_add, pass_args=True))
dp.add_handler(CommandHandler('rem', cmd_rem, pass_args=True))
dp.add_handler(CommandHandler('distance', cmd_distance, pass_args=True))
dp.add_handler(CommandHandler('list', cmd_list))
dp.add_handler(CommandHandler('keyboard', cmd_keyboard ))
dp.add_handler(MessageHandler([Filters.text], cmd_text))
dp.add_handler(MessageHandler([Filters.location], cmd_location))
jq = updater.job_queue
jq.put(Job(callback_periodic_check, 30.0), next_t=0.0)
dp.add_error_handler(error)
logger.info("Starting PokeBot.")
updater.start_polling()
updater.idle()
except Exception as e:
logger.error("Error starting Bot: {}".format(e))
if __name__ == '__main__':
main()
Raw
pokedb.py
# The MIT License (MIT)
# Copyright (c) 2016 Rafael Vargas (rsvargas AT gmail)
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software
# and associated documentation files (the "Software"), to deal in the Software without restriction,
# including without limitation the rights to use, copy, modify, merge, publish, distribute,
# sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all copies or
# substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT
# NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
import threading
import sqlite3
import mysql.connector
import logging
import collections
import time
import json
DB_threadlocal = threading.local()
class DB(object):
def __new__(cls, **kwargs):
if getattr(DB_threadlocal, 'db_instance', None) is None:
config = {}
with open('poke.json') as config_file:
config = json.load(config_file)
db = config["database"]
DB_threadlocal.db_instance = object.__new__(cls)
DB_threadlocal.db_instance.conn = mysql.connector.connect(user=db["user"],
password=db["password"], host=db["host"], database=db["database"])
DB_threadlocal.db_instance.cursor_param = {"dictionary": True}
DB_threadlocal.db_instance.__createTables(drop_before=kwargs.get('wipe'))
return DB_threadlocal.db_instance
def __createTables(self, **kwargs):
drop_before = kwargs.get('drop_before')
cursor = self.cursor()
try:
cursor.execute('SELECT `version` FROM `version` ORDER BY `version` DESC LIMIT 1')
v = cursor.fetchone()
self.version = v['version']
except Exception as e:
logging.warn("Error reading DB version. ({})".format(e))
self.version = 0
try:
if drop_before:
logging.warning('Dropping the existing database')
cursor.execute('DROP TABLE IF EXISTS `users`')
cursor.execute('DROP TABLE IF EXISTS `user_positions`')
cursor.execute('DROP TABLE IF EXISTS `location_groups`')
cursor.execute('DROP TABLE IF EXISTS `locations`')
cursor.execute('DROP TABLE IF EXISTS `pokemons`')
cursor.execute('DROP TABLE IF EXISTS `user_filters`')
cursor.execute('DROP TABLE IF EXISTS `spawns`')
cursor.execute('DROP TABLE IF EXISTS `notifications`')
cursor.execute('DROP TABLE IF EXISTS `version`')
self.version = 0
self.conn.commit()
current_version = 20161002
if self.version < current_version:
cursor.execute('''CREATE TABLE `users` (
`id` INTEGER NOT NULL UNIQUE PRIMARY KEY,
`first_name` VARCHAR(256),
`last_name` VARCHAR(256),
`username` VARCHAR(256),
`chat_id` VARCHAR(256) NOT NULL UNIQUE,
`distance` INTEGER) ''')
cursor.execute('''CREATE TABLE `user_positions` (
`user_id` INTEGER,
`timestamp` INTEGER,
`latitude` REAL,
`longitude` REAL )''')
cursor.execute('''CREATE TABLE `location_groups` (
`id` INTEGER NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(256) )''')
cursor.execute('''CREATE TABLE `locations` (
`id` INTEGER NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
`location_group_id` INTEGER NOT NULL,
`name` VARCHAR(256),
`latitude` REAL,
`longitude` REAL )''')
cursor.execute('''CREATE TABLE `pokemons` (
`id` INTEGER NOT NULL UNIQUE PRIMARY KEY,
`name` VARCHAR(64),
`internal_name` VARCHAR(64),
`rarity` INTEGER )''')
cursor.execute('''CREATE TABLE `user_filters` (
`user_id` INTEGER,
`pokemon_id` INTEGER,
PRIMARY KEY( `user_id`, `pokemon_id` ) )''')
cursor.execute('''CREATE TABLE `spawns` (
`encounter_id` VARCHAR(64) UNIQUE,
`expiration_timestamp` INTEGER,
`latitude` REAL,
`longitude` REAL,
`name` VARCHAR(64),
`spawn_point_id` VARCHAR(256) )''')
cursor.execute('''CREATE TABLE `notifications` (
`encounter_id` VARCHAR(64),
`user_id` INTEGER,
PRIMARY KEY( `encounter_id`, `user_id`) )''')
cursor.execute('''CREATE TABLE `version` (
`version` INTEGER UNSIGNED NOT NULL )''')
cursor.execute('''INSERT INTO `version` (`version`) values ( %s )''', (current_version,) )
self.conn.commit()
self.version = current_version
logging.debug("Upgraded DB to version {}".format(current_version) )
except Exception as e:
self.conn.rollback()
logging.error("Error creating DB: ({}) - {}".format(kwargs, e))
raise
@classmethod
def connection(cls):
return cls().conn
@classmethod
def cursor(cls):
return cls().conn.cursor( **cls().cursor_param )
@classmethod
def commit(cls):
return cls().conn.commit()
@classmethod
def rollback(cls):
return cls().conn.rollback()
class Data(object):
def __init__(self, **kwargs):
for a in self._attrs():
setattr(self, a, kwargs.get(a))
def save(self):
try:
c = DB.cursor()
c.execute(self._insert(), self.__dict__)
DB.commit()
except Exception as e:
DB.rollback()
logging.warn("Error saving {} ({}) - {}".format(self.__class__.__name__, self.__dict__, e))
@classmethod
def _make(cls, args):
return cls(**args)
@classmethod
def _attrs(cls):
raise NotImplementedError
@classmethod
def _insert(cls):
raise NotImplementedError
class UserFilter(Data):
@classmethod
def _attrs(cls):
return [ 'user_id', 'pokemon_id']
class Spawn(Data):
@classmethod
def _attrs(cls):
return [ 'encounter_id', 'expiration_timestamp', 'latitude', 'longitude',
'name', 'spawn_point_id']
@classmethod
def _insert(cls):
return '''INSERT INTO spawns (encounter_id, expiration_timestamp, latitude,
longitude, name, spawn_point_id)
VALUES ( %(encounter_id)s, %(expiration_timestamp)s,
%(latitude)s, %(longitude)s, %(name)s, %(spawn_point_id)s )'''
@classmethod
def register(cls, obj):
Spawn(**obj).save()
@classmethod
def all_active(cls):
c = DB.cursor()
c.execute('''SELECT * FROM spawns
WHERE expiration_timestamp > UNIX_TIMESTAMP( NOW() )
ORDER BY expiration_timestamp ASC''')
def creator(data):
return cls(**dict(data))
return map(creator, c.fetchall())
class Filter(Data):
@classmethod
def _attrs(cls):
return [ 'internal_name', 'name' ]
class User(Data):
@classmethod
def _attrs(cls):
return [ 'id', 'first_name', 'last_name', 'username', 'chat_id', 'distance' ]
@classmethod
def _insert(cls):
return '''INSERT INTO `users` (id, first_name, last_name, username, chat_id, distance)
VALUES (%(id)s, %(first_name)s, %(last_name)s, %(username)s, %(chat_id)s,
%(distance)s )
ON DUPLICATE KEY UPDATE
first_name = %(first_name)s,
last_name = %(last_name)s,
username = %(username)s,
chat_id = %(chat_id)s,
distance = %(distance)s'''
def update_position(self, latitude, longitude):
self.last_pos = UserPosition(user_id=self.id, timestamp=time.time(),
latitude=latitude, longitude=longitude)
self.last_pos.save()
def position(self):
if not hasattr(self, 'last_pos') or self.last_pos == None:
self.last_pos = UserPosition.get_last(self.id)
return self.last_pos
def add_filter(self, pokemon_id):
try:
c = DB.cursor()
c.execute('INSERT INTO user_filters VALUES ( %s , %s )', (self.id, pokemon_id))
DB.commit()
except Exception as e:
DB.rollback()
logging.warning("Could not insert filter for user: {} - {} ({})".format(
self.username, pokemon_id, e))
pass
def del_filter(self, pokemon_id):
try:
c = DB.cursor()
c.execute('DELETE FROM user_filters WHERE user_id=%s AND pokemon_id=%s',
(self.id, pokemon_id))
DB.commit()
except Exceptino as e:
DB.rollback()
logging.warning("Could not remove filter for user: {} - {} ({})".format(
self.username, pokemon_id, e))
def filters(self):
c = DB.cursor()
c.execute('''SELECT internal_name, name FROM user_filters AS f
LEFT JOIN pokemons AS p ON p.id = f.pokemon_id
WHERE user_id=%s''', (self.id,) )
return map(Filter._make, c.fetchall())
def notify(self, encounter_id):
c = DB.cursor()
try:
c.execute('''INSERT INTO notifications VALUES ( %s, %s ) ''', (encounter_id, self.id))
DB.commit()
return True
except Exception as e:
DB.rollback()
return False
@classmethod
def new(cls, first, last, user, chat_id, distance=1000):
return User( id=None, first_name=first, last_name=last, username=user,
chat_id=chat_id, distance=distance).save()
@classmethod
def all(cls):
cursor = DB.cursor()
cursor.execute('select * from `users`')
def creator(data):
return cls(**dict(data))
return map(creator, cursor.fetchall())
@classmethod
def find(cls,chat_id):
cursor = DB.cursor()
cursor.execute('SELECT * from `users` where `chat_id`=%s', (chat_id,))
data = cursor.fetchone()
if data == None:
return None
return User(**dict(data))
class UserPosition(Data):
@classmethod
def _attrs(cls):
return [ 'user_id', 'timestamp', 'latitude', 'longitude' ]
@classmethod
def _insert(cls):
return '''INSERT INTO `user_positions` (user_id, timestamp, latitude, longitude)
VALUES (%(user_id)s, %(timestamp)s, %(latitude)s, %(longitude)s)'''
@classmethod
def get_last(cls, user_id):
c = DB.cursor()
c.execute('''SELECT * FROM user_positions WHERE user_id=%(user_id)s
ORDER BY timestamp DESC LIMIT 1''', {'user_id': user_id} )
data = c.fetchone()
if data == None:
return None
return UserPosition(**dict(data))
class Pokemon(Data):
@classmethod
def _attrs(cls):
return [ 'id', 'name', 'internal_name', 'rarity']
@classmethod
def _insert(cls):
return '''INSERT INTO `pokemons` (id, name, internal_name, rarity)
VALUES (%(id)s,%(name)s,%(internal_name)s,%(rarity)s)
ON DUPLICATE KEY UPDATE
name = %(name)s,
internal_name = %(internal_name)s,
rarity = %(rarity)s'''
@classmethod
def all(cls):
c = DB.cursor()
c.execute('SELECT * from `pokemons` ORDER BY id ASC')
return map(Pokemon._make, c.fetchall())
@classmethod
def find(cls, pokeid):
cursor = DB.cursor()
cursor.execute('SELECT * from `pokemons` where id = %s', (pokeid,))
return Pokemon._make(cursor.fetchone())
@classmethod
def by_name(cls,name):
cursor = DB.cursor()
cursor.execute('SELECT * from `pokemons` where internal_name LIKE %s or name LIKE %s', (name,name))
return Pokemon._make(cursor.fetchone())
class LocationGroup(Data):
@classmethod
def _attrs(cls):
return ['id', 'name']
@classmethod
def _insert(cls):
return '''INSERT INTO `location_groups` (id, name)
VALUES ( %(id)s, %(name)s )
ON DUPLICATE KEY UPDATE
name = %(name)s'''
def add_location(self, name, lat, lng):
l = Location( location_group_id=self.id, name=name, latitude=lat, longitude=lng)
l.save()
def locations(self):
return Location.by_group(self.id)
@classmethod
def new(cls, name):
l = LocationGroup(name=name)
l.save()
return cls.find(name)
@classmethod
def all(cls):
c = DB.cursor()
c.execute("SELECT * from location_groups")
return map(LocationGroup._make, c.fetchall())
@classmethod
def find(cls, name):
cursor = DB.cursor()
cursor.execute('SELECT * from `location_groups` where name = %s LIMIT 1', (name,))
return LocationGroup._make(cursor.fetchone())
@classmethod
def by_id(cls, group_id):
cursor = DB.cursor()
cursor.execute('SELECT * from `location_groups` where id = %s LIMIT 1', (group_id,))
return LocationGroup._make(cursor.fetchone())
class Location(Data):
@classmethod
def _attrs(cls):
return ['id', 'location_group_id', 'name', 'latitude','longitude']
def _insert(cls):
return '''INSERT INTO `locations` (id, location_group_id, name, latitude, longitude)
VALUES (%(id)s, %(location_group_id)s, %(name)s, %(latitude)s, %(longitude)s )
ON DUPLICATE KEY UPDATE
location_group_id = %(location_group_id)s,
name = %(name)s,
latitude = %(latitude)s,
longitude = %(longitude)s'''
def group(self):
if not hasattr(self, '__group'):
self.__group = LocationGroup.by_id(self.location_group_id)
return self.__group
@classmethod
def by_group(cls, group_id):
c = DB.cursor()
c.execute("SELECT * FROM locations WHERE location_group_id=%s", (group_id,))
return map(Location._make, c.fetchall())
@classmethod
def find(cls, loc_id):
cursor = DB.cursor()
cursor.execute('SELECT * from `location` where id = %s LIMIT 1', (loc_id,))
return LocationGroup._make(cursor.fetchone())
Raw
pokedb_data.py
from pokedb import *
def insert_pokemon():
Pokemon( id=1, name='Bulbasaur', internal_name='BULBASAUR', rarity=2).save()
Pokemon( id=2, name='Ivysaur', internal_name='IVYSAUR', rarity=3).save()
Pokemon( id=3, name='Venusaur', internal_name='VENUSAUR', rarity=4).save()
Pokemon( id=4, name='Charmander', internal_name='CHARMANDER', rarity=3).save()
Pokemon( id=5, name='Charmeleon', internal_name='CHARMELEON', rarity=4).save()
Pokemon( id=6, name='Charizard', internal_name='CHARIZARD', rarity=4).save()
Pokemon( id=7, name='Squirtle', internal_name='SQUIRTLE', rarity=2).save()
Pokemon( id=8, name='Wartortle', internal_name='WARTORTLE', rarity=3).save()
Pokemon( id=9, name='Blastoise', internal_name='BLASTOISE', rarity=4).save()
Pokemon( id=10, name='Caterpie', internal_name='CATERPIE', rarity=1).save()
Pokemon( id=11, name='Metapod', internal_name='METAPOD', rarity=3).save()
Pokemon( id=12, name='Butterfree', internal_name='BUTTERFREE', rarity=4).save()
Pokemon( id=13, name='Weedle', internal_name='WEEDLE', rarity=1).save()
Pokemon( id=14, name='Kakuna', internal_name='KAKUNA', rarity=3).save()
Pokemon( id=15, name='Beedrill', internal_name='BEEDRILL', rarity=3).save()
Pokemon( id=16, name='Pidgey', internal_name='PIDGEY', rarity=1).save()
Pokemon( id=17, name='Pidgeotto', internal_name='PIDGEOTTO', rarity=2).save()
Pokemon( id=18, name='Pidgeot', internal_name='PIDGEOT', rarity=3).save()
Pokemon( id=19, name='Rattata', internal_name='RATTATA', rarity=1).save()
Pokemon( id=20, name='Raticate', internal_name='RATICATE', rarity=3).save()
Pokemon( id=21, name='Spearow', internal_name='SPEAROW', rarity=1).save()
Pokemon( id=22, name='Fearow', internal_name='FEAROW', rarity=3).save()
Pokemon( id=23, name='Ekans', internal_name='EKANS', rarity=2).save()
Pokemon( id=24, name='Arbok', internal_name='ARBOK', rarity=4).save()
Pokemon( id=25, name='Pikachu', internal_name='PIKACHU', rarity=3).save()
Pokemon( id=26, name='Raichu', internal_name='RAICHU', rarity=5).save()
Pokemon( id=27, name='Sandshrew', internal_name='SANDSHREW', rarity=3).save()
Pokemon( id=28, name='Sandslash', internal_name='SANDSLASH', rarity=5).save()
Pokemon( id=29, name='NidoranFem', internal_name='NIDORAN_FEMALE', rarity=2).save()
Pokemon( id=30, name='Nidorina', internal_name='NIDORINA', rarity=4).save()
Pokemon( id=31, name='Nidoqueen', internal_name='NIDOQUEEN', rarity=4).save()
Pokemon( id=32, name='NidoranMale', internal_name='NIDORAN_MALE', rarity=2).save()
Pokemon( id=33, name='Nidorino', internal_name='NIDORINO', rarity=4).save()
Pokemon( id=34, name='Nidoking', internal_name='NIDOKING', rarity=4).save()
Pokemon( id=35, name='Clefairy', internal_name='CLEFAIRY', rarity=2).save()
Pokemon( id=36, name='Clefable', internal_name='CLEFABLE', rarity=4).save()
Pokemon( id=37, name='Vulpix', internal_name='VULPIX', rarity=3).save()
Pokemon( id=38, name='Ninetales', internal_name='NINETALES', rarity=5).save()
Pokemon( id=39, name='Jigglypuff', internal_name='JIGGLYPUFF', rarity=3).save()
Pokemon( id=40, name='Wigglytuff', internal_name='WIGGLYTUFF', rarity=5).save()
Pokemon( id=41, name='Zubat', internal_name='ZUBAT', rarity=1).save()
Pokemon( id=42, name='Golbat', internal_name='GOLBAT', rarity=3).save()
Pokemon( id=43, name='Oddish', internal_name='ODDISH', rarity=2).save()
Pokemon( id=44, name='Gloom', internal_name='GLOOM', rarity=4).save()
Pokemon( id=45, name='Vileplume', internal_name='VILEPLUME', rarity=5).save()
Pokemon( id=46, name='Paras', internal_name='PARAS', rarity=2).save()
Pokemon( id=47, name='Parasect', internal_name='PARASECT', rarity=3).save()
Pokemon( id=48, name='Venonat', internal_name='VENONAT', rarity=2).save()
Pokemon( id=49, name='Venomoth', internal_name='VENOMOTH', rarity=3).save()
Pokemon( id=50, name='Diglett', internal_name='DIGLETT', rarity=3).save()
Pokemon( id=51, name='Dugtrio', internal_name='DUGTRIO', rarity=5).save()
Pokemon( id=52, name='Meowth', internal_name='MEOWTH', rarity=3).save()
Pokemon( id=53, name='Persian', internal_name='PERSIAN', rarity=4).save()
Pokemon( id=54, name='Psyduck', internal_name='PSYDUCK', rarity=3).save()
Pokemon( id=55, name='Golduck', internal_name='GOLDUCK', rarity=4).save()
Pokemon( id=56, name='Mankey', internal_name='MANKEY', rarity=3).save()
Pokemon( id=57, name='Primeape', internal_name='PRIMEAPE', rarity=4).save()
Pokemon( id=58, name='Growlithe', internal_name='GROWLITHE', rarity=3).save()
Pokemon( id=59, name='Arcanine', internal_name='ARCANINE', rarity=4).save()
Pokemon( id=60, name='Poliwag', internal_name='POLIWAG', rarity=2).save()
Pokemon( id=61, name='Poliwhirl', internal_name='POLIWHIRL', rarity=3).save()
Pokemon( id=62, name='Poliwrath', internal_name='POLIWRATH', rarity=5).save()
Pokemon( id=63, name='Abra', internal_name='ABRA', rarity=3).save()
Pokemon( id=64, name='Kadabra', internal_name='KADABRA', rarity=4).save()
Pokemon( id=65, name='Alakazam', internal_name='ALAKAZAM', rarity=5).save()
Pokemon( id=66, name='Machop', internal_name='MACHOP', rarity=3).save()
Pokemon( id=67, name='Machoke', internal_name='MACHOKE', rarity=4).save()
Pokemon( id=68, name='Machamp', internal_name='MACHAMP', rarity=5).save()
Pokemon( id=69, name='Bellsprout', internal_name='BELLSPROUT', rarity=2).save()
Pokemon( id=70, name='Weepinbell', internal_name='WEEPINBELL', rarity=3).save()
Pokemon( id=71, name='Victreebel', internal_name='VICTREEBEL', rarity=5).save()
Pokemon( id=72, name='Tentacool', internal_name='TENTACOOL', rarity=3).save()
Pokemon( id=73, name='Tentacruel', internal_name='TENTACRUEL', rarity=4).save()
Pokemon( id=74, name='Geodude', internal_name='GEODUDE', rarity=3).save()
Pokemon( id=75, name='Graveler', internal_name='GRAVELER', rarity=4).save()
Pokemon( id=76, name='Golem', internal_name='GOLEM', rarity=5).save()
Pokemon( id=77, name='Ponyta', internal_name='PONYTA', rarity=3).save()
Pokemon( id=78, name='Rapidash', internal_name='RAPIDASH', rarity=4).save()
Pokemon( id=79, name='Slowpoke', internal_name='SLOWPOKE', rarity=3).save()
Pokemon( id=80, name='Slowbro', internal_name='SLOWBRO', rarity=4).save()
Pokemon( id=81, name='Magnemite', internal_name='MAGNEMITE', rarity=3).save()
Pokemon( id=82, name='Magneton', internal_name='MAGNETON', rarity=5).save()
Pokemon( id=83, name='Farfetch\'d', internal_name='FARFETCHD', rarity=5).save()
Pokemon( id=84, name='Doduo', internal_name='DODUO', rarity=2).save()
Pokemon( id=85, name='Dodrio', internal_name='DODRIO', rarity=4).save()
Pokemon( id=86, name='Seel', internal_name='SEEL', rarity=3).save()
Pokemon( id=87, name='Dewgong', internal_name='DEWGONG', rarity=5).save()
Pokemon( id=88, name='Grimer', internal_name='GRIMER', rarity=4).save()
Pokemon( id=89, name='Muk', internal_name='MUK', rarity=5).save()
Pokemon( id=90, name='Shellder', internal_name='SHELLDER', rarity=3).save()
Pokemon( id=91, name='Cloyster', internal_name='CLOYSTER', rarity=5).save()
Pokemon( id=92, name='Gastly', internal_name='GASTLY', rarity=2).save()
Pokemon( id=93, name='Haunter', internal_name='HAUNTER', rarity=3).save()
Pokemon( id=94, name='Gengar', internal_name='GENGAR', rarity=5).save()
Pokemon( id=95, name='Onix', internal_name='ONIX', rarity=3).save()
Pokemon( id=96, name='Drowzee', internal_name='DROWZEE', rarity=3).save()
Pokemon( id=97, name='Hypno', internal_name='HYPNO', rarity=3).save()
Pokemon( id=98, name='Krabby', internal_name='KRABBY', rarity=2).save()
Pokemon( id=99, name='Kingler', internal_name='KINGLER', rarity=4).save()
Pokemon( id=100, name='Voltorb', internal_name='VOLTORB', rarity=3).save()
Pokemon( id=101, name='Electrode', internal_name='ELECTRODE', rarity=5).save()
Pokemon( id=102, name='Exeggcute', internal_name='EXEGGCUTE', rarity=3).save()
Pokemon( id=103, name='Exeggutor', internal_name='EXEGGUTOR', rarity=4).save()
Pokemon( id=104, name='Cubone', internal_name='CUBONE', rarity=3).save()
Pokemon( id=105, name='Marowak', internal_name='MAROWAK', rarity=4).save()
Pokemon( id=106, name='Hitmonlee', internal_name='HITMONLEE', rarity=4).save()
Pokemon( id=107, name='Hitmonchan', internal_name='HITMONCHAN', rarity=3).save()
Pokemon( id=108, name='Lickitung', internal_name='LICKITUNG', rarity=3).save()
Pokemon( id=109, name='Koffing', internal_name='KOFFING', rarity=3).save()
Pokemon( id=110, name='Weezing', internal_name='WEEZING', rarity=4).save()
Pokemon( id=111, name='Rhyhorn', internal_name='RHYHORN', rarity=3).save()
Pokemon( id=112, name='Rhydon', internal_name='RHYDON', rarity=4).save()
Pokemon( id=113, name='Chansey', internal_name='CHANSEY', rarity=4).save()
Pokemon( id=114, name='Tangela', internal_name='TANGELA', rarity=3).save()
Pokemon( id=115, name='Kangaskhan', internal_name='KANGASKHAN', rarity=5).save()
Pokemon( id=116, name='Horsea', internal_name='HORSEA', rarity=3).save()
Pokemon( id=117, name='Seadra', internal_name='SEADRA', rarity=4).save()
Pokemon( id=118, name='Goldeen', internal_name='GOLDEEN', rarity=3).save()
Pokemon( id=119, name='Seaking', internal_name='SEAKING', rarity=4).save()
Pokemon( id=120, name='Staryu', internal_name='STARYU', rarity=2).save()
Pokemon( id=121, name='Starmie', internal_name='STARMIE', rarity=4).save()
Pokemon( id=122, name='MrMime', internal_name='MR_MIME', rarity=4).save()
Pokemon( id=123, name='Scyther', internal_name='SCYTHER', rarity=3).save()
Pokemon( id=124, name='Jynx', internal_name='JYNX', rarity=3).save()
Pokemon( id=125, name='Electabuzz', internal_name='ELECTABUZZ', rarity=3).save()
Pokemon( id=126, name='Magmar', internal_name='MAGMAR', rarity=3).save()
Pokemon( id=127, name='Pinsir', internal_name='PINSIR', rarity=2).save()
Pokemon( id=128, name='Tauros', internal_name='TAUROS', rarity=3).save()
Pokemon( id=129, name='Magikarp', internal_name='MAGIKARP', rarity=2).save()
Pokemon( id=130, name='Gyarados', internal_name='GYARADOS', rarity=5).save()
Pokemon( id=131, name='Lapras', internal_name='LAPRAS', rarity=4).save()
Pokemon( id=132, name='Ditto', internal_name='DITTO', rarity=5).save()
Pokemon( id=133, name='Eevee', internal_name='EEVEE', rarity=2).save()
Pokemon( id=134, name='Vaporeon', internal_name='VAPOREON', rarity=4).save()
Pokemon( id=135, name='Jolteon', internal_name='JOLTEON', rarity=4).save()
Pokemon( id=136, name='Flareon', internal_name='FLAREON', rarity=5).save()
Pokemon( id=137, name='Porygon', internal_name='PORYGON', rarity=4).save()
Pokemon( id=138, name='Omanyte', internal_name='OMANYTE', rarity=3).save()
Pokemon( id=139, name='Omastar', internal_name='OMASTAR', rarity=5).save()
Pokemon( id=140, name='Kabuto', internal_name='KABUTO', rarity=3).save()
Pokemon( id=141, name='Kabutops', internal_name='KABUTOPS', rarity=5).save()
Pokemon( id=142, name='Aerodactyl', internal_name='AERODACTYL', rarity=4).save()
Pokemon( id=143, name='Snorlax', internal_name='SNORLAX', rarity=3).save()
Pokemon( id=144, name='Articuno', internal_name='ARTICUNO', rarity=5).save()
Pokemon( id=145, name='Zapdos', internal_name='ZAPDOS', rarity=5).save()
Pokemon( id=146, name='Moltres', internal_name='MOLTRES', rarity=5).save()
Pokemon( id=147, name='Dratini', internal_name='DRATINI', rarity=3).save()
Pokemon( id=148, name='Dragonair', internal_name='DRAGONAIR', rarity=4).save()
Pokemon( id=149, name='Dragonite', internal_name='DRAGONITE', rarity=4).save()
Pokemon( id=150, name='Mewtwo', internal_name='MEWTWO', rarity=5).save()
Pokemon( id=151, name='Mew', internal_name='MEW', rarity=5).save()
if __name__ == '__main__':
DB(wipe=True)
insert_pokemon()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment