\n
\ud83d\udd78\ufe0f <\/span><\/span>The Challenge: Scraping a CMS Blog without an API<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
I recently faced this exact dilemma: scraping blog data from our CMS, which lacked an API. My trusty Python script, armed with BeautifulSoup (BS4) and Selenium, was ready for battle. Tackling the dynamic intricacies of web pages, especially in the context of pagination and the dynamic loading of client-side content (thanks to AJAX and JavaScript), posed a distinctive set of hurdles.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\ud83d\udee0\ufe0f <\/span><\/span>Configuring UserAgent, Sessions, TLS, Referrer, and Chrome options<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
Setting up the basic script was a smooth sailing. The excitement kicked in when I started exploring the DOM structure of the web pages, pinpointing the specific HTML tag that housed the data I needed for this project.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
The sight when any of my Python scripts first render web scraped data as list item is always such a joy and never gets old. It\u2019s that satisfying feeling of problem solved!<\/span><\/span><\/span><\/p>\n<\/div>\n\n
driver.find_element(By.TAG_NAME, 'tbody').find_elements(By.CLASS_NAME, 'profile_groups')\n\nload_more = driver.find_element(By.XPATH, \n '\/\/*[@id=\"_profilegroup\"]\/div[2]\/div\/div[2]\/div\/div\/button') \nload_more.click()<\/span><\/span><\/pre>\n<\/div>\n\n
\ud83d\udd75\ufe0f <\/span><\/span>The Webpage DOM and the \u201cLoad More\u201d Conundrum<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
In this case though, the joy of seeing the first set of rows loaded was short-lived! Things got interesting as I hunted down the elusive \u201cLoad More\u201d button, aiming to grab additional rows dynamically before I can read and store the data in a Pandas DataFrame.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
Attempting to load all the data in one go turned out to be a problem, causing memory overload and web page crashes. With thousands of rows, my script started to buckle under the weight of the data.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
Clearly this option of first loading all the data on the webpage and then scraping was not going to work. Traditional methods of storing everything in a Pandas DataFrame after full scraping is done or directly writing to a database like Snowflake were simply too resource-intensive.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\ud83e\udd86<\/span><\/span> DuckDB comes to rescue with Plan B<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
So, for my Plan B, I devised a strategy to leverage DuckDB. First, I would load the initial batch of rows, scrape the data, and then stash it away in storage. After that, I\u2019d load the next batch of rows on the webpage and repeat these steps, employing 2 loops. The key differentiator in this approach was to use Pandas DataFrame with a companion, DuckDB for the iterative inserts and continuous updates.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
To handle 2 different loop iterations in resonance, I had to come up with an interesting algorithm because one of the loops in my Python script was running on page load sequence but with every \u201cLoad More\u201d click, the secondary loop was loading additional rows. These added rows were stitched onto the ongoing sequence from the very start of the webpage load, not kicking off a fresh sequence. While one loop had to kick off anew, the other needed to pick up right where the first loop had left off.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
Wrapping my head around and implementing this puzzle piece turned out to be quite a delightful journey!<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\n
<\/p>\nThe mighty \u2018f\u2019 strings came to the rescue here and I could make the XPATH of the key elements dynamic \u2013 \u201ctr[{counter + 1}]\u201d.<\/span><\/span><\/span><\/p>\n<\/span><\/p><\/blockquote>\n<\/div>\n<\/div>\n
\n
if profiles: \n if len(profiles[starting_point:ending_point]) > 0: \n logging.info(f'beginning extraction of profile data for iteration no: {i}') \n for item in profiles[starting_point:ending_point]: \n \n # company \n try: \n item.find_elements(By.XPATH, \n f'\/\/*[@id=\"_profile\"]\/div[2]\/div\/div\/div\/table\/tbody\/tr[{counter + 1}]') \n except NoSuchElementException as err: \n logging.info(f'could not locate element for company on iteration no. {i}') \n company = '' \n else: \n company = item.find_element(By.XPATH, \n f'\/\/*[@id=\"_profile\"]\/div[2]\/div[2]\/div\/table\/tbody\/tr[{counter + 1}]').text \n \n # domain \n try: \n item.find_element(By.XPATH, \n f'\/\/*[@id=\"_profile\"]\/div[2]\/div[2]\/div\/div\/table\/tbody\/tr[{counter + 1}]') \n except NoSuchElementException as err: \n logging.info(f'could not locate element for domain on iteration no. {i}') \n domain = '' \n else: \n domain = item.find_element(By.XPATH, \n f'\/\/*[@id=\"_profile\"]\/div[2]\/div[2]\/div\/table\/tbody\/tr[{counter + 1}]').text<\/span><\/span><\/pre>\n<\/div>\n\n
This kind of algorithm and data volumes involve some serious input\/output (I\/O) action. Using just Pandas DataFrame to store this data incrementally in a CSV file or a database like Snowflake would burn a hole in my resources.<\/span><\/span><\/p>\n<\/div>\n\n
You see, dynamic and ongoing web scraping Python scripts have a tendency to get shaky, especially when faced with webpage refreshes and shaky internet connections. Given the hefty load of data and the repetitive looping through the loaded rows, I needed a solution to effortlessly fetch and store the data one iteration at a time, considering I had over 7500 iterations on my hands. And here\u2019s where DuckDB stepped in!<\/span><\/span><\/p>\n<\/div>\n\n
\ud83d\ude80 <\/span><\/em><\/strong>Harnessing DuckDB for Stability in Python-Based Web Scraping<\/span><\/strong><\/span><\/p>\n<\/div>\n\n
As part of my script, I initialized a DuckDB database and created a table with the relevant columns. Once a selenium iteration (as shown above) was finished successfully, I loaded the set of rows scraped into a DataFrame via a list of dictionaries. Once the DataFrame was built, I then inserted all the rows in the DataFrame into DuckDB table and clear out the DataFrame for next set of rows being scraped by Selenium Python script.<\/span><\/span><\/p>\n<\/div>\n\n
The primary objective of using this approach was to avoid the instability associated with Selenium scripts when it comes to scraping a massive database from a dynamic (AJAX \/ JavaScript) based web page and avoid Snowflake credit usage associated with such I\/O operations during the development cycle.<\/span><\/span><\/p>\n<\/div>\n\n
if company != '': \n \n try: \n str(company).strip() \n except: \n logging.info(f'company name clean-up not possible for iteration {i} and {counter}') \n else: \n company = str(company).strip() \n try: \n str(domain).strip() \n except: \n logging.info(f'domain name clean-up not possible for iteration {i} and {counter}') \n else: \n domain = str(domain).strip() \n try: \n str(title).strip() \n except: \n logging.info(f'title clean-up not possible for iteration {i} and {counter}') \n else: \n title = str(title).strip() \n \n extract.append( \n { \n 'company': company, \n 'title': title, \n 'domain': domain \n } \n ) \n \n logging.info(f'successfully extracted data for profile no: {counter + 1}') \n \n counter += 1 \n logging.info(f'successfully finished extraction for all profiles data for iteration no: {i}') \n else: \n logging.info(f'no profiles found in the iteration no: {i}') \n \n if len(contact_extract) > 0: \n df = pandas.DataFrame(data=extract) \n \n load_duckdb(df) \n \n logging.info(f'successfully loaded set no: {i} into duckDB.')<\/span><\/span><\/pre>\n<\/div>\n\n
In the realm of such Python web scraping scripts that stretch over hours,<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\n
<\/p>\ncustom Python logs emerged as unsung heroes, not only in keeping tabs on the script\u2019s ongoing status but also proving handy when things go south,<\/span><\/span><\/span><\/p>\n<\/span><\/p><\/blockquote>\n<\/div>\n<\/div>\n
\n
and Python scripts decide to play hide and seek with errors. In a project-first, I opted to store these logs in a separate JSON file, just for the fun of it.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
If only, I could do the same with SQL CTEs!<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\ud83e\uddf9 <\/span><\/span>Seamless Data Transition and Cleanup in Pandas<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
\n
<\/p>\nAfter finishing up the scraping process, I effortlessly brought all the data back from DuckDB into a DataFrame with just one line of code.<\/span><\/span><\/span><\/p>\n<\/span><\/p><\/blockquote>\n<\/div>\n<\/div>\n
\n
From there, it was business as usual, employing the familiar tools of Pandas to tidy up and normalize the data. While I would like to handle all the data cleanup and normalization within DuckDB (or Polars for that matter) someday, I\u2019m currently relishing the benefits of blending the best of both worlds.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\ud83d\udcb0 <\/span><\/span>Optimizing Snowflake Costs with Python-DuckDB Alchemy<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
After the data was transformed, I turned to SQLAlchemy to seamlessly dispatch it to a Snowflake table. Once settled in Snowflake, the next step involved<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\n
<\/p>\ntransferring this dataset to Marketo, a move facilitated by a Reverse ETL process utilizing Census.<\/span><\/span><\/span><\/p>\n<\/span><\/p><\/blockquote>\n<\/div>\n<\/div>\n
\n
Throughout this entire process, especially in the development cycle, it marked the first instance where I had to tap into Snowflake credits.<\/span><\/span><\/span><\/p>\n<\/div>\n\n
\ud83c\udf10 <\/span><\/span>The Power and Promise of DuckDB<\/span><\/span><\/strong><\/span><\/p>\n<\/div>\n\n
Cost optimization is currently a key focus for every organization when it comes to Snowflake, and instances like these showcase how Python, coupled with the prowess of an OLAP database like DuckDB (harnessing the unlimited power of M2 Macs), can play a vital role. While I had been casually experimenting with DuckDB for some random testing use cases,<\/span><\/span><\/span><\/p>\n<\/div>\n