def get(self, cursor=None, user_id=None, venue_id=None, **kwargs):
if (venue_id):
subqry = {'select': 'COUNT(id)',
'table': 'post_reports',
'where': ('post_id = posts.id')}
qry = {'select': ('posts.id', 'user_id', 'posts.venue_id', 'caption',
'time', 'hidden', 'users.forename',
'users.surname'),
'left_join': 'users',
'on': 'posts.user_id = users.id',
'table': 'posts',
'where': ('posts.venue_id = ?', 'hidden = 0',
'(' + util.query(**subqry) + ') < 3' #,
#'time > ' + str(util.now() - 691200)),
),
'order_by': 'time DESC',
'limit': 50}
cursor.execute(util.query(**qry), (venue_id,))
else:
qry = {'select': ('posts.id', 'venues.name', 'posts.time'),
'left_join': 'venues',
'on': 'posts.venue_id = venues.id',
'table': 'posts',
'where': ('posts.user_id = ?'),
'order_by': 'time DESC'}
cursor.execute(util.query(**qry), (user_id,))
return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, venue_id=None, delete=None,
promotion_id=None, title=None, description=None, start=None,
end=None, maximum=None, passcode=None, level=None, **kwargs):
if util.to_bool(delete) and promotion_id:
qry = {'update': 'promotions',
'set_values': ('hidden'),
'where': 'id = ?'}
cursor.execute(util.query(**qry), (1, promotion_id))
elif promotion_id:
qry = {'update': 'promotions',
'set_values': ('title', 'description', 'start', '[end]',
'maximum', 'passcode', 'venue_id', 'level'),
'where': 'id = ?'}
cursor.execute(util.query(**qry), (title, description, start, end,
maximum, passcode, venue_id,
level, promotion_id))
else:
qry = {'insert_into': 'promotions',
'columns': ('title', 'description', 'start', '[end]',
'maximum', 'creator', 'passcode',
'venue_id', 'level')}
cursor.execute(util.query(**qry), (title, description, start, end,
maximum, user_id, passcode,
venue_id, level))
return True
def _test_measures(numer_ids, numer_aggregate, section_tags, denom_reltype,
geom):
in_params = []
for numer_id in numer_ids:
in_params.append({
'numer_id': numer_id,
'normalization': 'predenominated'
})
params = query(u'''
SELECT {schema}OBS_GetMeta({geom}, '{in_params}')
'''.format(schema='cdb_observatory.' if USE_SCHEMA else '',
geom=geom,
in_params=json.dumps(in_params))).fetchone()[0]
# We can get duplicate IDs from multi-denominators, so for now we
# compress those measures into a single
params = OrderedDict([(p['id'], p) for p in params]).values()
assert_equal(len(params), len(in_params),
'Inconsistent out and in params for {}'.format(in_params))
q = u'''
SELECT * FROM {schema}OBS_GetData(ARRAY[({geom}, 1)::geomval], '{params}')
'''.format(schema='cdb_observatory.' if USE_SCHEMA else '',
geom=geom,
params=json.dumps(params).replace(u"'", "''"))
resp = query(q).fetchone()
assert_is_not_none(resp, 'NULL returned for {}'.format(in_params))
rawvals = resp[1]
vals = [v['value'] for v in rawvals]
assert_equal(len(vals), len(in_params))
for i, val in enumerate(vals):
assert_is_not_none(val, 'NULL for {}'.format(in_params[i]['numer_id']))
def test_getmeasure_performance(geom_complexity, api_method, normalization, geom, boundary):
print(api_method, geom_complexity, normalization, geom, boundary)
col = 'measure' if 'measure' in api_method.lower() else 'category'
results = []
rownums = (1, 5, 10, ) if geom_complexity == 'complex' else (5, 25, 50, )
for rows in rownums:
stmt = '''UPDATE obs_perftest_{complexity}
SET {col} = {schema}{api_method}({args})
WHERE cartodb_id <= {n}'''.format(
col=col,
complexity=geom_complexity,
schema='cdb_observatory.' if USE_SCHEMA else '',
api_method=api_method,
args=ARGS[api_method, normalization].format(geom, boundary),
n=rows)
start = time()
query(stmt)
end = time()
qps = (rows / (end - start))
results.append({
'rows': rows,
'qps': qps,
'stmt': stmt
})
print(rows, ': ', qps, ' QPS')
if 'OBS_RECORD_TEST' in os.environ:
record({
'geom_complexity': geom_complexity,
'api_method': api_method,
'normalization': normalization,
'boundary': boundary,
'geom': geom
}, results)
def get(self, cursor=None, user_id=None, venue_id=None, getall=None,
level=None, from_time=None, until_time=None, **kwargs):
red = {'select': 'COUNT(id)',
'table': 'promotion_redemptions',
'where': 'promotion_id = promotions.id'}
promo_qry = {'select': ['id', 'title', 'description',
'passcode', 'start', '[end]', 'maximum',
'creator', 'level',
'(' + util.query(**red) + ') AS redemptions'],
'table': 'promotions',
'where': ['venue_id = ?', 'hidden != 1'],
'order_by': 'id DESC'}
if from_time and until_time:
own_red = {'select': 'COUNT(id)',
'table': 'promotion_redemptions',
'where': ('promotion_id = promotions.id', 'time >= ' + from_time, 'time < ' + until_time, 'user_id = ' + str(user_id))}
promo_qry['select'].append('(' + util.query(**own_red) + ') AS own_redemptions')
if not util.to_bool(getall):
promo_qry['limit'] = 1
promo_qry['where'].append(str(util.now()) + ' >= start')
promo_qry['where'].append('([end] = 0 OR [end] > ' + str(util.now()) + ')')
promo_qry['where'].append('(maximum = 0 OR (' + util.query(**red) + ') < maximum)')
promo_qry['where'].append(level + ' >= level')
promo_qry['order_by'] = 'level DESC, id DESC'
cursor.execute(util.query(**promo_qry), (venue_id,))
row = cursor.fetchone()
if row:
return {t[0]: val for t, val in zip(cursor.description, row)}
else:
return None
cursor.execute(util.query(**promo_qry), (venue_id,))
return [util.row_to_dict(cursor, row) for row in cursor.fetchall()]
def test_getgeometryscores_performance(geom_complexity, api_method, filters, target_geoms):
print(api_method, geom_complexity, filters, target_geoms)
rownums = (1, 5, 10, ) if 'complex' in geom_complexity else (5, 25, 50,)
results = []
for rows in rownums:
stmt = '''SELECT {schema}{api_method}(geom, {filters}, {target_geoms})
FROM obs_perftest_{complexity}
WHERE cartodb_id <= {n}'''.format(
complexity=geom_complexity,
schema='cdb_observatory.' if USE_SCHEMA else '',
api_method=api_method,
filters=filters,
target_geoms=target_geoms,
n=rows)
start = time()
query(stmt)
end = time()
qps = (rows / (end - start))
results.append({
'rows': rows,
'qps': qps,
'stmt': stmt
})
print(rows, ': ', qps, ' QPS')
if 'OBS_RECORD_TEST' in os.environ:
record({
'geom_complexity': geom_complexity,
'api_method': api_method,
'filters': filters,
'target_geoms': target_geoms
}, results)
def set(self, cursor=None, user_id=None, post_id=None, **kwargs):
qry = {'select': 'id',
'table': 'post_reports',
'where': ('user_id = ?', 'post_id = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (user_id, post_id))
res = cursor.fetchone()
if not res:
qry = {'insert_into': 'post_reports',
'columns': ('user_id', 'post_id')}
cursor.execute(util.query(**qry), (user_id, post_id))
return True
def set(self, cursor=None, facebook_id=None, user_id=None, venue_id=None,
name=None, address=None, country=None, phone=None, email=None,
email_verified=None, category_id=None, headline=None, tonight=None,
website=None, facebook=None, twitter=None, v_facebook_id=None,
twitter_id=None, twitter_token=None, twitter_secret=None, lat=None,
lon=None, official=None, verified=None, customer_spend=None,
authenticated=None, creator_version=None, **kwargs):
data = {'name': name,
'address': address,
'country': country,
'phone': phone,
'email': email,
'email_verified': util.to_bool(email_verified),
'category_id': util.to_int(category_id),
'headline': headline,
'tonight': tonight,
'website': website,
'facebook': facebook,
'twitter': twitter,
'facebook_id': v_facebook_id,
'twitter_id': twitter_id,
'twitter_token': twitter_token,
'twitter_secret': twitter_secret,
'lat': util.to_float(lat),
'lon': util.to_float(lon),
'official': util.to_bool(official),
'verified': util.to_bool(verified),
'customer_spend': util.to_float(customer_spend),
'authenticated': util.to_bool(authenticated),
'creator': user_id,
'creator_version': creator_version}
columns = []
values = []
for key, val in data.iteritems():
if val != None:
columns.append(key)
values.append(val)
if venue_id:
qry = {'update': 'venues',
'set_values': columns,
'where': 'id = ?'}
values.append(venue_id)
cursor.execute(util.query(**qry), values)
else:
qry = {'insert_into': 'venues',
'columns': columns}
cursor.execute(util.query(**qry), values)
cursor.execute(util.query(last_id=True))
return int(cursor.fetchone().identity)
def get(self, cursor=None, venue_id=None, **kwargs):
nameqry = {'select': ('CONCAT(forename, \' \', SUBSTRING(surname, 1, 1))',),
'table': 'users',
'where': ('users.id = venue_comments.user_id',)}
fbidqry = {'select': ('facebook_id',),
'table': 'users',
'where': ('users.id = venue_comments.user_id',)}
qry = {'select': ('id', 'user_id', 'venue_id', 'time', 'comment',
'(' + util.query(**nameqry) + ') AS name',
'(' + util.query(**fbidqry) + ') AS facebook_id'),
'table': 'venue_comments',
'where': ('venue_id = ?',),
'order_by': 'time DESC',
'limit': 10}
cursor.execute(util.query(**qry), (venue_id,))
return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, post_id=None, media_id=None,
**kwargs):
qry = {'insert_into': 'post_shares',
'columns': ('user_id', 'post_id', 'media_id', 'time')}
cursor.execute(util.query(**qry), (user_id, post_id, media_id,
util.now()))
return True
def get(self, cursor=None, user_id=None, **kwargs):
qry = {'select': ('id', 'term', 'time'),
'table': 'user_searches',
'where': 'user_id = ?',
'order_by': 'time DESC'}
cursor.execute(util.query(**qry), (user_id,))
return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, venue_id=None, comment=None,
**kwargs):
qry = {'insert_into': 'venue_comments',
'columns': ('user_id', 'venue_id', 'time', 'comment')}
cursor.execute(util.query(**qry), (user_id, venue_id, util.now(),
comment))
return True
def get(self, cursor=None, **kwargs):
qry = {'select': ('id', 'type'),
'table': 'venue_categories',
'where': '',
'order_by': 'type ASC'}
cursor.execute(util.query(**qry))
return [util.row_to_dict(cursor, row) for row in cursor]
def query(company, page_results=10000):
url = "https://api.rocketreach.co/v1/api/search"
params = {
"api_key": keys["rocketreach"]["key"],
"company": "{}".format(company),
"page_size": page_results
}
more_results = True
while more_results:
r = util.query(url, params)
response = json.loads(r.text)
if "error" in response or ("detail" in response and response["detail"]
== "Invalid API key"):
sys.stderr.write("Query error: {}\n".format(response))
sys.exit(1)
if "profiles" in response:
for profile in response["profiles"]:
yield profile
pagination = response["pagination"]
if pagination["total"] > pagination["nextPage"]:
params["start"] = pagination["nextPage"]
else:
more_results = False
def index(self, cursor=None, venue_id=None, user_id=None, hashd=None,
**kwargs):
if not venue_id or not user_id or not hashd:
return 'Error!'
qry = {'select': ('name', 'email', 'phone', 'website'),
'table': 'venues',
'where': ('id = ?')}
cursor.execute(util.query(**qry), (venue_id,))
venue = cursor.fetchone()
if not venue:
return 'Error!'
qry = {'select': ('forename', 'surname'),
'table': 'users',
'where': ('id = ?')}
cursor.execute(util.query(**qry), (user_id,))
user = cursor.fetchone()
if not user:
return 'Error!'
if hashd != hashlib.md5(venue.email + '|' + str(venue_id) + '|' + str(user_id) + '|confirm|' + os.environ['APP_SECRET']).hexdigest():
return 'Error!'
qry = {'update': 'venues',
'set_values': ('email_verified'),
'where': 'id = ?'}
cursor.execute(util.query(**qry), (1, venue_id))
with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'email_confirmed.txt'), 'rb') as f:
msg = f.read()
msg = msg.replace('[EmailAddress]', venue.email)
msg = msg.replace('[PhoneNumber]', venue.phone)
msg = msg.replace('[Website]', venue.website)
msg = msg.replace('[Name]', user.forename + ' ' + user.surname)
msg = msg.replace('[VenueName]', venue.name)
subject = 'Thanks for verifying [EmailAddress], we will now complete the verification of [VenueName]'
subject = subject.replace('[EmailAddress]', venue.email)
subject = subject.replace('[VenueName]', venue.name)
msg = email.mime.text.MIMEText(msg)
msg['Subject'] = subject
msg['From'] = os.environ['EMAIL']
msg['To'] = venue.email
s = smtplib.SMTP(os.environ['SMTP_SERVER'])
s.ehlo()
s.starttls()
s.ehlo()
s.login(os.environ['SMTP_USER'], os.environ['SMTP_PASS'])
s.sendmail(msg['From'], [msg['To']], msg.as_string())
s.quit()
return 'Confirmed.'
def set(self, cursor=None, user_id=None, venue_id=None, caption=None,
hide=None, post_id=None, image=None, **kwargs):
if post_id and util.to_bool(hide):
qry = {'update': 'posts',
'set_values': ('hidden'),
'where': 'id = ?'}
cursor.execute(util.query(**qry), ('1', post_id))
return post_id
else:
qry = {'insert_into': 'posts',
'columns': ('user_id', 'venue_id', 'caption', 'time')}
cursor.execute(util.query(**qry), (user_id, venue_id, caption,
util.now()))
cursor.execute(util.query(last_id=True))
post_added = int(cursor.fetchone().identity)
azureutil.store(image.file, 'post', str(post_added))
return post_added
def set(self, cursor=None, user_id=None, venue_id=None, **kwargs):
qry = {'select': 'id',
'table': 'venue_managers',
'where': ('user_id = ?', 'venue_id = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (user_id, venue_id))
res = cursor.fetchone()
if not res:
qry = {'insert_into': 'venue_managers',
'columns': ('user_id', 'venue_id', 'time')}
cursor.execute(util.query(**qry), (user_id, venue_id, util.now()))
qry = {'update': 'venues',
'set_values': ('official'),
'where': 'id = ?'}
cursor.execute(util.query(**qry), (1, venue_id))
qry = {'select': ('name', 'email'),
'table': 'venues',
'where': ('id = ?')}
cursor.execute(util.query(**qry), (venue_id,))
venue = cursor.fetchone()
qry = {'select': ('forename', 'surname'),
'table': 'users',
'where': ('id = ?')}
cursor.execute(util.query(**qry), (user_id,))
user = cursor.fetchone()
with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'email_confirm.txt'), 'rb') as f:
msg = f.read()
msg = msg.replace('[Name]', user.forename + ' ' + user.surname)
msg = msg.replace('[VenueName]', venue.name)
msg = msg.replace('[Link]', 'https://shnergle-api.azurewebsites.net/confirm/?venue_id=' + str(venue_id) + '&user_id=' + str(user_id) + '&hashd=' + hashlib.md5(venue.email + '|' + str(venue_id) + '|' + str(user_id) + '|confirm|' + os.environ['APP_SECRET']).hexdigest())
subject = 'Verify Email Address ownership for [VenueName] on Shnergle'
subject.replace('[VenueName]', venue.name)
msg = email.mime.text.MIMEText(msg)
msg['Subject'] = subject
msg['From'] = os.environ['EMAIL']
msg['To'] = venue.email
s = smtplib.SMTP(os.environ['SMTP_SERVER'])
s.ehlo()
s.starttls()
s.ehlo()
s.login(os.environ['SMTP_USER'], os.environ['SMTP_PASS'])
s.sendmail(msg['From'], [msg['To']], msg.as_string())
s.quit()
return True
def set(self, cursor=None, user_id=None, staff_user_id=None, venue_id=None,
manager=None, promo_perm=None, delete=None, **kwargs):
if util.to_bool(delete):
qry = {'delete': 'venue_staff',
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
qry = {'delete': 'venue_managers',
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
elif util.to_bool(manager):
qry = {'select': 'id',
'table': 'venue_managers',
'where': ('user_id = ?', 'venue_id = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
res = cursor.fetchone()
if not res:
qry = {'delete': 'venue_staff',
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
qry = {'insert_into': 'venue_managers',
'columns': ('user_id', 'venue_id', 'time')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id,
util.now()))
else:
qry = {'select': 'id',
'table': 'venue_staff',
'where': ('user_id = ?', 'venue_id = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
res = cursor.fetchone()
if not res:
qry = {'delete': 'venue_managers',
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id))
qry = {'insert_into': 'venue_staff',
'columns': ('user_id', 'venue_id', 'time',
'promo_perm')}
cursor.execute(util.query(**qry), (staff_user_id, venue_id,
util.now(),
1 if util.to_bool(promo_perm) else 0))
else:
qry = {'update': 'venue_staff',
'set_values': ('promo_perm'),
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (1 if util.to_bool(promo_perm) else 0,
staff_user_id, venue_id))
return True
def set(self, cursor=None, user_id=None, term=None, **kwargs):
qry = {'select': 'id',
'table': 'user_searches',
'where': ('user_id = ?', 'term = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (user_id, term))
res = cursor.fetchone()
if res:
qry = {'update': 'user_searches',
'set_values': ('time'),
'where': 'user_id = ?'}
cursor.execute(util.query(**qry), (util.now(), user_id))
else:
qry = {'insert_into': 'user_searches',
'columns': ('user_id', 'term', 'time')}
cursor.execute(util.query(**qry), (user_id, term, util.now()))
return True
def set(self, cursor=None, user_id=None, promotion_id=None, **kwargs):
cnt = {'select': ('COUNT(id)'),
'table': 'promotion_redemptions',
'where': ('promotion_id = promotions.id')}
promo = {'select': ('[end]', 'maximum', 'passcode',
'(' + util.query(**cnt) + ') AS count'),
'table': 'promotions',
'where': ('id = ?')}
cursor.execute(util.query(**promo), (promotion_id,))
row = cursor.fetchone()
if int(row.end) != 0 and int(row.end) < util.now():
return 'time'
if int(row.maximum) != 0 and int(row.count) >= int(row.maximum):
return 'number'
qry = {'insert_into': 'promotion_redemptions',
'columns': ('user_id', 'promotion_id', 'time')}
cursor.execute(util.query(**qry), (user_id, promotion_id, util.now()))
return row.passcode
def set(self, cursor=None, user_id=None, venue_id=None, following=None,
**kwargs):
qry = {'select': 'id',
'table': 'venue_followers',
'where': ('user_id = ?', 'venue_id = ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (user_id, venue_id))
res = cursor.fetchone()
if util.to_bool(following) and not res:
qry = {'insert_into': 'venue_followers',
'columns': ('user_id', 'venue_id')}
cursor.execute(util.query(**qry), (user_id, venue_id))
elif not util.to_bool(following) and res:
qry = {'delete': 'venue_followers',
'where': ('user_id = ?', 'venue_id = ?')}
cursor.execute(util.query(**qry), (user_id, venue_id))
return True
def train_al_and_report(model_name, kernel, warp, ard):
dataset_dir = os.path.join(MODEL_DIR, DATASET)
try:
os.makedirs(dataset_dir)
except OSError:
print "skipping output folder"
for fold in xrange(1):
fold_dir = os.path.join(SPLIT_DIR, DATASET, str(fold))
train_data = np.loadtxt(os.path.join(fold_dir, 'train'))
test_data = np.loadtxt(os.path.join(fold_dir, 'test'))
params_file = None
output_dir = os.path.join(dataset_dir, str(fold))
try:
os.makedirs(output_dir)
except OSError:
print "skipping output folder"
if ard:
iso_dir = output_dir.replace('True', 'False')
params_file = os.path.join(iso_dir, 'params')
# Split train into train and pool
pool_data = train_data[50:]
train_data = train_data[:50]
metrics_list = []
while True:
# Train gp
gp = util.train_gp_model(train_data, kernel, warp, ard, params_file)
# Get metrics on test
metrics = util.get_metrics(gp, test_data)
metrics_list.append([metrics[0], metrics[1], metrics[2][0],
metrics[2][1], metrics[3]])
# Predict pool and select instance in pool with higher variance
new_instance, new_i = util.query(gp, pool_data)
# Update train and pool
train_data = np.append(train_data, [new_instance], axis=0)
pool_data = np.delete(pool_data, (new_i), axis=0)
#if pool_data.shape[0] == 0:
# break
if train_data.shape[0] == 500:
break
print pool_data.shape
gc.collect(2)
# Final metrics on full train set (sanity check)
gp = util.train_gp_model(train_data, kernel, warp, ard, params_file)
metrics = util.get_metrics(gp, test_data)
metrics_list.append([metrics[0], metrics[1], metrics[2][0],
metrics[2][1], metrics[3]])
util.save_metrics_list(metrics_list, os.path.join(output_dir, 'metrics'))
def get(self, cursor=None, user_id=None, **kwargs):
qry = {'select': ('venues.name', 'promotion_redemptions.time',
'promotions.passcode', 'promotions.description'),
'left_join': ('promotions', 'venues'),
'on': ('promotion_redemptions.promotion_id = promotions.id', 'venues.id = promotions.venue_id'),
'table': 'promotion_redemptions',
'where': ('promotion_redemptions.user_id = ?'),
'order_by': 'time DESC'}
cursor.execute(util.query(**qry), (user_id,))
return [util.row_to_dict(cursor, row) for row in cursor]
def retrieve(self, cursor=None, user_id=None, term=None):
if util.to_bool(term):
qry = {'select': ['id',
'facebook_id',
'forename',
'surname'
],
'table': 'users',
'where': ("CONCAT(forename, \' \', surname) LIKE ?",),
'order_by': 'surname ASC, forename ASC'}
cursor.execute(util.query(**qry), ("%" + term.replace(' ', "%") + "%",))
return [util.row_to_dict(cursor, row) for row in cursor]
else:
qry = {'select': ['id',
'facebook',
'twitter',
'forename',
'surname',
'age',
'birth_day',
'birth_month',
'birth_year',
'gender',
'employee',
'joined',
'country',
'language',
'email',
'top5',
'save_locally',
'last_login',
'last_facebook',
'last_twitter'
],
'table': 'users',
'order_by': 'id'}
qry['select'].append('twitter_id')
qry['select'].append('twitter_token')
qry['select'].append('twitter_secret')
qry.update({'where': 'id = ?', 'limit': 1})
cursor.execute(util.query(**qry), (user_id,))
res = cursor.fetchone()
return util.row_to_dict(cursor, res)
def get(self, cursor=None, venue_id=None, from_time=None, until_time=None,
own=None, user_id=None, **kwargs):
qry = {'select': 'COUNT(id) AS cnt',
'table': 'venue_rsvps',
'where': ('venue_id = ?', 'maybe = 1', 'going = 0',
'time >= ?', 'time < ?')}
values = (venue_id, from_time, until_time)
if util.to_bool(own):
qry['where'] += ('user_id = ?',)
values += (user_id,)
cursor.execute(util.query(**qry), values)
maybe = cursor.fetchone().cnt
qry = {'select': 'COUNT(id) AS cnt',
'table': 'venue_rsvps',
'where': ('venue_id = ?', 'going = 1',
'time >= ?', 'time < ?')}
if util.to_bool(own):
qry['where'] += ('user_id = ?',)
cursor.execute(util.query(**qry), values)
going = cursor.fetchone().cnt
return {'maybe': maybe, 'going': going}
def get(self, cursor=None, venue_id=None, **kwargs):
nameqry = {'select': ('CONCAT(forename, \' \', surname)',),
'table': 'users'}
fbidqry = {'select': ('facebook_id',),
'table': 'users'}
nameqry['where'] = ('users.id = venue_staff.user_id',)
fbidqry['where'] = nameqry['where']
qry = {'select': ('id', 'user_id', 'promo_perm', 'time',
'(' + util.query(**nameqry) + ') AS name',
'(' + util.query(**fbidqry) + ') AS facebook_id'),
'table': 'venue_staff',
'where': 'venue_id = ?',
'order_by': 'time DESC'}
cursor.execute(util.query(**qry), (venue_id,))
staff = [util.row_to_dict(cursor, row) for row in cursor]
nameqry['where'] = ('users.id = venue_managers.user_id',)
fbidqry['where'] = nameqry['where']
qry = {'select': ('id', 'user_id', 'time',
'(' + util.query(**nameqry) + ') AS name',
'(' + util.query(**fbidqry) + ') AS facebook_id'),
'table': 'venue_managers',
'where': 'venue_id = ?',
'order_by': 'time DESC'}
cursor.execute(util.query(**qry), (venue_id,))
managers = [util.row_to_dict(cursor, row) for row in cursor]
return {'staff': staff, 'managers': managers}
def get(self, cursor=None, user_id=None, entity=None, entity_id=None,
**kwargs):
if not entity or not entity_id:
raise cherrypy.HTTPError(404)
if entity == 'venue':
entity = 'post'
venue_id = entity_id
subqry = {'select': 'COUNT(id)',
'table': 'post_reports',
'where': ('post_id = posts.id')}
qry = {'select': ('id', 'venue_id', 'time'),
'table': 'posts',
'where': ('venue_id = ?', 'hidden = 0',
'(' + util.query(**subqry) + ') < 3' #,
#'time > ' + str(util.now() - 691200)),
),
'order_by': 'time DESC',
'limit': 50}
cursor.execute(util.query(**qry), (entity_id,))
try:
entity_id = str(cursor.fetchone().id)
qry = {'insert_into': 'venue_loads',
'columns': ('user_id', 'venue_id', 'time')}
cursor.execute(util.query(**qry), (user_id, venue_id, util.now()))
image = azureutil.retrieve(entity, entity_id)
if not image:
image = cherrypy.thread_data.placeholder_image
except:
image = cherrypy.thread_data.placeholder_image
else:
image = azureutil.retrieve(entity, entity_id)
if not image:
image = cherrypy.thread_data.placeholder_image
#if image:
# cherrypy.response.headers['Content-Type'] = 'image/jpeg'
# return image
cherrypy.response.headers['Content-Type'] = 'image/jpeg'
return image
def set(self, cursor=None, user_id=None, venue_id=None, maybe=None,
going=None, from_time=None, until_time=None, **kwargs):
qry = {'select': 'id',
'table': 'venue_rsvps',
'where': ('user_id = ?', 'venue_id = ?',
'time >= ?', 'time < ?'),
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (user_id, venue_id, from_time,
until_time))
res = cursor.fetchone()
if res:
values = []
columns = []
if maybe:
values.append(util.to_bool(maybe))
columns.append('maybe')
if going:
values.append(util.to_bool(going))
columns.append('going')
values.append(res.id)
qry = {'update': 'venue_rsvps',
'set_values': columns,
'where': 'id = ?'}
cursor.execute(util.query(**qry), values)
else:
values = [user_id, venue_id, util.now()]
columns = ['user_id', 'venue_id', 'time']
if maybe:
values.append(util.to_bool(maybe))
columns.append('maybe')
if going:
values.append(util.to_bool(going))
columns.append('going')
qry = {'insert_into': 'venue_rsvps',
'columns': columns}
cursor.execute(util.query(**qry), values)
return True
def wrap(cls, object_id=None, form_data=None, request=None, session=None):
form = wrapped(cls)
if object_id is not None:
obj = util.query(cls, object_id, request=request, session=session)
if obj is not None:
for field_name in form.__dict__.keys():
attribute_name = form.__dict__[field_name].attr
value = obj.__dict__[attribute_name]
form.__dict__[field_name].data = value
elif form_data is not None:
for field_name in form_data.keys():
if field_name in form.__dict__.keys():
form.__dict__[field_name].data = form_data[field_name]
setattr(form, '__form_name__', cls.__form_name__)
setattr(form, '__form_attached_to__', cls)
return form
def thresholds(self, cursor):
users = {'select': 'COUNT(id) AS count', 'table': 'users'}
cursor.execute(util.query(**users))
users = cursor.fetchone().count
thresholds = []
for percent in (0.8, 0.95, 0.99):
number = math.floor(percent * users)
venue_shares = {'select': 'COUNT(id)',
'table': 'venue_shares',
'where': ('user_id = users.id',
'time > ' + str(util.now() - 2592000))}
post_shares = {'select': 'COUNT(id)',
'table': 'post_shares',
'where': ('user_id = users.id',
'time > ' + str(util.now() - 2592000))}
comments = {'select': 'COUNT(id)',
'table': 'venue_comments',
'where': ('user_id = users.id',
'time > ' + str(util.now() - 2592000))}
likes = {'select': 'COUNT(id)',
'table': 'post_likes',
'where': ('user_id = users.id',
'time > ' + str(util.now() - 2592000))}
posts = {'select': 'COUNT(id)',
'table': 'posts',
'where': ('user_id = users.id',
'time > ' + str(util.now() - 2592000))}
thresholdqry = {'select': ('((' + util.query(**venue_shares) + ') * 5 + ' +
'(' + util.query(**post_shares) + ') * 5 + ' +
'(' + util.query(**posts) + ') * 4 + ' +
'(' + util.query(**comments) + ') * 2 + ' +
'(' + util.query(**likes) + ') * 2) AS count',),
'table': 'users',
'group_by': 'id',
'order_by': 'count',
'limit': (number - 1, 1)}
cursor.execute(util.query(**thresholdqry))
count = cursor.fetchone()
thresholds.append(count.count if count else 0)
return thresholds
'fr.insee.P12_RP_CASE', 'fr.insee.P12_RP_TTEGOU', 'fr.insee.P12_RP_ELEC',
'fr.insee.P12_ACTOCC15P_ILT45D', 'fr.insee.P12_RP_CHOS',
'fr.insee.P12_RP_HABFOR', 'fr.insee.P12_RP_EAUCH', 'fr.insee.P12_RP_BDWC',
'fr.insee.P12_RP_MIDUR', 'fr.insee.P12_RP_CLIM', 'fr.insee.P12_RP_MIBOIS',
'fr.insee.P12_RP_CASE', 'fr.insee.P12_RP_TTEGOU', 'fr.insee.P12_RP_ELEC',
'fr.insee.P12_ACTOCC15P_ILT45D', 'uk.ons.LC3202WA0007',
'uk.ons.LC3202WA0010', 'uk.ons.LC3202WA0004', 'uk.ons.LC3204WA0004',
'uk.ons.LC3204WA0007', 'uk.ons.LC3204WA0010', 'br.geo.subdistritos_name'
])
MEASURE_COLUMNS = query('''
SELECT ARRAY_AGG(DISTINCT numer_id) numer_ids,
numer_aggregate,
denom_reltype,
section_tags
FROM observatory.obs_meta
WHERE numer_weight > 0
AND numer_id NOT IN ('{skip}')
AND section_tags IS NOT NULL
AND subsection_tags IS NOT NULL
GROUP BY numer_aggregate, section_tags, denom_reltype
'''.format(skip="', '".join(SKIP_COLUMNS))).fetchall()
#CATEGORY_COLUMNS = query('''
#SELECT distinct numer_id
#FROM observatory.obs_meta
#WHERE numer_type ILIKE 'text'
#AND numer_weight > 0
#''').fetchall()
#
#BOUNDARY_COLUMNS = query('''
#SELECT id FROM observatory.obs_column
'''INSERT INTO obs_perftest_complex (point, geom, offset_geom, name)
SELECT ST_PointOnSurface(the_geom) point,
the_geom geom,
ST_Translate(the_geom, -0.1, 0.1) offset_geom,
geom_refs AS name
FROM (SELECT * FROM {schema}OBS_GetBoundariesByGeometry(
st_makeenvelope(-75.05437469482422,40.66319159533881,
-73.81885528564453,41.745696344339564, 4326),
'us.census.tiger.county_clipped')) foo
ORDER BY ST_NPoints(the_geom) DESC
LIMIT 50;'''):
q_formatted = q.format(
schema='cdb_observatory.' if USE_SCHEMA else '',
)
start = time()
resp = query(q_formatted)
end = time()
print('{} for {}'.format(int(end - start), q_formatted))
if q.lower().startswith('insert'):
if resp.rowcount == 0:
raise Exception('''Performance fixture creation "{}" inserted 0 rows,
this will break tests. Check the query to determine
what is going wrong.'''.format(q_formatted))
commit()
ARGS = {
('OBS_GetMeasureByID', None): "name, 'us.census.acs.B01001002', '{}'",
('OBS_GetMeasure', 'predenominated'): "{}, 'us.census.acs.B01003001', null, {}",
('OBS_GetMeasure', 'area'): "{}, 'us.census.acs.B01001002', 'area', {}",
('OBS_GetMeasure', 'denominator'): "{}, 'us.census.acs.B01001002', 'denominator', {}",
def query(q):
url = "http://avoindata.prh.fi/bis/v1/"
return util.query(url + q)
import time
import json
import random
import pandas as pd
import urllib.parse
import urllib.request
import wikiparser as parser
import wikipedia as wiki
from pprint import pprint
from nltk.tokenize import word_tokenize
import util
import integration
QA = integration.QustionAnswering()
myquery = util.query()
app = Flask(__name__, static_folder='static')
app.config['DEBUG'] = True
def get_url(string):
try:
tmp = wiki.page(string)
return tmp.url
except Exception as e:
info = e.__str__()
entities = []
entities = info.split("\n")[1:-3]
if(len(entities) == 0):
return ""
return wiki.page(entities[0]).url
def index():
# list of group numbers
groups = [1, 2, 3, 4]
# list to hold group data
user_data = []
# list of all countries
countries = ['USA', 'Canada', 'UK', 'Romania', 'Switzerland', 'Rwanda', 'Hong Kong', 'France', 'C yprus', \
'Israel', 'Portugal', 'Ireland I', 'Germany', 'Australia', 'China', 'New Zealand', 'Palestine']
# loop to query data for each group
for x in groups:
# loop split data by country
for country in countries:
data = util.query('WebAppsDatabase.db', x, country)
# loop to split data if there are more than 10 elements
if len(data) >= 10:
labels = util.cluster_user_data(data)
data = util.split_user_data(data, labels)
# adds data to user_data list
user_data.append(data)
# loop to see if an element is a list of split data
# Only used to see which countries are at which index and have split data
for data in user_data:
if len(data) != 0:
print(data[0][1])
print(user_data.index(data))
print("\n")
if isinstance(data[0], list):
country = util.get_country(data[0][0])
print("***SPLIT***")
print(country)
print(user_data.index(data))
print("\n")
return render_template('index.html',
column_html=column_names,
data1usa1_html=user_data[0][0],
data1usa2_html=user_data[0][1],
data1usa3_html=user_data[0][2],
data1canada_html=user_data[1],
data1uk_html=user_data[2],
data1romania1_html=user_data[3][0],
data1romania2_html=user_data[3][1],
data1romania3_html=user_data[3][2],
data1switz_html=user_data[4],
data1rwanda_html=user_data[5],
data1isreal_html=user_data[9],
data1germany_html=user_data[12],
data2usa1_html=user_data[17][0],
data2usa2_html=user_data[17][1],
data2usa3_html=user_data[17][2],
data2canada_html=user_data[18],
data2uk_html=user_data[19],
data2romania_html=user_data[20],
data2switz_html=user_data[21],
data2rwanda_html=user_data[22],
data2hongkong_html=user_data[23],
data2france_html=user_data[24],
data2germany_html=user_data[29],
data2nz_html=user_data[32],
data2pal_html=user_data[33],
data3usa1_html=user_data[34][0],
data3usa2_html=user_data[34][1],
data3usa3_html=user_data[34][2],
data3canada_html=user_data[35],
data3uk_html=user_data[36],
data3romania1_html=user_data[37][0],
data3romania2_html=user_data[37][1],
data3romania3_html=user_data[37][2],
data3switz1_html=user_data[38][0],
data3switz2_html=user_data[38][1],
data3switz3_html=user_data[38][2],
data3rwanda_html=user_data[39],
data3portugal_html=user_data[44],
data3ireland_html=grp3ireland,
data3germany_html=user_data[46],
data3australia_html=user_data[47],
data3china_html=user_data[48],
data4usa1_html=user_data[51][0],
data4usa2_html=user_data[51][1],
data4usa3_html=user_data[51][2],
data4canada1_html=user_data[52][0],
data4canada2_html=user_data[52][1],
data4canada3_html=user_data[52][2],
data4uk_html=user_data[53],
data4romania1_html=user_data[54][0],
data4romania2_html=user_data[54][1],
data4romania3_html=user_data[54][2],
data4switz_html=user_data[55],
data4portugal_html=user_data[61],
data4germany_html=user_data[63],
data4australia_html=user_data[64])
def get(self, cursor=None, user_id=None, term=None, following_only=None,
my_lat=None, my_lon=None, distance=None, own=None, quiet=None,
trending=None, from_time=None, until_time=None, promotions=None,
level=None, around_me=None, **kwargs):
subqry = {'select': 'COUNT(id)',
'table': 'venue_followers',
'where': ('user_id = ' + str(user_id),
'venue_id = venues.id')}
red = {'select': 'COUNT(id)',
'table': 'promotion_redemptions',
'where': 'promotion_id = promotions.id'}
promoqry = {'select': 'COUNT(id)',
'table': 'promotions',
'where': ('venue_id = venues.id',
str(util.now()) + ' >= start',
'([end] = 0 OR [end] > ' + str(util.now()) + ')',
'(maximum = 0 OR (' + util.query(**red) + ') < maximum)',
level + ' >= level',
'hidden != 1')}
managerqry = {'select': 'COUNT(id)',
'table': 'venue_managers',
'where': ('user_id = ' + str(user_id),
'venue_id = venues.id')}
staffqry = {'select': 'COUNT(id)',
'table': 'venue_staff',
'where': ('user_id = ' + str(user_id),
'venue_id = venues.id')}
staffppqry = {'select': 'SUM(promo_perm)',
'table': 'venue_staff',
'where': ('user_id = ' + str(user_id),
'venue_id = venues.id')}
fields = ['id', 'name', 'address', 'country', 'phone', 'email',
'email_verified', 'category_id', 'headline', 'tonight',
'website', 'facebook', 'twitter', 'facebook_id',
'twitter_id', 'twitter_token', 'twitter_secret', 'lat',
'lon', 'official', 'verified', 'customer_spend',
'authenticated', 'creator',
'(' + util.query(**managerqry) + ') AS manager',
'(' + util.query(**staffqry) + ') AS staff',
'(' + util.query(**staffppqry) + ') AS promo_perm',
"(" + util.query(**subqry) + ") AS following",
'(' + util.query(**promoqry) + ') AS promotions']
order_by = ('name ASC',)
if term:
where = ("name LIKE ?",)
elif util.to_bool(following_only):
where = ("(" + util.query(**subqry) + ") > 0")
elif own:
where = ('(' + util.query(**managerqry) + ') = 1 OR (' + util.query(**staffqry) + ') = 1')
elif my_lat and my_lon and distance:
maybe = {'select': 'COUNT(id)',
'table': 'venue_rsvps',
'where': ('maybe = 1', 'venue_id = venues.id',
'going = 0', 'time >= ?', 'time < ?')}
going = {'select': 'COUNT(id)',
'table': 'venue_rsvps',
'where': ('going = 1', 'venue_id = venues.id',
'time >= ?', 'time < ?')}
if util.to_bool(quiet):
order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 ASC',)
elif util.to_bool(trending):
order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 DESC',)
else:
order_by = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) ASC',)
where = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) <= ? * ?',)
if util.to_bool(promotions):
where += ('(' + util.query(**promoqry) + ') > 0',)
elif util.to_bool(quiet) or util.to_bool(trending):
fields[0] = 'TOP(12) id'
elif util.to_bool(around_me):
psubqry = {'select': 'COUNT(id)',
'table': 'post_reports',
'where': ('post_id = posts.id')}
post_count = {'select': 'CASE WHEN COUNT(id) > 0 THEN 1 ELSE 0 END',
'table': 'posts',
'where': ('posts.venue_id = venues.id',
'hidden = 0',
'(' + util.query(**psubqry) + ') < 3',
'time > ' + str(util.now() - 691200))}
order_by = ('(' + util.query(**post_count) + ') DESC',) + order_by
else:
where = ''
qry = {'select': fields,
'table': 'venues',
'where': where,
'order_by': order_by}
if term:
cursor.execute(util.query(**qry), ("%" + term + "%",))
return [util.row_to_dict(cursor, row) for row in cursor]
else:
values = tuple()
if my_lat and my_lon and distance:
values += (float(my_lat), float(my_lat), float(my_lon),
float(my_lon), float(distance), float(distance))
if util.to_bool(quiet) is None and util.to_bool(trending) is None:
values += (float(my_lat), float(my_lat), float(my_lon),
float(my_lon))
else:
values += (from_time, until_time, from_time, until_time)
cursor.execute(util.query(**qry), values)
return [util.row_to_dict(cursor, row) for row in cursor]
def query(q):
url = "https://www.asiakastieto.fi/yritykset/haku"
parameter_dict = {"type": "BUSINESS"}
parameter_dict["q"] = q
r = util.query(url, parameter_dict)
return r.text
def test_getdata_performance(geom_complexity, normalization, geom, boundary):
print(geom_complexity, normalization, geom, boundary)
cols = ['us.census.acs.B01001002',
'us.census.acs.B01001003',
'us.census.acs.B01001004',
'us.census.acs.B01001005',
'us.census.acs.B01001006',
'us.census.acs.B01001007',
'us.census.acs.B01001008',
'us.census.acs.B01001009',
'us.census.acs.B01001010',
'us.census.acs.B01001011', ]
in_meta = [{"numer_id": col,
"normalization": normalization,
"geom_id": None if boundary.lower() == 'null' else boundary.replace("'", '')}
for col in cols]
rownums = (1, 5, 10, ) if geom_complexity == 'complex' else (10, 50, 100)
for num_meta in (1, 10, ):
results = []
for rows in rownums:
stmt = '''
with data as (
SELECT id, data FROM {schema}OBS_GetData(
(SELECT array_agg(({geom}, cartodb_id)::geomval)
FROM obs_perftest_{complexity}
WHERE cartodb_id <= {n}),
(SELECT {schema}OBS_GetMeta(
(SELECT st_setsrid(st_extent({geom}), 4326)
FROM obs_perftest_{complexity}
WHERE cartodb_id <= {n}),
'{in_meta}'::JSON
))
))
UPDATE obs_perftest_{complexity}
SET measure = (data->0->>'value')::Numeric
FROM data
WHERE obs_perftest_{complexity}.cartodb_id = data.id
;
'''.format(
point_or_poly='point' if geom == 'point' else 'polygon',
complexity=geom_complexity,
schema='cdb_observatory.' if USE_SCHEMA else '',
geom=geom,
in_meta=json.dumps(in_meta[0:num_meta]),
n=rows)
start = time()
query(stmt)
end = time()
qps = (rows / (end - start))
results.append({
'rows': rows,
'qps': qps,
'stmt': stmt
})
print(rows, ': ', qps, ' QPS')
if 'OBS_RECORD_TEST' in os.environ:
record({
'geom_complexity': geom_complexity,
'api_method': 'OBS_GetData',
'normalization': normalization,
'boundary': boundary,
'geom': geom,
'num_meta': str(num_meta)
}, results)
def get(self, cursor=None, user_id=None, **kwargs):
t = self.thresholds(cursor)
posts_total = {'select': 'COUNT(id) AS count',
'table': 'posts',
'where': 'user_id = ?'}
cursor.execute(util.query(**posts_total), (user_id,))
posts_total = cursor.fetchone().count
following_total = {'select': 'COUNT(id) AS count',
'table': 'venue_followers',
'where': 'user_id = ?'}
cursor.execute(util.query(**following_total), (user_id,))
following_total = cursor.fetchone().count
redemptions_total = {'select': 'COUNT(id) AS count',
'table': 'promotion_redemptions',
'where': 'user_id = ?'}
cursor.execute(util.query(**redemptions_total), (user_id,))
redemptions_total = cursor.fetchone().count
posts = {'select': 'COUNT(id) AS count',
'table': 'posts',
'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**posts), (user_id,))
posts = cursor.fetchone().count
comments = {'select': 'COUNT(id) AS count',
'table': 'venue_comments',
'where': ('user_id = ?',
'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**comments), (user_id,))
comments = cursor.fetchone().count
likes = {'select': 'COUNT(id) AS count',
'table': 'post_likes',
'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**likes), (user_id,))
likes = cursor.fetchone().count
redemptions = {'select': 'COUNT(id) AS count',
'table': 'promotion_redemptions',
'where': ('user_id = ?',
'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**redemptions), (user_id,))
redemptions = cursor.fetchone().count
share_venue = {'select': 'COUNT(id) AS count',
'table': 'venue_shares',
'where': ('user_id = ?',
'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**share_venue), (user_id,))
share_venue = cursor.fetchone().count
share_posts = {'select': 'COUNT(id) AS count',
'table': 'post_shares',
'where': ('user_id = ?',
'time >' + str(util.now() - 2592000))}
cursor.execute(util.query(**share_posts), (user_id,))
share_posts = cursor.fetchone().count
score = ((share_posts + share_venue) * 5 + posts * 4 + comments * 2 + likes * 2)
for threshold in range(len(t)):
if score < t[threshold]:
res = threshold
break
else:
res = 3
return {'thresholds': t,
'level': res,
'posts_total': posts_total,
'following_total': following_total,
'redemptions_total': redemptions_total,
'posts': posts,
'redemptions': redemptions,
'share': share_posts + share_venue,
'rsvps': 0,
'comments': comments,
'likes': likes,
'score': score}
def set(self, cursor=None, user_id=None, post_id=None, **kwargs):
qry = {'insert_into': 'post_views',
'columns': ('user_id', 'post_id')}
cursor.execute(util.query(**qry), (user_id, post_id))
return True
def home(id):
c_list = util.query(id)
return render_template('pages/home.html', results=c_list) ## TODO fix this (should include distance as second param)
def set(self, cursor=None, facebook_id=None, twitter_token=None,
facebook=None, twitter=None, forename=None, surname=None, age=None,
birth_day=None, birth_month=None, birth_year=None, gender=None,
employee=None, country=None, language=None, email=None, top5=None,
twitter_id=None, twitter_secret=None, save_locally=None,
app_version=None, iphone_model=None, ios_version=None,
last_facebook=None, last_twitter=None, **kwargs):
if not facebook_id:
raise cherrypy.HTTPError(403)
qry = {'select': 'COUNT(id) AS count',
'table': 'users',
'where': 'facebook_id = ?'}
cursor.execute(util.query(**qry), (facebook_id,))
res = cursor.fetchone().count
data = {'twitter_id': twitter_id,
'twitter_token': twitter_token,
'twitter_secret': twitter_secret,
'facebook': facebook,
'twitter': twitter,
'forename': forename,
'surname': surname,
'age': util.to_int(age),
'birth_day': util.to_int(birth_day),
'birth_month': util.to_int(birth_month),
'birth_year': util.to_int(birth_year),
'gender': gender,
'employee': util.to_bool(employee),
'country': country,
'language': language,
'email': email,
'top5': util.to_bool(top5),
'save_locally': util.to_bool(save_locally),
'app_version': app_version,
'iphone_model': iphone_model,
'ios_version': ios_version,
'last_login': util.now(),
'last_facebook': util.to_bool(last_facebook),
'last_twitter': util.to_bool(last_twitter)}
columns = []
values = []
for key, val in data.iteritems():
if val != None:
columns.append(key)
if val is not True and val is not False:
values.append(val)
else:
if val:
values.append('1')
else:
values.append('0')
values.append(facebook_id)
if res:
qry = {'update': 'users',
'set_values': columns,
'where': 'facebook_id = ?'}
cursor.execute(util.query(**qry), values)
else:
columns.append('facebook_id')
columns.append('joined')
values.append(util.now())
qry = {'insert_into': 'users',
'columns': columns}
cursor.execute(util.query(**qry), values)
qry = {'select': 'id',
'table': 'users',
'where': 'facebook_id = ?',
'order_by': 'id',
'limit': 1}
cursor.execute(util.query(**qry), (facebook_id,))
user_id = cursor.fetchone().id
return self.retrieve(cursor=cursor, user_id=user_id)
