provide a script that crawl urls that exists in mainlink table in sqlite database and…
- User
provide a script that crawl urls that exists in main_link table in sqlite database and store html via following structure to a json file per each url: article body via selector: .article_body article comments via selector: .comment_list
also scrap .keywords links into a separated file named tags.json that should include link and anchor
- Assistant
Certainly! Below is a Python script that accomplishes the task you described. This script uses the
sqlite3module to interact with the SQLite database,requeststo fetch web pages,BeautifulSoupto parse HTML, andjsonto store the results.First, ensure you have the required libraries installed:
pip install requests beautifulsoup4Here's the script:
import sqlite3 import requests from bs4 import BeautifulSoup import json import os # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Query to get all URLs from the main_link table cursor.execute("SELECT url FROM main_link") urls = cursor.fetchall() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize a list to hold all keywords all_keywords = [] for url_row in urls: url = url_row[0] print(f"Crawling URL: {url}") try: # Fetch the URL response = requests.get(url) response.raise_for_status() # Ensure we notice bad responses # Parse the HTML content soup = BeautifulSoup(response.content, 'html.parser') # Extract article body article_body = soup.select_one('.article_body') article_body_text = article_body.get_text() if article_body else "" # Extract article comments article_comments = soup.select('.comment_list') comments = [comment.get_text() for comment in article_comments] # Store the extracted data in a JSON file article_data = { "article_body": article_body_text, "article_comments": comments } with open(f'articles/{url.replace("http://", "").replace("https://", "").replace("/", "_")}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) except requests.RequestException as e: print(f"Failed to crawl {url}: {e}") # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Close the database connection conn.close() print("Crawling completed.")Explanation:
- Database Connection: The script connects to an SQLite database and fetches all URLs from the
main_linktable. - Directory Creation: It creates directories
articlesandtagsto store the JSON files. - URL Crawling: For each URL, it fetches the web page, parses the HTML, and extracts the article body and comments using BeautifulSoup.
- JSON Storage: It stores the extracted data in JSON files within the
articlesdirectory. - Keyword Extraction: It extracts keywords (links and anchors) and stores them in a separate JSON file named
tags.json. - Error Handling: It includes basic error handling for network requests.
Make sure to replace
'your_database.db'with the actual path to your SQLite database. - Database Connection: The script connects to an SQLite database and fetches all URLs from the
- User
consider urls are more than 150000 review your code and make it more efficient
- Assistant
Handling a large number of URLs (over 150,000) requires optimizing the script for efficiency, especially in terms of memory usage and I/O operations. Here are some improvements:
- Batch Processing: Fetch and process URLs in batches to avoid loading all URLs into memory at once.
- Asynchronous Requests: Use
aiohttpfor asynchronous HTTP requests to speed up the crawling process. - Efficient JSON Writing: Write JSON files in batches to reduce I/O operations.
Below is the revised script with these optimizations:
import sqlite3 import asyncio import aiohttp from bs4 import BeautifulSoup import json import os # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize a list to hold all keywords all_keywords = [] async def fetch(session, url): async with session.get(url) as response: return await response.text() async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL html_content = await fetch(session, url) # Parse the HTML content soup = BeautifulSoup(html_content, 'html.parser') # Extract article body article_body = soup.select_one('.article_body') article_body_text = article_body.get_text() if article_body else "" # Extract article comments article_comments = soup.select('.comment_list') comments = [comment.get_text() for comment in article_comments] # Store the extracted data in a JSON file article_data = { "article_body": article_body_text, "article_comments": comments } with open(f'articles/{url.replace("http://", "").replace("https://", "").replace("/", "_")}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) except Exception as e: print(f"Failed to crawl {url}: {e}") async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Optimizations:
- Batch Processing: The script fetches URLs in batches of 1,000 to avoid loading all URLs into memory at once. This is done using
LIMITandOFFSETin the SQL query. - Asynchronous Requests: The script uses
aiohttpfor asynchronous HTTP requests, which allows multiple requests to be made concurrently, significantly speeding up the crawling process. - Efficient JSON Writing: JSON files are written immediately after processing each URL, reducing the need to hold all data in memory.
Ensure you have
aiohttpinstalled:pip install aiohttp beautifulsoup4This script should handle large numbers of URLs more efficiently.
- User
json file should include html tags not raw text
- Assistant
Got it! Below is the revised script that stores the HTML content (including tags) for the article body and comments, and also handles a large number of URLs efficiently using asynchronous requests.
import sqlite3 import asyncio import aiohttp from bs4 import BeautifulSoup import json import os # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize a list to hold all keywords all_keywords = [] async def fetch(session, url): async with session.get(url) as response: return await response.text() async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL html_content = await fetch(session, url) # Parse the HTML content soup = BeautifulSoup(html_content, 'html.parser') # Extract article body with HTML tags article_body = soup.select_one('.article_body') article_body_html = str(article_body) if article_body else "" # Extract article comments with HTML tags article_comments = soup.select('.comment_list') comments_html = [str(comment) for comment in article_comments] # Store the extracted data in a JSON file article_data = { "article_body": article_body_html, "article_comments": comments_html } with open(f'articles/{url.replace("http://", "").replace("https://", "").replace("/", "_")}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) except Exception as e: print(f"Failed to crawl {url}: {e}") async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- HTML Content: The script now extracts the HTML content for the article body and comments using
str(article_body)andstr(comment). - Asynchronous Requests: The script uses
aiohttpfor asynchronous HTTP requests to handle a large number of URLs efficiently. - Batch Processing: URLs are fetched and processed in batches to avoid loading all URLs into memory at once.
This script should now store the HTML content (including tags) for each article and handle a large number of URLs efficiently.
- HTML Content: The script now extracts the HTML content for the article body and comments using
- User
also consider retrying if url fetch failed
- Assistant
Certainly! Adding retry logic for failed URL fetches will make the script more robust. Below is the updated script with retry logic included:
import sqlite3 import asyncio import aiohttp from bs4 import BeautifulSoup import json import os # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize a list to hold all keywords all_keywords = [] MAX_RETRIES = 3 async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: return # Parse the HTML content soup = BeautifulSoup(html_content, 'html.parser') # Extract article body with HTML tags article_body = soup.select_one('.article_body') article_body_html = str(article_body) if article_body else "" # Extract article comments with HTML tags article_comments = soup.select('.comment_list') comments_html = [str(comment) for comment in article_comments] # Store the extracted data in a JSON file article_data = { "article_body": article_body_html, "article_comments": comments_html } with open(f'articles/{url.replace("http://", "").replace("https://", "").replace("/", "_")}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) except Exception as e: print(f"Failed to crawl {url}: {e}") async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Retry Logic: The
fetchfunction now includes retry logic. If a fetch fails, it will retry up toMAX_RETRIEStimes before giving up. - Error Handling: The script will print an error message if a URL fails to fetch after the maximum number of retries.
- Asynchronous Requests: The script continues to use
aiohttpfor asynchronous HTTP requests to handle a large number of URLs efficiently. - Batch Processing: URLs are fetched and processed in batches to avoid loading all URLs into memory at once.
This script should now be more robust and handle failed URL fetches by retrying them.
- Retry Logic: The
- User
also make it full compatible with windows os naming conventions
- Assistant
Certainly! To make the script fully compatible with Windows OS naming conventions, we need to ensure that the filenames do not contain characters that are invalid in Windows file systems (such as
<,>,:,",/,\,|,?,*).Below is the updated script with these considerations:
import sqlite3 import asyncio import aiohttp from bs4 import BeautifulSoup import json import os import re # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize a list to hold all keywords all_keywords = [] MAX_RETRIES = 3 # Function to sanitize filenames for Windows def sanitize_filename(filename): invalid_chars = r'[<>:"/\\|?*]' return re.sub(invalid_chars, '_', filename) async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: return # Parse the HTML content soup = BeautifulSoup(html_content, 'html.parser') # Extract article body with HTML tags article_body = soup.select_one('.article_body') article_body_html = str(article_body) if article_body else "" # Extract article comments with HTML tags article_comments = soup.select('.comment_list') comments_html = [str(comment) for comment in article_comments] # Sanitize the URL for use as a filename sanitized_url = sanitize_filename(url) # Store the extracted data in a JSON file article_data = { "article_body": article_body_html, "article_comments": comments_html } with open(f'articles/{sanitized_url}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) except Exception as e: print(f"Failed to crawl {url}: {e}") async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Filename Sanitization: The
sanitize_filenamefunction removes or replaces characters that are invalid in Windows file names. - Using Sanitized Filenames: The script uses the sanitized URL as the filename when saving the JSON files.
This script should now be fully compatible with Windows OS naming conventions.
- Filename Sanitization: The
- User
also create two files first is crawled.json that contains urls successfully crawled after each url scrap completed should append to there another one failed url on each execution crawled file should check to avoid recrawl a scraped url
- Assistant
Certainly! Below is the updated script that includes the functionality to maintain a
crawled.jsonfile for successfully crawled URLs and afailed.jsonfile for failed URLs. The script will check these files at the beginning of each execution to avoid re-crawling already processed URLs.import sqlite3 import asyncio import aiohttp from bs4 import BeautifulSoup import json import os import re # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the JSON files os.makedirs('articles', exist_ok=True) os.makedirs('tags', exist_ok=True) # Initialize lists to hold all keywords, crawled URLs, and failed URLs all_keywords = [] crawled_urls = [] failed_urls = [] MAX_RETRIES = 3 # Load crawled and failed URLs from JSON files if they exist if os.path.exists('crawled.json'): with open('crawled.json', 'r', encoding='utf-8') as f: crawled_urls = json.load(f) if os.path.exists('failed.json'): with open('failed.json', 'r', encoding='utf-8') as f: failed_urls = json.load(f) # Function to sanitize filenames for Windows def sanitize_filename(filename): invalid_chars = r'[<>:"/\\|?*]' return re.sub(invalid_chars, '_', filename) async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) return # Parse the HTML content soup = BeautifulSoup(html_content, 'html.parser') # Extract article body with HTML tags article_body = soup.select_one('.article_body') article_body_html = str(article_body) if article_body else "" # Extract article comments with HTML tags article_comments = soup.select('.comment_list') comments_html = [str(comment) for comment in article_comments] # Sanitize the URL for use as a filename sanitized_url = sanitize_filename(url) # Store the extracted data in a JSON file article_data = { "article_body": article_body_html, "article_comments": comments_html } with open(f'articles/{sanitized_url}.json', 'w', encoding='utf-8') as f: json.dump(article_data, f, ensure_ascii=False, indent=4) # Extract keywords keywords = soup.select('.keywords a') for keyword in keywords: link = keyword.get('href') anchor = keyword.get_text() all_keywords.append({"link": link, "anchor": anchor}) # Add the URL to the list of crawled URLs crawled_urls.append(url) with open('crawled.json', 'w', encoding='utf-8') as f: json.dump(crawled_urls, f, ensure_ascii=False, indent=4) except Exception as e: print(f"Failed to crawl {url}: {e}") failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break # Filter out already crawled and failed URLs urls = [url for url in urls if url[0] not in crawled_urls and url[0] not in failed_urls] if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Store all keywords in a separate JSON file with open('tags/tags.json', 'w', encoding='utf-8') as f: json.dump(all_keywords, f, ensure_ascii=False, indent=4) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Loading Crawled and Failed URLs: The script loads the
crawled.jsonandfailed.jsonfiles at the beginning to avoid re-crawling already processed URLs. - Saving Crawled and Failed URLs: The script appends successfully crawled URLs to
crawled.jsonand failed URLs tofailed.jsonafter each attempt. - Filtering URLs: Before processing a batch of URLs, the script filters out URLs that are already in the
crawled_urlsorfailed_urlslists. - Filename Sanitization: The script continues to sanitize filenames to be compatible with Windows OS naming conventions.
This script should now maintain a record of successfully crawled and failed URLs, avoiding re-crawling them in subsequent executions.
- Loading Crawled and Failed URLs: The script loads the
- User
i changed my mind and now i want entire page to be stored
- Assistant
Sure! Below is the updated script to store the entire HTML page for each URL, along with handling retries, maintaining a list of crawled and failed URLs, and ensuring compatibility with Windows OS naming conventions.
import sqlite3 import asyncio import aiohttp import json import os import re # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the HTML files os.makedirs('pages', exist_ok=True) # Initialize lists to hold crawled and failed URLs crawled_urls = [] failed_urls = [] MAX_RETRIES = 3 # Load crawled and failed URLs from JSON files if they exist if os.path.exists('crawled.json'): with open('crawled.json', 'r', encoding='utf-8') as f: crawled_urls = json.load(f) if os.path.exists('failed.json'): with open('failed.json', 'r', encoding='utf-8') as f: failed_urls = json.load(f) # Function to sanitize filenames for Windows def sanitize_filename(filename): invalid_chars = r'[<>:"/\\|?*]' return re.sub(invalid_chars, '_', filename) async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) return # Sanitize the URL for use as a filename sanitized_url = sanitize_filename(url) # Store the entire HTML page with open(f'pages/{sanitized_url}.html', 'w', encoding='utf-8') as f: f.write(html_content) # Add the URL to the list of crawled URLs crawled_urls.append(url) with open('crawled.json', 'w', encoding='utf-8') as f: json.dump(crawled_urls, f, ensure_ascii=False, indent=4) except Exception as e: print(f"Failed to crawl {url}: {e}") failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break # Filter out already crawled and failed URLs urls = [url for url in urls if url[0] not in crawled_urls and url[0] not in failed_urls] if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Storing Entire HTML Page: The script now stores the entire HTML content of each page in the
pagesdirectory. - Filename Sanitization: The
sanitize_filenamefunction ensures that the filenames are compatible with Windows OS naming conventions. - Loading and Saving Crawled/Failed URLs: The script loads and saves the
crawled.jsonandfailed.jsonfiles to keep track of which URLs have been successfully crawled or failed. - Filtering URLs: Before processing a batch of URLs, the script filters out URLs that are already in the
crawled_urlsorfailed_urlslists.
This script should now store the entire HTML page for each URL and handle retries, while avoiding re-crawling already processed URLs.
- Storing Entire HTML Page: The script now stores the entire HTML content of each page in the
- User
also now i store crawled urls in database crawled table
- Assistant
Sure! Below is the updated script to store the crawled URLs in a
crawledtable in the SQLite database, in addition to maintaining thecrawled.jsonandfailed.jsonfiles.import sqlite3 import asyncio import aiohttp import json import os import re # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the HTML files os.makedirs('pages', exist_ok=True) # Initialize lists to hold crawled and failed URLs crawled_urls = [] failed_urls = [] MAX_RETRIES = 3 # Load crawled and failed URLs from JSON files if they exist if os.path.exists('crawled.json'): with open('crawled.json', 'r', encoding='utf-8') as f: crawled_urls = json.load(f) if os.path.exists('failed.json'): with open('failed.json', 'r', encoding='utf-8') as f: failed_urls = json.load(f) # Function to sanitize filenames for Windows def sanitize_filename(filename): invalid_chars = r'[<>:"/\\|?*]' return re.sub(invalid_chars, '_', filename) async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) return # Sanitize the URL for use as a filename sanitized_url = sanitize_filename(url) # Store the entire HTML page with open(f'pages/{sanitized_url}.html', 'w', encoding='utf-8') as f: f.write(html_content) # Add the URL to the list of crawled URLs crawled_urls.append(url) with open('crawled.json', 'w', encoding='utf-8') as f: json.dump(crawled_urls, f, ensure_ascii=False, indent=4) # Store the URL in the database cursor.execute("INSERT INTO crawled (url) VALUES (?)", (url,)) conn.commit() except Exception as e: print(f"Failed to crawl {url}: {e}") failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break # Filter out already crawled and failed URLs urls = [url for url in urls if url[0] not in crawled_urls and url[0] not in failed_urls] if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Ensure the 'crawled' table exists cursor.execute(""" CREATE TABLE IF NOT EXISTS crawled ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL UNIQUE ) """) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Database Table Creation: The script ensures that the
crawledtable exists in the SQLite database. If it doesn't, it creates it. - Storing Crawled URLs in Database: After successfully crawling a URL, the script inserts the URL into the
crawledtable in the database. - Filename Sanitization: The
sanitize_filenamefunction ensures that the filenames are compatible with Windows OS naming conventions. - Loading and Saving Crawled/Failed URLs: The script continues to load and save the
crawled.jsonandfailed.jsonfiles to keep track of which URLs have been successfully crawled or failed. - Filtering URLs: Before processing a batch of URLs, the script filters out URLs that are already in the
crawled_urlsorfailed_urlslists.
This script should now store the entire HTML page for each URL, maintain a record of crawled and failed URLs in JSON files, and store crawled URLs in the SQLite database.
- Database Table Creation: The script ensures that the
- User
what if file with that name exists?
- Assistant
To handle the case where a file with the same name already exists, we can add a mechanism to append a unique identifier (such as a timestamp) to the filename to ensure uniqueness. Below is the updated script with this addition:
import sqlite3 import asyncio import aiohttp import json import os import re from datetime import datetime # Connect to the SQLite database conn = sqlite3.connect('your_database.db') cursor = conn.cursor() # Create directories to store the HTML files os.makedirs('pages', exist_ok=True) # Initialize lists to hold crawled and failed URLs crawled_urls = [] failed_urls = [] MAX_RETRIES = 3 # Load crawled and failed URLs from JSON files if they exist if os.path.exists('crawled.json'): with open('crawled.json', 'r', encoding='utf-8') as f: crawled_urls = json.load(f) if os.path.exists('failed.json'): with open('failed.json', 'r', encoding='utf-8') as f: failed_urls = json.load(f) # Function to sanitize filenames for Windows def sanitize_filename(filename): invalid_chars = r'[<>:"/\\|?*]' return re.sub(invalid_chars, '_', filename) # Function to generate a unique filename def generate_unique_filename(base_filename): timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")[:-3] # Remove last 3 digits of microseconds return f"{base_filename}_{timestamp}.html" async def fetch(session, url, retries=0): try: async with session.get(url) as response: response.raise_for_status() # Ensure we notice bad responses return await response.text() except aiohttp.ClientError as e: if retries < MAX_RETRIES: print(f"Retrying {url} (attempt {retries + 1})") return await fetch(session, url, retries + 1) else: print(f"Failed to fetch {url} after {MAX_RETRIES} attempts: {e}") return None async def process_url(session, url): print(f"Crawling URL: {url}") try: # Fetch the URL with retries html_content = await fetch(session, url) if not html_content: failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) return # Sanitize the URL for use as a base filename sanitized_url = sanitize_filename(url) # Generate a unique filename unique_filename = generate_unique_filename(sanitized_url) # Store the entire HTML page with open(f'pages/{unique_filename}', 'w', encoding='utf-8') as f: f.write(html_content) # Add the URL to the list of crawled URLs crawled_urls.append(url) with open('crawled.json', 'w', encoding='utf-8') as f: json.dump(crawled_urls, f, ensure_ascii=False, indent=4) # Store the URL in the database cursor.execute("INSERT INTO crawled (url) VALUES (?)", (url,)) conn.commit() except Exception as e: print(f"Failed to crawl {url}: {e}") failed_urls.append(url) with open('failed.json', 'w', encoding='utf-8') as f: json.dump(failed_urls, f, ensure_ascii=False, indent=4) async def main(): # Query to get all URLs from the main_link table in batches batch_size = 1000 offset = 0 while True: cursor.execute("SELECT url FROM main_link LIMIT ? OFFSET ?", (batch_size, offset)) urls = cursor.fetchall() if not urls: break # Filter out already crawled and failed URLs urls = [url for url in urls if url[0] not in crawled_urls and url[0] not in failed_urls] if not urls: break async with aiohttp.ClientSession() as session: tasks = [process_url(session, url[0]) for url in urls] await asyncio.gather(*tasks) offset += batch_size # Ensure the 'crawled' table exists cursor.execute(""" CREATE TABLE IF NOT EXISTS crawled ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT NOT NULL UNIQUE ) """) # Run the main function asyncio.run(main()) # Close the database connection conn.close() print("Crawling completed.")Explanation of Changes:
- Unique Filename Generation: The
generate_unique_filenamefunction appends a timestamp to the sanitized URL to ensure that the filename is unique. - Storing HTML Content: The script uses the unique filename when saving the HTML content to avoid overwriting existing files.
- Database and JSON File Updates: The script continues to update the
crawled.jsonfile and thecrawledtable in the database with the successfully crawled URLs.
This script should now handle the case where a file with the same name already exists by appending a unique identifier to the filename.
- Unique Filename Generation: The
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.