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()
