def profile(): myform = MyForm() if request.method == 'POST': if myform.validate_on_submit(): firstname = myform.firstname.data lastname = myform.lastname.data email = myform.email.data location = myform.location.data biography = myform.biography.data gender = myform.gender.data photo = myform.photo.data filename = secure_filename(photo.filename) photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename)) userid = str(uuid.uuid4()) created_on = format_date_joined() db = connect_db() cur = db.cursor() query = "insert into Profiles (firstname, lastname, email, location, biography, gender, photo, userid, created_on) values (%s, %s, %s, %s, %s, %s, %s, %s, %s);" data = (firstname, lastname, email, location, biography, gender, filename, userid, created_on) cur.execute(query, data) db.commit() flash('Profile successfully added!', 'success') return redirect(url_for("profiles")) flash_errors(myform) return render_template('profile.html', form=myform)
def admin_insert_book(): if request.method == "POST": bookname = request.form.get("bookname") author = request.form.get("author") quantity = request.form.get("quantity") section = request.form.get("section") serial_no = request.form.get("serial_no") # db.execute("INSERT INTO Ibook(bookname) VALUES(:bookname)",{'bookname':bookname}) db.execute( "INSERT INTO bookcn(bookname) VALUES(:bookname)", {"bookname": bookname} ) db.execute( "INSERT INTO book(bookname,author,quantity,section,serial_no) VALUES(:bookname,:author,:quantity,:section,:serial_no)", { "bookname": bookname, "author": author, "quantity": quantity, "section": section, "serial_no": serial_no, }, ) # connection.commit() db.commit() flash("book inserted successfully", "success") return render_template("admin_insert_book.html")
def registers(): if request.method == "POST": name = request.form.get("name") username = request.form.get("username") password = request.form.get("password") confirm = request.form.get("confirm") # secure_password=sha256_crypt.encrypt(str(password)) user_data = db.execute( "SELECT username FROM users WHERE username=:username", {"username": username}, ).fetchone() if user_data is not None: flash("username already available please use some other", "danger") else: if password == confirm: db.execute( "INSERT INTO users(name,username,password) VALUES(:name,:username,:password)", {"name": name, "username": username, "password": password}, ) # db.execute() db.commit() flash("you can login now", "success") return redirect(url_for("login")) else: flash("password does not match", "danger") return render_template("registers.html") return render_template("registers.html")
def heaven_for_users(): try: db = pymysql.connect(host="rm-uf6gw23j409s5ui7qmoo.mysql.rds.aliyuncs.com", port=3306, user="******", password="******", charset="utf8") res = request.get_json() user_id = res.get('user_id') open_id = res.get('open_id') new_user_id = res.get('new_user_id') new_open_id = res.get('new_open_id') if not all([user_id, open_id, new_user_id, new_open_id]): return jsonify(errno=-1, errmsg="参数不完整") cursor = db.cursor() timeStamp = int(time.time()) dateArray = datetime.datetime.utcfromtimestamp(timeStamp) otherStyleTime = dateArray.strftime("%Y-%m-%d %H:%M:%S") sql = f"INSERT INTO zjlivenew.zj_heaven_for_user(create_time,update_time,user_id,openid,new_user_id,new_openid,status)" \ f"VALUES('{otherStyleTime}','{otherStyleTime}',{int(user_id)},'{open_id}',{int(new_user_id)},'{new_open_id}',{int(0)})" print(sql) cursor.execute(sql) db.commit() db.close() return jsonify(errno=0, errmsg="OK") except Exception as e: Logging.logger.error('errmsg:{0}'.format(e)) return jsonify(errno=-1, errmsg='网络异常')
def freeze_admin(): if (request.method == 'POST'): print "freeze applications" sql = "UPDATE main_table SET freeze_status = \'true\' WHERE status = \'submitted\'" try: cursor.execute(sql) db.commit() print sql except: print "Db error" sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted" cursor.execute(sql) rows1 = list(cursor.fetchall()) rows = [] for i in range(len(rows1)): temp = [ rows1[i][0], rows1[i][1], rows1[i][2], str(rows1[i][0]) + ".pdf" ] rows.append(temp) print rows return render_template('admin_view_part1.html', email_=session['email'], rows=rows)
def update_ad_teams(): db = app.db.dbWriteInstance() ad = app.ad.ActiveDirectory() cur = db.cursor() cur.execute("SELECT userid FROM tblusers WHERE team IS NULL") users = {} while True: rc = cur.fetchone() if not rc: break users[rc[0].strip().lower()] = rc[0].strip() mapping = json.loads(config.group_mapping) userMapping = {} for group in mapping: adGroup = group[0] name = group[1] print "Processing %s (%s)" % (adGroup, name) allGroupUsers = ad.get_all_members_of_group(adGroup) for u in allGroupUsers: if u.lower() in users: userMapping[users[u.lower()]] = name del users[u.lower()] for u in userMapping: print "Mapping %s to %s" % (u, userMapping[u]) cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s", [userMapping[u], u]) db.commit()
def register(): myform = MyForm() if request.method == 'POST': if myform.validate_on_submit(): username = myform.username.data password = myform.password.data firstname = myform.firstname.data lastname = myform.lastname.data email = myform.email.data location = myform.location.data biography = myform.biography.data photo = myform.photo.data filename = secure_filename(photo.filename) photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename)) db = connect_db() cur = db.cursor() joined_on=date.today() cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,joined_on) values (%s, %s, %s, %s, %s, %s, %s,%s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on)) #cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,photo) values (%s,%s, %s, %s, %s, %s, %s, %s, %s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo'])) db.commit() flash('You have successfully filled out the form', 'success') #SAVING DATA TO DATABASE WITH SQLALCHEMY BELOW #user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo'],joined_on) user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on) db.session.add(user) db.session.commit() return render_template('result.html', username=username, password=password, firstname=firstname, lastname=lastname, email=email, location=location, biography=biography,filename=filename) flash_errors(myform) return render_template('register.html', form=myform)
def profile(): """Render the website's adding profile page.""" User = UserProfile() if request.method == 'POST': if User.validate_on_submit(): db = connect_db() cur = db.cursor() firstname = User.firstname.data lastname = User.lastname.data gender = User.gender.data email = User.email.data location = User.location.data biography = User.biography.data profilepic = User.profilepic.data date = User.date.data filename = secure_filename(profilepic.filename) profilepic.save(os.path.join(app.config['UPLOAD_FOLDER'], filename)) cur.execute( 'insert into user_profiles (firstname, lastname, gender, email, location, biography, date)values (%s, %s, %s, %s, %s, %s %s)', (request.form['firstname'], request.form['lastname'], request.form['gender'], request.form['email'], request.form['location'], request.form['biography'], request.form['date'])) db.commit() flash('The profile was succesfully added', 'success') return redirect(url_for('profiles'), filename=filename, form=User) flash_errors(User) return render_template('profile.html')
def extract(): global PERFORM_FETCH timestamp = time.time() config = Config() api = Mobileclient() manager = ApiManager(api, config) manager.login() now = datetime.datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0) data_playcounts, data_genres, data_artists = manager.get_plays(now) manager.logout() if data_playcounts is not None: graph_dict = graph_data(data_genres) if PERFORM_FETCH: inserts = len(data_playcounts) inserts += DatabaseInsert.genre_playcount(data_genres, now) inserts += DatabaseInsert.artist_playcount(data_artists, now) else: inserts = 0 app.logger.info("%s inserts have been made into the database." % inserts) timestamp = time.time() - timestamp record = models.Metrics(date=now, query_time=timestamp) db.add(record) db.commit() else: graph_dict = None app.logger.info("Database has already been updated for today, avoiding double entry.") return data_playcounts, graph_dict, data_artists
def post(self, *args, **kwargs): data = request.get_json() bankname = data.get("bankname", '') universalbranchcode = data.get("universalbranchcode", "") schema = Kanpai.Object({ "bankname": (Kanpai.String().required().max(32)), "universalbranchcode": (Kanpai.String().required()) }) validation_results = schema.validate(data) Validations.validate_error(validation_results) if data: bankcode = BankCodeDB(bankname=bankname, universalbranchcode=universalbranchcode) db.add(bankcode) db.commit() return jsonify({ "status": "success", "data": validation_results["data"] })
def buyer_register(): db = pymysql.connect(**config) cur = db.cursor() sql_s = 'SELECT * FROM users WHERE email = %s' data = cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']]) user = '******' if int(data) == 0: sql = '''INSERT INTO users (user_category, email, user_name) VALUES (%s, %s, %s)''' cur.execute(sql, [ user, session[constants.JWT_PAYLOAD]['email'], session[constants.JWT_PAYLOAD]['name'] ]) db.commit() is_buyer = True else: is_buyer = False cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']]) check = cur.fetchone() user = check[1] return render_template('accounts/register_customer.html', user=user, is_buyer=is_buyer, userinfo=session[constants.PROFILE_KEY])
def do_import(file_path, file_size): try: with open(file_path, 'r') as f: large_file = float(file_size / 1000000) > 100 record_list = list() for line in f: param = line.split(' | ') new_record = Journal(level=param[0], title=param[1], detail=param[2], datetime=param[3]) record_list.append(new_record) if large_file: if len(record_list) > 10000: db.session.add_all(record_list) db.commit() record_list = list() else: pass if len(record_list) > 0: db.session.add_all(record_list) db.commit() return True except Exception as e: return False
def register(): if request.method == 'POST': fname=request.form['fname'] mname=request.form['mname'] lname=request.form['lname'] dob=request.form['dob'] gender=request.form['gender'] street=request.form['street'] city=request.form['city'] phone=request.form['phone'] global patnum patnum += 1 #patnum= cursor.execute('SELECT MAX(patientID) FROM patients') pID= 'P-0000000' + str(patnum) #execute sql commands cursor.execute("INSERT INTO patients VALUES ('{}','{}','{}','{}','{}','{}')".format(pID,lname,fname,mname,gender,dob)) cursor.execute("INSERT INTO patient_phoneno VALUES ('{}','{}')".format(pID,phone)) cursor.execute("INSERT INTO patient_address VALUES ('{}','{}','{}')".format(pID,city,street)) #commit changes to the database db.commit() #in case there is any error #db.rollback() return render_template("register.html")
def register(user, address, account): userModel = addressModel = None try: try: userModel = UserModel(**user) userModel = userDao.save(userModel, autocommit=False) except Exception as e: print(e) if 'Duplicate' in str(e): raise ValueError('Username already taken') raise ValueError('User informations are not valid') try: addressModel = AddressModel(**address) addressModel = addressDao.save(addressModel, autocommit=False) except Exception as e: print(e) raise ValueError('Address informations are not valid') try: accountModel = AccountModel(**account) accountModel.id_User = userModel.id accountModel.id_Address = addressModel.id accountDao.save(accountModel, autocommit=False) except Exception as e: print(e) raise ValueError('Account informations are not valid') db.commit() return userDao.getById(userModel.id) except Exception as e: db.rollback() raise e
def newNotebook(): if current_user.enable: res = requests.post(f'{APISERVER}/api/notebook', json={'name': request.form['name']}) if res.status_code == 201: dates = json.loads(res.text) notebook_id = dates.get('id') db = mysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PSSW, database=MYSQL_DB) cur = db.cursor() cur.execute( 'INSERT INTO user_object_id (object_id, object_type, user_name) VALUES (%s,%s,%s)', ( notebook_id, "notebook", current_user.username, )) db.commit() db.close() flash(f'Notebook created!') else: flash(res.status_code) return redirect(url_for('notebooks')) else: return redirect(url_for('403'))
def addMeal(n_meal): cursor = db.cursor() command_get_meal_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'meal';" command_meal = "insert into meal (calorie, num_servings) values (%s, %s);" command_made_from = "insert into made_from (meal_id, recipe_id) values (%s, %s);" # get increment value cursor.execute(command_get_meal_id) response = cursor.fetchall() n_meal_id = response[0][0] # insert meal cursor.execute(command_meal, ( n_meal.calorie, n_meal.num_serving, )) db.commit() # insert made_from cursor.execute(command_made_from, ( n_meal_id, n_meal.recipe_id, )) db.commit()
def delete_user(data): sql = "SELECT * FROM users WHERE user_email = '%s'" % data["user_email"] print(sql) status = 0 res = '' try: cursor.execute(sql) result = cursor.fetchone() print(result[7]) if result[7] == 4 or result[7] == 15 or result[7] == 7: sql2 = "DELETE FROM users WHERE user_email = '%s'" % data[ "user_email"] print(sql2) cursor.execute(sql2) db.commit() status = 200 res = "User deleted successfully" else: status = 201 res = "Not authorized" except: print("Error: unable to fetch data") return res, status
def updateUser(user): cursor = db.cursor() command = "update users set first_name=%s, last_name=%s, email=%s, gender=%s, password=%s where user_id=%s;" cursor.execute(command, (user.fname, user.lname, user.email, user.gender, user.password, user.id)) db.commit()
def updateRecipe(recipe): cursor = db.cursor() command = "update recipe set name=%s, created_date=%s, calorie=%s, image_url=%s where recipe_id=%s;" cursor.execute(command, (recipe.name, recipe.date_created, recipe.calorie, recipe.image_url, recipe.id)) db.commit()
def addNewRecipe(user_id, recipe): cursor = db.cursor() command_get_recipe_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'recipe';" command_recipe = "insert into recipe (name, created_date, calorie, image_url) values (%s, %s, %s, %s);" command_create = "insert into creates (user_id, recipe_id) values ( %s, %s);" # get increment value cursor.execute(command_get_recipe_id) response = cursor.fetchall() n_recipe_id = response[0][0] cursor.execute(command_recipe, ( recipe.name, recipe.date_created, recipe.calorie, recipe.image_url, )) db.commit() cursor.execute(command_create, ( user_id, n_recipe_id, )) db.commit() for ingredient in recipe.ingredients: addIngredient(n_recipe_id, ingredient) for instruction in recipe.instructions: addInstruction(n_recipe_id, instruction)
def post(self, *args, **kwargs): data = request.get_json() username = data.get("username", "") roledesc = data.get("roledesc", "") status = data.get("status", "") schema = Kanpai.Object({ "username": (Kanpai.String(error="user name should be string").trim().required( error="user name should required").max( 32, error="should not be more than 32 characters")), "roledesc": (Kanpai.String(error="role desc should be string").trim().required( error="role desc should required").max( 32, error="should not be more than 32 characters")), "status": (Kanpai.String(error="role desc should be string").trim().required( error="role desc should required").max( 32, error="should not be more than 32 characters")) }) validation_results = schema.validate(data) Validations.validate_error(obj=validation_results) check_username_indb = session.query(UserDB).filter_by( username=username).first() check_roledesc_indb = session.query(RoleDB).filter_by( roledesc=roledesc).first() if not check_username_indb: return jsonify({ "status": "error", "errors": f"user {check_username_indb} not present in db" }) if not check_roledesc_indb: return jsonify({ "status": "error", "errors": f"role description {check_roledesc_indb} not present in db" }) if data: userrole = UserRoleDB(userid=check_username_indb.id, roleid=check_roledesc_indb.id, status=status) db.add(userrole) db.commit() return jsonify({ "status": "success", "data": validation_results["data"] })
def addInstruction(recipe_id, n_instruction): cursor = db.cursor() command_get_instr_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'instructions';" command_prepare = "insert into prepare (recipe_id, instruction_id, step_no) values (%s, %s, %s);" command_instructions = "insert into instructions (step_no, step_description) values (%s, %s);" # get increment value cursor.execute(command_get_instr_id) response = cursor.fetchall() n_instruction_id = response[0][0] # insert instruction cursor.execute(command_instructions, ( n_instruction.step_no, n_instruction.description, )) db.commit() # insert prepare cursor.execute(command_prepare, ( recipe_id, n_instruction_id, n_instruction.step_no, )) db.commit()
def before_request(): g.user=current_user if g.user.is_authenticated(): a=str(datetime.utcnow()); cur.execute('update user set last_seen = "'+a+'" where id= %d'%(g.user.id)); db.commit(); g.search_form=SearchForm();
def update_user(request:Request, department_id, department_data: schemas.DepartmentIn): department = db.query(models.Department).get(department_id) if not department: raise HTTPException(status_code=404, detail=f"Could not find Department with ID {department_id}") department.name = department_data.name db.commit() return {'data':department}
def delete_table(table_name): query = '''DROP TABLE IF EXISTS ''' + table_name db = get_db() #db.execute("PRAGMA busy_timeout = 30000") cur = db.execute(query) db.commit() cur.close()
def attend(event_id): schema = EventSchema() event = Event.get(id=event_id) event.attended_by.add(g.current_user.id) db.commit() return schema.dumps(event)
def post(self, *args, **kwargs): schema = Kanpai.Object({ 'name': (Kanpai.String( error="Description should be string").trim().required( error="Description should required").max( 64, error="Should not be more than 64 characters")), 'idnumber': (Kanpai.String( error="Description should be string").trim().required( error="Description should required").max( 64, error="Should not be more than 64 characters")) }) data = request.get_json() validation_result = schema.validate(data) if validation_result.get('success', False) is False: return jsonify({ "status": "error", "errors": validation_result.get('error') }) if data: idtype = IdTypeDB(name=data['name'], idnumber=data['idnumber']) db.add(idtype) db.commit() return jsonify({ "status": "success", "result": validation_result['data'] })
def new_password_t(): L = request.get_data() L = json.loads(L) db = pymysql.connect( host='database-1.clr3d8nnckz4.us-east-2.rds.amazonaws.com', user='******', password='******', port=3306, db='chatbot') cursor = db.cursor() sql = "SELECT password from staff where id=%s" id = L['id'] password = L['old_passwd'] new_password = L['new_passwd'] cursor.execute(sql, id) ((results, ), ) = cursor.fetchall() if check_password_hash(results, password): new_password = generate_password_hash(new_password) sql = "UPDATE staff set password=%s WHERE id = %s " cursor.execute(sql, (new_password, id)) db.commit() db.close() return '200' else: return '300'
def register(): form = RegistrationForm() if form.validate_on_submit(): user_check = db.execute( "SELECT * FROM users WHERE username = :username", { "username": form.username.data }).fetchone() # check on duplicate username if user_check: flash('User already exists') return redirect(url_for('register')) email_check = db.execute("SELECT * FROM users WHERE email = :email", { "email": form.email.data }).fetchone() # check on duplicate email if email_check: flash('Email already exists') return redirect(url_for('register')) # create user username = form.username.data email = form.email.data password_hash = generate_password_hash(form.password.data) db.execute( "INSERT INTO users (username, email, password_hash) VALUES (:username, :email, :password_hash)", { "username": username, "email": email, "password_hash": password_hash }) db.commit() flash('Congratulations, you are now a registered user!') return redirect(url_for('login')) return render_template('register.html', title='Register', form=form)
def query_db(query, args=(), username='******', one=False): try: cur = get_db().execute(query, args) rv = cur.fetchall() cur.close() return (rv[0] if rv else None) if one else rv except: sql_create_table = """ CREATE TABLE IF NOT EXISTS """ + current_user.username + """ ( user_id text NOT NULL, title text NOT NULL, date text NOT NULL, is_done integer default 0 )""" query = ''' INSERT INTO ''' + username + '''(user_id,title,date,is_done) VALUES(?,?,?,?) ''' db = get_db() #db.execute("PRAGMA busy_timeout = 30000") cur = db.execute(sql_create_table) db.commit() cur.close() # #create the first todo for first-time user # args = (username, 'This is your first todo', datetime.datetime.now().strftime("%m/%d/%Y, %H:%M:%S"),0) # cur = db.execute(query, args) # #cur.commit() query = 'select * from ' + current_user.username cur = get_db().execute(query, ()) rv = cur.fetchall() cur.close() return (rv[0] if rv else None) if one else rv
def select_achievement(): db = pymysql.connect("127.0.0.1", "root", "638436", "adms") cursor = db.cursor() # username = "******" # r_mname = "ADNI_002_S_0295_MR_HarP_135_final_release_2015_Br_20150226095012465_S13408_I474728.nii" # mri = "select * from mri where m_name='" + r_mname + "'" # 方法一: # select Score, (select count(distinct Score) from Scores where Score>=s.Score) as Rank from Scores as s order by Score desc; # rank = "select ea_eid, ea_score, (select count(distinct ea_score) from eachievement where ea_score >= s.ea_score) " \ # "as Rank from eachievement as s order by ea_score desc" username = "******" resu = "select * from resu where r_uid='" + username + "'" cursor.execute(resu) rhipvlou_r = cursor.fetchall() print(rhipvlou_r) data = [] for i in rhipvlou_r: x_data = "海马体数量" + "-" + i[4].strftime('%Y.%m.%d') y_data = int(i[6]) print(x_data) print(y_data) data_dict = {"name":x_data,"num":y_data} data.append(data_dict) print(data) # r_uid = mri_r[0][1] # r_utime = mri_r[0][3] # print(mri_r) # print(r_uid) # print(r_utime) db.commit() db.close()
def create_user(user): password = hash_password(user["password"]) sql = "INSERT INTO users(user_name, user_email, user_password, user_address, user_phone, created_by, actions) VALUES ('%s','%s','%s','%s','%s', %d, %d)" % ( user["user_name"], user["user_email"], password, user["user_address"], user["user_phone"], user["created_by"], user["actions"]) status = 0 res = '' try: cursor.execute(sql) db.commit() status = 200 res = 'User created successfully' except pymysql.Error as e: try: print("MySQL Error [%d]: %s") % (e.args[0], e.args[1]) return None except IndexError: print("MySQL Error: %s") % str(e) return None except: db.rollback() status = 401 res = 'Something went wrong. Try again' return res, status
def update_phone_from_discount(input): # Se actualiza la información del celular en la base sifco_discount client_name = Sifco_nits.Sifco_nits.get_name_by_nit(input) phone = input['phone'] card = input['card'] if len(phone) != 10: raise BadLengthException("El número de celular no es válido") cursor = db.cursor() sql_insert_query = """ update sifco_discount_{} as sd set celular = '{}' from ( select nu_tarjeta_sha256 as tarjeta, fecha_compra as fecha from sifco_discount_{} where nu_tarjeta_sha256 = '{}' order by fecha desc limit 1 ) as last_val where last_val.tarjeta = sd.nu_tarjeta_sha256 and last_val.fecha = sd.fecha_compra """.format(client_name, phone, client_name, card) cursor.execute(sql_insert_query) db.commit() cursor.close() return None
def getAthleteStats(challenges): client = stravalib.client.Client(access_token=config.TOKEN) for c in challenges: athStats=models.AthleteStats(client.get_athlete_stats(c.athId)) if athStats.YTDCount > minNumberRuns: db.add(athId=c.athId,athStats=athStats) db.add(c) db.commit()
def add_entry(): if not session.get('logged_in'): abort(401) db = get_db() print [request.form['title'], request.form['text']] db.cursor().execute('insert into entries (title, text) values (%s, %s)', [request.form['title'], request.form['text']]) db.commit() flash('New entry was successfully posted') return redirect(url_for('show_entries'))
def delete_delivery_jobs(id): """ Delete delivery jobs based on id parameter """ d = DeliveryJob.query.filter_by(id=id) if d is not None: d.delete() db.commit() return redirect('/delivery-jobs/') return page_not_found(d)
def delete(self, userId): """This method deletes a user and returns result none. Args: userId - An integer, primary key that identifies the user. """ user = User.query.get(userId) db.session.delete(user) db.commit() return buildOkResponse(None)
def remove_user(username): ''' Remove the user with the given username Return True if the user is deleted ''' user = User.query.filter(User.username == username).first() if user: db.session.delete(user) db.commit() return True else: return False
def lookup_parts(self): for bompart in self.bomparts: vendor = vendors[bompart.lookup_source] vendor_part_number = bompart.lookup_id query = VendorPart.query query = query.filter_by(vendor=vendor, vendor_part_number=vendor_part_number) query = query.order_by(VendorPart.fetch_timestamp).desc() vendor_part = query.first() if vendor_part: bompart.part = vendor_part.part db.commit()
def __init__(self,nickname,email): self.nickname=nickname; self.email=email; if(cur.execute('select * from user where nickname="'+self.nickname+'"')==0): cur.execute('insert into user(nickname,email) values("'+self.nickname+'","'+self.email+'")'); db.commit(); cur.execute('select * from user where nickname="'+self.nickname+'"') for a in cur: self.id=a[0]; self.about_me=a[3]; self.last_seen=a[4]; break;
def artist_playcount(data, now): app.logger.info("Inserting Artist PlayCount Data ...") insertions = 0 for record in data: insert = models.Artists( date=now, artist=record[0], playCount=record[1] ) db.add(insert) insertions += 1 db.commit() return insertions
def genre_playcount(data, now): app.logger.info("Inserting Genre PlayCount Data ...") insertions = 0 for record in data: insert = models.Genre( date=now, genre=record[0], playCount=record[1] ) db.add(insert) insertions += 1 db.commit() return insertions
def index(): if g.user: return redirect(url_for('profile')) form = RegisterForm(request.form) if request.method == 'POST' and form.is_valid(): user = form.save() user.add_role('user') db.commit() # commit BEFORE doing auth.login! auth.login(user) return redirect(url_for('profile')) return render_template('index.html', **locals())
def create_categories(): # Create all unique categories in the db. categories = set() for node in nodes: if node['categories']: categories.update(node['categories']) categories = [category for category in categories if len(category) > 2] for name in categories: category = Category(name) db.add(category) db.commit()
def create_key_people(): # Create all key_people in the db. key_people = set() for node in nodes: if node['key_people']: for kp in node['key_people']: key_people.add(kp['name']) key_people = list(key_people) for person in key_people: key_person = Keyperson(person) db.add(key_person) db.commit()
def delete_group(group_id): data = request.json group = Group.query.get(group_id) try: db.session.delete(group) db.commit() status, code = "success", 200 except: status, code = "Service down", 500 finally: db.session.close() return jsonify({"result" : status}), code
def facebook_authorized(resp): if resp is None: flash(u'Access denied: reason=%s error=%s' % ( request.args['error_reason'], request.args['error_description']) ) return redirect(url_for('sign_in')) session['facebook_token'] = (resp['access_token'], '') me = facebook.get('/me') # >>> me.data # { # "username": "******", # "id": "581604320", # "email": "*****@*****.**" # "locale": "en_US", # "timezone": -5, # "first_name": "Juan-Pablo", # "last_name": "Scaletti", # "name": "Juan-Pablo Scaletti", # "gender": "male", # "link": "http://www.facebook.com/jpscaletti", # "updated_time": "2013-04-15T06:33:55+0000", # } user = db.query(User).filter(User.facebook_id == me.data['id']).first() if not user: # new user! if g.user: user = g.user else: login = get_unique_login(me.data.get('username')) user = User(login=login) db.add(user) user.facebook_id = me.data['id'] user.last_sign_in = datetime.utcnow() # in any case we update the authentication token in the db # In case the user temporarily revoked access we will have # new tokens here. user.facebook_token = resp['access_token'] # don't forget to commit **before** doing ``auth.login(user)` db.commit() auth.login(user) next = request.args.get('next') or url_for('profile') return redirect(next)
def edit(): form=EditForm(); if form.validate_on_submit(): a=cur.execute('select * from user where nickname="'+form.nickname.data+'"') if(a==0 or form.nickname.data==g.user.nickname): cur.execute('update user set nickname = "'+form.nickname.data+'",about_me="'+form.about_me.data+'" where id= %d'%(g.user.id)); db.commit(); flash('Your changes have been saved.') return redirect(url_for('user',nickname=g.user.nickname)) else: flash('The name you set has been used by others, please choose another name'); return redirect(url_for('edit')); else: form.nickname.data=g.user.nickname form.about_me.data=g.user.about_me return render_template('edit.html',form=form);
def after_login(resp): if resp.email is None or resp.email == "": flash('Invalid login. Please try again') return redirect(url_for('login')) user = User.query.filter_by(email=resp.email).first() if user is None: nickname = resp.nickname user = User(nickname=nickname, email=resp.email) db.session.add(user) db.commit() remember_me = False if "remember_me" in session: remember_me = session["remember_me"] session.pop("remember_me", None) login_user(user, remember=remember_me) return redirect(request.args.get("next") or url_for("index"))
def _empty_tables(): parsed = urlparse(DATABASE_URL) app_tables_query = """ SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_catalog = '{0}' AND table_name != 'schema_version';""".format(parsed.path.strip('/')) cursor = db.cursor() cursor.execute(app_tables_query) tables = [r[0] for r in cursor.fetchall()] for t in tables: query = 'TRUNCATE TABLE {0} CASCADE;'.format(t) cursor.execute(query) db.commit() cursor.close()
def song_playcount(data, now): app.logger.info("Inserting Song PlayCount Data ...") insertions = 0 for record in data: insert = models.Songs( date=now, google_id=record[0], playCount=record[1], artist=record[2], album=record[3], title=record[4], trackNumber=record[5], genre=record[6] ) db.add(insert) insertions += 1 db.commit() return insertions
def create_tournament(): form = CreateTournament(request.form) db = get_db() if request.method == 'POST': nam = request.form['tourn_name'] loc = request.form['location'] cal = request.form['calendar'] sys = request.form['system'] tie = request.form['tie_break'] db.execute('''INSERT INTO tournament \ (id, name, location, calendar, system, tie_break, round_num) \ VALUES (NULL, ?, ?, ?, ?, ?, ?)''', (nam, loc, cal, sys, tie, 1)) db.commit() tournamentID = str( db.execute('''SELECT max(id) FROM tournament''').fetchone()[0]) db.close() return redirect(url_for('add_players', tournamentID=tournamentID)) return render_template('create_tournament.html', title='Create new tournament', form=form)
def _isValidGroup(self, name): db = self.getDB() cur = db.cursor() cur.execute("SELECT groupid FROM tblgroups WHERE name=%s", [name]) rc = cur.fetchone() if rc: return True # Might be a new group if self.getAD().is_valid_group(name): # Cache the group members db = self.getWriteDB() cur = db.cursor() cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [name]) rc = cur.fetchone() groupid = rc[0] members = self.getAD().get_all_members_of_group(name) for m in members: cur.execute("INSERT INTO tblgroupusers (groupid, userid) VALUES (%s, %s)", [groupid, m]) db.commit() return True return False
def twitter_authorized(resp): if resp is None: flash(u'You denied the request to sign in.') return redirect(url_for('sign_in')) session['twitter_token'] = resp['oauth_token'] # >>> resp # { # "oauth_token_secret": "...", # "oauth_token": "...", # "user_id": "11640332", # "screen_name": "jpscaletti" # } user = db.query(User).filter(User.twitter_id == resp['user_id']).first() if not user: # new user! if g.user: user = g.user else: login = get_unique_login(resp['screen_name']) user = User(login=login) db.add(user) user.twitter_id = resp['user_id'] user.last_sign_in = datetime.utcnow() # in any case we update the authentication token in the db # In case the user temporarily revoked access we will have # new tokens here. user.twitter_username = resp['screen_name'] user.twitter_token = resp['oauth_token'] user.twitter_secret = resp['oauth_token_secret'] # don't forget to commit **before** doing ``auth.login(user)` db.commit() auth.login(user) next = request.args.get('next') or url_for('profile') return redirect(next)
def __init__(self,body,timestamp,user_id): self.body=body; self.timestamp=str(timestamp); self.user_id=user_id; cur.execute("insert into post(body,timestamp,user_id) values ('"+self.body+"','"+self.timestamp+"',%d)"%(self.user_id)); db.commit();
def unfollow(self,user): if self.is_following(user): cur.execute('delete from followers where follower_id=%d and followed_id=%d'%(self.id,user.id)); db.commit(); return self; else: return 0;
# Create all relations in the db. # create_relations() # Create all cities in the db. #create_cities() for node in nodes: create_entity(node) # Manually defining IDs broken in civic.json... old_to_new[289] = old_to_new[902] # New York Tech Meetup old_to_new[541] = old_to_new[552] # Open Government Partnership # Make all connections between entities. make_connections() db.commit() print "Wrote %d Entity entries." % len(Entity.query.all()) print "Wrote %d Category entries." % len(Category.query.all()) print "Wrote %d Location entries." % len(Location.query.all()) print "Wrote %d Keyperson entries." % len(Keyperson.query.all()) print "Wrote %d Revenue entries." % len(Revenue.query.all()) print "Wrote %d Expense entries." % len(Expense.query.all()) print "Wrote %d Grant entries." % len(Grant.query.all()) print "Wrote %d Investment entries." % len(Investment.query.all()) print "Wrote %d Dataconnection entries." % len(Dataconnection.query.all()) print "Wrote %d Relation entries." % len(Relation.query.all()) print "Wrote %d Collaboration entries." % len(Collaboration.query.all())
def init_db(): with closing(connect_db()) as db: with app.open_resource('schema.sql', mode='r') as f: db.cursor().executescript(f.read()) db.commit()
def follow(self,user): if not self.is_following(user): cur.execute('insert followers (follower_id,followed_id) values (%d,%d)'%(self.id,user.id)); db.commit(); return self; else: return 0;