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 newEndpoint(): if current_user.enable: name = request.form['name'] training_id = request.form['training_id'] db = mysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PSSW, database=MYSQL_DB) cur = db.cursor() cur.execute( 'SELECT * FROM user_object_id WHERE object_id =%s AND object_type=%s AND user_name=%s', ( training_id, "training", current_user.username, )) training = cur.fetchone() db.close() if training == None: flash('Invalid training id') else: training_id = training[1] res = requests.post(f'{APISERVER}/api/endpoints/endpoint', json={ 'name': name, 'training_id': training_id }) if res.status_code == 201: dates = json.loads(res.text) endpoint_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)', ( endpoint_id, "endpoint", current_user.username, )) db.commit() db.close() flash(f'Endpoint created!') else: flash(res.status_code) return redirect(url_for('endpoints')) else: return redirect(url_for('403'))
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 update_code_from_discount(input): # Se actualiza la información de un código dado. colnames = [column.lower() for column in input] input = dict(zip(colnames, input.values())) if len(input) == 4: input['nu_tarjeta_sha256'] = input.pop('card') cursor = db.cursor() query = """ select codigo from sifco_discount_%s as sd where sd.codigo = '%s' and sd.fecha_redencion is null and current_date <= sd.fecha_expiracion """ % (input['name'], input['code']) cursor.execute(query) codes = cursor.fetchall() cursor.close() try: if len(codes) == 1: card = input['nu_tarjeta_sha256'] card = \ list(map(lambda card: card if len(card) == 64 else hashlib.sha256(str.encode(card)).hexdigest(), [card]))[0] tz = pytz.timezone('America/Bogota') dater = datetime.datetime.now( tz=tz).strftime('%Y/%m/%d %H:%M:%S"') cursor = db.cursor() sql_insert_query = """ update sifco_discount_{} as sd set nu_tarjeta_sha256_redencion = '{}', fecha_redencion = '{}', terminal_redencion = '{}' where codigo = '{}' and fecha_redencion is null and current_date <= fecha_expiracion """.format(input['name'], card, dater, input['terminal'], codes[0][0]) cursor.execute(sql_insert_query) db.commit() cursor.close() codes_query = len(codes) else: raise NotFoundException() except NotFoundException as ex: if len(codes) != 1: codes_query = len(codes) return codes_query return codes_query
def chat_notification_allowed(receiver_id, sender_id): sql = '''SELECT count(*) AS coun FROM chat WHERE receiver_id=%s AND user_id=%s AND seen=0;''' cursor = db.cursor() cursor.execute(sql, (receiver_id, sender_id)) result = cursor.fetchone() sql = '''SELECT count(*) AS coun FROM notifications WHERE user_id=%s AND from_user=%s AND seen=0 AND msg_type="incoming_massage";''' cursor = db.cursor() cursor.execute(sql, (receiver_id, sender_id)) count_same = cursor.fetchone()['coun'] cursor.close() if result['coun'] > 0 and count_same == 0: return True return False
def create_event(name, description, location, min_budget, max_budget, attendees, event_type_id, starting_date_time, ending_date_time): c = db.cursor() c.execute( """INSERT INTO event(name, description, location, min_budget, max_budget, attendees, event_type_id, starting_date_time, ending_date_time) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)""", (name, description, location, min_budget, max_budget, attendees, event_type_id, starting_date_time, ending_date_time)) db.commit() c = db.cursor() c.execute("""SELECT LAST_INSERT_ID()""") result = c.fetchall() return json.dumps({'event_id': result[0]['LAST_INSERT_ID()']})
def connected(uid_1, uid_2): sql = '''SELECT COUNT(*) AS cou FROM connections WHERE this_uid=%s AND other_uid=%s;''' cursor = db.cursor() cursor.execute(sql, (uid_1, uid_2)) first = cursor.fetchone()['cou'] cursor.close() sql = '''SELECT COUNT(*) AS cou FROM connections WHERE this_uid=%s AND other_uid=%s;''' cursor = db.cursor() cursor.execute(sql, (uid_2, uid_1)) second = cursor.fetchone()['cou'] cursor.close() if first == 1 and second == 1: return True return False
def deleteTraining(username, id_to_delete, password): """Delete endpoint""" user = User.query.filter_by(username=username).first_or_404( description='Invalid username') if user.check_password(password): if user.enable: db = mysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PSSW, database=MYSQL_DB) cur = db.cursor() cur.execute( 'SELECT * FROM user_object_id WHERE user_name = %s AND object_id = %s', (user.username, id_to_delete)) endpoint_to_delete = cur.fetchone() db.close() if endpoint_to_delete: res = requests.delete( f'{APISERVER}/api/endpoint/{id_to_delete}') if res.status_code == 200: db = mysql.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PSSW, database=MYSQL_DB) cur = db.cursor() cur.execute( 'DELETE FROM user_object_id WHERE object_id = %s AND object_type = %s', ( id_to_delete, "notebook", )) db.commit() db.close() click.echo('\nEndpoint eliminated!\n') else: click.echo(res.status_code) else: click.echo('Invalid endpoint_id') else: click.echo('You can\'t delete endpoint') else: click.echo('Invalid password')
def update_amount_cards_per_phone(input): # Se actualiza la cantidad de tarjetas diferentes que tienen el mismo teléfono celular client_name = Sifco_nits.Sifco_nits.get_name_by_nit(input) phone = input['phone'] card = input['card'] # validación de la longitud del celular if len(phone) != 10: raise BadLengthException("El número de celular no es válido") cursor = db.cursor() # Devuelve la cantidad de tarjetas diferentes que tienen el mismo teléfono celular sql_select_query = """ select count(distinct nu_tarjeta_sha256) as cards_amount from sifco_discount_{} as sd where celular = '{}' """.format(client_name, phone) cursor.execute(sql_select_query) cards_amount = cursor.fetchall() cursor.close() cursor = db.cursor() sql_insert_query = """ update sifco_discount_{} as sd set cantidad_tarjetas = {} from ( select nu_tarjeta_sha256 as tarjeta, fecha_compra as fecha from sifco_discount_{} where nu_tarjeta_sha256 = '{}' order by fecha_compra desc limit 1 ) as last_val where last_val.tarjeta = sd.nu_tarjeta_sha256 and last_val.fecha = sd.fecha_compra and sd.celular = '{}' """.format(client_name, cards_amount[0][0], client_name, card, phone) cursor.execute(sql_insert_query) db.commit() cursor.close() return None
def get_geolocation(user_id): sql = "SELECT lat, lon FROM geolocation WHERE user_id=%s;" cursor = db.cursor() cursor.execute(sql, (user_id, )) result = cursor.fetchone() cursor.close() return result
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 logout(): try: cursor = db.cursor() typedata = "" # f=open('./app/static/js/TypecodeIndex.js','r+') f = open(JsTypecodeIndexSrc, "r+") cc = f.read() TypecodeIndex_num = cc.count(" ") - 3 sql = "SELECT COUNT(*) FROM product_records" cursor.execute(sql) results = cursor.fetchall() Num_total = int(str(results[0])[1:-3]) if Num_total > TypecodeIndex_num: num = Num_total - TypecodeIndex_num sql = "SELECT * FROM product_records LIMIT " + str(TypecodeIndex_num) + "," + str(num) cursor.execute(sql) results = cursor.fetchall() for row in results: typedata = typedata + " " + str(row[0]) content = typedata + "';" f.seek(-2, 2) f.write(content) f.close() logout_user() except: logout_user() return redirect(url_for("login"))
def profiles(): """Render the website's profiles page.""" db = connect_db() cur = db.cursor() cur.execute('SELECT * FROM Profiles') profiles = cur.fetchall() return render_template('profiles.html', profiles=profiles)
def login(): cursor = db.cursor() form = LoginForm() error_message = [] if not form.login.data: validate_code() if form.login.data: userphone = str(request.form.get('form-userphone')) password = str(request.form.get('form-password')) validate = str(request.form.get('form-validation')) if validate.upper() in session['validate'].upper(): sql = "SELECT * FROM user_info where USERID = '%s' and PASS_WORD = '%s' limit 1" %(userphone, password) res = cursor.execute(sql) cursor.close() if res: return redirect(url_for('get_code')) else: error_message = [u"用户名或密码输入错误,请重新输入。。。"] else: error_message = [u"验证码输入有误,请重新输入。。。"] if form.register.data: return redirect(url_for('register')) if form.visitor.data: return redirect(url_for('visitor')) return render_template('login.html', form = form, error_message = error_message, validate_code=session['validate'])
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 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 get_sifco_from_card(data): colnames = [column.lower() for column in data] data = dict(zip(colnames, data.values())) card = data['card'] cardh = list( map( lambda cards: cards if len(cards) == 64 else hashlib.sha256( str.encode(cards)).hexdigest(), [card]))[0] cursor = db.cursor() query = """ select * from sifco_%s where nu_tarjeta_sha256 = '%s' """ % (data['name'], cardh) cursor.execute(query) output = cursor.fetchall() if len(output) != 1: raise NotFoundException( "El input de tarjeta es desconocido, cliente nuevo") colnames = [ column.lower() for column in (desc[0] for desc in cursor.description) ] cursor.close() return dict(zip(colnames, output[0]))
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 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 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(): 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 test_password_reset_confirm_with_matching_code(self): self.simulate_post(USER_RESOURCE_ROUTE, VALID_DATA) self.assertEqual(self.srmock.status, falcon.HTTP_201) self.simulate_post(PASSWORD_RESET_REQUEST_ROUTE, {'email': VALID_DATA['email']}) self.assertEqual(self.srmock.status, falcon.HTTP_201) cursor = db.cursor() cursor.execute('SELECT code FROM app_password_reset') result = cursor.fetchone() cursor.close() request_data = { 'code': result[0], 'password': '******' } self.simulate_post(PASSWORD_RESET_CONFIRM_ROUTE, request_data) self.assertEqual(self.srmock.status, falcon.HTTP_200) request_data = { 'email': VALID_DATA['email'], 'password': '******' } body = self.simulate_post(USER_AUTH_ROUTE, request_data) self.assertEqual(self.srmock.status, falcon.HTTP_200) self.assertNotEqual(len(body['token']), 0)
def set_campaign(campaign_id): c = db.cursor() a = request.form c.execute( '''update campaign_user set link = "{}" where campaign_id = {};'''. format(a["link"], campaign_id)) return json.dumps({"data": list(c.fetchall())})
def login(): form = UserForm() cursor = db.cursor() if form.validate_on_submit() or form.submit.data: if form.submit.data: username = form.username.data password = form.password.data user = models.User.query.filter_by(name=str(username)).first() if user: if str(password) == "123456": try: src = ImagefileSrc Material_serial_Index() for item in os.listdir(src): os.remove(src + item) login_user(user) except: login_user(user) return redirect( url_for("index", machinecode="+", flag_return=0, version_info="init", remark_info="x") ) else: flash(encodechinese("密码输入错误,请重新输入!")) else: flash(encodechinese("用户名输入错误,请重新输入!")) return render_template("login.html", form=form)
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 getUserIngredient(user_id): cursor = db.cursor() command = """ SELECT ingredients.ingredient_id, ingredients.name, ingr_unit.unit, ingr_quan.quantity FROM (SELECT stores.ingredient_id, stores.quantity FROM stores WHERE stores.user_id = {}) ingr_quan LEFT JOIN ingredients ON ingr_quan.ingredient_id = ingredients.ingredient_id LEFT JOIN (SELECT measured_in.ingredient_id , measurement.unit FROM measured_in LEFT JOIN measurement on measured_in.measurement_id = measurement.measurement_id)as ingr_unit on ingr_quan.ingredient_id = ingr_unit.ingredient_id """.format(user_id) cursor.execute(command) response = cursor.fetchall() ingredients = [] if len(response) > 0: for data in response: ingredients.append(Ingredient(data[0], data[1], data[2], data[3])) return ingredients
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 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 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 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 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 updateIngredient(recipe_id, n_ingredient): cursor = db.cursor() command_getmeasurementid = "SELECT measurement.measurement_id from measurement WHERE measurement.unit= '{}';".format( n_ingredient.unit) command_ingr = "update ingredients set name=%s where ingredient_id=%s ;" command_made_of = "update made_of set amount=%s where recipe_id=%s and ingredient_id=%s" command_measured_in = "update measured_in set measurement_id=%s where ingredient_id=%s" # get measuremen id cursor.execute(command_getmeasurementid) response = cursor.fetchall() measurement_id = response[0][0] #update ingredient cursor.execute(command_ingr, ( n_ingredient.name, n_ingredient.id, )) db.commit() # update made of cursor.execute(command_made_of, ( n_ingredient.quantity, recipe_id, n_ingredient.id, )) db.commit() # uodate measured in cursor.execute(command_measured_in, ( measurement_id, n_ingredient.id, )) db.commit()
def getAllMeals(): cursor = db.cursor() command = """ SELECT meal.meal_id, meal.calorie, meal.num_servings, made_from.recipe_id FROM meal LEFT JOIN made_from ON meal.meal_id = made_from.meal_id; """ cursor.execute(command) response = cursor.fetchall() meals = [] if len(response) > 0: for data in response: ml = Meal( data[0], data[2], data[3], ) ml.calorie = data[1] meals.append(ml) return meals
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 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 show_entries(): db = get_db() cursor = db.cursor() ret = cursor.execute('select title, text from entries') entries = {} if ret: entries = cursor.fetchall() print entries return render_template('show_entries.html', entries=entries)
def get_all_note_coords(): """ Get the coordinates of each Note in the database :return: an {int: Point} dict of each id and its associated coordinates """ with db: with db.cursor() as cursor: cursor.execute("SELECT id, x_coordinate, y_coordinate FROM notes") coords = {row[0]: Point(row[1], row[2]) for row in cursor.fetchall()} return coords
def get_all_notes(cls): """ Get all Notes in the database :return: a list of Note objects """ with db: with db.cursor() as cursor: cursor.execute("SELECT * FROM notes") notes = [cls._from_db_row(row) for row in cursor.fetchall()] return notes
def findpartname(id): cursor = db.cursor() sql = "SELECT * FROM product_partname \ WHERE id = '%d'" % (int(id)) cursor.execute(sql) partname = cursor.fetchall() cursor.close() if len(partname)>0: return partname[0][1] else: return 'ERROR!'
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 test_password_reset_request_with_matching_user(self): self.simulate_post(USER_RESOURCE_ROUTE, VALID_DATA) self.assertEqual(self.srmock.status, falcon.HTTP_201) self.simulate_post(PASSWORD_RESET_REQUEST_ROUTE, {'email': VALID_DATA['email']}) self.assertEqual(self.srmock.status, falcon.HTTP_201) cursor = db.cursor() cursor.execute('SELECT COUNT(id) FROM app_password_reset') result = cursor.fetchone() self.assertEqual(int(result[0]), 1) cursor.close()
def from_id(cls, database_id): """ Create a Note object from a db row with a given id :param database_id: the id of the row to use :return: the Note object """ with db: with db.cursor() as cursor: cursor.execute("SELECT * FROM notes WHERE notes.id = %s", (database_id,)) row = cursor.fetchone() return cls._from_db_row(row)
def create_tables(): """ Create all required tables on the database """ with db, db.cursor() as cursor: cursor.execute(""" CREATE TABLE notes ( id SERIAL PRIMARY KEY, x_coordinate INTEGER, y_coordinate INTEGER ); CREATE UNIQUE INDEX coordinates_uindex ON notes (x_coordinate, y_coordinate); """)
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 lookup_detailid(self, jobid, phase, test): reply = -1 db = self.getDB() cur = db.cursor() cur.execute( "SELECT detailid from tblResults WHERE jobid = %s AND " "phase = %s AND test = %s", [jobid, phase, test] ) rc = cur.fetchone() if rc and rc[0]: reply = int(rc[0]) cur.close() return reply
def store(self): """ Write the note to the database. Note.id is also updated to the id it receives when written to the db """ with db: with db.cursor() as cursor: try: cursor.execute( "INSERT INTO notes (x_coordinate, y_coordinate) VALUES (%s, %s) RETURNING id", (self.coords.x, self.coords.y)) self.id = cursor.fetchone()[0] except psycopg2.IntegrityError as e: self._persistence_state = 'failed' if e.pgcode == '23505': # = unique constraint violation raise CoordinatesOccupied(e) else: raise self._store_sound_file() self._persistence_state = 'saved'
def bomcalculate(product_flag="#", code_search="+", product_id="#"): form = BomForm() cursor = db.cursor() table_product = [] table_product_bom = [] # table of product bom info, in order to calculate and print the sum of materials table_material = {} table_product_name = {} table_material_print = [] # the print table info table_material_print_use = [] table_material_print_head = "" code_input = "" code = "" flag_print = "No" place_id = [] rights = g.user.rights if "1" in str(rights): flag_edit = "Yes" else: flag_edit = "No" conn = pymssql.connect( host=SQLSERVER_HOST, user=SQLSERVER_USER, password=SQLSERVER_PASS, database=SQLSERVER_DB, port=SQLSERVER_PORT, charset="UTF-8", ) cou = conn.cursor() if code_search != "+" and code_search != " ": code = code_search code_input = code # 产品列表初始化 codex = str(request.form.get("select_info")) if not form.submit.data or (form.submit.data and (len(codex) == 0 or codex == "None")): sql = "SELECT * FROM bom_index " cursor.execute(sql) results = cursor.fetchall() i = 1 for rows in results: if rows[6] == 1: info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]] table_product.insert(len(table_product), info_product) i = i + 1 # 搜索产品 if form.submit.data or code != "": if form.submit.data: code = str(request.form.get("select_info")) if len(code) > 0 and code != "None": code_input = code table_product = [] if "." in code: sql = ( "SELECT * FROM bom_index \ WHERE erp_serial = '%s'" % (code) ) else: sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s'" % (code) ) cursor.execute(sql) results = cursor.fetchall() i = 1 for rows in results: if rows[6] == 1: info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]] table_product.insert(len(table_product), info_product) i = i + 1 # 物料汇总表信息 if product_flag != "#": product_id = product_id + "#" + str(product_flag) i = 1 flag_same_product = "" if product_id != "#": id = product_id.split("#") for x in id: if x != "": y = "#" + x if not (y in flag_same_product): flag_same_product = flag_same_product + "#" + x sql = ( "SELECT * FROM bom_index \ WHERE id = '%d'" % (int(x)) ) cursor.execute(sql) results = cursor.fetchall() for rows in results: info_product = [i, rows[1], rows[2], rows[3], x] table_product_bom.insert(len(table_product), info_product) i = i + 1 name = rows[1] + "_" + rows[2] table_product_name[x] = name place_id.insert(len(place_id), x) sql = ( "SELECT * FROM bom_detail \ WHERE product_index = '%d' order by erp_material" % (int(x)) ) cursor.execute(sql) results = cursor.fetchall() j = 1 table_material_copy = [] for rows in results: """ material = [j, rows[1], '', '', rows[2]] table_material_copy.insert(len(table_material_copy), material) j = j + 1 """ sql = "select * from dbo.t_ICItemCore where FNumber = '%s'" % (str(rows[1]).strip()) cou.execute(sql) resultsx = cou.fetchall() if len(resultsx) > 0: bom_info = [ resultsx[0][2].encode("latin1").decode("gbk"), resultsx[0][1].encode("latin1").decode("gbk"), ] material = [j, rows[1], bom_info[0], bom_info[1], rows[4], rows[2]] else: material = [j, rows[1], "", "", rows[4], rows[2]] table_material_copy.insert(len(table_material_copy), material) j = j + 1 table_material_print.insert(len(table_material_print), table_material_copy) table_material[x] = table_material_copy product_id = flag_same_product else: product_id = "#" # 打印物料表单信息 flag = True counts = 0 if form.calculate.data: flag_print = "Yes" # print table_material_print for x in table_material_print: place_id_info = "select_info" + place_id[counts] num = str(request.form.get(place_id_info)) t_print_info = str(table_product_bom[counts][1]) + "_" + str(table_product_bom[counts][2]) + "_" + str(num) table_material_print_head = table_material_print_head + ";" + t_print_info counts = counts + 1 for z in x: z[-1] = z[-1] * int(num) for y in table_material_print_use: if z[1] == y[1]: y[-1] = int(y[-1]) + int(z[-1]) flag = False break else: flag = True if flag: z[0] = len(table_material_print_use) + 1 table_material_print_use.insert(len(table_material_print_use), z) table_material_print_use_x = "" for x in table_material_print_use: for y in x: table_material_print_use_x = table_material_print_use_x + "+#" + str(y).strip() table_material_print_use_x = table_material_print_use_x[1:] if code_input == "": code_input = " " cursor.close() conn.close() return render_template( "bomcalculate.html", flag="bomcalculate", form=form, flag_edit=flag_edit, username=str(g.user.name), code_input=code_input, product_id=product_id, table_product=table_product, table_product_bom=table_product_bom, table_product_name=table_product_name, table_material=table_material, table_material_print_use=table_material_print_use_x, flag_print=flag_print, table_material_print_head=table_material_print_head, )
def refresh_ad_caches(removeUsers=False): db = app.db.dbWriteInstance() ad = app.ad.ActiveDirectory() cur = db.cursor() print "Validating users in tblusers..." cur.execute("SELECT userid,email,disabled FROM tblusers") userCount = 0 usersToRemove = [] usersToDisable = [] usersToEnable = [] emailUpdates = [] while True: rc = cur.fetchone() if not rc: break userCount += 1 userid = rc[0].strip() try: adEmail = ad.get_email(userid) dbEmail = rc[1] and rc[1].strip() if adEmail != dbEmail: emailUpdates.append((userid, adEmail)) adDisabled = ad.is_disabled(userid) dbDisabled = rc[2] if adDisabled and not dbDisabled: usersToDisable.append(userid) elif not adDisabled and dbDisabled: usersToEnable.append(userid) except KeyError: usersToRemove.append(userid) if len(usersToRemove) > (userCount / 20): raise Exception("AD suggests >5% of users no longer exist, this seems unlikely...") if removeUsers: for u in usersToRemove: print "Removing %s" % u cur.execute("DELETE FROM tblusers WHERE userid=%s", [u]) elif len(usersToRemove) > 0: print "There are %d users no longer in AD" % (len(usersToRemove)) for u,e in emailUpdates: print "Updating email address for %s (%s)" % (u,e) cur.execute("UPDATE tblusers SET email=%s WHERE userid=%s", [e,u]) for u in usersToEnable: print "Enabling %s" % u cur.execute("UPDATE tblusers SET disabled='0' WHERE userid=%s", [u]) for u in usersToDisable: print "Disabling %s" % u cur.execute("UPDATE tblusers SET disabled='1' WHERE userid=%s", [u]) print "\nRefreshing group cache..." def _deleteGroup(groupid): cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s", [groupid]) cur.execute("DELETE FROM tblgroups WHERE groupid=%s", [groupid]) cur.execute("SELECT groupid,name FROM tblgroups") groups = {} while True: rc = cur.fetchone() if not rc: break groups[rc[0]] = rc[1].strip() aclGroups = [] cur.execute("SELECT userid FROM tblaclentries WHERE type='group'") while True: rc = cur.fetchone() if not rc: break aclGroups.append(rc[0].strip()) # Also put the admin group in the cache if not config.admin_group in aclGroups: aclGroups.append(config.admin_group) # Add any groups not in the DB to the DB extraGroups = set(aclGroups) - set(groups.values()) for g in extraGroups: cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [g]) rc = cur.fetchone() groups[rc[0]] = g for gid in groups: gname = groups[gid] print gname, # Check if the group is still actually in use by any acls if not gname in aclGroups: print "..No longer required, removing" _deleteGroup(gid) continue # Is it valid in AD? if not ad.is_valid_group(gname): print "..not found in AD, removing" _deleteGroup(gid) # TODO: Email the ACL owner(s) to let them know we've removed entries pointing at the group cur.execute("DELETE FROM tblaclentries WHERE type='group' AND userid=%s", [gname]) continue # Update the members adMembers = ad.get_all_members_of_group(gname) dbMembers = [] cur.execute("SELECT userid FROM tblgroupusers WHERE groupid=%s", [gid]) while True: rc = cur.fetchone() if not rc: break dbMembers.append(rc[0].strip()) am = set(adMembers) dm = set(dbMembers) newMembers = am - dm removeMembers = dm - am for m in newMembers: cur.execute("INSERT INTO tblgroupusers (groupid,userid) VALUES (%s,%s)", [gid, m]) for m in removeMembers: cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s AND userid=%s", [gid, m]) print "..%d added, %d removed" % (len(newMembers), len(removeMembers)) db.commit()
def init_db(): """Initializes the database.""" db = get_db() sql = 'CREATE TABLE entries( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100) NOT NULL, text VARCHAR(1000) NOT NULL );' db.cursor().execute(sql) db.commit()
from app import db_user, models, db db_user.create_all() cursor = db.cursor() sqlx = "SELECT * FROM user_table" cursor.execute(sqlx) table = cursor.fetchall() for i in range(len(table)): u = models.User(id = table[i][0], name = table[i][1], password = table[i][2], rights = table[i][3], date = table[i][4]) db_user.session.add(u) db_user.session.commit()
def search(machinecode, page=1): form = DcodeForm() img = "" code = "" filenamex = "/static/img_system/main.jpg" user = "" code_input = "" flag_register = 0 flag_page = 0 datainfo = [] typedata = "" cursor = db.cursor() PAGE_NUM = 100 page_total = 1 prev = False nextv = False partname = "" code_use = "" rights = g.user.rights if "1" in str(rights): flag_edit = "Yes" else: flag_edit = "No" f = open(JsTypecodeIndexSrc, "r") cc = f.read() TypecodeIndex_num = cc.count(" ") - 3 f.close() sql = "SELECT COUNT(*) FROM product_records" cursor.execute(sql) resultsx = cursor.fetchall() Num_total = int(str(resultsx[0])[1:-3]) if Num_total > TypecodeIndex_num: num = Num_total - TypecodeIndex_num sql = "SELECT * FROM product_records LIMIT " + str(TypecodeIndex_num) + "," + str(num) cursor.execute(sql) resultsx = cursor.fetchall() for row in resultsx: typedata = typedata + " " + str(row[0]) if Num_total % PAGE_NUM == 0: page_max = Num_total / PAGE_NUM else: page_max = Num_total / PAGE_NUM + 1 page_total = page_max datastart = Num_total - page * PAGE_NUM if datastart >= 0: sql = "SELECT * FROM product_records LIMIT " + str(datastart) + "," + str(PAGE_NUM) else: dataleft = Num_total % PAGE_NUM sql = "SELECT * FROM product_records LIMIT 0," + str(dataleft) cursor.execute(sql) results = cursor.fetchall() # print Num_total, TypecodeIndex_num, typedata if form.submit.data or machinecode != "+": code = str(request.form.get("select_info")) code_input = code if len(code) > 0 and code != "None": sql = ( "SELECT * FROM product_records \ WHERE product_serial = '%s'" % (code) ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: subname = results[0][1] code_use = subname[-3:] + "-" + results[0][0] sql = ( "SELECT * FROM product_sub_partname \ WHERE sub_partname = '%s'" % (subname) ) cursor.execute(sql) spartname = cursor.fetchall() if len(results) > 0: partname = findpartname(spartname[0][2]) if form.submit.data: return redirect(url_for("search", machinecode=code, page=page)) if machinecode != "+" and len(results) > 0: if form.submit.data and len(code) > 0 and code != "None": machinecode = code sql = ( "SELECT * FROM product_records \ WHERE product_serial = '%s'" % (machinecode) ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: subname = results[0][1] code_use = subname[-3:] + "-" + results[0][0] sql = ( "SELECT * FROM product_sub_partname \ WHERE sub_partname = '%s'" % (subname) ) cursor.execute(sql) spartname = cursor.fetchall() if len(results) > 0: partname = findpartname(spartname[0][2]) else: partname = "TEST_ERROR" filenamex = TwoDimentionCode(code_use, partname) code_input = machinecode if form.submit.data: filenamex = "/static/img_system/main.jpg" if form.submitpage.data and form.inputpage.data: if form.inputpage.data <= page_max: return redirect(url_for("search", machinecode="+", page=form.inputpage.data)) else: flag_page = 1 # 页码超过指定范围 if len(results) > 0: prev = True nextv = True if page == 1: prev = False if page == page_total: nextv = False for x in range(len(results)): if len(results) == 1: pp = 1 else: pp = Num_total - (page - 1) * PAGE_NUM - x index = len(results) - x - 1 sql = ( "SELECT * FROM user_table \ WHERE id = '%d'" % (results[index][3]) ) cursor.execute(sql) user_search = cursor.fetchall() info = [ str(pp), results[index][0], results[index][1], user_search[0][1], results[index][2], results[index][4], ] datainfo.insert(len(datainfo), info) if code != "": page_total = 1 page = 1 prev = False nextv = False if form.print_button.data: if filenamex == "/static/img_system/main.jpg": flag_register = 10 else: flag_register = 11 cursor.close() return render_template( "index.html", flag="search", partname=partname, code_use=code_use, form=form, username=str(g.user.name), flag_edit=flag_edit, img=1, flag_register=flag_register, filename=filenamex, typedata=typedata, datainfo=datainfo, code_input=code_input, page_total=page_total, prev=prev, nextv=nextv, pagenow=page, flag_page=flag_page, )
def bominfo(product_flag="#", code_search="+"): form = BomForm() cursor = db.cursor() table_product = [] table_product_bom = [] table_material = {} table_product_name = {} code_input = "" code = "" rights = g.user.rights if "1" in str(rights): flag_edit = "Yes" else: flag_edit = "No" conn = pymssql.connect( host=SQLSERVER_HOST, user=SQLSERVER_USER, password=SQLSERVER_PASS, database=SQLSERVER_DB, port=SQLSERVER_PORT, charset="UTF-8", ) cou = conn.cursor() if code_search != "+" and code_search != " ": code = code_search code_input = code # 产品列表初始化 codex = str(request.form.get("select_info")) if not form.submit.data or (form.submit.data and (len(codex) == 0 or codex == "None")): sql = "SELECT * FROM bom_index " cursor.execute(sql) results = cursor.fetchall() i = 1 for rows in results: if rows[6] == 1: info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]] table_product.insert(len(table_product), info_product) i = i + 1 name = rows[1] + "_" + rows[2] table = BomTabbleInfo(name, rows[4], rows[5], rows[0], cursor) table_product_name = dict(table_product_name.items() + table.items()) bom_info = ERP_bomsearch(rows[0], cursor, cou) table_material = dict(table_material.items() + bom_info.items()) # 搜索产品 if form.submit.data or code != "": if form.submit.data: code = str(request.form.get("select_info")) if len(code) > 0 and code != "None": code_input = code table_product = [] if "." in code: sql = ( "SELECT * FROM bom_index \ WHERE erp_serial = '%s'" % (code) ) else: sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s'" % (code) ) cursor.execute(sql) results = cursor.fetchall() i = 1 for rows in results: if rows[6] == 1: info_product = [i, rows[1], rows[2], rows[3], rows[5], rows[7], rows[0]] table_product.insert(len(table_product), info_product) i = i + 1 name = rows[1] + "_" + rows[2] table = BomTabbleInfo(name, rows[4], rows[5], rows[0], cursor) table_product_name = dict(table_product_name.items() + table.items()) bom_info = ERP_bomsearch(rows[0], cursor, cou) table_material = dict(table_material.items() + bom_info.items()) if code_input == "": code_input = " " cursor.close() conn.close() return render_template( "bominfo.html", flag="bominfo", form=form, flag_edit=flag_edit, username=str(g.user.name), code_input=code_input, table_product=table_product, table_product_bom=table_product_bom, table_product_name=table_product_name, table_material=table_material, )
def bomedit(num_rows=3, table_detail="#"): form = BomForm_Edit() flag_edit = "Yes" flag_register = 1 typedata = "" bom_copy_info = ["", "", "", "", ""] table_detail_html = [] table_detailname_html = [] cursor = db.cursor() conn = pymssql.connect( host=SQLSERVER_HOST, user=SQLSERVER_USER, password=SQLSERVER_PASS, database=SQLSERVER_DB, port=SQLSERVER_PORT, charset="UTF-8", ) cou = conn.cursor() sql = "SELECT * FROM product_sub_partname" cursor.execute(sql) results = cursor.fetchall() for row in results: typedata = typedata + " " + str(row[0]) input_subname = "" input_version = "1.0" if table_detail != "#": res = table_detail.split("$") input_subname = res[0] input_version = res[1] sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s' and version = '%s'" % (input_subname, input_version) ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: input_id = results[0][0] bom_copy_info = [input_subname, input_version, results[0][3], results[0][6], results[0][7]] sql = ( "SELECT * FROM bom_detail \ WHERE product_index = '%d' order by erp_material" % (input_id) ) cursor.execute(sql) results = cursor.fetchall() num_rows = len(results) for x in results: erp_serial = str(x[1]) erp_serial = erp_serial.strip() sql = "select * from dbo.t_ICItemCore where FNumber = '%s'" % (str(erp_serial)) cou.execute(sql) resultsx = cou.fetchall() material_info = [ resultsx[0][2].encode("latin1").decode("gbk"), resultsx[0][1].encode("latin1").decode("gbk"), ] info = ["", erp_serial, material_info[0], material_info[1], x[2], x[4]] # info = ['', erp_serial, '', '', x[2], x[4]] table_detail_html.insert(len(table_detail_html), info) if form.newbom.data: input_subname = "" input_version = "1.0" bom_copy_info = ["", "", "", "1", ""] table_detail_html = [] num_rows = 3 if str(request.form.get("commit_flag")) == "YES_COMMIT": table_detail_html = [] get_subname = str(request.form.get("subname_vice")) get_version = str(request.form.get("version_vice")) get_erp_serial = str(request.form.get("erp_serial")) get_status = str(request.form.get("status")) get_comments = str(request.form.get("comments")) # print get_comments, get_status, get_version, get_subname, get_erp_serial material_check = request.form.getlist("material_check") material_check = checked_get(material_check) material_serial = request.form.getlist("material_serial") material_name = request.form.getlist("material_name") material_model = request.form.getlist("material_model") material_quantity = request.form.getlist("material_quantity") material_comment = request.form.getlist("material_comment") num_rows = len(material_serial) for i in range(num_rows): info = [ "", material_serial[i], material_name[i], material_model[i], material_quantity[i], material_comment[i], ] table_detail_html.insert(len(table_detail_html), info) # print table_detail_html bom_copy_info = [get_subname, get_version, get_erp_serial, get_status, get_comments] # print material_check, len(material_check) date_now = str(datetime.datetime.now()) sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s' and version = '%s' and erp_serial = '%s'" % (get_subname, get_version, get_erp_serial) ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: flag_register = 20 # 已经被注册 else: try: sql = ( "INSERT INTO bom_index(subname, version, erp_serial, user, date, status, comments) \ VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%s')" % ( get_subname, get_version, get_erp_serial, int(g.user.id), date_now[:-7], int(get_status), get_comments, ) ) cursor.execute(sql) db.commit() flag_register = 21 # bom_indx注册成功 except: flag_register = 30 # bom_indx注册失败 if flag_register == 21: try: sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s' and version = '%s' and erp_serial = '%s'" % (get_subname, get_version, get_erp_serial) ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: id_register = results[0][0] for i in range(num_rows): if material_serial[i]: sql = ( "INSERT INTO bom_detail(erp_material, quantity, product_index, comments) \ VALUES ('%s', '%d', '%d', '%s')" % (material_serial[i], int(material_quantity[i]), id_register, material_comment[i]) ) cursor.execute(sql) db.commit() flag_register = 22 # bom_detail 注册成功 except: flag_register = 31 # bom_detail 注册失败 try: sql = ( "DELETE FROM bom_index \ WHERE subname = '%s' and version = '%s' and erp_serial = '%s'" % (get_subname, get_version, get_erp_serial) ) cursor.execute(sql) except: flag_register = 32 if len(table_detail_html) != num_rows: for i in range(num_rows - len(table_detail_html)): info = ["", "", "", "", "1", ""] table_detail_html.insert(len(table_detail_html), info) material_table_info = place_name_create(num_rows) # print flag_register cursor.close() cou.close() return render_template( "bomedit.html", flag="bomedit", flag_edit=flag_edit, username=str(g.user.name), flag_register=flag_register, form=form, typedatax=typedata, num_rows=num_rows, bom_copy_info=bom_copy_info, table_detail=table_detail_html, material_table_info=material_table_info, )
def open_cursor_hook(req, res, resource, params): resource.db = db resource.cursor = db.cursor()
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 index(version_info="init", machinecode="+", flag_return=0, remark_info=""): form = DcodeForm() img = "" code = "" filenamex = "/static/img_system/main.jpg" user = "" code_input = "" flag_register = 0 datainfo = [] typedata = "" cursor = db.cursor() code_use = "" partname = "" rights = g.user.rights if "1" in str(rights): flag_edit = "Yes" else: flag_edit = "No" sql = "SELECT * FROM product_sub_partname" cursor.execute(sql) results = cursor.fetchall() for row in results: typedata = typedata + " " + str(row[0]) if form.submit.data or machinecode != "+": code = str(request.form.get("select_info")) code_input = code if len(code) > 0 and code != "None": machinecode_return = code searchname = "%" + code + "%" sql = ( "SELECT * FROM product_sub_partname \ WHERE sub_partname like '" + searchname + "'" ) cursor.execute(sql) results = cursor.fetchall() else: machinecode_return = machinecode if form.submit.data: return redirect( url_for("index", machinecode=machinecode_return, flag_return=0, version_info="init", remark_info="NULL") ) elif machinecode != "+" and not form.submit.data and flag_return != 0: if len(code) > 0 and code != "None": machinecode = code_input else: code_input = machinecode sql = ( "SELECT * FROM product_sub_partname \ WHERE sub_partname = '%s'" % (machinecode) ) cursor.execute(sql) results_machine = cursor.fetchall() if len(results_machine) > 0: # sqlx = "SELECT * FROM product_records order by product_produce_date desc limit 1" sqlx = "SELECT * FROM product_records order by product_serial desc limit 1" cursor.execute(sqlx) list_date = cursor.fetchall() date_use = list_date[0][0] date_now = datetime.datetime.now() x = str(date_now).find(" ") now = str(date_now)[:x] now = now.replace("-", "") now = now[2:] hex_now = hex(int(now))[2:].upper() if hex_now == date_use[:-3]: if flag_return != 3: num = hex(int(date_use[-3:], 16) + 1) else: num = hex(int(date_use[-3:], 16)) if len(num) == 3: num_use = "00" + num[2:] elif len(num) == 4: num_use = "0" + num[2:] else: num_use = num[2:] code_use = str(machinecode)[-3:] + "-" + date_use[:-3] + num_use else: code_use = str(machinecode)[-3:] + "-" + hex_now + "001" partname = findpartname(results_machine[0][2]) code_use = code_use.upper() filenamex = TwoDimentionCode(code_use, partname) results = results_machine else: partname = "!ERROR!" code_use = "!WRONG_RESULT!" elif flag_return == 0: if machinecode != "+": code_input = machinecode searchname = "%" + machinecode + "%" sql = ( "SELECT * FROM product_sub_partname \ WHERE sub_partname like '" + searchname + "'" ) cursor.execute(sql) results = cursor.fetchall() if len(results) > 0: i = 1 for row in results: partnamex = findpartname(int(row[2])) sql = ( "SELECT * FROM bom_index \ WHERE subname = '%s'" % (row[0]) ) cursor.execute(sql) results_bom = cursor.fetchall() if version_info == "init" and flag_return == 0: if len(results_bom) > 0: for j in range(len(results_bom)): mark = str(row[1]) + " ## DIFFERENCE: " + str(results_bom[j][7]) info = [str(i), row[0], results_bom[j][2], partnamex, mark] datainfo.insert(len(datainfo), info) i += 1 else: info = [str(i), row[0], "1.0", partnamex, row[1]] datainfo.insert(len(datainfo), info) i += 1 else: info = [str(i), row[0], version_info, partnamex, row[1]] datainfo.insert(len(datainfo), info) i += 1 if request.form.get("register"): # print code_input, machinecode if filenamex == "/static/img_system/main.jpg" or flag_return == 0: # flash(encodechinese("请在列表中选择您想注册的产品!")) flag_register = 4 # 示例照片,不能注册 elif flag_return == 3: flag_register = 5 # 重复注册 else: flag_register = 1 # 正常注册 if flag_return == 2: try: sql = ( "INSERT INTO product_records(product_serial, product_subname, product_produce_date, product_producer, product_comment) \ VALUES ('%s', '%s', '%s', '%d', '%s')" % (code_use[4:], machinecode, str(date_now)[:-7], int(g.user.id), encodechinese(remark_info[1:])) ) cursor.execute(sql) db.commit() flag_register = 2 # 注册成功,是否打印 except: flag_register = 3 # 注册失败 cursor.close() remark_use = encodechinese(form.remark.data) s = remark_use.splitlines() x = "x" for line in s: x = x + line + " " if version_info != "init" and flag_return < 2: if x.find("xNULL") >= 0: x = "Version:" + version_info else: comp = "Version:" + version_info re_find = re.findall(r"Version:[\d+]\.[\d+]", x) if len(re_find) > 0: if re_find[0] != comp: x = re.sub(r"Version:[\d+]\.[\d+]", comp, x) else: x = comp + "_" + x form.remark.data = x elif flag_return >= 2: form.remark.data = remark_info x = remark_info[1:] return render_template( "index.html", flag="index", remark_info=x, partname=partname, code_use=code_use, form=form, username=str(g.user.name), flag_edit=flag_edit, version_info=version_info, img=1, flag_register=flag_register, filename=filenamex, typedata=typedata, datainfo=datainfo, code_input=code_input, )