Scrape data from a webpage and store them in a database

Question

I've written a script in python to parse different names ,bubbles and reviews of various restaurant names from tripadvisor and finally store them in a table named webdata in a database. There are two functions within my script: one for storing the scraped data in MySQL and the other for printing the same in the console. The scraper is doing its job just flawless. As this is my first ever try to store items in a database and print the already stored items in the console, I might not have been able to follow the rules of DRY.

I will vastly appreciate any modification I should pursue to make the script robust.

This is my attempt so far:

import mysql.connector
from bs4 import BeautifulSoup
import requests

URL = "https://www.tripadvisor.com.au/Restaurants-g255068-c8-Brisbane_Brisbane_Region_Queensland.html"

def store_info(link):
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd = "test123",
      database="mydatabase"
    )

    mycursor = mydb.cursor()
    mycursor.execute("DROP TABLE webdata") #kick out the table if it already exists
    mycursor.execute("CREATE TABLE webdata (name VARCHAR(255), bubble VARCHAR(255), review VARCHAR(255))")

    response = requests.get(link)
    soup = BeautifulSoup(response.text,"lxml")
    for items in soup.find_all(class_="shortSellDetails"):
        name = items.find(class_="property_title").get_text(strip=True)
        bubble = items.find(class_="ui_bubble_rating").get("alt")
        review = items.find(class_="reviewCount").get_text(strip=True)

        mycursor.execute("INSERT INTO webdata (name,bubble,review) VALUES (%s,%s,%s)",(name,bubble,review))
    mydb.commit()

def get_data():
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd = "test123",
      database="mydatabase"
    )
    mycursor = mydb.cursor()
    mycursor.execute("SELECT * FROM webdata")
    for item in mycursor.fetchall():
        print(item)
    mydb.close()

if __name__ == '__main__':
    store_info(URL)
    get_data() #retrieve the data from that table

Answer 1

• The database setup mydb should be in it's own function, or even just a global since this script is rather minimal. There's no reason to duplicate this code.
• If mysql.connector doesn't, I'd look for a database connector that supports the with ... statement for connections and transactions (relatively sure e.g. sqlalchemy supports that). Because you can always forget to call commit (or rollback), but with with you have a little bit less mental overhead.
• It's a simple script ... long term I'd worry about losing data that way. Also why is it dropping the whole table instead of, say, DELETE FROM webdata; and only recreating the table if it doesn't exist?
• The bs4 part looks okay and the insert too; I'd perhaps have some checks in there that you don't overflow on the 255 limit with the review (or the name I suppose). Also some warnings around empty values might be good for you to debug / notice that something's off.
• AFAIK requests is a good choice, you might also want to check the status code though.

Okay, so long-term:

• To be more flexible, look at argparse or any of the command line parsing libraries so that you never have to edit the script in case one of the parameters changes (database connection, URL). I'd keep the HTML querying like it is, that's usually not wise to put into a configuration unless it changes frequently.