def __init__(self, **kwargs):
super(ScoreSource, self).__init__(**kwargs)
self._conn = MySQLdb.connect(
user=settings.MYSQL_USER,
passwd=settings.MYSQL_PASS,
db=settings.MYSQL_DATABASE,
host=settings.MYSQL_HOST,
port=settings.MYSQL_PORT,
)
cursor = self._conn.cursor()
self._stats = {}
for field in sql_fieldnames():
stmt = """SELECT AVG(%s), STDDEV_POP(%s) FROM locations""" % (field, field)
cursor.execute(stmt)
for row in cursor:
self._stats[field] = row
#cursor.execute("""DELETE FROM scores""")
cursor.close()
def __iter__(self):
cursor = self._conn.cursor(cursors.DictCursor)
stmt = """SELECT * FROM locations"""
cursor.execute(stmt)
for row in cursor:
record = {
'code': row['code'],
}
for field in sql_fieldnames():
value = row[field]
if value is None:
score = 0
else:
(avg, stddev) = self._stats[field]
diff = float(value - avg)
score = int(diff / stddev)
if field.startswith('naccrra_') or field.startswith('rpp_'):
score *= -1
record[field] = score
yield record
cursor.close()
def score():
conn = MySQLdb.connect(user='******', passwd='', db='k2', host='localhost', port=3308)
cursor = conn.cursor()
stats = {}
for field in sql_fieldnames():
stmt = """SELECT AVG(%s), STDDEV_POP(%s) FROM locations""" % (field, field)
cursor.execute(stmt)
for row in cursor:
stats[field] = row
print stats
cursor.close()
cursor = conn.cursor(cursors.DictCursor)
stmt = """SELECT * FROM locations"""
cursor.execute(stmt)
for row in cursor:
print row['name']
total_score = 0
for field in sql_fieldnames():
(avg, stddev) = stats[field]
diff = float(row[field] - avg)
score = int(diff / stddev)
if field.startswith('naccrra_') or field.startswith('rpp_'):
score *= -1
total_score += score
print ' ', field, score, avg, row[field]
print ' ', total_score
def compare(occupation_id, location, compare_to=None, weights=None):
if weights is None:
weights = DEFAULT_WEIGHTS.copy()
occ = get_occupation(location['oes'], occupation_id, '2010')
res = {
'location': {
'code': location['code'],
'name': location['name'],
'geo': location['geo'],
'census': location.get('census', None),
},
'data': {
'occupation': {
'id': occupation_id,
'value': occ['mean'],
'is_average': occ['id'] == '00-0000',
},
},
'scores': {},
'weights': weights,
}
res['scores']['location'] = location_score(occupation_id, location['code'], weights)
for fieldname in mongo_fieldnames():
val = get_value(location, fieldname)
if fieldname.startswith('rpp_state'):
val = float(val)
res['data'][fieldname.replace('.', '_')] = {
'value': val,
}
if compare_to:
res['compare_to'] = {
'code': compare_to['code'],
'name': compare_to['name'],
}
res['scores']['cmp'] = location_score(occupation_id, compare_to['code'], weights)
occ = get_occupation(compare_to['oes'], occupation_id, '2010')
res['data']['occupation']['compare_to'] = {
'id': occ['id'],
'value': occ['mean'],
'cmp': cmp(res['data']['occupation']['value'], occ['mean']),
'is_average': occ['id'] == '00-0000',
}
for fieldname in mongo_fieldnames():
try:
key = fieldname.replace('.', '_')
val = get_value(compare_to, fieldname)
if fieldname.startswith('rpp_state'):
val = float(val)
res['data'][key]['compare_to'] = {
'value': val,
'cmp': smartcmp(res['data'][key]['value'], val, key),
}
except KeyError:
res['data'][key]['compare_to'] = None
else:
res['scores']['cmp'] = {k: 0 for k in res['scores']['location'].iterkeys()}
for k in res['scores']['cmp']:
res['scores']['cmp'][k] = cmp(res['scores']['location'][k], res['scores']['cmp'][k])
# national average
fields = [f for f in sql_fieldnames()]
stmt = """
SELECT %s,
lo.occupation AS occupation_id,
lo.mean AS occupation_mean
FROM locations l
JOIN locations_occupations lo
ON l.code = lo.code
WHERE (lo.occupation = %%s OR lo.occupation = '00-0000')
AND l.code = '00000'
AND lo.year = '2010'
ORDER BY lo.occupation DESC
""" % ', '.join(fields)
fields.extend(('occupation_id', 'occupation_mean'))
cursor = connection.cursor()
cursor.execute(stmt, (occupation_id,))
doc = cursor.fetchone()
row = dict(zip(fields, doc))
cursor.close()
def convert(v):
if isinstance(v, Decimal):
return float(v)
else:
return v
national_occ = {}
for k, v in row.iteritems():
v = convert(v)
if k in res['data']:
if k.startswith('rpp_'):
v = 100.0
res['data'][k]['national'] = {
'value': v,
'cmp': smartcmp(res['data'][k]['value'], v, k),
}
elif k == 'occupation_mean':
national_occ['value'] = v
national_occ['cmp'] = cmp(res['data']['occupation']['value'], v)
elif k == 'occupation_id':
national_occ['id'] = v
national_occ['is_average'] = v == '00-0000'
res['data']['occupation']['national'] = national_occ
return res
