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()
