def warn(self): first_name_reply = self.msg['reply_to_message']['from']['first_name'] user_reply_id = self.msg['reply_to_message']['from']['id'] if user_reply_id not in self.get_admin_list(user_reply=True): try: advs = int(sql.procurar(self.chat_id, user_reply_id)[1]) except: sql.inserir(self.chat_id, user_reply_id) advs = int(sql.procurar(self.chat_id, user_reply_id)[1]) self.bot.sendMessage( self.chat_id, '{user} <b>has been warned</b> ({advs}/3).'.format( user=first_name_reply, advs=advs + 1), parse_mode='HTML', reply_markup=self.keyboard_warn(user_reply_id)) sql.advertir(self.chat_id, user_reply_id) if advs >= 3: self.bot.sendMessage( self.chat_id, '<b>{}</b> expulso por atingir o limite de advertencias.'. format(first_name_reply), parse_mode='HTML') self.bot.kickChatMember(self.chat_id, user_reply_id) sql.delete(self.chat_id, user_reply_id) else: pass else: return self.bot.sendMessage( self.chat_id, '<b>{}</b> é um dos administradores. Não posso advertir administradores.' .format(first_name_reply), parse_mode='HTML')
def delete(): id = request.args.get("id") select = request.args.get('select') #delete sql.delete(select, id) #jump to insert page return redirect(url_for('insert', title='insert', select=select))
def clean(tablename): urls = sql.oldest(tablename) checkBar = ChargingBar('Checking for expired listings', max = len(urls), suffix = '%(index)d/%(max)d') for url in urls: if craigslist.is_expired(url): sql.delete(tablename, 'url', url) checkBar.next() checkBar.finish()
def index(self): self.oldurls = [] self.newurls = {} self.parent = None self.catlist = db.fetchvals(sql.get_f('p.translit', 'prop p, cat c', 'c.id = p.id')) site.gvar.traceurl = self.traceurl db.execute(sql.update('status', 'sitemap'), [0]) for (mod, data) in const.SITE_MAP.iteritems(): for path in data: url = geturl(None, [mod, path], alwaysext = True) self.traceurl(url) q = sql.get_f('*', 'prop', 'id_group = getpgbytr("articles")') for row in db.fetchobj(q): url = geturl(None, [const.MOD_ARTICLE, row.translit], alwaysext = True) self.traceurl(url) while True: self.urls = self.newurls.copy() if not self.urls: break self.newurls.clear() for url in self.urls.iterkeys(): self.addurl(url) return site.gvar.traceurl = None db.execute(sql.delete('sitemap', 'status = 0'), commit = True)
def delete_product(name,productId): try: result = dict() # 打开数据库连接 db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8') # 使用cursor()方法获取操作游标 cursor = db.cursor() condition = dict() condition["commodity_id"] = productId condition["company"] = vendor_name2company_name(name) query = sql.delete("provide", condition) try: cursor.execute(query) db.commit() db.close() return True, "删除商品成功", "null" except: db.rollback() db.close() return False, "删除商品失败", "null" except: return False, "连接数据库失败", "null"
def ban(self): first_name_reply = self.msg['reply_to_message']['from']['first_name'] reply_id = self.msg['reply_to_message']['from']['id'] if reply_id not in self.get_admin_list(user_reply=True): self.bot.kickChatMember(self.chat_id, reply_id) self.bot.sendMessage( self.chat_id, '<b>{}</b> foi retirado do grupo.'.format(first_name_reply), parse_mode='HTML' ) try: sql.delete(self.chat_id, reply_id) except: pass return True else: return self.bot.sendMessage(self.chat_id, '<b>{}</b> é um dos administradores. Não posso remover administradores.'.format(first_name_reply), parse_mode='HTML' )
def clear(self, ftext): """Очистка наличия товаров на складе перед загрузкой новых данных о наличии""" # Получение идентификаторов товаров, данные о наличии которых # будут обновлятся f = self.parsefilter(ftext) q = sql.get_f('DISTINCT s.id_product', 'store s', f) idl = db.fetchvals(q) pcnt = len(idl) if pcnt: log("Found %d products" % pcnt) else: log("No products found, nothing to clear!", M_WARN) return # Вывод 5-ти случайных товаров из того списка, данные о наличии # которых будут очищены log('----------------------------') imax = min(pcnt, 5) for i in xrange(imax): index = random.randint(0, pcnt - 1) product = Product(None, idl[index]) log("Random product (%d of %d): %s" % (i + 1, imax, real_translit(product.name))) log('----------------------------') log('%d products will be deleted from store. Continue? (y/n)' % pcnt) # Подтверждение об удалении наличия (если требуется) if self.o.prompt: res = raw_input() else: res = "y" # Очистка наличия товаров из базы if res == 'y': idl = db.fetchvals(sql.get_f('id', 'store s', f)) q = sql.delete('store', sql.fin('id', idl)) db.execute(q) db.commit() log("Store records deleted") else: log("User abort, exiting...") sys.exit(0)
def enter_spec(name, commodityId, detail, url): try: # 打开数据库连接 db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8') # 使用cursor()方法获取操作游标 cursor = db.cursor() company_name = vendor_name2company_name(name) # 查询条件 condition = dict() condition['company'] = company_name condition['commodity_id'] = commodityId # 生成SQL语句 query = sql.delete("provide", condition) # 使用execute方法执行SQL语句 try: cursor.execute(query) db.commit() for obj in detail: condition = dict() condition["spec"] = obj["spec"] condition["price"] = obj["price"] condition["commodity_id"] = commodityId condition["pdf_path"] = url condition["company"] = company_name query = sql.insert("provide", condition) cursor.execute(query) db.commit() except: db.rollback() return False, "录入商品支数和价格失败", "null" db.close() return True, "录入商品支数和价格成功", "null" except: return False, "无法连接数据库" , "null"
fields[key] = form.getvalue(key) ######## If UPDATE button is pressed then ... ############################################################################ ignore_update = []; if form.getvalue("submit") == "Update": fields['Salt'] = uuid.uuid4().hex if fields["HashedPassword"] != None: fields["HashedPassword"] = hashlib.sha512(fields['HashedPassword'] + fields['Salt']).hexdigest() else: ignore_update = ignore message = sql.update(db, cursor, table, fields, keys, pk, ignore=ignore_update) ######## If DELETE button is pressed then ... ########################################################################### if form.getvalue("submit") == "Delete": message = sql.delete(db, cursor, table, fields, pk) ####### GENERATE AND EXECUTE SEARCH QUERY ########################################################################## if (fields['UserName'] == None): fields['UserName'] = userName result = sql.search(db, cursor, table, fields, keys, exact_keys, ignore=ignore_form, limit=10, fetch_one=True) row = result[0]; if row == None: row = ["", "", "", "", "", "", "", "", "", "", "", "", ""] row = list(row) for i in range(13): if row[i] == None: row[i] = ""
def delete(self, **kw): q = {} q.update(kw) q['_where'] = self.where(q) return self.db.query(sql.delete(self.table, **q))
fields = dict.fromkeys(keys) row = None message = "" for key in fields: fields[key] = form.getvalue(key) ######## If INSERT button is pressed then ... ########################################################################### if form.getvalue("submit") == "Insert": message = sql.insert(db, cursor, table, fields, keys) ######## If DELETE button is pressed then ... ########################################################################### if form.getvalue("submit") == "Delete": message = sql.delete(db, cursor, table, fields, pk) ######## If UPDATE button is pressed then ... ############################################################################ if form.getvalue("submit") == "Update": message = sql.update(db, cursor, table, fields, keys, pk) ######## LOAD RESULT ... ############################################################################ if(fields['GameID'] != None): result = sql.search(db, cursor, table, fields, keys, exact_keys, ignore=ignore, limit=10, fetch_one=True) row = result[0]; else: row = None if row == None: row = ["", "", "", "", "", "", "", "", ""]
def acao_delete(tabela): bold_underline = '\033[1m \033[4m' end_bold_underline = '\033[0m' id_tabela = 'id_' + tabela # faz um select e printa todos os dados da tabela print(f'\nA tabela {tabela} contem os seguintes dados: ') colunas_query = colunas_all(tabela) query = f'SELECT {colunas_query} FROM {tabela}' colunas_vetor = colunas_query.split(', ') df_all = select(query, colunas_vetor) # se a tabela estiver vazia, não será possível fazer delete if not df_all.empty: print(df_all.to_string(index=False)) else: print('Essa tabela está vazia, não é possível realizar um delete!\n') input('Pressione enter para continuar\n') return 'Tabelas' # se for uma tabela da beirada, irá pedir o código da sua chave primária. se for uma tabela do meio, irá pedir os códigos que são chave primária e estrangeira condition = [] if id_tabela in colunas_vetor: value = int( input( f'\nInsira o id do registro {bold_underline}({id_tabela}){end_bold_underline} que você deseja deletar: ' )) condition.append(value) else: for coluna in colunas_vetor: value = int( input( f'\nInsira o id do registro {bold_underline}({coluna}){end_bold_underline} que você deseja deletar: ' )) condition.append(value) # confirma se é pra fazer o delete verif = str( input( f'\nTem certeza que deseja deletar {bold_underline}{id_tabela} = {condition}{end_bold_underline}? [Y/N] ' )) if (verif == 'N' or verif == 'n'): return 'Tabelas' # monta a query if id_tabela in colunas_vetor: query = f'DELETE FROM {tabela} WHERE {id_tabela} = {condition[0]}' else: count = 0 query = f'DELETE FROM {tabela} WHERE ' for item in colunas_vetor: if 'id_' in item: query = query + f'{item} = {condition[count]} AND ' count += 1 query = query[:-5] # tenta fazer o delete try: delete(query) if id_tabela in colunas_vetor: print( f'{bold_underline}{id_tabela} = {condition}{end_bold_underline} deletada com sucesso!\n' ) else: print( f'{bold_underline}{condition}{end_bold_underline} deletada com sucesso!\n' ) verif = str( input( f'\n Deseja deletar outro registro desta mesma tabela? [Y/N] ') ) if (verif == 'Y' or verif == 'y'): acao_delete(tabela) else: input('\nPressione enter para continuar\n') return 'Tabelas' # se não conseguir, trata os erros except psycopg2.Error as e: if e.pgcode == '22P02': print( f'\nVocê inseriu um campo de número com o formato errado. Insira novamente.' ) input('\nPressione enter para continuar\n') acao_delete(tabela, colunas) elif e.pgcode == '23503': tabela_ref = str(e.pgerror).split('\"')[-2] print( f'Não é possível deletar essa linha porque está referenciada na tabela {bold_underline}{tabela_ref}{end_bold_underline}.' ) input('\nPressione enter para continuar\n') else: print('Ocorreu algum erro! Retornando para o menu de tabela!\n') input('\nPressione enter para continuar\n') return 'Tabelas'
keys = ['AchievementID', 'InstanceRunID', 'WhenAchieved', 'AchievementName', 'RewardBody'] exact_keys = ['AchievementID', 'InstanceRunID', 'WhenAchieved'] pk = ['AchievementID'] fields = dict.fromkeys(keys) for key in fields: fields[key] = form.getvalue(key) ######## If INSERT button is pressed then ... ########################################################################### if form.getvalue("submit") == "Insert": print sql.insert(db, cursor, table, fields, keys) ######## If DELETE button is pressed then ... ########################################################################### if form.getvalue("submit") == "Delete": print sql.delete(db, cursor, table, fields, pk) ######## If UPDATE button is pressed then ... ############################################################################ if form.getvalue("submit") == "Update": print sql.update(db, cursor, table, fields, keys, pk) ####### GENERATE AND EXECUTE SEARCH QUERY ################################################################################ result = sql.search(db, cursor, table, fields, keys, exact_keys, limit=10) rows = result[0]; print result[1]; ####### DISPLAY RESULTS TABLE ############################################################################################# print '<table class="gridtable" align="center">' # Print column headers
def index(): # Bootstrap alerts and errors that will popup on the top of the screen alerts = [] errors = [] # Arguments being passed through to the html page htmlArguments = {} # Checks if an error is detected in the HTML arguments (generally the text after '?' in the domain) and adds the information to the Bootstrap errors if 'error' in request.args: errors.append({ 'error': request.args['error'], 'error_description': request.args['error_description'] }) # Checks if the user isn't logged in locally if not session.get("user"): # Creates a state for the session for the user session["state"] = str(uuid.uuid4()) # Creates the OAuth2 redirect URL for the user to be logged into, which is passed through into the html arguments auth_url = _build_auth_url(scopes=app_config.SCOPE, state=session["state"]) htmlArguments['auth_url'] = auth_url else: # Gets email of the user, and looks up user in the database emailOfUser = session["user"]["preferred_username"] databaseInfo = sql.fetch(emailOfUser).fetchone() # if user is not found in database or invalid refresh token if not databaseInfo or not databaseInfo[0]: # logs out user return redirect(url_for("logout")) # Checks if user requires SMS verification, by searching if user has phone number saved, but not verified requireSMSVerification = databaseInfo[1] and not databaseInfo[5] # Checks if the user wishes to receive Microsoft Teams notifications getTeamsNotifications = databaseInfo[2] # Checks if the user wishes to send and receive emails over SMS emailOverSMS = databaseInfo[4] # Prefills phone number on HTML form if phone number is already in the database if databaseInfo[1]: htmlArguments['prefilledPhoneNumber'] = databaseInfo[1] else: htmlArguments['prefilledPhoneNumber'] = "" # Checks if the user has made a POST request if request.method == 'POST': # Checks if the user pressed the update button if 'updateButton' in request.form: # Gets the phone number from the form phoneNumber = request.form['phoneNumber'] # Gets the verification code from the form if required if requireSMSVerification: verificationCodeFromUser = request.form[ 'smsVerificationCode'] # Checks if user attempted entering a verification code if verificationCodeFromUser: # Clears verification code and sets phone as verified if verified code is correct if verificationCodeFromUser == databaseInfo[6]: sql.updateVal(emailOfUser, 'VerifiedPhone', True) sql.updateVal(emailOfUser, 'VerificationCode', None) requireSMSVerification = False send( "OfficeConnected: You have successfully connected your phone! Reply with 'CMD' to get a full list of commands you can do with OfficeConnected", databaseInfo[1]) else: # Tells user that verification code is wrong through Bootstrap errors.append({ "error": "Invalid SMS verification code", "error_description": "You have entered an invalid verification code, make sure you've typed the right characters. If you would like a new verification code, you can reply 'LINK' to the SMS message" }) # Checks if user is trying to update phone the phone number to a different one from database if databaseInfo[1] != phoneNumber and phoneNumber: # Checks if updated phone number already exists in the database and tells user error through Bootstrap if sql.fetchPhone(phoneNumber).fetchone(): errors.append({ "error": "Phone number already exists", "error_description": "An account with that phone number already exists in our database, please enter a valid phone number, or to unlink that number, text 'UNLINK' to +1 (844)-961-2701" }) else: # Updates unverified phone number in database sql.updateVal(emailOfUser, 'PhoneNumber', phoneNumber) sql.updateVal(emailOfUser, 'VerifiedPhone', False) sql.updateVal(emailOfUser, 'VerificationCode', None) # Replace html argument to updated phone number htmlArguments['prefilledPhoneNumber'] = phoneNumber requireSMSVerification = True # Notifying user over text and Bootstrap alert to verify phone number send( "OfficeConnected: Verify your phone by responding with the message 'LINK' to receive your verification code", phoneNumber) alerts.append( "A message has been sent to your phone. Please verify your phone by responding with the message 'LINK' and entering your verification code" ) # Updates if the user wants to get Teams notifications based on if the getTeamsNotification checkbox is checked in HTML if 'getTeamsNotifications' in request.form and request.form[ 'getTeamsNotifications'] == 'on': getTeamsNotifications = True sql.updateVal(emailOfUser, 'GetSMSTeamNotifications', True) else: getTeamsNotifications = False sql.updateVal(emailOfUser, 'GetSMSTeamNotifications', False) # Updates if the user wants to allow email over SMS based on if the emailOverSMS checkbox is checked in HTML if 'emailOverSMS' in request.form and request.form[ 'emailOverSMS'] == 'on': emailOverSMS = True sql.updateVal(emailOfUser, 'EmailOverSMS', True) else: emailOverSMS = False sql.updateVal(emailOfUser, 'EmailOverSMS', False) # Checks if the deleteAccount button has been pressed, and clears user from database elif 'deleteAccount' in request.form: sql.delete(emailOfUser) return redirect(url_for("logout")) # sets respective HTML arguments to their variables on Python to be passed through in Flask htmlArguments['getTeamsNotificationsBool'] = getTeamsNotifications htmlArguments['emailOverSMSBool'] = emailOverSMS htmlArguments['requireSMSVerification'] = requireSMSVerification # Passes through basic user info to Flask htmlArguments['user'] = session['user'] # Passes through Bootstrap alerts and errors to HTML htmlArguments['errors'] = errors htmlArguments['alerts'] = alerts # Renders the HTML, with htmlArguments as it's arguments return render_template('home.html', **htmlArguments)
message = sql.insert(db, cursor, 'CrowdFundingViewer', fields, keys_c) print "insert" if 'Error' in message: print message error = 1 if(fields['ViewerType'] == 'B' or fields['ViewerType'] == 'P'): if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'C'): message = sql.insert(db, cursor, 'PremiumViewer', fields, keys_p) print "insert" if 'Error' in message: print message error = 1 if error == 1: #delete tables if there are any error if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'P'): sql.delete(db, cursor, 'CrowdFundingViewer', fields, ['ViewerID']) if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'C'): sql.delete(db, cursor, 'PremiumViewer', fields, ['ViewerID']) message2 = "Update Error! Error in changing types!" #delete tables if viewer is now no longer premium or crowdfunding if(fields['ViewerType'] == 'N' or fields['ViewerType'] == 'P'): sql.delete(db, cursor, 'CrowdFundingViewer', fields, ['ViewerID']) if(fields['ViewerType'] == 'N' or fields['ViewerType'] == 'C'): sql.delete(db, cursor, 'PremiumViewer', fields, ['ViewerID']) #update current tables message = sql.update(db, cursor, table, fields, keys, ['ViewerID'], ignore=ignore_update) if fields['ViewerTypeOld'] != 'C' or fields['ViewerTypeOld'] != 'B': message = sql.update(db, cursor, 'CrowdFundingViewer', fields, keys_c, ['ViewerID'], ignore=ignore_update)
def delete_row(self): sql.delete(self.parent.table, self.row_data) self.parent.change_table(self.parent.table) self.reqWindow.close()
def delete_record(self): sql.delete(self.table.tablename, self.values) self.table.update_data() self.destroy()