def patch(self): current_id = get_jwt_identity() args = parser.parse_args() error = None # name error if not args["name"]: error = "Name이 유효하지 않습니다." elif not english_re.match(args["name"]) and not korean_re.match( args["name"]): error = "Name은 한글 또는 영어로만 입력하여야 합니다." if error is None: with db.cursor(DictCursor) as cursor: sql = "UPDATE `users` SET `name` = %s WHERE id = %s" cursor.execute(sql, (args["name"], current_id)) db.commit() with db.cursor(DictCursor) as cursor: sql = "UPDATE `userinfo` SET `info` = %s WHERE user_id = %s" cursor.execute(sql, (args["info"], current_id)) db.commit() return jsonify(status="success", logged_in_as=current_id) return jsonify(status="fail", result={"message": error})
def blacklists(): data = request.get_json() user_id = data.get("user_id") reported_id = data.get("reported_id") error = None with db.cursor(DictCursor) as cursor: sql = "SELECT reported_id FROM blacklists WHERE user_id = %s AND reported_id=%s" cursor.execute(sql, (user_id, reported_id)) user = cursor.fetchone() if user: error = "이미 신고하셨습니다!" if error is None: with db.cursor(DictCursor) as cursor: sql = "INSERT INTO `blacklists` (`user_id`, `reported_id`) VALUES (%s, %s)" cursor.execute(sql, (user_id, reported_id)) db.commit() with db.cursor(DictCursor) as cursor: sql = "UPDATE users SET report_count = report_count + 1 WHERE id = %s" cursor.execute(sql, reported_id) db.commit() with db.cursor(DictCursor) as cursor: sql = "SELECT report_count FROM users WHERE id = %s" cursor.execute(sql, reported_id) check_count = cursor.fetchone() if check_count["report_count"] >= 3: with db.cursor(DictCursor) as cursor: sql = "DELETE FROM users WHERE id = %s" cursor.execute(sql, reported_id) db.commit() return jsonify(status="success", result={"message": "신고가 접수되었습니다!"}) return jsonify(status="fail", result={"message": error})
def get(self, user_id=None): current_id = get_jwt_identity() if not user_id: with db.cursor(DictCursor) as cursor: sql = "SELECT id, title, description, acquisition_date FROM `certificates` WHERE user_id = %s" cursor.execute(sql, (current_id, )) result = cursor.fetchall() return jsonify(status="success", result=result) with db.cursor(DictCursor) as cursor: sql = "SELECT id, title, description, acquisition_date FROM `certificates` WHERE user_id = %s" cursor.execute(sql, (user_id, )) result = cursor.fetchall() return jsonify(status="success", result=result)
def patch(self): current_id = get_jwt_identity() args = parser.parse_args() error = None if not args["college"]: error = "학교 이름은 필수 입력값입니다." elif not args["major"]: error = "전공은 필수 입력값입니다." elif args["degree"] == -1: error = "학위는 필수 입력값입니다." if error is None: with db.cursor(DictCursor) as cursor: sql = "UPDATE `educations` SET college = %s, major = %s, degree = %s WHERE `id` = %s AND `user_id` = %s" cursor.execute( sql, ( args["college"], args["major"], args["degree"], args["id"], current_id, ), ) db.commit() return jsonify(status="success") return jsonify(status="fail", result={"message": error})
def get_wallet_incomes(): """ Calculates sum of incoming money to users (wallets according to users table). Returns ------- """ cur = db.cursor() query = """ select sum(ohne.satoshis) as satoshis, ohne.wallet as wallet from ( select satoshis, wallet from txoutput except select o.satoshis, o.wallet from txoutput o, txinput i where (o.txid = i.txid and o.wallet = i.wallet) ) ohne group by wallet order by satoshis desc; """ cur.execute(query) res = cur.fetchall() cur.close() return res
def get(self, user_id=None): current_id = get_jwt_identity() if not user_id: with db.cursor(DictCursor) as cursor: sql = "SELECT email, name, image_path, info FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE users.id = %s" cursor.execute(sql, (current_id, )) user = cursor.fetchone() return jsonify(status="success", user=user, logged_in_as=current_id) with db.cursor(DictCursor) as cursor: sql = "SELECT email, name, image_path, info FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE users.id = %s" cursor.execute(sql, (user_id, )) user = cursor.fetchone() return jsonify(status="success", user=user, logged_in_as=current_id)
def patch(self): current_id = get_jwt_identity() args = parser.parse_args() error = None if not args["title"]: error = "프로젝트 제목은 필수 입력값입니다." if error is None: with db.cursor(DictCursor) as cursor: sql = "UPDATE `projects` SET title = %s, description = %s, startdate = %s, enddate= %s WHERE `id` = %s AND `user_id` = %s" cursor.execute( sql, ( args["title"], args["description"], args["startdate"].split("T")[0], args["enddate"].split("T")[0], args["id"], current_id, ), ) db.commit() return jsonify(status="success") return jsonify(status="fail", result={"message": error})
def post(self): username_or_email = self.get_argument("username").lower() pwd = self.get_argument("password") password = hash_password(pwd) cursor = db.cursor() cursor.execute(""" SELECT ID, user_login, user_email FROM yagra_user WHERE (user_login = %s OR user_email = %s) AND user_passwd = %s""", (username_or_email, username_or_email, password)) row = cursor.fetchone() if row: ID, username, email = row m = hashlib.md5() m.update(email) email_md5 = m.hexdigest() logging.info(row) self.session["login"] = True self.session["_csrf_token"] = make_digest(uuid.uuid4().get_hex()) self.session["id"] = ID self.session["username"] = username self.session["email"] = email self.session["email_md5"] = email_md5 self.set_cookie("session_id", self.session.session_id) self.redirect("/user") return # 登录失败 html_string = Template.render("login", login_failed=True) self.write(html_string)
def post(self): new_image_id = self.get_argument("new_image_id") csrf_token = self.get_argument("csrf_token") username = self.session["username"] user_id = self.session["id"] # check csrf if csrf_token != self.session["_csrf_token"]: self.set_status(403) return c = db.cursor() c.execute(""" SELECT * FROM yagra_user, yagra_image WHERE ID = user_id AND user_login = %s AND image_id = %s """, (username, new_image_id)) row = c.fetchone() if row: c.execute(""" UPDATE yagra_user_head SET image_id = %s WHERE user_id = %s""", (new_image_id, user_id)) # self.redirect("/user") self.write({"status": "OK"}) return self.write({"status": "Failed", "msg": "选择的图片不正确!"})
def getUserInfo(): user={} # 打开数据库连接 db = pymysql.connect(host='13.231.165.68', port=33070, user='******', passwd='880309jQl', db='tfs', charset='utf8') # 使用cursor()方法获取操作游标 cursor = db.cursor() sql = "SELECT * FROM user" try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: user[row[0]]=row[5] # print(len(results)) except: print("Error: unable to fecth user data") # 关闭数据库连接 db.close() return user;
def post(self): cursor = db.cursor() data = self.assure_input_valid() if not data: return username, pwd, email = data hash_pwd = hash_password(pwd) cursor.execute(""" INSERT INTO `yagra`.`yagra_user` ( `user_login`, `user_passwd`, `display_name`, `user_email`, `user_register`, `user_status`) VALUES (%s, %s, %s, %s, %s, %s) """, (username, hash_pwd, username, email, time.strftime('%Y-%m-%d %H:%M:%S'), str(1))) db.commit() # self.write("Username: %s registered success!" % escape(username)) self.redirect("/accounts/login")
def post(self): current_id = get_jwt_identity() args = parser.parse_args() error = None if not args["college"]: error = "학교 이름은 필수 입력값입니다." elif not args["major"]: error = "전공은 필수 입력값입니다." elif args["degree"] == "-1": error = "학위는 필수 입력값입니다." if error is None: with db.cursor(DictCursor) as cursor: sql = "INSERT INTO `educations` (`college`, `major`, `degree`, `user_id`) VALUES (%s, %s, %s, %s)" cursor.execute( sql, (args["college"], args["major"], args["degree"], current_id), ) db.commit() return jsonify(status="success") return jsonify(status="fail", result={"message": error})
def retranslateUi(self, Dialog): _translate = QtCore.QCoreApplication.translate Dialog.setWindowTitle(_translate("Dialog", "Settings")) self.label_2.setText(_translate("Dialog", "Change Password")) self.label_7.setText(_translate("Dialog", "Set IP")) self.label_8.setText(_translate("Dialog", "Set URL")) self.btn_saveIp.setText(_translate("Dialog", "Save")) self.btn_saveUrl.setText(_translate("Dialog", "Save")) self.label.setText(_translate("Dialog", "Settings")) self.label_9.setText(_translate("Dialog", "Old Password")) self.label_11.setText(_translate("Dialog", "Username")) self.label_12.setText(_translate("Dialog", "New Password")) self.btn_save.setText(_translate("Dialog", "Save")) self.btn_back.setText(_translate("Dialog", "Back")) self.label_3.setText(_translate("Dialog", "Change Password")) self.label_4.setText(_translate("Dialog", "Settings")) self.btn_saveUrl.clicked.connect(self.saveUrl) self.btn_saveIp.clicked.connect(self.saveIP) self.btn_save.clicked.connect(self.saveUserDetails) cursor = db.cursor() cursor.execute('SELECT url FROM url') self.txt_url.setText(cursor.fetchone()[-1]) cursor.execute('SELECT ip FROM ip') self.txt_ip.setText(cursor.fetchone()[-1]) cursor.close()
def get_wallet_transactions(): """ Fetches transactions between users. Returns ------- res: array of tuples Query result. It cointains userid (from) userid (to) satoshis transferred timestamp transaction_id """ cur = db.cursor() query = """ select distinct i.wallet, outputs.wallet, outputs.satoshis, outputs.timest, outputs.txid from ( select * from txoutput except select o.* from txoutput o, txinput i where (o.txid = i.txid and o.wallet = i.wallet) ) outputs, txinput i where i.txid = outputs.txid and i.wallet <> outputs.wallet; """ cur.execute(query) res = cur.fetchall() cur.close() return res
def get_subscribers(): c = db.cursor() c.execute('SELECT id FROM subscribers') subscribers = [] for id in c.fetchall(): subscribers.append(id[0]) c.close() return subscribers
def network(): # Access the identity of the current user with get_jwt_identity current_id = get_jwt_identity() if request.method == "POST": data = request.get_json() searchname = data.get("searchname") with db.cursor(DictCursor) as cursor: sql = f"""SELECT email, name, image_path, info, users.id FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE name LIKE '%{searchname}%' """ cursor.execute(sql, ) user = cursor.fetchall() return jsonify(status="success", user=user) with db.cursor(DictCursor) as cursor: sql = "SELECT email, name, image_path, info, users.id FROM users JOIN userinfo ON users.id = userinfo.user_id" cursor.execute(sql, ) user = cursor.fetchall() return jsonify(status="success", user=user)
def delete(self): args = parser.parse_args() with db.cursor(DictCursor) as cursor: sql = "DELETE FROM `certificates` WHERE `id` = %s" cursor.execute(sql, (args["id"], )) db.commit() return jsonify(status="success")
def get(self, user_id=None): current_id = get_jwt_identity() if not user_id: with db.cursor(DictCursor) as cursor: sql = "SELECT id, college, major, degree FROM `educations` WHERE user_id = %s" cursor.execute(sql, (current_id, )) result = cursor.fetchall() return jsonify(status="success", result=result) with db.cursor(DictCursor) as cursor: sql = ( "SELECT id, college, major, degree FROM `educations` WHERE user_id = %s" ) cursor.execute(sql, (user_id, )) result = cursor.fetchall() return jsonify(status="success", result=result)
def saveIP(self): ip = self.txt_ip.toPlainText() cursor = db.cursor() cursor.execute('DELETE FROM ip') cursor.execute('INSERT INTO ip VALUES(?)', (ip, )) db.commit() cursor.execute('SELECT ip FROM ip') self.txt_ip.setText(cursor.fetchone()[-1])
def post(self): user_head = self.get_argument("user_head") csrf_token = self.get_argument("csrf_token") if csrf_token != self.session["_csrf_token"]: self.set_status(403) return username = self.session["username"] upload_filename = unicode(self.request.files["user_head"].filename, encoding="utf-8") filename = create_random_filename(username + "_" + upload_filename) full_filename = "uploads/" + filename logging.info("Writing file to %s" % (filename, )) with open(full_filename, "wb") as out: out.write(user_head) # 检查用户是否存在 cursor = db.cursor() cursor.execute(""" SELECT ID, user_email FROM yagra_user WHERE user_login = %s""", (username, )) row = cursor.fetchone() if row: user_id, user_email = row m = hashlib.md5() m.update(user_email) email_md5 = m.hexdigest() # 插入图片信息到数据库中 cursor.execute(""" INSERT INTO yagra_image (user_id, filename, upload_date) VALUES (%s, %s, %s)""", (str(user_id), filename, time.strftime('%Y-%m-%d %H:%M:%S'))) image_id = cursor.lastrowid # 判断用户是否已经有默认头像,没有则设置默认头像 cursor.execute(""" SELECT * FROM yagra_user_head WHERE user_email_md5 = %s""", (email_md5, )) row = cursor.fetchone() if not row: logging.info("Insert into db. Filename %s, " "image_id: %d, " "user_id: %d" % (filename, image_id, user_id)) cursor.execute(""" INSERT INTO yagra_user_head (user_id, image_id, user_email_md5) VALUES (%s, %s, %s)""", (user_id, image_id, email_md5)) db.commit() self.redirect("/user")
def saveUrl(self): url = self.txt_url.toPlainText() cursor = db.cursor() cursor.execute('DELETE FROM url') cursor.execute('INSERT INTO url VALUES(?)', (url, )) db.commit() cursor.execute('SELECT url FROM url') self.txt_url.setText(cursor.fetchone()[-1])
def fetch(): with closing(db.cursor()) as c: c.execute( """SELECT DISTINCT url FROM search_console_data WHERE (date=%s and user_check=0)""", (my_date, )) fetch = c.fetchall() for f_row in fetch: url = f_row[0] yield url print len(fetch)
def get_users(): cur = db.cursor() cur.execute(""" select distinct userid from users; """) res = cur.fetchall() cur.close() return [int(r[0]) for r in res]
def main(): event = sys.argv[1] query = "SELECT * FROM event WHERE event='%s'" % event cursor = db.cursor() cursor.execute(query) for row in cursor: event, stream, start, end = row[0], row[1], row[2], row[3] vector = np.asarray(row[4:]) query = "SELECT " for col in data['cols'][:-1]: query += "%s, " % col query += "%s " % data['cols'][-1] query += "FROM hcdm_vectors" cursor = db.cursor() cursor.execute(query) results = {} for row in cursor: key = (row[0], row[1], row[2]) mvector = np.asarray(row[3:]) sim = csim(vector, mvector) results[key] = sim results = sorted(results.items(), key=lambda x: x[1], reverse=True) final = {} for item in results: nstream, nstart, nend = item[0][0], item[0][1], item[0][2] if nstream not in final and nstream != stream: final[nstream] = (nstart, nend, item[1]) final = sorted(final.items(), key=lambda x: x[1][2], reverse=True) print('%s,%s,%s' % (stream, start, end)) for i in range(100): print('%s,%s,%s,%s' % (final[i][0], final[i][1][0], final[i][1][1], final[i][1][2])) sys.stdout.flush()
def on_get(self, req, resp): '''Return json object related to GET with lat & lon HTTP parameters ''' raw_lat = req.get_param('lat') raw_lon = req.get_param('lon') if raw_lat is None or raw_lon is None: resp.status = falcon.HTTP_400 lat = None lon = None else: lat = decimal.Decimal(raw_lat) lon = decimal.Decimal(raw_lon) if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)): resp.status = falcon.HTTP_400 else: cur = db.cursor() nb_maps_raw = req.get_param('nb_maps') nb_addr_raw = req.get_param('nb_addr') if nb_maps_raw is None: nb_maps_raw = '20' if nb_addr_raw is None: nb_addr_raw = '10' nb_maps = decimal.Decimal(nb_maps_raw) nb_addr = decimal.Decimal(nb_addr_raw) #id (uuid), path (str), geom (geom), address (str), level (str), building (str) loc = "st_setsrid(st_makepoint(%s,%s),4326)" % (lon,lat) query = "SELECT array_to_json(array_agg(row_to_json(t)))::text FROM (" query += " SELECT floor(st_distance(geom::geography, %s::geography)) as dist, " % loc query += " ST_X(geom) as lon, ST_Y(geom) as lat, * FROM ((select * from ban_nomap where ST_DWithin(geom, %s, 0.001) order by st_distance(geom,%s) limit %s) union (select * from map_info where ST_DWithin(geom, %s, 0.1) limit %s)) as d ORDER BY ST_Distance(geom,%s), level" % ( loc,loc,adapt(nb_addr),loc,adapt(nb_maps),loc) query += " ) t" cur.execute(query) what_is_around = cur.fetchone()[0] resp.set_header('X-Powered-By', 'OpenEvacMap') if what_is_around is None: resp.status = falcon.HTTP_204 else: resp.status = falcon.HTTP_200 resp.set_header('Access-Control-Allow-Origin', '*') resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With') resp.body = (what_is_around) query = """INSERT INTO log (loc, ip) VALUES (ST_SetSRID(ST_GeometryFromText('POINT(%s %s)'),4326),'%s');""" % (lon,lat,req.env['REMOTE_ADDR']) cur.execute(query) db.commit() cur.close()
def ban_roadblock_author(bot, query): if query.message.chat.id == mods_chat: nmaps_message = retrieve_roadblock(mods_id=query.message.message_id) else: nmaps_message = retrieve_roadblock(roads_id=query.message.message_id) query.edit_message_text(BOT_USER_BANNED.format(user_name(query.from_user), query.from_user.id), parse_mode='markdown') c = db.cursor() c.execute('INSERT INTO banned VALUES(%s)', (nmaps_message['user_id'], )) db.commit() c.close()
def create_user_table(wallet_userid): cur = db.cursor() cur.execute(""" create table users( wallet varchar(34) primary key, userid int ); """) execute_values(cur, "insert into users (wallet, userid) values %s", wallet_userid.items()) db.commit() cur.close()
def update_subscription(bot, update): c = db.cursor() if not subscribed(update.message.from_user.id): c.execute('INSERT INTO subscribers VALUES (%s)', (update.message.from_user.id, )) update.message.reply_text(BOT_SUBSCRIBED_USR, reply_markup=get_keyboard(update, True)) else: c.execute('DELETE FROM subscribers WHERE id=(%s)', (update.message.from_user.id, )) update.message.reply_text(BOT_UNSUBSCRIBED_USR, reply_markup=get_keyboard(update, False)) db.commit() c.close()
def output_data() -> None: with db.cursor() as cur: cur.execute(OUTPUT_DATA_SQL_FILE.read_text()) rows = cur.fetchall() assert len(rows) > 0, "At least one row must be returned" columns = [ column for column in rows[0].keys() if column not in IGNORE_COLUMNS ] data: List[Dict[str, Any]] = [] for row in rows: data.append({column: row[column] for column in columns}) data_json = json.dumps(data, indent=2) OUTPUT_DATA_JS_FILE.write_text(f"var data = {data_json};\n") print(f"Wrote {OUTPUT_DATA_JS_FILE}.")
def unhide(keep): with db.cursor() as cur: for nid, data in cur: note = keep.get(nid) data = data.decode() title, text = data.split(TEXT_TITLE_SEP, maxsplit=1) note.title = title note.text = text note.archived = True note.pinned = False print_note(note) keep.sync(dump=True) drop_db()
def saveUserDetails(self): username = self.txt_username.toPlainText() new_password = self.txt_newPassword.toPlainText() old_password = self.txt_oldPassword.toPlainText() url = self.txt_url.toPlainText() cursor = db.cursor() cursor.execute( '''INSERT INTO databasecredentials(url, usernname,password) VALUES(?,?,?)''', (url, username, new_password)) db.commit() self.txt_url.setText("") self.txt_username.setText("") self.txt_newPassword.setText("") self.txt_oldPassword.setText("")
def on_get(self, req, resp): '''Return JPEG map image according to id param ''' map_id = req.get_param('id') if not self.map_id_is_valid(map_id): resp.status = falcon.HTTP_400 else: cur = db.cursor() #id (uuid), path (str), geom (geom), address (str), level (str), building (str) query = "SELECT path, ST_astext(geom) as geom FROM %s" % self._table query += " WHERE id='%s'" % (map_id) cur.execute(query) cur_map = cur.fetchall() map_path = cur_map[0][0] map_geom = cur_map[0][1] resp.set_header('X-Powered-By', 'OpenEvacMap') if map_path is None: resp.status = falcon.HTTP_404 else: full_path = os.path.join(config.map_dir,map_path) preview = req.get_param('preview') if preview is None: preview = '0' if preview=='1': outfile = os.path.splitext(full_path)[0] + "_preview.jpg" print(outfile) if os.path.exists(outfile)==False: im = Image.open(full_path) im.thumbnail(preview_size, Image.ANTIALIAS) im.save(outfile, "JPEG") full_path=outfile resp.status = falcon.HTTP_200 resp.set_header('Access-Control-Allow-Origin', '*') resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With') resp.set_header("Content-Type", "image/jpeg") resp.set_header("Content-Length", "%s" % os.path.getsize( full_path)) with open(full_path, "rb") as f: resp.body = f.read() query = """INSERT INTO log (loc, ip, map) VALUES (ST_GeometryFromText('%s'),'%s','%s');""" % (map_geom,req.env['REMOTE_ADDR'],map_id) cur.execute(query) db.commit() cur.close()
def post(self): current_id = get_jwt_identity() args = parser.parse_args() if args["college"] != "" and args["major"] != "": with db.cursor(DictCursor) as cursor: sql = "INSERT INTO `educations` (`college`, `major`, `degree`, `user_id`) VALUES (%s, %s, %s, %s)" cursor.execute( sql, (args["college"], args["major"], args["degree"], current_id), ) db.commit() return jsonify(status="success") return jsonify(status="fail")
def post(self): current_id = get_jwt_identity() args = parser.parse_args() error = None if not args["title"]: error = "수상내역은 필수 입력값입니다." if error is None: with db.cursor(DictCursor) as cursor: sql = "INSERT INTO `awards` (`title`, `description`, `user_id`) VALUES (%s, %s, %s)" cursor.execute( sql, (args["title"], args["description"], current_id)) db.commit() return jsonify(status="success") return jsonify(status="fail", result={"message": error})
def print_response(response, url): with closing(db.cursor()) as x: for report in response.get('reports', []): for row in report.get('data', {}).get('rows', []): dateRangeValues = row.get('metrics', []) for value in dateRangeValues: val = (value.get('values')) x.execute( """UPDATE search_console_data SET nusers = (%s), user_check = 1 WHERE url = (%s) AND date = (%s)""", (val, url, my_date)) x.execute( """INSERT INTO ga_urls (ganusers, gaurl, gadate) VALUES (%s, %s, %s)""", (val, url, my_date)) db.commit()
def bypass_moderators(bot, update): update.message.reply_text( BOT_MSG_ACCEPT.format(user_name(update.message.from_user), update.message.from_user.id)) bot.forward_message(roads_chat, update.message.chat.id, update.message.message_id) roads_message = bot.send_message(roads_chat, BOT_NEW_ROADBLOCK, reply_markup=staff_keyboard) c = db.cursor() c.execute('INSERT INTO roads VALUES(%s, %s, %s, %s, %s)', (update.message.from_user.id, update.message.chat.id, update.message.message_id, 0, roads_message.message_id)) db.commit() c.close()
def on_get(self, req, resp): '''Return logged requests ''' cur = db.cursor() #id (uuid), path (str), geom (geom), address (str), level (str), building (str) query = "SELECT array_to_json(array_agg(row_to_json(t)))::text FROM (select EXTRACT(epoch FROM age(time,now()))::integer as age, st_asgeojson(loc) as geometry from log where time > now()- interval '1 hour') as t;" cur.execute(query) log_data = cur.fetchone()[0] resp.set_header('X-Powered-By', 'OpenEvacMap') if log_data is None: resp.status = falcon.HTTP_404 else: resp.status = falcon.HTTP_200 resp.set_header('Access-Control-Allow-Origin', '*') resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With') resp.body = (log_data) cur.close()
def get(self): username = self.session["username"] email_md5 = self.session["email_md5"] csrf_token = self.session["_csrf_token"] c = db.cursor() c.execute(""" SELECT filename, i.image_id, i.upload_date, user_email_md5 FROM yagra_user AS u, yagra_image AS i LEFT JOIN yagra_user_head AS h ON i.image_id = h.image_id WHERE u.ID = i.user_id AND u.user_login = %s """, (username, )) html_string = Template.render("userhome", username, email_md5, imgs=c.fetchall(), csrf_token=csrf_token) self.write(html_string)
def get(self): cursor = db.cursor() cursor.execute("SELECT user_email, user_login FROM yagra_user") for row in cursor.fetchall(): email, name = row self.write("%s: %s<br/>" % (name, email))
def assure_input_valid(self): """检查用户名、邮箱是否合法,返回小写的用户名、邮箱以及密码 return None or (username_lower, pwd, email_lower) """ self.set_status(403) # 检查用户名 username = self.get_argument("username") if not yagra_check_username_valid(username): html_string = Template.render("error", "username %s is not valid" % ( escape(username), )) self.write(html_string) return email = self.get_argument("email") # 检查密码 pwd = self.get_argument("password") pwd2 = self.get_argument("password-again") if pwd != pwd2: html_string = Template.render("error", "two password not matched") self.write(html_string) return # XXX # 检查email if not yagra_check_email_valid(email): html_string = Template.render("error", "email: %s is not valid." % escape(email)) self.write(html_string) return cursor = db.cursor() email_lower = email.lower() cursor.execute(""" SELECT ID FROM yagra_user WHERE user_email = %s""", (email_lower, )) if cursor.fetchone(): html_string = Template.render("error", "email %s registered!" % escape(email)) self.write(html_string) return username_lower = username.lower() cursor.execute(""" SELECT ID FROM yagra_user WHERE user_login = %s""", (username_lower, )) if cursor.fetchone(): html_string = Template.render("error", "username %s registered!" % escape(username)) self.write(html_string) return self.set_status(200) return username_lower, pwd, email_lower
def on_post(self, req, resp, **kargs): '''Update database according to JSON and give ID back ''' address_id = kargs.get('id') env = req.env env.setdefault('QUERY_STRING', '') form = cgi.FieldStorage(fp=req.stream, environ=env) try: file_item = form['image'] raw_lat_item = form['lat'] raw_lon_item = form['lon'] except KeyError: raw_lat = None raw_lon = None image_file = None else: image_file = file_item.file raw_lat = raw_lat_item.value raw_lon = raw_lon_item.value if raw_lat is None or raw_lon is None or image_file is None: resp.status = falcon.HTTP_400 lat = None lon = None else: lat = decimal.Decimal(raw_lat) lon = decimal.Decimal(raw_lon) if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)): resp.status = falcon.HTTP_400 else: cur = db.cursor() columns = ['geom', 'address'] values = ['st_Setsrid(st_makePoint(%s,%s),4326)' % (lon, lat), "%s" % adapt(address_id)] for param in ('name','building','level'): try: param_item = form[param] except KeyError: pass else: columns.append(param) values.append("%s" % adapt(param_item.value)) insert = "INSERT INTO %s (%s)" % (self._table, ",".join(columns)) insert += " VALUES (%s)" % ",".join(values) insert += " RETURNING *" cur.execute(insert) uuid = cur.fetchone()[0] image_path = os.path.join(config.map_dir, "%s.jpg" % uuid) with open(image_path, "wb") as f: f.write(image_file.read()) cur2 = db.cursor() update = "UPDATE %s" % self._table update += " SET path=%s" % adapt(image_path) update += " WHERE id=%s" % adapt(uuid) cur2.execute(update) db.commit() cur.close() cur2.close() try: redirect_url_item = form['redirectUrl'] redirect_url = redirect_url_item.value #split_result = urllib.parse.urlsplit(redirect_url) #netloc = split_result[1] #if netloc != config.netloc: # raise ValueError("What do you think you are doing Dave?") except: resp.status = falcon.HTTP_200 else: resp.status = falcon.HTTP_302 resp.set_header('Location', redirect_url) resp.set_header('Access-Control-Allow-Origin', '*') resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With')