def insertStateToDB(state, tier, tierDict):
# used for multi-processing
# this function will insert state's data in form dictionary into database (e.g. NY_toptier, CA_onebed, IL_bottomtier)
createTableQuery = "CREATE TABLE IF NOT EXISTS %s_%s (price INT, date DATETIME NOT NULL, regionid INT, FOREIGN KEY (regionid) REFERENCES main_index(regionid))" % (
state, tier)
execute.run_query(createTableQuery)
stateText = SQLtools.dftostring(tierDict[state], key='state')
insertQuery = "INSERT INTO %s_%s (price, date, regionid) VALUES %s" % (
state, tier, stateText)
execute.run_query(insertQuery)
print("%s_%s is now in MySQL database." % (state, tier))
def getDataFromDB(regionid, state):
citydata = {}
tiers = ['toptier', 'bottomtier', 'midtier', 'onebed', 'twobed', 'threebed', 'fourbed', 'fivebed']
for tier in tiers:
try:
query = "SELECT * FROM %s_%s WHERE regionid = %s" %(state, tier, regionid)
target = execute.run_query(query, fetch=True, fetch_option='fetchall')
targetDF = pd.DataFrame(target)
citydata.update({tier : targetDF})
except:
pass
return citydata
def getdatafromDB(regionID):
getdataquery = "SELECT * FROM city_%s" %(regionID)
target = execute.run_query(getdataquery, fetch=True, fetch_option='fetchall')
targetDF = pd.DataFrame(target)
return targetDF
######################## SCRIPTS TO EXECUTE BEGIN HERE ########################
print("Do you want to import the index tables? (yes/no)")
createIndexTable = input()
# This script creates 8 index tables, which are subsequently combined to create 1 main index table via UNION query. See below.
if createIndexTable == "yes":
for filename, name in zip(csvfile, csvname):
temp = DataSet(filename, name)
temp.clean(before2000=False)
text = SQLtools.dftostring(temp.indexdf, key='index')
createQuery = "CREATE TABLE IF NOT EXISTS %s_index (regionid INT PRIMARY KEY, cityname VARCHAR(255), statename VARCHAR(255))" % (
name)
execute.run_query(createQuery)
insertQuery = "INSERT INTO %s_index (regionid, cityname, statename) VALUES %s" % (
name, text)
execute.run_query(insertQuery)
print(
"All index tables have been created in MySQL database. Please go to database and execute appropriate queries. FYI, these queuries can be found in main_data_processing.py"
)
# # You must execute 2 queries. First is UNION query to combine all index tables to create table main_index:
# CREATE TABLE main_index AS
# SELECT * FROM toptier_index
# UNION
# SELECT * FROM midtier_index
# UNION
# SELECT * FROM bottomtier_index
# UNION
def showResult(request):
form = InputCity()
city = request.GET['city']
state = request.GET['state']
# additional form to receive city and state information from user
requestData = ClientInfo(request).getData() # get user's IP address, etc.
requestData['city'] = city
requestData['state'] = state
logger.info(requestData)
# state names are abbreviated in MySQL database
# enable to handle state names that are lowercase state, abbreviated, etc.
if len(state) == 2:
state = state.upper()
else:
try:
state = translateState.state_dictionary.get(tools.capitalize_words(state), state)
except TypeError:
raise Http404('Invalid state. Please check your spelling!')
# capitalize city name where appropriate
city = tools.capitalize_words(city)
try:
getIDquery = "SELECT * FROM main_index WHERE (cityname = '%s' and statename = '%s')" %(city, state)
regionid = execute.run_query(getIDquery, fetch=True, fetch_option='fetchone')['regionid']
# get regionid from main_index table
except TypeError:
raise Http404("Invalid location. Please check that your information is correct.")
if regionid:
citydata = SQLtools.getDataFromDB(regionid, state)
else:
raise Http404("This location is not in database.")
# GRAPH historical data - midtier
image_png = computecity.plothistory(citydata['midtier'])
graphic = base64.b64encode(image_png)
graphic = graphic.decode('utf-8')
# OBTAIN average value of each tier
citysummary = {}
for tier in citydata:
temp = citydata[tier]
try:
endprice = '$' + '{:,}'.format(temp['price'].iloc[-1]) # 'price' column, last row, add dollar sign
citysummary.update({tier : endprice})
except:
endprice = 'N/A'
citysummary.update({tier : endprice})
# CALCULATE rise percentage
percentperyear, latestdate = computecity.calcpercent(citydata['midtier'])
# # Convert into appropriat form for purpose of display
# targetDF['date'] = targetDF['date'].apply(lambda x: x.strftime('%B %d, %Y'))
# targetDF['price'] = targetDF['price'].apply(lambda x: '${:,}'.format(x))
info = {'city' : tools.capitalize_words(city),
'state' : state.upper(),
'graphic' : graphic,
'form' : form,
'citysummary': citysummary,
'percentperyear' : percentperyear,
'latestdate' : latestdate,
}
return render(request, 'myapp/displayresult.html', info)