Discussion forum post scraper

Post Scraper is a Python based web scraper that can scrape the text-only data of online discussion forums relating to a list of apps and save that data to MySQL database, with keywords and sentiment analysis.

The purpose is to create a table where each row contains:
1) Name of the app,
2) URL crawled,
3) Date when the topic was started within the discussion forum,
4) Scraped text-only content (i.e. the online discussion forum comments from users commenting about the app),
5) Extracted keywords,
6) Overall sentiment of the discussion, e.g. positive, neutral or negative.

The implementation shall be based on Python’s Beautiful Soup, KeyBERT for the keyword extraction and VADER or other similar library for sentiment analysis. Note: All the used libraries must be such that can be used locally, i.e. nothing remote API based.

If the discussion forum requires JavaScript to work, or it is protected against scraping, then such forum does not need to be supported (i.e. no Selenium)

The data analysis of the scraped forum content must be generic, so it will support all of the example forums (see below), but also other forums that use the same forum software (e.g. phpBB, vBulletin or XenForo).

When saving the text content, i.e. the discussion relating to the apps, only the first page of the discussion shall be saved (i.e. the sub pages of discussion are not crawled or saved), and the text content of the discussion shall be saved without any html or bbCode tags, and without username or other metadata.

When determining whether a discussion is related to any apps within the apps table, only the topic (headline) of the discussion shall be analyzed and all topics without any app name matches shall be ignored and not analyzed.

The Python script must work when run from Windows and from Linux host. With the final delivery, please also include the pip etc calls required to install all used Python libraries, and SQL calls to create all the used MySQL tables if different from below. Please discuss and agree with me any changes to the database table structures.
Suggested database structure

I suggest the following MySQL database tables:

Post_scraper_input_urls shall contain the high level starting URLs:

CREATE TABLE `post_scraper_input_urls` (
`id` INT NOT NULL AUTO_INCREMENT,
`url` VARCHAR(512),
UNIQUE KEY `url-idx` (`url`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

For testing purposes, we shall assume this table contains rows:

https://www.elevenforum.com/questions/apps-software/
https://portableapps.com/forums/general/general_discussion
https://malwaretips.com/forums/other-software.207/
https://www.portablefreeware.com/forums/viewforum.php?f=15
https://forums.tomshardware.com/forums/apps-and-software.27/
https://www.sevenforums.com/software/
https://forums.anandtech.com/forums/windows.10/
https://www.bleepingcomputer.com/forums/f/57/all-other-applications/


Post_scraper_input_apps shall contain the names of apps:

CREATE TABLE `post_scraper_input_apps` (
`id` INT NOT NULL AUTO_INCREMENT,
`app` VARCHAR(512),
UNIQUE KEY `app-idx` (`app`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

For testing purposes, we shall assume this table contains rows:

Notepad++
Revo Uninstaller
Firefox
Asana
Google Chrome
uTorrent
Spotify
Pinta
Adobe Photoshop



Post_scraper_queue shall contain all the URLs. This table is used to store the queue of not yet crawled URLs to be crawled in the future (i.e. `crawled` = NULL), and all the already crawled URLs in order to not to crawl these URLs again (i.e. `crawled` NOT NULL)

CREATE TABLE `post_scraper_queue` (
`id` INT NOT NULL AUTO_INCREMENT,
`url` VARCHAR(512),
`updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`crawled` TIMESTAMP DEFAULT NULL,
UNIQUE KEY `url-idx` (`url`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;



Post_scraper_data shall contain the results of the scraping.

CREATE TABLE `post_scraper_data` (
`id` INT NOT NULL AUTO_INCREMENT,
`url` VARCHAR(512),
`updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`post_content` TEXT,
`post_date` DATE,
`post_sentiment` INT,
`post_keywords` VARCHAR(512),
UNIQUE KEY `url-idx` (`url`),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Note: The post_sentiment here is defined as INT. I’m suggesting that we define that if post_sentiment < 0 then the sentiment of the discussion relating to this app is negative, if post_sentiment >= 0 AND post_sentiment <= 100 then discussion is neutral and if post_sentiment > 100 then discussion is positive. However, if you wish to save the sentiment in some other format, that is also possible, but please confirm with me before doing so.

The post_keywords shall be the list of extracted keywords, separated by comma.

Please implement a hard coded minimum and maximum lengths of post_content. Let’s define MIN_POST_CONTENT = 200 and MAX_POST_CONTENT = 6000 characters.

The post_content shall contain the text only content without any html or other formatting tags, and without any metadata (e.g. usernames, post times, user signatures) of the entire discussion’s first page.