def add_command(command, response, admin_only): """Adds a command to the database""" if not get_command(command): cursor = database.get_db().cursor() cursor.execute("INSERT INTO commands (command, response, admin_only) VALUES (?, ?, ?)", (command, response, admin_only, )) database.get_db().commit() return "Command {} has been added".format(command) return "Command already exists"
def remove_command(command): """Removes command from the database""" if get_command(command): cursor = database.get_db().cursor() cursor.execute("DELETE from commands where command = ?", (command, )) database.get_db().commit() return "Command {} has been removed".format(command) return False
def store(teams, division): db = get_db() cursor = db.cursor() cursor.execute('DELETE FROM division WHERE label=%s', division) cursor.execute('INSERT INTO division set label=%s', division) division_id = cursor.lastrowid # clear old version of teams using that division label #cursor.execute('DELETE FROM team WHERE division_id=%s', division_id) # should be handled by foreign key constraint for team in teams: team['division_id'] = division_id cursor.execute('DESCRIBE team') fields = set((x[0] for x in cursor.fetchall())) for team in teams: keys = [] values = [] for k, v in team.items(): if k in fields: keys.append(k) values.append(v) placeholder = ", ".join(["%s"] * len(keys)) stmt = "insert into `{table}` ({columns}) values ({values});".format(table='team', columns=",".join(keys), values=placeholder) cursor.execute(stmt, list(values)) db.commit()
def check_if_admin(group=None, user_id=None): """Returns true or false based if the user is an admin in the group""" cursor = database.get_db().cursor() data = cursor.execute('SELECT DISTINCT m.member_jid from memberTable as m, KIKcontactsTable as c where m.is_admin = 1 and m.group_id = ? and m.member_jid = ?', (group, user_id, )).fetchone() if data: return True return False
def _process_form(): name = request.form['name'] location = request.form['location'] database = get_db() database.execute( f'insert into users (name, location) values ("{name}", "{location}")' ) database.commit() return f'Added user: {name} from {location}'
def show_commands(): """Shows all the available commands""" cursor = database.get_db().cursor() result = "" data = cursor.execute('SELECT * from commands').fetchall() result = "Commands that are available: \n" for command in data: result += "{} {} {} \n".format(command[1], command[2], command[3]) return result;
def getGames(division): header = [('GameNum', 'Date', 'Time', 'FieldID', 'HomeTeam', 'AwayTeam', 'RoundTypeCode')] db = get_db() cursor = db.cursor() cursor.execute('''SELECT 0, DATE_FORMAT(gamedate, "%%m/%%d/%%Y") as date, TIME_FORMAT(gametime, "%%T") as time, 0, home, away, "B" FROM game WHERE agegroup=%s AND archive IS NULL''', division) return header + list(cursor.fetchall())
def update_venue(division): db = get_db() cursor = db.cursor() cursor.execute('SELECT name, id FROM venue') venues = cursor.fetchall() print(venues) cursor.execute( '''SELECT team.id, name, declaredvenue, venue FROM team LEFT JOIN division ON division.id=division_id WHERE venue_id IS NULL AND division.label=%s ORDER BY name''', division) updates = [] print('\n\n') for i, (tid, team_name, decl, venue) in enumerate(cursor.fetchall()): # choose "venue" or "declared venue", with preference to "venue" target = venue or decl scores = [] if target: for venue_name, venue_id in venues: scores.append( (fuzz.partial_ratio(target, venue_name), venue_id, venue_name)) scores.sort(reverse=True) if scores and scores[0][0] > 90: updates.append((tid, scores[0][1])) print( f'{i+1:2}. Matched "{team_name}" using "{target}" with "{scores[0][2]}"\n' ) elif target is None: print(f'{i+1:2}. Error: No declared venue for "{team_name}"') else: print( f'{i+1:2}. Error: No good matches for "{team_name}" using "{target}"' ) print(' Best matches are:') for best in scores[:3]: print(' ', best[2])
def read_new_messages(app=None): """Reads new messages based on latest timestamp and checks for commands""" global last_pull, lurker_mode with app.app_context(): cursor = database.get_db().cursor() group = '*****@*****.**' data = cursor.execute('SELECT c.display_name, m.body, m.timestamp, m.partner_jid FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid and m.timestamp > ? order BY m.timestamp ASC;', (group, int(last_pull), )).fetchall() for line in data: if not line[1] is None: command = line[1].lower() result = get_command(command) if result and not lurker_mode: if not result[1]: send_message(result[2]) elif check_if_admin(group, line[3]): send_message(result[2]) if check_if_admin(group, line[3]): if '!add' in command: new_command = command.split(' ')[1] admin_only = command.split(' ')[2] response = " ".join(command.split(' ')[3:]) query = add_command(new_command, response, admin_only) send_message(query) elif '!remove' in command: remove = command.split(' ')[1] response = remove_command(remove) if response: send_message(response) elif '!show' in command: send_message(show_commands()) if command == "!lurk": lurker_mode = not lurker_mode if lurker_mode: send_message("Lurking mode enabled") else: send_message("Lurking mode disabled")
def pull_db(app=None): """Pulls the database from the phone and merges it with our own database""" global last_pull with app.app_context(): cursor = database.get_db().cursor() group = '*****@*****.**' last_pull = int(cursor.execute('SELECT MAX(m.timestamp) FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid order BY m.timestamp ASC;', (group, )).fetchone()[0]) print ("PULLING DATA") call(["adb", "pull", "/data/data/kik.pikek/databases/kikDatabase.db", "./databases"]) db_con = sqlite3.connect('databases/kikDatabase.db') with io.open(config.DATA_BASE_DIR + 'dump.sql', 'w', encoding='utf8') as f: for line in db_con.iterdump(): if 'CREATE TABLE' in line: line = line.replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS') if 'INSERT INTO' in line: line = line.replace('INSERT INTO', 'INSERT OR IGNORE INTO ') f.write('%s\n' % line) db_con.close() f = io.open(config.DATA_BASE_DIR + 'dump.sql','r', encoding='utf8') command = f.read() f.close() cursor.executescript(command) os.remove(config.DATA_BASE_DIR + 'kikDatabase.db') read_new_messages(app)
def get_all_venues(): db = get_db() cursor = db.cursor() cursor.execute('SELECT id, name FROM venue ORDER BY name') return cursor.fetchall()
return cursor.fetchall() def get_params(): parser = ArgumentParser() parser.add_argument('--check', action='store') parser.add_argument('--show', action='store_true') parser.add_argument('-c', action='store') parser.add_argument('-v', action='store', type=int) params = parser.parse_args() return params if __name__ == '__main__': db = get_db() args = get_params() if args.check: teams = get_teams(db, args.check) venues = get_venues(db, teams) if not venues: if args.show: for vid, name in get_all_venues(): print(f'{vid:3} {name}') print('Assign a venue to the missing club') if args.c: if not args.v: print('Provide a venue ID to be assigned to the club') else: cursor = db.cursor() c_id = cursor.execute('INSERT INTO club SET name=%s', args.c)
def get_command(command): """returns a single command""" cursor = database.get_db().cursor() return cursor.execute('SELECT command, admin_only, response FROM commands where command = ?', (command, )).fetchone()
def group_chat(group=None): c = database.get_db().cursor() # data = c.execute('select display_name, jid FROM KIKcontactsTable WHERE jid like "%groups.kik.com%"').fetchall() data = c.execute('SELECT c.display_name, m.body, m.timestamp FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid order BY m.timestamp ASC;', (group, )).fetchall() return render_template('group.html' , data=data)
def home(): c = database.get_db().cursor() data = c.execute('select display_name, jid FROM KIKcontactsTable WHERE jid like "%groups.kik.com%" and display_name != "none"').fetchall() # data = c.execute('SELECT c.display_name, m.body, m.timestamp FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = "*****@*****.**" and m.partner_jid = c.jid order BY m.timestamp ASC;').fetchall() return render_template('home.html' , data=data)