How can this be improved? ''' A Module that creates and modifies a database. In this…
- User
How can this be improved?
''' A Module that creates and modifies a database. In this case, the data is weather information scraped from a webpage. ''' import sqlite3 import logging from dateutil import parser from scrape_weather import WeatherScraper
class DBOperations: """Class for performing operations on a SQLite database"""
def __init__(self, dbname): """ Constructor for DBOperations class. Parameters: - dbname: str, the name of the SQLite database file to use """ self.dbname = dbname self.logger = logging.getLogger(__name__) def initialize_db(self): """ Initialize the SQLite database by creating the weather_data table. This method should be called every time the program runs. """ with self.get_cursor() as cursor: try: cursor.execute(''' CREATE TABLE IF NOT EXISTS weather_data ( id INTEGER PRIMARY KEY AUTOINCREMENT, sample_date TEXT UNIQUE, location TEXT, min_temp REAL, max_temp REAL, avg_temp REAL ) ''') self.logger.info("Initialized database successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while creating the table: %s", error) def save_data(self, data): """ Save weather data to the SQLite database. If the data already exists in the database, it will not be duplicated. Parameters: - data: dict, the weather data to save to the database. Must have keys for sample_date, location, min_temp, max_temp, and avg_temp. """ with self.get_cursor() as cursor: try: cursor.execute(''' INSERT OR IGNORE INTO weather_data (sample_date, location, min_temp, max_temp, avg_temp) VALUES (?, ?, ?, ?, ?) ''', (data['sample_date'], data['location'], data['min_temp'], data['max_temp'], data['avg_temp'])) self.logger.info("Data saved successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while saving data to the database: %s", error) def fetch_data(self, location): """ Fetch weather data from the SQLite database for a specified location. Parameters: - location: str, the location to fetch weather data for Returns: - A list of tuples containing the weather data for the specified location, where each tuple has the format (sample_date, min_temp, max_temp, avg_temp). Returns an empty list if no data is found for the specified location. """ with self.get_cursor() as cursor: try: cursor.execute(''' SELECT sample_date, min_temp, max_temp, avg_temp FROM weather_data WHERE location = ? ''', (location,)) data = cursor.fetchall() self.logger.info("Data fetched successfully.") return data except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def fetch_data_year_to_year(self, first_year, last_year): ''' Fetch weather data from the SQLite database for a specified year range. Parameters: - first_year: int, the first year in the range. - end_year: int, the final year in the range. Returns: - A list of data that falls in the range of years specified by the user.''' month_data = {1:[], 2:[], 3:[], 4:[], 5:[], 6:[], 7:[], 8:[], 9:[], 10:[], 11:[], 12:[]} start_year = f'{first_year}-01-01' end_year = f'{last_year}-01-01' with self.get_cursor() as cursor: try: for row in cursor.execute(''' SELECT sample_date, avg_temp FROM weather_data WHERE sample_date BETWEEN ? AND ? ORDER BY sample_date''',(start_year, end_year)): month = parser.parse(row[0]).month month_data[month].append(row[1]) self.logger.info("Data fetched successfully.") return month_data except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def fetch_data_single_month(self, month, year): ''' Fetch weather data from the SQLite database for a specified month and year. Parameters: - month: int, the month to search for. - year: int, the year to search for. Returns: - A list of temperatures for the month and year the user searched for''' temperatures = {} with self.get_cursor() as cursor: try: for row in cursor.execute(''' SELECT sample_date, avg_temp FROM weather_data WHERE sample_date LIKE ?||'-'||'0'||?||'-'||'%' ORDER BY sample_date''',(year, month)): temperatures[row[0]] = row[1] return temperatures except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def purge_data(self): """ Purge all weather data from the SQLite database. """ with self.get_cursor() as cursor: try: cursor.execute('DELETE FROM weather_data') self.logger.info("Data purged successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while purging data from the database: %s", error) def get_cursor(self): """ Get a cursor to use for database operations. Returns: - A cursor object for the SQLite database. """ return DBCM(self.dbname)class DBCM: ''' A class that represents a connection to a database. ''' def init(self, dbname): self.dbname = dbname self.logger = logging.getLogger(name)
def __enter__(self): try: self.conn = sqlite3.connect(self.dbname) self.cursor = self.conn.cursor() self.logger.info("Connection to database established successfully.") return self.cursor except sqlite3.Error as error: self.logger.error("An error occurred while connecting to the database: %s", error) return None def __exit__(self, exc_type, exc_val, exc_tb): if exc_type is not None: self.conn.rollback() else: try: self.conn.commit() self.logger.info("Changes committed successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while committing changes to the database: %s", error) try: self.cursor.close() self.conn.close() self.logger.info("Connection to database closed successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while closing the database connection: %s", error)def main(): ''' The main method. '''
# Initialize the database data_base = DBOperations("mydatabase.db") data_base.initialize_db() # Get the weather data scraper = WeatherScraper() data = scraper.get_data() # Process the data and prepare the rows rows = [] for date, temps in data.items(): row = ( date, "Winnipeg", temps["Max"], temps["Min"], temps["Mean"] ) rows.append(row) # Save the data to the database with data_base.get_cursor() as cursor: try: cursor.executemany(''' INSERT OR IGNORE INTO weather_data (sample_date, location, min_temp, max_temp, avg_temp) VALUES (?, ?, ?, ?, ?) ''', rows) data_base.logger.info("Inserted %s rows into the database.", len(rows)) except sqlite3.Error as error: data_base.logger.error("An error occurred while inserting data: %s", error)if name == 'main': main()
- Assistant
''' A module that creates and modifies a weather data database. Weather data is web scraped and stored in the SQLite database. The module performs operations like saving, fetching, and purging data in the database. '''
import sqlite3 import logging from dateutil import parser from scrape_weather import WeatherScraper
class DBOperations: """Class for handling SQLite database operations related to weather data."""
def __init__(self, dbname): """ Constructor for DBOperations class. Parameters: - dbname: str, the name of the SQLite database file to use """ self.dbname = dbname self.logger = logging.getLogger(__name__) def initialize_db(self): """ Initialize the SQLite database by creating the weather_data table. This method should be called every time the program runs. """ with self.get_cursor() as cursor: try: cursor.execute(''' CREATE TABLE IF NOT EXISTS weather_data ( id INTEGER PRIMARY KEY AUTOINCREMENT, sample_date TEXT UNIQUE, location TEXT, min_temp REAL, max_temp REAL, avg_temp REAL ) ''') self.logger.info("Initialized database successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while creating the table: %s", error) def save_data(self, data): """ Save weather data to the SQLite database. If the data already exists in the database, it will not be duplicated. Parameters: - data: dict, the weather data to save to the database. Must have keys for sample_date, location, min_temp, max_temp, and avg_temp. """ with self.get_cursor() as cursor: try: cursor.execute(''' INSERT OR IGNORE INTO weather_data (sample_date, location, min_temp, max_temp, avg_temp) VALUES (?, ?, ?, ?, ?) ''', (data['sample_date'], data['location'], data['min_temp'], data['max_temp'], data['avg_temp'])) self.logger.info("Data saved successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while saving data to the database: %s", error) def fetch_data(self, location): """ Fetch weather data from the SQLite database for a specified location. Parameters: - location: str, the location to fetch weather data for Returns: - A list of tuples containing the weather data for the specified location, where each tuple has the format (sample_date, min_temp, max_temp, avg_temp). Returns an empty list if no data is found for the specified location. """ with self.get_cursor() as cursor: try: cursor.execute(''' SELECT sample_date, min_temp, max_temp, avg_temp FROM weather_data WHERE location = ? ''', (location,)) data = cursor.fetchall() self.logger.info("Data fetched successfully.") return data except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def fetch_data_year_to_year(self, first_year, last_year): ''' Fetch weather data from the SQLite database for a specified year range. Parameters: - first_year: int, the first year in the range. - end_year: int, the final year in the range. Returns: - A dictionary containing average temperatures for each month within the specified year range. The keys are month numbers, and the values are lists of temperatures.''' month_data = {1:[], 2:[], 3:[], 4:[], 5:[], 6:[], 7:[], 8:[], 9:[], 10:[], 11:[], 12:[]} start_year = f'{first_year}-01-01' end_year = f'{last_year}-01-01' with self.get_cursor() as cursor: try: for row in cursor.execute(''' SELECT sample_date, avg_temp FROM weather_data WHERE sample_date BETWEEN ? AND ? ORDER BY sample_date''',(start_year, end_year)): month = parser.parse(row[0]).month month_data[month].append(row[1]) self.logger.info("Data fetched successfully.") return month_data except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def fetch_data_single_month(self, month, year): ''' Fetch weather data from the SQLite database for a specified month and year. Parameters: - month: int, the month to search for. - year: int, the year to search for. Returns: - A dictionary containing average temperatures for each day in the specified month and year. The keys are date strings, and the values are temperatures.''' temperatures = {} with self.get_cursor() as cursor: try: for row in cursor.execute(''' SELECT sample_date, avg_temp FROM weather_data WHERE sample_date LIKE ?||'-'||'0'||?||'-'||'%' ORDER BY sample_date''',(year, month)): temperatures[row[0]] = row[1] return temperatures except sqlite3.Error as error: self.logger.error("An error occurred while fetching data from the database: %s", error) return [] def purge_data(self): """ Purge all weather data from the SQLite database. """ with self.get_cursor() as cursor: try: cursor.execute('DELETE FROM weather_data') self.logger.info("Data purged successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while purging data from the database: %s", error) def get_cursor(self): """ Get a cursor to use for database operations. Returns: - A cursor object for the SQLite database. """ return DBCM(self.dbname)class DBCM: ''' A class that represents a connection to a database. ''' def init(self, dbname): self.dbname = dbname self.logger = logging.getLogger(name)
def __enter__(self): try: self.conn = sqlite3.connect(self.dbname) self.cursor = self.conn.cursor() self.logger.info("Connection to database established successfully.") return self.cursor except sqlite3.Error as error: self.logger.error("An error occurred while connecting to the database: %s", error) return None def __exit__(self, exc_type, exc_val, exc_tb): if exc_type is not None: self.conn.rollback() else: try: self.conn.commit() self.logger.info("Changes committed successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while committing changes to the database: %s", error) try: self.cursor.close() self.conn.close() self.logger.info("Connection to database closed successfully.") except sqlite3.Error as error: self.logger.error("An error occurred while closing the database connection: %s", error)def main(): ''' The main method. '''
# Initialize the database data_base = DBOperations("mydatabase.db") data_base.initialize_db() # Get the weather data scraper = WeatherScraper() data = scraper.get_data() # Process the data and prepare the rows rows = [] for date, temps in data.items(): row = ( date, "Winnipeg", temps["Max"], temps["Min"], temps["Mean"] ) rows.append(row) # Save the data to the database with data_base.get_cursor() as cursor: try: cursor.executemany(''' INSERT OR IGNORE INTO weather_data (sample_date, location, min_temp, max_temp, avg_temp) VALUES (?, ?, ?, ?, ?) ''', rows) data_base.logger.info("Inserted %s rows into the database.", len(rows)) except sqlite3.Error as error: data_base.logger.error("An error occurred while inserting data: %s", error)if name == 'main': main()