def select_all(table): from db import conn, cur sql = "select * from " + table cur.execute(sql) conn.commit() lines = cur.fetchall() return lines
async def lookForNotes(event): if '/look@best_open_bot' == event.raw_text or '/look' in event.raw_text: cur.execute("""SELECT NAME FROM notes;""") rows = cur.fetchall() look = "" for r in rows: look += f"{r[0]}\n" await event.respond('There are all notes\n\n' + look)
def insert_team_stats(headers, team, team_stats, year): query = 'INSERT IGNORE INTO team_stats (team_id, year,' query += ', '.join(headers) query += ') VALUES (%d, %d,' query += ', '.join('%f' for x in headers) query += ')' values = (team['id'], year) + tuple(team_stats) cur.execute(query % values) db.commit()
def save_student(student_name, point, major, student_class, age): sql = """ insert into students(student_name, point, major, student_class, age) VALUES(%s, %s,%s,%s,%s); """ cur.execute(sql, (student_name, point, major, student_class, age)) conn.commit()
def insert_player_stats(headers, player_id, stats, year): query = 'INSERT IGNORE INTO player_stats (player_id, year,' query += ', '.join(headers) query += ') VALUES (%d, %d,' query += ', '.join('%f' for x in headers) query += ')' values = (player_id, year) + tuple(stats) cur.execute(query % values) db.commit()
def get_meanings(word): query = f"SELECT meaning FROM dictionary WHERE expression='{word.lower()}'" print(query) cur.execute(query) conn.commit() meanings = cur.fetchone() if meanings: meanings = meanings[0].replace('{', '').replace('}', '').split(',') return meanings
def _summary_dev_pass(): sql_script = """ SELECT devunit, count(*) count FROM details WHERE devunit != "" GROUP BY devunit ORDER BY count DESC LIMIT 10 """ cur.execute(sql_script) row = cur.fetchall() return row
def insert_team_players(rows, team_id, year): for row in rows: names = row[3].split(' ') first = names[0] last = ' '.join(names[1:]) query = 'INSERT IGNORE INTO player (id, first, last, num, position, height, weight, birthday, school) VALUES (%d, "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")' values = (row[12], first, last, row[4], row[5], row[6], row[7], row[8], row[11]) cur.execute(query % values) db.commit() query = "INSERT IGNORE INTO player_team_rel (player_id, team_id, year) VALUES (%d, %d, %d)" values = (row[12], team_id, year) cur.execute(query % values) db.commit()
def load_player_stats_schema(headers): query = ''' CREATE TABLE IF NOT EXISTS `player_stats` ( `player_id` int(16) NOT NULL, `year` int(4) NOT NULL, ''' for header in headers: query += '`%s` float,' % (header.lower()) query += ''' PRIMARY KEY (`player_id`, `year`) ) ENGINE=InnoDB ''' cur.execute(query)
async def set_message(self, from_chat_id: int, to_chat_id: int, from_message_id: int, to_message_id: int): """ :param from_chat_id: chat_id of the origin message. :param to_chat_id: chat_id of the message the bot just forwarded. :param from_message_id: message_id of the origin message. :param to_message_id: message_id of the message the bot just forwarded. :return: True """ cur.execute(f"INSERT INTO msgs_to_{to_chat_id} VALUES (?,?,?)", (from_chat_id, from_message_id, to_message_id)) conn.commit()
async def get_message_details(self, to_chat_id: int, to_message_id: int): """ :param to_chat_id: chat_id of the message the bot forwarded. :param to_message_id: message_id of the message the bot forwarded. :return: a tuple containing the chat_id and message_id of the origin chat. """ cur.execute( f"SELECT from_chat_id, from_message_id from msgs_to_{to_chat_id} WHERE to_message_id = ?", (to_message_id, )) data = cur.fetchone() if not data: raise ValueError("Message not registered.") else: return data
async def writeNote(event): if '/note@best_open_bot' in event.raw_text or '/note' in event.raw_text: text = event.raw_text.split('/note')[1] if text == '': await event.reply( 'Write name and note!\n\n/note <name_of_note> <note>') elif len(text.split(' ')) == 2: await event.reply('Write note!\n\n/note <name_of_note> <note>') else: name = text.split(' ')[1] note_text = text.split(name + ' ')[1] cur.execute( f"""INSERT INTO notes (NAME, TEXT) VALUES('{name}', '{note_text}');""" ) conn.commit() await event.reply('Note has been saved')
def getNewMessages(): cur.execute( ''' SELECT time_stamp, message_type, message FROM emergency_message WHERE reviewed = FALSE; ''' ) messages = cur.fetchall() message_count = len(messages) if message_count == 0: return False message_string = '%s new emergency messages have been added to the database!\n\n----------------------------------------\n' % ( str(message_count)) for message in messages: message_string += '''message time: {0}\n message type: {1}\n message: {2}\n------------------------------------\n '''.format( *message) cur.execute(''' UPDATE emergency_message SET reviewed = TRUE; ''') conn.commit() cur.close() conn.close() return message_string
async def removeNote(event): if '/remove@best_open_bot' in event.raw_text or '/remove' in event.raw_text: cur.execute("""SELECT NAME FROM notes;""") rows = cur.fetchall() if len(rows) == 0: await event.reply('There no notes') else: text = event.raw_text.split('/remove')[1] if text == '': await event.reply( 'Write name of note!\n\n/remove <name_of_note>') elif len(text.split(' ')) == 2: name = text.split(' ')[1] if name in str(rows): cur.execute( f"""DELETE FROM notes WHERE NAME = '{name}';""") await event.reply('Note has been removed') else: await event.reply('There no such note') else: await event.reply( "What are you doing?\n\n/remove <name_of_note>")
def saveXML(self): print('Storing new xml elements into the database') for insert_dict in self.insert_dict_list: insert_string = ''' INSERT INTO emergency_message (message_id, time_stamp, message_type, region_area, canceled_time_stamp, message) VALUES ('{messageId}', '{timestamp}', '{message_type}', '{regionArea}', '{canceledTimestamp}', '{message}' ) '''.format(**insert_dict) try: cur.execute(insert_string) except: print('duplicate entry') conn.commit()
async def getinfo(cid): async with aiohttp.ClientSession() as session: async with session.get(info_url + str(cid), headers = headers) as resp: # async with session.get(info_url + str(cid), proxy = proxylist[randint(0, len(proxylist)-1)]) as resp: soup = BeautifulSoup(await resp.text()) content = soup.find_all('td', class_ = 'cont') conts = [] for item in content: conts.append(item.string) if len(conts) != 0: i = cur.execute('select * from students where cid=' + conts[0]) if i != None: stu = [] stu.append(conts) insert_students(stu, cur)
def select_columns(table, column): sql = "select " + column + " from " + table cur.execute(sql) lines = cur.fetchall() return lines
def scrape_game_if_needed(team_name, row, year): game_id = row[1] cur.execute("SELECT id FROM game WHERE id = %s" % game_id) data = cur.fetchall() if len(data) == 0: scrape_game(game_id, team_name, year)
def del_student(id): sql = "delete from students where id=%s" cur.execute(sql,(id,)) conn.commit()
def save_users(email, password): sql = "insert into users(email, password)VALUES(%s, %s)" % (email, password) cur.execute(sql) conn.commit()
def insert_game_details(game_id, row): query = 'INSERT IGNORE INTO game (id, date, home_team_id, visitor_team_id) VALUES ("%s", "%s", %d, %d)' values = (game_id, row[0], row[6], row[7]) cur.execute(query % values) db.commit()
def select_email(email): sql = "select * from users where email= %s" cur.execute(sql,(email,)) lines = cur.fetchall() return lines
if len(conts) != 0: i = cur.execute('select * from students where cid=' + conts[0]) if i != None: stu = [] stu.append(conts) insert_students(stu, cur) async def bound_get(sem, cid): async with sem: await getinfo(cid) async def run(start, end): tasks = [] sem = asyncio.Semaphore(100) for cid in range(start, end): task = asyncio.ensure_future(bound_get(sem, cid)) tasks.append(task) resps = asyncio.gather(*tasks) await resps if __name__ == '__main__': loop = asyncio.get_event_loop() #future = asyncio.ensure_future(run(2013210001, 2013214859)) future = asyncio.ensure_future(run(2014211001, 2014212001)) #future = asyncio.ensure_future(run(2015210001, 2015215001)) #future = asyncio.ensure_future(run(2016210001, 2016215001)) #future = asyncio.ensure_future(run(2017210001, 2017215001)) loop.run_until_complete(future) cur.execute("SELECT COUNT(*) FROM STUDENTS;")
def insert_team_details(headers, row): query = "INSERT IGNORE INTO team (id, name, abbreviation) VALUES (%d, '%s', '%s')" values = (row[0], row[2], row[1]) cur.execute(query % values) db.commit()
def insert_game_player_stats(game_id, row): if row[8] != None: query = 'INSERT IGNORE INTO game_player_stats (game_id, player_id, start_position, comment, min, fgm, fga, fg_pct, fg3m, fg3a, fg3_pct, ftm, fta, ft_pct, oreb, dreb, reb, ast, stl, blk, `to`, pf, pts, plus_minus) VALUES ("%s", %d, "%s", "%s", "%s", %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f)' values = (game_id, row[4]) + tuple(row[6:]) cur.execute(query % values) db.commit()
def insert_game_team_stats(game_id, row): query = 'INSERT IGNORE INTO game_team_stats (game_id, team_id, min, fgm, fga, fg_pct, fg3m, fg3a, fg3_pct, ftm, fta, ft_pct, oreb, dreb, reb, ast, stl, blk, `to`, pf, pts, plus_minus) VALUES ("%s", %d, "%s", %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f, %f)' values = (game_id, row[1]) + tuple(row[5:]) cur.execute(query % values) db.commit()
def admin_doc_detail(): docId = '1030393A' cur.execute('SELECT * FROM docs WHERE id=?',(docId,)) row = cur.fetchone() return row
def clear_db(): cur.execute("UPDATE trd_chats SET chat_points = ?", (0,)) con.commit()
def migrate_chat(old_chat, new_chat): cur.execute( "UPDATE trd_chats SET chat_id = ? WHERE chat_id = ?", (new_chat, old_chat) ) con.commit()