def apply_evolution(username, position):
con = get_connection()
evolution_result = check_evolution_eligibility(username, position)
trade_evolution_result = check_trade_evolution_eligibility(
username, position)
if evolution_result is not None:
nickname = evolution_result[0]
id = evolution_result[3]
if nickname == evolution_result[1]:
nickname = evolution_result[2]
with con:
cur = con.cursor()
cur.execute("""
UPDATE userpokemon SET pokemon_id = %s, nickname = %s
WHERE username = %s AND position = %s
""", [id, nickname, username, position])
cur.close()
return nickname + " has evolved! Raise your Kappa s!!!"
elif trade_evolution_result is not None:
con = get_connection()
nickname = trade_evolution_result[0]
id = trade_evolution_result[3]
if nickname == trade_evolution_result[1]:
nickname = trade_evolution_result[2]
with con:
cur = con.cursor()
cur.execute("""
UPDATE userpokemon SET pokemon_id = %s, nickname = %s
WHERE username = %s AND position = %s
""", [id, nickname, username, position])
cur.close()
return nickname + " has evolved! Raise your Kappa s!!!"
else:
return "No Pokemon eligible for evolution."
def get_user_stats(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT wins, losses FROM users WHERE username = %s
""", [username])
user_data = cur.fetchone()
print "USER DATA", user_data
if len(user_data) > 0:
y = user_data[0]
z = user_data[1]
try:
ratio = int((float(y) / ((float(y) + float(z)))) * 100)
except:
if y > 0:
ratio = 100
else:
ratio = 0
response = "W{0}, L{1}, {2}%".format(
y, z, ratio)
else:
response = "You've got to actually battle first!"
cur.close()
return response
def pokemon_market_set():
con = get_connection()
with con:
base_price = 500
cur = con.cursor()
cur.execute("""DELETE FROM market""")
cur.execute("""ALTER TABLE market AUTO_INCREMENT=1""")
cur.close()
cur = con.cursor()
cur.execute("""SELECT id, rarity FROM pokemon""")
pokemon = cur.fetchall()[0:151]
cur.close()
selected_pokemon = []
rarity_index = {"0": 4, "1": 3, "2": 2, "3": 1}
def get_random_pokemon():
random_pokemon = random.choice(pokemon)
if random_pokemon[0] in selected_pokemon:
get_random_pokemon()
else:
selected_pokemon.append(random_pokemon[0])
return random_pokemon
for i in range(5):
random_pokemon = get_random_pokemon()
cur = con.cursor()
cur.execute("""
INSERT INTO market(pokemon_id, price, time) VALUES(%s, %s, %s)
""", [
random_pokemon[0], rarity_index[str(random_pokemon[1])] * base_price,
datetime.now()
])
cur.close()
def buy_items(id, username):
con = get_connection()
with con:
try:
if int(id) in (1, 2, 3, 4, 5, 11):
points = int(get_user_points(username))
value = int(get_item_value(id))
if points >= value:
cur = con.cursor()
cur.execute("""
INSERT INTO useritems (username, item_id, quantity)
VALUES (%s, %s, 1) ON DUPLICATE KEY
UPDATE quantity = quantity + 1""", [
username, id])
cur.execute("""
UPDATE users SET points = points - %s
WHERE username = %s""", [value, username])
cur.close()
return "Transaction successful."
else:
return "You need more points for that!"
else:
return "That is not a valid item position."
except Exception as error:
print error
return "item ID must be a number"
def sell_transaction(username):
con = get_connection()
seller = 'lorenzotherobot'
buyer = username
open_position = 5
with con:
cur = con.cursor()
cur.execute("""SET @seller = %s""", [seller])
cur.execute("""@position = %s""", [party_position])
cur.execute("""SET @buyer = %s""", [buyer])
cur.execute("""SET @position_free = %s""", [open_position])
cur.execute("""
SET @price = (
SELECT asking_price FROM userpokemon WHERE username = @owner
AND position = @position)""")
cur.execute("""
UPDATE userpokemon SET username = @buyer, for_sale = 0,
position = @position_free WHERE username = @seller
AND position = @position""")
cur.execute("""
UPDATE users SET points = points + @price
WHERE username = @seller""")
cur.execute("""
UPDATE users SET points = points - @price
WHERE username = @buyer""")
cur.execute("""COMMIT""")
cur.close()
def user_pokemon_types_summary(username, position):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""SELECT userpokemon.nickname AS 'Nickname',
type_primary.id as 'ID 1', type_secondary.id AS 'ID 2',
pokemon.name AS 'Name', type_primary.identifier AS 'Type 1',
type_secondary.identifier AS 'Type 2'
FROM userpokemon INNER JOIN pokemon
ON pokemon.id = userpokemon.pokemon_id inner
JOIN types AS type_primary
ON (type_primary.id = pokemon.type_primary)
LEFT OUTER JOIN types AS type_secondary
ON (type_secondary.id = pokemon.type_secondary)
WHERE username = %s AND userpokemon.position = %s""", [
username, position])
types_summary = cur.fetchone()
nickname = types_summary[0]
pokemon_type1_id = types_summary[1]
pokemon_type2_id = types_summary[2]
pokemon_name = types_summary[3]
pokemon_type1 = types_summary[4]
cur.close()
if types_summary[2] and types_summary[5] is not None:
pokemon_type2 = types_summary[5]
return nickname, pokemon_type1_id, pokemon_type2_id, pokemon_name, pokemon_type1, pokemon_type2
else:
pokemon_type2 = "No secondary type."
return nickname, pokemon_type1_id, 'none', pokemon_name, pokemon_type1, 'none'
def modify_points_all_users_timer(all_users, points_to_increment=1):
con = get_connection()
user_list_for_query = [(x, str(points_to_increment)) for x in all_users]
try:
with con:
cur = con.cursor()
dData = user_list_for_query # exact input you gave
sql = """
INSERT INTO users (username, points)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE points = points +
""" + str(points_to_increment)
cur.executemany(sql, ((user, points) for user, points in dData))
sql = """
INSERT INTO users (username, time_in_chat)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE time_in_chat = time_in_chat +
""" + str(5)
cur.executemany(sql, ((user, points) for user, points in dData))
cur.close()
return "success"
except Exception as error:
cur.close()
print "ERROR", error
return "Error incrementing points:" + str(error)
def add_loss(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
UPDATE users SET losses = losses + 1 WHERE username = %s
""", [username])
cur.close()
def set_battle_timestamp(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""UPDATE users SET lastbattle = %s WHERE username = %s""", [
datetime.now(), username])
cur.close()
def level_up_user_pokemon(username, position):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""update userpokemon set level = level + 1
where username = %s and position = %s
""", [username, position])
cur.close()
def get_item_value(id):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""SELECT value FROM items WHERE id = %s""", [id])
value = cur.fetchone()
cur.close()
return value[0]
def reset_trade_timestamp(time):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
UPDATE userpokemon SET for_trade = 0, time_trade_set = NULL
WHERE time_trade_set < %s
""", [time])
cur.close()
def get_last_battle(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""SELECT lastbattle from users WHERE username = %s""", [username])
last_battle = cur.fetchone()
cur.close()
return last_battle[0]
def add_to_blacklist(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""
INSERT INTO blacklist (id, username) VALUES (NULL, %s)
""", [username])
cur.close()
def modify_user_points(username, delta):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""INSERT INTO users (username, points)
VALUES (%s, %s) ON DUPLICATE KEY
UPDATE points = points + %s""", [username, delta, delta])
cur.close()
def remove_from_blacklist(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""
DELETE FROM blacklist WHERE username = %s
""", [username])
cur.close()
def update_nickname(nickname, username, position):
con = get_connection()
# TODO - error message on no entry
with con:
cur = con.cursor()
cur.execute("""
UPDATE userpokemon SET nickname = %s WHERE username = %s
AND position = %s""", [nickname, username, position])
cur.close()
def check_for_pokemon_for_sale():
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT userpokemon.username, pokemon.name, userpokemon.asking_price
FROM pokemon
LEFT JOIN userpokemon on userpokemon.pokemon_id = pokemon.id
WHERE for_sale = 1 and pokemon.name = %s""", [pokemon_query])
cur.close()
def check_items():
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT id, name, value FROM items WHERE id IN (1,2,3,4,5,11)
""")
for_sale = cur.fetchall()
cur.close()
return for_sale
def get_defender_multiplier(attacker_type, defender_type):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""SELECT * from types WHERE id = %s""", [defender_type])
defender_multipliers = cur.fetchone()
row_correction = attacker_type + 1
defender_effect = defender_multipliers[row_correction]
cur.close()
return defender_effect
def get_pokemon_id(username, position):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT userpokemon.pokemon_id WHERE username = %s and position = %s
""", [username, position])
pokemon_id = cur.fetchone()
cur.close()
return pokemon_id
def update_asking_price(username):
con = get_connection()
asking_price = 4000
with con:
cur = con.cursor()
cur.execute("""
UPDATE userpokemon SET asking_price = %s
WHERE username = %s AND position = %s""", [
asking_price, username, party_position])
cur.close()
def set_user_points(delta_user, delta):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""
UPDATE users SET points = %s
WHERE username = %s
""", [delta, delta_user])
cur.close()
def get_oauth(channel):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""
SELECT twitch_oauth FROM auth WHERE channel = %s
""", [channel])
oauth = cur.fetchone()
cur.close()
return oauth
def get_moderator(username, channel):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT username, channel FROM moderators
WHERE username = %s AND channel = %s
""", [username, channel])
moderator = cur.fetchone()
cur.close()
return moderator
def get_user_battle_info(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT userpokemon.position, userpokemon.level
FROM userpokemon WHERE username = %s
ORDER BY userpokemon.position""", [username])
party_members = cur.fetchall()
cur.close()
return party_members
def check_for_blacklist(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute(
"""
SELECT username FROM blacklist WHERE username = %s
""", [username])
user = cur.fetchone()
cur.close()
return user
def get_giver_trade_status(position, giver):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT asking_trade, asking_level FROM userpokemon
WHERE username = %s AND position = %s
""", [giver, position])
trader_party = cur.fetchall()
cur.close()
return trader_party
def show_all_pokemon_for_sale():
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT userpokemon.username
AS 'Owner', pokemon.name, userpokemon.asking_price
FROM userpokemon
INNER JOIN pokemon ON pokemon.id = userpokemon.pokemon_id
WHERE for_sale = 1;""")
cur.close()
def show_user_pokemon_for_sale(username):
con = get_connection()
with con:
cur = con.cursor()
cur.execute("""
SELECT userpokemon.username, pokemon.name, userpokemon.position
FROM userpokemon
INNER JOIN pokemon ON pokemon.id = userpokemon.pokemon_id
WHERE for_sale = 1 AND username = %s""", [
username])
cur.close()