Python Web Scraping - How do I avoid scraping duplicates for my database?

user_15315272 0
Viewed 446

I am new to Python and have recently, with a lot of help from here and YouTube, coded a program that is able to scrape through the pages of news articles posted on a news website. My next step is to build a database and feed it with the articles I have scraped.

Setting up the database worked. But the obvious problem I run into is that news articles are scraped multiple times and therefore are added multiple times into the database when running my program over and over again. Unfortunately I haven't found any answers or videos so far that could fix this for me, so I'm hoping someone can help me with that (maybe I have been searching for the wrong terms, tried my best).

The program code itself works as intended. It's just some sort of "IDying" the objects or something different that I would need. Gladly appreciate any help :) Code is the following:

import requests
import sqlite3
from bs4 import BeautifulSoup
from time import sleep
from random import randint

connect = sqlite3.connect('StoredArticles.db')
cursor = connect.cursor()

# cursor.execute('''CREATE TABLE articlestable
# (article_page INT, article_time TEXT, article_title TEXT, article_link TEXT, article_description TEXT)''')

# Scraping Function
def getarticles(page):
    headers = {
        'User-Agent':
            'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko)' +
            'Version/14.0.1 Safari/605.1.15'
    }
    url = 'https://www.prnewswire.com/news-releases/news-releases-list/?page=' + str(page) + '&pagesize=100'
    r = requests.get(url, headers=headers)
    soup = BeautifulSoup(r.text, 'html.parser')

    articles = soup.select('.card-list > .row')  # select all rows that are under class "card-list"
    print("Scraping page " + str(page) + "...")
    sleep(randint(0, 1))

    for item in articles:
        article_page = page
        article_time = item.select_one('h3 small').text
        article_title = item.select_one('h3 small').find_next_sibling(text=True).strip()
        article_link = 'https://www.prnewswire.com/' + item.select_one('a')['href']
        article_description = item.select_one('p').get_text(strip=True, separator='\n')
        cursor.execute('''INSERT INTO articlestable VALUES(?,?,?,?,?)''',
                       (article_page, article_time, article_title, article_link, article_description))
    return

# Range of Pages to scrape through
for x in range(1, 3):
    getarticles(x)

# Add to Database and Finish Program
connect.commit()
cursor.execute('''SELECT * FROM articlestable''')
results = cursor.fetchall()
print(results)

connect.close()
web-scraping sqlite python database
1 Answers

Answer to my own question: Super simple

Here's the code I added to make it work.

Added this code right under the declaration of "connect" and "cursor" variables:

try:
    cursor.execute('''CREATE TABLE articlestable
    (article_page INT, article_time TEXT, article_title TEXT, article_link TEXT, article_description TEXT)''')
    cursor.execute('''CREATE UNIQUE INDEX index_article_link ON articlestable(article_link)''')
except:
    pass

This essentially just tries to create the table AND a unique index. The unique index, in my case, is the URL. Which makes sense, since these are all individual. This has to be done once and afterwards will just be skipped.

The second and last change happened in the loop, where I insert my variables into the database. Literally all you have to do is write INSERT OR IGNORE instead of just INSERT. Looks like this then:

cursor.execute('''INSERT OR IGNORE INTO articlestable VALUES(?,?,?,?,?)''', (article_page, article_time, article_title, article_link, article_description))

Voilà! It's done.

user_153152720
Related