def crawl_task(conn: Connection, cur: Cursor, contest: ContestListPage.Contest) -> bool: slug: str = contest.contest_slug cur.execute('SELECT COUNT(*) FROM tasks WHERE contest_slug = ?', (slug, )) count_result = cur.fetchone() exists_in_table = (count_result[0] > 0) if exists_in_table: print(f' -> There already exists in table') return False tlprr: TaskListPageRequestResult = TaskListPageRequestResult.create_from_request( slug) if tlprr.is_closed: print(f' -> Task list: 404') return True print(f' -> Task size: {len(tlprr.task_list_page.tasks)}') seq_of_parameters: List[TaskDBInsertData] = tlprr.generate_insert_data() cur.executemany('INSERT INTO tasks VALUES (?,?,?,' '?,?,?)', seq_of_parameters) conn.commit() return True
def crawl_contest(conn: Connection, cur: Cursor, contest: ContestListPage.Contest) -> None: slug: str = contest.contest_slug # 開始するページ番号の決定 cur.execute('SELECT MAX(pagenum) FROM submissions WHERE contest = ?', (slug, )) pagenum_max_result: Tuple[Optional[int]] = cur.fetchone() pagenum_max: Optional[int] = pagenum_max_result[0] pagenum: int = 1 if pagenum_max is not None: pagenum = pagenum_max + 1 # return while True: # ページ取得 result: SubmissionListPageRequestResult = SubmissionListPageRequestResult.create_from_request( slug, pagenum) # print(result) # exit() count_result: Tuple[Optional[int]] exists_in_table: bool if result.is_closed: print(f' -> Page {result.pagenum}: 404') # コンテスト情報挿入 cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?', (slug, )) count_result = cur.fetchone() exists_in_table = (count_result[0] == 1) if not exists_in_table: cur.execute('INSERT INTO contests VALUES (?,?,?,?,?,?)', (slug, contest.contest_name, contest.time_unix, int((contest.time + timedelta(minutes=contest.duration_minutes) ).timestamp()), 1, 1)) conn.commit() break else: print( f' -> Page {result.pagenum}: size={len(result.submission_list_page.submissions)}, ' f'min={result.submission_list_page.submissions[0].time}, max={result.submission_list_page.submissions[-1].time}' ) # コンテスト情報挿入 cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?', (slug, )) count_result = cur.fetchone() exists_in_table = (count_result[0] == 1) if not exists_in_table: cur.execute( 'INSERT INTO contests VALUES (?,?,?,?,?,?)', (slug, result.submission_list_page.contest_title, result.submission_list_page.contest_starttime_unix, result.submission_list_page.contest_endtime_unix, 0, 0)) # 提出情報挿入 seq_of_parameters: List[DBInsertData] = result.generate_insert_data() try: cur.executemany( 'INSERT INTO submissions VALUES (?,?,?,?,' '?,?,?,?,?,?,?,?,?,?)', seq_of_parameters) except sqlite3.Error as e: print(e) break conn.commit() # 最後のページなら抜ける if result.is_last_page: break pagenum += 1 time.sleep(3) cur.execute( 'UPDATE contests SET crawl_completed = 1 WHERE contest_slug = ?', (slug, )) conn.commit()