def csv_to_db(path_to_csv_file):
with open(path_to_csv_file) as file:
csv_reader = csv.DictReader(file)
for row in csv_reader:
csv_row_data = {}
# keys to lowercase, remove spaces
row = dict(
(k.lower().replace(' ', '_'), v) for k, v in row.items())
keys = row.keys()
if not row['first_name'] or not row['last_name']:
continue
first = row['first_name']
last = row['last_name']
csv_row_data['first'] = first
csv_row_data['last'] = last
csv_row_data['username'] = str.lower(first[0] + last)
bonus_keys = ['address', 'num_seats', 'phone']
for key in bonus_keys:
if key in keys:
if key == 'phone':
csv_row_data[key] = validate_phone_number(row[key])
else:
csv_row_data[key] = row[key]
if key == 'address':
address_dict = validate_address(row[key])
if address_dict is not None:
coord_dict = get_coordinates_from_address(
address_dict)
if coord_dict is not None:
address_dict = {**address_dict, **coord_dict}
# merge the address_dict into csv_row_data
csv_row_data = {**csv_row_data, **address_dict}
if key == 'num_seats':
if len(row[key]) > 0:
csv_row_data[key] = int(row[key])
else:
csv_row_data[key] = 0
else:
csv_row_data[key] = None
if 'm/w' in keys and 'v/n' in keys:
csv_row_data['team'] = row['m/w'] + row['v/n']
else:
csv_row_data['team'] = None
log.debug('creating new user with data: {}'.format(csv_row_data))
User.user_from_csv_row(csv_row_data)
def details(forum, related=None):
if not related:
related = []
query = Query.SELECT.format(
columns='*',
table=Tables.TABLE_NAMES['FORUM']
) + Query.WHERE.format(
clause='short_name = %r' % str(forum)
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
code = Codes.OK
if 'user' in related:
code, response['user'] = User.details(response['user'])
return code, response
def list_users():
request_data = get_json(request)
code, response = User.list_by_forum(request_data['forum'], request_data)
return json.dumps(
{'code': code,
'response': response}
)
def unfollow():
request_data = get_json(request)
code, response = User.unfollow(request_data['follower'], request_data['followee'])
return json.dumps({
'code': code,
'response': response
})
def list_following():
request_data = get_json(request)
code, response = User.list_following(request_data['user'])
return json.dumps({
'code': code,
'response': response
})
def details():
request_data = get_json(request)
code, response = User.details(request_data['user'])
return json.dumps({
'code': code,
'response': response
})
def update():
request_data = get_json(request)
code, response = User.update(request_data['user'], request_data['name'], request_data['about'])
return json.dumps({
'code': code,
'response': response
})
def details(short_name, related=None):
if not related:
related = []
code, result = Forum.get_forum(short_name)
if code == Codes.OK and 'user' in related:
code, result['user'] = User.details(result['user'])
return code, result
def create():
request_data = get_json(request)
columns = ', '.join(request_data.keys())
values = get_values(request_data)
code, response = User.create(columns, values, request_data['email'])
return json.dumps({
'code': code,
'response': response
})
def list_by_forum(forum, request, related=None):
if not related:
related = []
code, result = Post.get_posts_by_forum(forum, related, request)
if code != Codes.OK:
return code, result
for post in result:
if 'user' in related:
code, post['user'] = User.details(post['user'])
return code, result
def details(post_id, related=None):
if not related:
related = []
code, result = Post.get_post(post_id)
if code != Codes.OK:
return code, result
if code == Codes.OK and 'user' in related:
code, result['user'] = User.details(result['user'])
if code == Codes.OK and 'forum' in related:
code, result['forum'] = Forum.details(result['forum'])
if code == Codes.OK and 'thread' in related:
code, result['thread'] = Thread.details(result['thread'])
return code, result
def details(thread_id, related=None):
if not related:
related = []
if 'thread' in related:
return Codes.NOT_CORRECT, 'Thread can\'t be in related'
code, result = Thread.get_thread(thread_id)
if code != Codes.OK:
return code, result
if code == Codes.OK and 'user' in related:
code, result['user'] = User.details(result['user'])
if code == Codes.OK and 'forum' in related:
code, result['forum'] = Forum.details(result['forum'], None)
return code, result
def UpdateGCM(self,instanttype,ohurl):
bid = self.request.get("bid")
bname = self.request.get("bname")
regid = self.request.get("regid")
message = self.request.get("message")
atplace = self.request.get("atplace")
timestamp = datetime.datetime.now()
userid = self.user_id
userDetails = self.current_user
push_query = GcmData.query(GcmData.bid == bid)
pushes = push_query.fetch(1)
registration_ids = []
for push in pushes:
instants = push.instants
if len(instants) > 20:
del instants[-1]
instant = InstantMesg(message = message,atplace = atplace,userid=self.user_id,timestamp = timestamp,instanttype = instanttype,ohid = ohurl)
instants.append(instant)
push.instants = instants
push.put()
secs = calendar.timegm(timestamp.timetuple())
#querry databse for registration ids
user_query = User.query(User.instants.gcm_bids == bid)
users = user_query.fetch()
for user in users:
if user.key.id() != self.user_id:
registration_ids.append(user.instants.gcm_regid)
logging.info("%s" %registration_ids)
if len(registration_ids) == 0:
self.response.write("")
return
Bodyfields = {
"data": {"live":message,"username":userDetails.name,"bid":bid,"bname":bname,"atplace":atplace,"timestamp":secs,"instanttype":instanttype,'ohurl':ohurl},
"registration_ids": registration_ids
}
result = urlfetch.fetch(url="https://android.googleapis.com/gcm/send",
payload=json.dumps(Bodyfields),
method=urlfetch.POST,
headers={'Content-Type': 'application/json','Authorization': 'key=AIzaSyBNnXeISW8-KfETBKE-r0ASytx4WyC6NTk'})
#self.response.out.write('Server response, status: ' + result.content )
return result
def new_signup():
# forms to handle sign up and sign in
signup_form = web_forms.SignUpForm()
signin_form = web_forms.SignInForm()
login = True
if request.method == 'POST':
if signin_form.data['submit_bttn']:
if signin_form.validate_on_submit():
username = signin_form.data['username_field']
password = signin_form.data['password_field']
result = db.select('users', ['password', 'user_id'],
['username'], [username])
log.info('here is result: {}'.format(result))
if result:
hash = result['password']
password_match = pbkdf2_sha256.verify(password, hash)
if password_match:
curr_user = User(result['user_id'])
login_user(curr_user)
next_url = request.args.get('next')
if not is_safe_url(next_url):
return abort(400)
if not current_user.is_profile_complete():
flash(
'Please complete your profile before continuing!'
)
return redirect(next_url or url_for('profile'))
return redirect(next_url or url_for('team'))
signin_form.username_field.errors.append(
"Invalid Username or Password.")
elif signup_form.data['submit']:
if signup_form.validate():
# create token
token = ts.dumps(signup_form.data['email'],
salt='email-confirm-key')
# build url
confirm_url = url_for('confirm_email',
token=token,
_external=True)
# set up html that makes up email
html = render_template('signup_email.html',
validate_url=confirm_url,
user={
'first': signup_form.data['first'],
'last': signup_form.data['last']
})
# create thread to speed up process
subject = "Confirm Your Email"
t1 = threading.Thread(target=util_basic.send_email,
args=(signup_form.data['email'], html,
subject))
t1.start()
# create user
curr_user = User.user_from_form(signup_form.data)
# log user in
login_user(curr_user)
# wait for thread
t1.join()
# flash message and redirect user to their new profile page
flash(
'Please check your email and follow the instructions to confirm your email address.',
'alert-success')
return redirect(url_for('profile'))
login = False
return render_template(
'signup.html',
sign_up=signup_form,
sign_in=signin_form,
login=login,
_url=
"https://s3-us-west-2.amazonaws.com/athlessary-images/defaults/login_photo.jpg"
)
def load_user(user_id):
try:
return User(user_id)
except ValueError:
return None
def details(post, related=None):
if not related:
related = []
columns = 'p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam, ' \
'p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent'
if 'forum' in related:
columns += ', f.name f_name, f.short_name f_short_name, f.user f_user, f.id f_id'
if 'thread' in related:
columns += ', t.id t_id, t.forum t_forum, t.title t_title, t.isClosed t_isClosed, ' \
't.user t_user, t.date t_date, t.message t_message, t.slug t_slug, t.isDeleted t_isDeleted, ' \
't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'] + ' as p'
)
if 'forum' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['FORUM'] + ' as f',
clause='p.forum = f.short_name'
)
if 'thread' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['THREAD'] + ' as t',
clause='p.thread = t.id'
)
query += Query.WHERE.format(
clause='p.id = %s' % post
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
if 'list index out of range' in str(e):
return Codes.NOT_FOUND, 'Post %s not found' % post
else:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = Post.get_response_values(response)
code = Codes.OK
if 'thread' in related:
response['t_date'] = str(response['t_date'])
response['t_isDeleted'] = bool(response['t_isDeleted'])
response['t_isClosed'] = bool(response['t_isClosed'])
response['thread'] = {}
keys = []
for val in response:
if val[:2] == 't_':
response['thread'][val[2:]] = response[val]
keys.append(val)
for key in keys:
del response[key]
if 'forum' in related:
response['forum'] = {}
keys = []
for val in response:
if val[:2] == 'f_':
response['forum'][val[2:]] = response[val]
keys.append(val)
for key in keys:
del response[key]
if 'user' in related:
code, response['user'] = User.details(response['user'])
return code, response
def list_users():
request_data = get_json(request)
code, response = User.list_by_forum(request_data["forum"], request_data)
return json.dumps({"code": code, "response": response})
def list_by_forum(forum, request, related=None):
if not related:
related = []
if 'order' not in request:
request['order'] = 'DESC'
columns = 'p.id, p.message, p.date, p.likes, p.dislikes, p.points, p.isApproved, ' \
'p.isHighlighted, p.isEdited, p.isSpam, p.isDeleted, p.forum, p.thread, p.user, p.parent'
if 'forum' in related:
columns += ', f.id f_id, f.name f_name, f.short_name f_short_name, f.user f_user'
if 'thread' in related:
columns += ', t.id t_id, t.title t_title, t.slug t_slug, t.message t_message, t.date t_date, ' \
't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points, ' \
't.isClosed t_isClosed, t.isDeleted t_isDeleted, t.forum t_forum, t.user t_user'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'] + ' as p'
)
if 'forum' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['FORUM'] + ' as f',
clause='p.forum = f.short_name'
)
if 'thread' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['THREAD'] + ' as t',
clause='p.thread = t.id'
)
query += Query.WHERE.format(
clause='p.forum = %r' % str(forum)
)
if 'since' in request:
query += Query.AND_CLAUSE.format(
clause='p.date > %r' % str(request['since'])
)
query += Query.ORDER_BY.format(
column='p.date',
type=request['order']
)
if 'limit' in request:
query += Query.LIMIT.format(
count=request['limit']
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
posts = dictfetchall(cursor)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = []
code = Codes.OK
for post in posts:
if 'forum' in related:
post['forum'] = {}
keys = []
for val in post:
if val[:2] == 'f_':
post['forum'][val[2:]] = post[val]
keys.append(val)
for key in keys:
del post[key]
if 'thread' in related:
post['thread'] = {}
keys = []
for val in post:
if val[:2] == 't_':
post['thread'][val[2:]] = post[val]
keys.append(val)
for key in keys:
del post[key]
post['thread'] = Thread.get_response_values(post['thread'])
if 'user' in related:
code, post['user'] = User.details(post['user'])
response.append(Post.get_response_values(post))
return code, response
