def CreateDatabase(): try: sys.stdout.write('Creating database... ') sys.stdout.flush() cursor.execute( '''DROP TABLE IF EXISTS GlobalLandTemperaturesByCountry''') cursor.execute( '''CREATE TABLE GlobalLandTemperaturesByCountry (EventDate VARCHAR(25), AverageTemperature DECIMAL(25), AverageTemperatureUncertainty DECIMAL(25), Country VARCHAR(25))''' ) cursor.execute( '''DROP TABLE IF EXISTS GlobalLandTemperaturesByMajorCity''') cursor.execute( '''CREATE TABLE GlobalLandTemperaturesByMajorCity (EventDate VARCHAR(25), AverageTemperature DECIMAL(25), AverageTemperatureUncertainty DECIMAL(25), City VARCHAR(25), Country VARCHAR(25), Latitude VARCHAR(25), Longitude VARCHAR(25))''' ) cursor.execute( '''DROP TABLE IF EXISTS GlobalLandTemperaturesByState''') cursor.execute( '''CREATE TABLE GlobalLandTemperaturesByState (EventDate VARCHAR(25), AverageTemperature DECIMAL(25), AverageTemperatureUncertainty DECIMAL(25), State VARCHAR(25), Country VARCHAR(25))''' ) conn.commit() print('Database creation successful.\n') except: print('Error C4: Database creation failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CalculateMaxMinAvg(): try: sys.stdout.write('Loading max, min, avg temperature data... ') sys.stdout.flush() cursor.execute( "SELECT MAX(AverageTemperature) FROM GlobalLandTemperaturesByState WHERE State = 'Queensland' AND EventDate LIKE '2000%'" ) maxTemp = cursor.fetchone() cursor.execute( "SELECT MIN(AverageTemperature) FROM GlobalLandTemperaturesByState WHERE State = 'Queensland' AND EventDate LIKE '2000%'" ) minTemp = cursor.fetchone() cursor.execute( "SELECT AVG(AverageTemperature) FROM GlobalLandTemperaturesByState WHERE State = 'Queensland' AND EventDate LIKE '2000%'" ) avgTemp = cursor.fetchone() print('Max, min, avg data loaded successfully.\n') except: print('Error S4: Failed to load Major_City workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1) print('Maximum temperature', maxTemp[0]) print('Minimum temperature', minTemp[0]) print('Average temperature', avgTemp[0], '\n')
def Run(): CreateWorkbook() SelectChinaTempData() InsertDataIntoWorkbook() CreateLineChart() worldTemp_workbook.save(wb_filename) utilities.CloseDatabaseConnection(conn)
def SelectStateTempData(_query): try: sys.stdout.write('Selecting data from Temperatures_by_State... ') sys.stdout.flush() cursor.execute(_query) print('Data select successful.\n') except: print('Error N4: Data select failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def LoadWorkbook(_filename): try: sys.stdout.write('Loading World_Temperature workbook... ') sys.stdout.flush() _workbook = load_workbook(_filename) print('World_Temperature workbook loaded successfully.\n') return _workbook except: print('Error N1: Failed to load World_Temperature workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def SelectSouthernCitiesData(_cursor): try: cursor = _cursor sys.stdout.write('Loading Southern_Cities data... ') sys.stdout.flush() cursor.execute( "SELECT City, Country, Latitude, Longitude FROM GlobalLandTemperaturesByMajorCity WHERE Latitude LIKE '%s'" ) print('Southern_Cities data loaded successfully.\n') except: print('Error S3: Failed to load Major_City workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def SelectChinaTempData(): try: sys.stdout.write('Selecting data from Temperatures_by_Country... ') sys.stdout.flush() _query = "SELECT SUBSTR(EventDate, 0, 5) as Year, AverageTemperature FROM GlobalLandTemperaturesByCountry WHERE Country = 'China' AND AverageTemperature != 'NULL' ORDER BY Year" cursor.execute(_query) print('Data select successful.\n') except: print('Error E3: Data select failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateWorkbook(): try: sys.stdout.write('Creating World_Temperature workbook... ') sys.stdout.flush() worldTemp_worksheet = worldTemp_workbook.active worldTemp_worksheet.title = "Temperature by City" print('World_Temperature workbook created successfully.\n') except: print('Error E1: Failed to load World_Temperature workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateDatabase(): try: sys.stdout.write('Creating database... ') sys.stdout.flush() cursor.execute('''DROP TABLE IF EXISTS SouthernCities''') cursor.execute( '''CREATE TABLE SouthernCities (City VARCHAR(25), Country VARCHAR(25), Latitude DECIMAL(25), Longitude DECIMAL(25))''' ) conn.commit() print('Database creation successful.\n') except: print('Error S1: Database connection failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def InsertDataIntoWorkbook(): try: sys.stdout.write('Inserting data into World_Temperature workbook... ') sys.stdout.flush() _chinaTempData = cursor.fetchall() for rows in _chinaTempData: worldTemp_worksheet.append(rows) print('Data successfully inserted.\n') except: print('Error E4: Data insert failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateStateWorkbook(): filename_state = 'assets/GlobalLandTemperaturesByState.xlsx' sys.stdout.write('Loading State workbook... ') sys.stdout.flush() try: workbook_state = load_workbook(filename_state) sheet_ranges_state = workbook_state.sheetnames sheet_state = workbook_state[sheet_ranges_state[0]] print('Workbook load successful.\n') return sheet_state except: print('Error C3: Failed to load State workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def AddWorkbookSheet(_workbook): try: DeleteWorksheet(_workbook) sys.stdout.write('Creating new worksheet... ') sys.stdout.flush() _worksheet = _workbook.create_sheet('Comparison') _worksheet.title = "Comparison" print('Worksheet created successfully.\n') return _worksheet except: print('Error N2: Failed to create worksheet.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateCountryWorkbook(): filename_country = 'assets/GlobalLandTemperaturesByCountry.xlsx' # Workbook 1 sys.stdout.write('Loading Country workbook... ') sys.stdout.flush() try: workbook_country = load_workbook(filename_country) sheet_ranges_country = workbook_country.sheetnames sheet_country = workbook_country[sheet_ranges_country[0]] print('Workbook load successful.\n') return sheet_country except: print('Error C1: Failed to load Country workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateMajorCityWorkbook(): filename_major_city = 'assets/GlobalLandTemperaturesByMajorCity.xlsx' # Workbook 2 sys.stdout.write('Loading Major_City workbook... ') sys.stdout.flush() try: workbook_major_city = load_workbook(filename_major_city) sheet_ranges_major_city = workbook_major_city.sheetnames sheet_major_city = workbook_major_city[sheet_ranges_major_city[0]] print('Workbook load successful.\n') return sheet_major_city except: print('Error C2: Failed to load Major_City workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def InsertCountryWorksheet(_worksheet): try: sys.stdout.write('Inserting Country data into database... ') sys.stdout.flush() for row in _worksheet.iter_rows(min_row=2): _date = row[0].value _averageTemp = row[1].value _averageTempUncertain = row[2].value _country = row[3].value _query = "INSERT INTO GlobalLandTemperaturesByCountry (EventDate, AverageTemperature, AverageTemperatureUncertainty, Country) VALUES (?, ?, ?, ?)" _values = (_date, _averageTemp, _averageTempUncertain, _country) cursor.execute(_query, _values) conn.commit() print('Data insert successful.\n') except: print('Error C5: Data insert failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def InsertSouthernCitiesWorksheet(): try: sys.stdout.write('Inserting Southern_Cities data into database... ') sys.stdout.flush() rows = cursor.fetchall() for row in rows: _city = row[0] _country = row[1] _latitude = row[2] _longitude = row[3] _query = "INSERT INTO SouthernCities (City, Country, Latitude, Longitude) VALUES (?, ?, ?, ?)" _values = (_city, _country, _latitude, _longitude) cursor.execute(_query, _values) conn.commit() print('Data insert successful.\n') except: print('Error S2: Failed to load Major_City workbook.\n') utilities.CloseDatabaseConnection(conn) exit(1)
def CreateLineChart(): try: sys.stdout.write('Creating World_Temperature LineChart... ') sys.stdout.flush() # setup linechart worldTemp_lineChart.title = "China Temperature by Date" worldTemp_lineChart.style = 12 worldTemp_lineChart.y_axis.title = 'Temp' worldTemp_lineChart.x_axis.title = 'Date' worldTemp_lineChart.x_axis.tickLblPos = "low" data = Reference(worldTemp_worksheet, min_col=2, min_row=1, max_col=2, max_row=2200) worldTemp_lineChart.add_data(data, titles_from_data=True) dates = Reference(worldTemp_worksheet, min_col=1, min_row=1, max_col=1, max_row=2200) worldTemp_lineChart.set_categories(dates) worldTemp_worksheet.add_chart(worldTemp_lineChart, "C1") print('Creation successful.\n') except: print('Error E2: LineChart creation failed.\n') utilities.CloseDatabaseConnection(conn) exit(1)
sys.stdout.write('Inserting data into World_Temperature workbook... ') sys.stdout.flush() _chinaTempData = cursor.fetchall() for rows in _chinaTempData: worldTemp_worksheet.append(rows) print('Data successfully inserted.\n') except: print('Error E4: Data insert failed.\n') utilities.CloseDatabaseConnection(conn) exit(1) def Run(): CreateWorkbook() SelectChinaTempData() InsertDataIntoWorkbook() CreateLineChart() worldTemp_workbook.save(wb_filename) utilities.CloseDatabaseConnection(conn) CreateWorkbook() SelectChinaTempData() InsertDataIntoWorkbook() CreateLineChart() worldTemp_workbook.save(wb_filename) utilities.CloseDatabaseConnection(conn)