def welcomereply():
resp = twilio.twiml.Response()
logging.basicConfig( level=logging.INFO)
connInfo = {
"dbname": os.getenv('DBNAME', "echoalert"),
"user": os.getenv('DBUSER', "postgres"),
"host": os.getenv('DBHOST', "localhost"),
"port": os.getenv('DBPORT', "5432"),
"password": os.getenv('DBPASS', "defaultpassword"),
}
logging.info("initialized")
PgDb.setup( **connInfo)
fr = request.form.get("From")
sid = request.form.get("SmsMessageSid")
accountsid = request.form.get("AccountSid")
smssid = request.form.get("SmsSid")
body = request.form.get("Body")
if len(fr) > 0:
account = Account.get_account_by_phone(fr)
logging.info("found {} accounts".format(len(account)))
logging.info("From: {}, Body: {}".format(fr,body))
resp.message("I got your message, but i'm not programmed to know how to handle it.")
logging.info(request.form)
return str(resp)
def mark_sent(notify_id, msg):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"UPDATE echoalert.notifications SET status=1,status_message=%s, sent_ts=now() WHERE id=%s",
[msg, notify_id])
res = PgDb.getInstance().conn.commit()
cur.close()
return res
def insert(account_id, course_id, mapdata):
save = mapdata.copy()
save['account_id'] = account_id
save['course_id'] = course_id
save['created_ts'] = 'now()'
if 'course' in save:
save.pop('course', None)
PgDb.getInstance().insert("echoalert.grade_summary", save)
def add_courses(account_id, courses):
for course in courses:
savemap = {
'account_id': account_id,
'status': 1,
'created_ts': 'now()',
'course_name': course['course'],
'course_term': course['term']
}
logging.info("Adding new course: {}".format(course['course']))
PgDb.getInstance().insert("echoalert.courses", savemap)
def get_user_by_username(uname):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT id,echo_username,echo_password, notification_email,notification_sms FROM echoalert.accounts WHERE echo_username = %s",
(uname))
res = cur.fetchall()
cur.close()
return res
def main():
START_YEAR = 2003
END_YEAR = 2016
NUM_SCHOOLS = 1500
SCHEMA = 'lam' # use 'lax' for production
outfile_base = 'lax_rank_'
genders = ['boy', 'grl']
pgdb = PgDb(dbname='lax',
user='******',
password='******',
host='/tmp/',
port=5432)
for year in range(START_YEAR, END_YEAR + 1):
for gender in genders:
outfile_name = outfile_base + gender + '_' + str(year) + '.tsv'
sql = """SELECT
hr.zipf_weight,
hs.latitude,
hs.longitude,
hr.rank,
hs.raw_name || ', ' || hs.state || ', ' || hs.country
FROM """ + SCHEMA + """.high_sChools hs
INNER JOIN lax.hs_ranks hr ON hs.id = hr.hs_id
WHERE hr.gender = '""" + gender + "' " + \
"AND hr.year = " + str(year) + \
" AND hr.rank <= " + str(NUM_SCHOOLS) + \
" AND geolocated = True ORDER BY hr.rank;"
rows = pgdb.get_cursor(sql)
with open(outfile_name, 'w') as file:
header = 'weight\tlatitude\tlongitude\trank\ths_name\n'
file.write(header)
for row in rows:
file_line = str(row[0]) + '\t' + str(row[1]) + '\t' + str(
row[2]) + '\t' + str(row[3]) + '\t' + row[4] + '\n'
file.write(file_line)
file.close()
print('wrote ' + outfile_name)
print('Successfully completed write_heatmap_files.py')
def get_new_notices():
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"""SELECT N.id, AC.notification_email, AC.notification_sms,N.account_id,N.notification_type,N.created_ts
FROM echoalert.notifications AS N JOIN echoalert.accounts AS AC on AC.id=N.account_id
WHERE N.status=0 AND AC.enabled=true ORDER BY N.created_ts ASC""")
res = cur.fetchall()
cur.close()
return res
def get_grade_summary(account_id, course_id):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT * FROM echoalert.grade_summary WHERE account_id=%s AND course_id=%s ORDER BY created_ts DESC limit 1",
(account_id, course_id))
res = cur.fetchone()
cur.close()
return GradeSummary(res)
def insert(account_id, notifyType, notifydate):
save = {}
save['account_id'] = account_id
save['status'] = 0
save['notification_type'] = notifyType
save['created_ts'] = notifydate
return PgDb.getInstance().insert("echoalert.notifications", save)
def get_grade_summaries(account_id):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"with lastrecord AS ( SELECT groupset FROM echoalert.grade_summary WHERE account_id=%s ORDER by created_ts desc LIMIT 1) SELECT gs.* FROM lastrecord AS l JOIN echoalert.grade_summary AS gs ON gs.groupset = l.groupset",
[account_id])
res = cur.fetchall()
cur.close()
return res
'''
def insert(self):
mapdata = self.data.copy()
mapdata['assignment_type'] = self.data['assignment_type']
mapdata['account_id'] = self.course.data['account_id']
mapdata['course_id'] = self.course.data['id']
mapdata['due_date'] = self.parse_due_date(self.data['due'])
mapdata['created_ts'] = 'now()'
mapdata['completed'] = False
return PgDb.getInstance().insert("echoalert.assignments", mapdata)
def exist_list(self, hashtuple):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT hash FROM echoalert.assignments WHERE account_id=%s AND course_id=%s AND hash IN %s",
[
self.course.data['account_id'], self.course.data['id'],
hashtuple
])
res = cur.fetchall()
cur.close()
return res
def get_assignments_after(account_id, date):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"""
SELECT C.course_name,A.assignment_type,A.title,A.due,A.created_ts FROM echoalert.assignments AS A
LEFT JOIN echoalert.courses AS C ON C.id=A.course_id
WHERE A.account_id=%s AND A.created_ts > %s::timestamp - interval '5 min'""",
(account_id, date))
res = cur.fetchall()
cur.close()
return res
def get_course_grades(account_id):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"""SELECT DISTINCT ON (gs.course_id) c.course_name, gs.account_id,gs.score, gs.created_ts
FROM echoalert.grade_summary AS gs
JOIN echoalert.courses AS c ON c.id=gs.course_id
WHERE gs.account_id=1 ORDER BY gs.course_id, gs.created_ts DESC""",
(account_id))
res = cur.fetchall()
cur.close()
return res
def exists(self):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT id FROM echoalert.assignments WHERE account_id=%s AND course_id=%s AND title=%s AND due=%s AND assignment_type=%s",
[
self.course.data['account_id'], self.course.data['id'],
self.data['title'], self.data['due'],
self.data['assignment_type']
])
res = cur.fetchone()
cur.close()
return res
def get_user_paginate(start, limit):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT id,echo_username,echo_password, notification_email,notification_sms,echo_site FROM echoalert.accounts ORDER BY id asc offset %s limit %s",
(start, limit))
res = cur.fetchall()
cur.close()
userlist = []
for user in res:
userlist += [Account(user)]
return userlist
def main():
# N = number of items in population
# k = rank
# s = Zipf coefficient. > 1 is more left-skewed
N = 1500
s = 0.33
SCHEMA = 'lam' # Use 'lax' for production
pgdb = PgDb(dbname='lax',
user='******',
password='******',
host='/tmp/',
port=5432)
for k in range(1, N + 1):
z = ZipfNorm(N, k, s)
sql = ('UPDATE ' + SCHEMA + '.hs_ranks ' + 'SET zipf_weight = ' +
str(z) + ' WHERE rank = ' + str(k) + ';')
pgdb.exec_sql(sql)
print('Updated rank = ', k, ' with Zipf = ', '{0:.3f}'.format(z))
def get_courses(account_id):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"SELECT * FROM echoalert.courses WHERE account_id=%s AND status=1",
[account_id])
res = cur.fetchall()
cur.close()
courses = {}
for course in res:
courses[course['id']] = Course(course)
return courses
def get_account_by_phone(phone):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"""SELECT ea.id,na.sms_name,ea.echo_username,na.sms_contact FROM echoalert.notify_accounts AS na
JOIN echoalert.accounts AS ea ON ea.id=na.account_id
WHERE na.sms_contact=%s""", [phone])
res = cur.fetchall()
cur.close()
userlist = []
for user in res:
userlist += [Account(user)]
return userlist
def compare_grades_after(account_id, date):
cur = PgDb.getInstance().get_dict_cursor()
cur.execute(
"""
SELECT C.course_name,p.score FROM echoalert.grade_summary AS GS1
JOIN LATERAL (
SELECT * FROM echoalert.grade_summary AS GS
WHERE GS.account_id=%s AND GS.course_id = GS1.course_id
ORDER BY GS.created_ts DESC LIMIT 2) p on true
JOIN echoalert.courses AS C ON C.id=GS1.course_id
WHERE GS1.account_id=%s
AND GS1.created_ts > %s::timestamp - interval '5 min'""",
(account_id, account_id, date))
res = cur.fetchall()
cur.close()
return res
def main():
START_YEAR = 2003
END_YEAR = 2016
SCHEMA = 'lam' #change to lax for production
NUM_SCHOOLS_PER_YEAR = 1500
pgdb = PgDb(dbname='lax', user='******', password='******', host='/tmp/', port=5432)
urls = generateUrlRecords(START_YEAR, END_YEAR)
#hsRankRecords = []
for urlRecord in urls:
hsRankRecords = getHSRankRecords(urlRecord)
for HSRankRecord in hsRankRecords:
sql = getHSRankSQL(HSRankRecord, SCHEMA)
pgdb.exec_sql(sql)
print('Completed ', HSRankRecord.getGender(), ':', str(HSRankRecord.getYear()))
# populate the high_schools table with high schools
sql = ('INSERT INTO ' + SCHEMA + '.high_schools (' +
'raw_name, ' +
'state) ' +
'(SELECT DISTINCT ' +
'raw_hs_name, ' +
'state ' +
'FROM ' + SCHEMA + '.hs_ranks ' +
'WHERE rank <= ' + str(NUM_SCHOOLS_PER_YEAR) + ');')
pgdb.exec_sql(sql)
print('Populated ' + SCHEMA + '.high_schools')
# update hs_ranks.hs_id
sql = ('UPDATE ' + SCHEMA + '.hs_ranks AS hr ' +
'SET hs_id = (' +
'SELECT hs.id ' +
'FROM ' + SCHEMA + '.high_schools hs ' +
'WHERE hs.raw_name = hr.raw_hs_name ' +
'AND hs.state = hr.state);')
pgdb.exec_sql(sql)
print('Updated ' + SCHEMA + '.hs_ranks')
from notify import Notifier
logging.basicConfig(level=logging.INFO)
connInfo = {
"dbname": os.getenv('DBNAME', "echoalert"),
"user": os.getenv('DBUSER', "postgres"),
"host": os.getenv('DBHOST', "localhost"),
"port": os.getenv('DBPORT', "5432"),
"password": os.getenv('DBPASS', "defaultpassword"),
}
print connInfo
print "connecting to db"
PgDb.setup(**connInfo)
print "getting active users"
accounts = Account.get_user_paginate(0, 100)
print "loading scraper"
echo = Echosite()
for account in accounts:
echo.login(account.data['echo_site'], account.data['echo_username'],
account.data['echo_password'])
courses = account.get_courses()
if len(courses) == 0:
logging.info("adding new account... getting courses")
def main():
SCHEMA = 'lax' # Use 'lax' for production
num_schools = 3
pgdb = PgDb(dbname='lax', user='******', password='******', host='/tmp/', port=5432)
geocoder = 'googlev3'
#geocoder = 'nominatim'
'''
arcgis https://developers.arcgis.com/rest/geocode/api-reference/overview-world-geocoding-service.htm
baidu api_key http://developer.baidu.com/map/webservice-geocoding.htm
bing api_key https://msdn.microsoft.com/en-us/library/ff701715.aspx
geocodefarm https://www.geocode.farm/geocoding/free-api-documentation/
geocoderdotus http://geocoder.us/
geonames http://www.geonames.org/export/geonames-search.html
googlev3 https://developers.google.com/maps/documentation/geocoding/
liveaddress auth_token https://smartystreets.com/products/liveaddress-api
navidata http://www.navidata.pl
nominatim https://wiki.openstreetmap.org/wiki/Nominatim
opencage api_key http://geocoder.opencagedata.com/api.html
ERROR IN pygeo openmapquest http://developer.mapquest.com/web/products/open/geocoding-service
yahooplacefinder consumer_key https://developer.yahoo.com/boss/geo/docs/
whatthreewords api_key http://what3words.com/api/reference
DO NO USE yandex http://api.yandex.com/maps/doc/geocoder/desc/concepts/input_params.xml'''
# get list of high schools
sql = ('SELECT ' +
'id, ' +
'searched_name, ' +
'state, ' +
'country ' +
'FROM ' + SCHEMA + '.high_schools ' +
'WHERE geotried_' + geocoder + ' Is False ' +
'AND geolocated Is False ' +
'ORDER BY id DESC ' +
'LIMIT ' + str(num_schools) + ';')
HSList = pgdb.get_cursor(sql)
num_geocoded = 0
num_located = 0
lookup_counter = 0
for high_school in HSList:
num_geocoded += 1
hs_id = high_school[0]
# SCHEMA.high_schools.searched_name .state .country
lookup_hs_name = high_school[1] + ', ' + high_school[2] +', ' + high_school[3]
location = geocodeHS(geocoder, hs_id, lookup_hs_name)
lookup_counter += 1
if location == None:
sql = 'UPDATE ' + SCHEMA + '.high_schools SET geotried_' + geocoder + '=TRUE WHERE id=' + str(hs_id) + ';'
pgdb.exec_sql(sql)
print(str(lookup_counter) + ' of ' + str(num_schools) +
'\tdid not find:\t', str(hs_id), '\t', lookup_hs_name)
else:
sql = ("UPDATE " + SCHEMA + ".high_schools SET geotried_" + geocoder + " = TRUE, " +
"geolocated = TRUE, " +
"latitude = " + str(location.latitude) + ", " +
"longitude = " + str(location.longitude) + ", " +
"geo_loc = ST_SetSRID(ST_MakePoint(" + str(location.longitude) + "," + str(location.longitude) + "), 4326), "
"address = '" + location.address.replace("'", "''") + "', " +
"json_response = '" + str(location.raw).replace("'", "''") + # escape embedded ' with ''
"' WHERE id=" + str(hs_id) + ";")
pgdb.exec_sql(sql)
num_located += 1
print(str(lookup_counter) + ' of ' + str(num_schools) +
'\tfound:\t\t', str(hs_id), '\t', lookup_hs_name)
print('\n' + str(num_geocoded) + ' schools looked up\t', str(num_located), ' geocoded')
def main():
SCHEMA = 'lam' # Use 'lax' for production
pgdb = PgDb(dbname='lax',
user='******',
password='******',
host='/tmp/',
port=5432)
'''
Scripted high school name replacements:
HS High School
Sch School
CDS Country Day School
Did not script these possible replacements:
CS Country School
Christian School
Catholic School
'''
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET script_updated_name = REPLACE(raw_name, ' HS', ' High School')
WHERE raw_name LIKE '% HS';
'''
pgdb.exec_sql(sql)
print('HS to High School replacement made')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET script_updated_name = REPLACE(raw_name, ' Sch', ' School')
WHERE raw_name LIKE '% Sch';
'''
pgdb.exec_sql(sql)
print('Sch to School replacement made')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET script_updated_name = REPLACE(raw_name, ' CDS', ' Country Day School')
WHERE raw_name LIKE '% CDS';
'''
pgdb.exec_sql(sql)
print('CDS to Country Day School replacement made')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET script_updated_name = raw_name || ' High School'
WHERE raw_name NOT LIKE '%School%'
AND raw_name NOT LIKE '% CS'
AND script_updated_name IS NULL;
'''
pgdb.exec_sql(sql)
print('appended High School to names not containing School made')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET searched_name = COALESCE(script_updated_name, raw_name);
'''
pgdb.exec_sql(sql)
print('populated searched_name field')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET country = 'Canada'
WHERE state in ('AB', 'BC', 'MB', 'NB', 'NS', 'NT', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT');
'''
pgdb.exec_sql(sql)
print('updated Canadian schools')
sql = '''
UPDATE ''' + SCHEMA + '''.high_schools
SET country = 'United States of America'
WHERE country IS NULL;
'''
pgdb.exec_sql(sql)
print('updated USA schools')
print('prep_hs_names.py complete')
def insert(self, mapdata):
return PgDb.getInstance().insert("echoalert.accounts", mapdata)
def main():
SCHEMA = 'lam' #Change to lax for production
pgdb = PgDb(dbname='lax',
user='******',
password='******',
host='/tmp/',
port=5432)
# Prepare the database and schema
sql = ('DROP SCHEMA IF EXISTS ' + SCHEMA + ' CASCADE;')
pgdb.exec_sql(sql)
sql = 'CREATE SCHEMA ' + SCHEMA + ';'
pgdb.exec_sql(sql)
# create tables
sql = ('CREATE TABLE ' + SCHEMA + '.high_schools (' + '''
id SERIAL PRIMARY KEY,
created_on TIMESTAMP DEFAULT now(),
raw_name TEXT,
state TEXT,
country TEXT,
script_updated_name TEXT,
manually_updated_name TEXT,
searched_name TEXT,
geo_code_name TEXT,
json_response TEXT,
json JSONB,
address TEXT,
latitude NUMERIC,
longitude NUMERIC,
geo_loc GEOMETRY,
geo_accuracy TEXT,
geotried_arcgis BOOLEAN DEFAULT FALSE,
geotried_baidu BOOLEAN DEFAULT FALSE,
geotried_bing BOOLEAN DEFAULT FALSE,
geotried_databc BOOLEAN DEFAULT FALSE,
geotried_geocodefarm BOOLEAN DEFAULT FALSE,
geotried_geocoderdotus BOOLEAN DEFAULT FALSE,
geotried_geonames BOOLEAN DEFAULT FALSE,
geotried_googlev3 BOOLEAN DEFAULT FALSE,
geotried_liveaddress BOOLEAN DEFAULT FALSE,
geotried_navidata BOOLEAN DEFAULT FALSE,
geotried_nominatim BOOLEAN DEFAULT FALSE,
geotried_opencage BOOLEAN DEFAULT FALSE,
geotried_openmapquest BOOLEAN DEFAULT FALSE,
geotried_yahooplacefinder BOOLEAN DEFAULT FALSE,
geotried_whatthreewords BOOLEAN DEFAULT FALSE,
geotried_yandex BOOLEAN DEFAULT FALSE,
geolocated BOOLEAN DEFAULT FALSE);''')
pgdb.exec_sql(sql)
print(SCHEMA + '.high_shools created')
sql = ('CREATE TABLE ' + SCHEMA + '.hs_ranks (' +
'id SERIAL PRIMARY KEY,' +
'hs_id BIGINT REFERENCES ' + SCHEMA +
'.high_schools (id),' + 'created_on TIMESTAMP DEFAULT now(),' +
'url TEXT,' + 'gender CHAR(3),' +
'year NUMERIC,' + 'rank NUMERIC,' +
'zipf_weight NUMERIC,' + 'raw_hs_name TEXT,' +
'state CHAR(2));')
pgdb.exec_sql(sql)
print(SCHEMA + '.hs_ranks created')