def search_city():
city = request.args['city']
with cursor() as cur:
cur.execute("""SELECT DISTINCT city FROM msa WHERE city ~~* '%{}%' """.format(city))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = city
return jsonify({'results': results})
def search_state():
with cursor() as cur:
state = request.args['state']
cur.execute("""SELECT DISTINCT state FROM msa WHERE state ~~* '{}%' """.format(state))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = state
return jsonify({'results': results})
def search_city():
city = request.args['city']
with cursor() as cur:
cur.execute(
"""SELECT DISTINCT city FROM msa WHERE city ~~* '%{}%' """.format(
city))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = city
return jsonify({'results': results})
def search_state():
with cursor() as cur:
state = request.args['state']
cur.execute(
"""SELECT DISTINCT state FROM msa WHERE state ~~* '{}%' """.format(
state))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = state
return jsonify({'results': results})
def load_states():
analyzer = NgramAnalyzer(1,2)
state_schema = Schema(state=ID(stored=True,analyzer=analyzer))
with cursor() as cur:
print('Loading states...')
cur.execute('SELECT DISTINCT state FROM msa');
state_index = storage.create_index(state_schema)
writer = state_index.writer()
for s in cur.fetchall():
writer.add_document(state=s[u'state'])
writer.commit()
return state_index
def search_occ():
query = request.args['occ_title']
# results = search.find_occ(query)
# for r in results:
# r['inputPhrase'] = query
# return jsonify({'results': results})
with cursor() as cur:
cur.execute("""SELECT DISTINCT occ_title, occ_code FROM msa WHERE occ_title ~~* '%{}%' """.format(query))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = query
return jsonify({'results': results})
def load_states():
analyzer = NgramAnalyzer(1, 2)
state_schema = Schema(state=ID(stored=True, analyzer=analyzer))
with cursor() as cur:
print('Loading states...')
cur.execute('SELECT DISTINCT state FROM msa')
state_index = storage.create_index(state_schema)
writer = state_index.writer()
for s in cur.fetchall():
writer.add_document(state=s[u'state'])
writer.commit()
return state_index
def load_cities():
analyzer = NgramAnalyzer(1)
city_schema = Schema(state=ID(stored=True),
city=ID(stored=True, analyzer=analyzer))
with cursor() as cur:
print('Loading cities...')
cur.execute('SELECT DISTINCT state, city FROM msa')
city_index = storage.create_index(city_schema)
writer = city_index.writer()
for s in cur.fetchall():
writer.add_document(state=s[u'state'], city=s[u'city'])
writer.commit()
return city_index
def a_median_by_state():
with cursor() as cur:
# pdb.set_trace()
sql="""SELECT occ_code,
state,
SUM(a_mean*tot_emp)/SUM(tot_emp) AS a_mean
FROM msa
WHERE occ_code = '{occ_code}'
GROUP BY 1,2""".format(**request.args)
cur.execute(sql)
print(sql)
return jsonify({'data':cur.fetchall()})
def load_occs():
analyzer = NgramAnalyzer(3)
occ_schema = Schema(occ_title=TEXT(stored=True,analyzer=analyzer),
occ_code=ID(stored=True))
with cursor() as cur:
print('Loading occs...')
cur.execute('SELECT DISTINCT occ_code, occ_title FROM msa');
occ_index = storage.create_index(occ_schema)
writer = occ_index.writer()
for s in cur.fetchall():
writer.add_document(occ_title=s[u'occ_title'],occ_code=s[u'occ_code'])
writer.commit()
return occ_index
def load_cities():
analyzer = NgramAnalyzer(1)
city_schema = Schema(state=ID(stored=True),
city=ID(stored=True,analyzer=analyzer))
with cursor() as cur:
print('Loading cities...')
cur.execute('SELECT DISTINCT state, city FROM msa');
city_index = storage.create_index(city_schema)
writer = city_index.writer()
for s in cur.fetchall():
writer.add_document(state=s[u'state'],city=s[u'city'])
writer.commit()
return city_index
def a_median_by_state():
with cursor() as cur:
# pdb.set_trace()
sql = """SELECT occ_code,
state,
SUM(a_mean*tot_emp)/SUM(tot_emp) AS a_mean
FROM msa
WHERE occ_code = '{occ_code}'
GROUP BY 1,2""".format(**request.args)
cur.execute(sql)
print(sql)
return jsonify({'data': cur.fetchall()})
def load_occs():
analyzer = NgramAnalyzer(3)
occ_schema = Schema(occ_title=TEXT(stored=True, analyzer=analyzer),
occ_code=ID(stored=True))
with cursor() as cur:
print('Loading occs...')
cur.execute('SELECT DISTINCT occ_code, occ_title FROM msa')
occ_index = storage.create_index(occ_schema)
writer = occ_index.writer()
for s in cur.fetchall():
writer.add_document(occ_title=s[u'occ_title'],
occ_code=s[u'occ_code'])
writer.commit()
return occ_index
def top_salaries_in_state_by_city():
sql = """SELECT occ_code,
state,
city,
a_mean
FROM msa
WHERE occ_code ='{occ_code}' AND state = '{state}'
ORDER BY a_mean DESC
LIMIT 10""".format(**request.args)
with cursor() as cur:
print(sql)
cur.execute(sql)
return jsonify({'data':cur.fetchall()})
def search_occ():
query = request.args['occ_title']
# results = search.find_occ(query)
# for r in results:
# r['inputPhrase'] = query
# return jsonify({'results': results})
with cursor() as cur:
cur.execute(
"""SELECT DISTINCT occ_title, occ_code FROM msa WHERE occ_title ~~* '%{}%' """
.format(query))
results = cur.fetchall()
for r in results:
r['inputPhrase'] = query
return jsonify({'results': results})
def top_salaries_in_state_by_city():
sql = """SELECT occ_code,
state,
city,
a_mean
FROM msa
WHERE occ_code ='{occ_code}' AND state = '{state}'
ORDER BY a_mean DESC
LIMIT 10""".format(**request.args)
with cursor() as cur:
print(sql)
cur.execute(sql)
return jsonify({'data': cur.fetchall()})
def stats_by_state_city_occ():
with cursor() as cur:
# pdb.set_trace()
sql="""SELECT state,
city,
occ_title,
occ_code,
tot_emp,
jobs_1000,
h_mean,
a_mean,
h_median,
a_median
FROM msa
WHERE state ~~* '{state}'
AND city ~~* '{city}'
AND occ_code = '{occ_code}'""".format(**request.args)
cur.execute(sql)
print(sql)
return jsonify({'data':cur.fetchall()})
def stats_by_state_city_occ():
with cursor() as cur:
# pdb.set_trace()
sql = """SELECT state,
city,
occ_title,
occ_code,
tot_emp,
jobs_1000,
h_mean,
a_mean,
h_median,
a_median
FROM msa
WHERE state ~~* '{state}'
AND city ~~* '{city}'
AND occ_code = '{occ_code}'""".format(**request.args)
cur.execute(sql)
print(sql)
return jsonify({'data': cur.fetchall()})