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)
