How to insert data into SQLite on page load/refresh using SQLite3?

Question

I have scraped some weather information onto my website and now want to put them into sqlite3 each time the weather page on my website refreshes. The problem I'm having is inserting the information into the database as the weather is already displaying on my website.

def weather_():
    page = requests.get("https://www.bbc.com/weather/0/2562305")
    soup = BeautifulSoup(page.content, 'html.parser') 
    today = soup.find('div',{'data-component-id' : 'forecast'})
    temp = today.find(class_ = 'wr-day-temperature__low')
    low_temp = (temp.get_text())
    return low_temp

CodeRaptor · Accepted Answer · 2019-02-16 01:32:20Z

Storing Weather Data

Here is useful tutorial on sqlite: http://www.sqlitetutorial.net/sqlite-python/insert/

and another S.O. link: Python and SQLite: insert into table

and really usefull: https://docs.python.org/2/library/sqlite3.html

import sqlite3
conn = sqlite3.connect('weather.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE weather
             (lowtemp text)''')

# Insert a row of data
c.execute("INSERT INTO weather VALUES (low_temp)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

Use an API

Looked into your scraping and the BBC just uses Meteo ( https://www.meteogroup.com/weather-api ).

This Met Office https://www.metoffice.gov.uk/public/weather might be really useful for you to get the raw data and skipping the screen scraping.

Data Points: https://www.metoffice.gov.uk/datapoint/about

Here is historical data: https://www.metoffice.gov.uk/pub/data/weather/uk/climate/stationdata/newtonriggdata.txt

Aness · Accepted Answer · 2019-02-17 08:10:49Z

You need to create a database file and a table inside to store your weather informations.

Refer to this doc for the sqlite3 installation.

CREATE TABLE Weather(Id INTEGER AUTOINCREMENT,low_temperature VARCHAR(25))

Add proper functions to update the database.

def create_connection(db_file):
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return None

def weather_():
    page = requests.get("https://www.bbc.com/weather/0/2562305")
    soup = BeautifulSoup(page.content, 'html.parser') 
    today = soup.find('div',{'data-component-id' : 'forecast'})
    temp = today.find(class_ = 'wr-day-temperature__low')
    low_temp = (temp.get_text())
    return low_temp

//Getting the data
low_temp = weather_()
//Updating the database
your_db_file= '/path_to_your_db_file/your_wheather_db.sqlite'
connector = create_connection(your_db_file)
cursor = connector .cursor()
sql = "INSERT INTO Weather(low_temperature) VALUES ('"+str(low_temp )+"')"
cursor.execute(sql)
conn.commit()
conn.close()

Collectives™ on Stack Overflow

How to insert data into SQLite on page load/refresh using SQLite3?

2 Answers 2

Storing Weather Data

Use an API

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Storing Weather Data

Use an API

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related