def saving_delay(self, src, dst, delay):
"""
saving the delay info to the sqlite
"""
saving_sql = 'UPDATE switch SET delay = ? WHERE sw1 = ? AND sw2 = ?'
data = [(str(delay), str(src), str(dst))]
sql.update(self.conn, saving_sql, data)
def saving_bw(self , dpid , out_port , bw):
'''
save the info about bw according to data type
type: only 'flow' now
'''
"""
finding flow using dpid and out-port in flow_sql table
"""
update_sql = 'UPDATE switch SET bw = ? WHERE sw1 = ? AND po1 = ?'
data = [(str(bw) , str(dpid) , out_port)]
print data
sql.update(self.conn, update_sql, data)
def update():
table_to_class = {
"patient_records": i_forms.AddPatient(),
"billed_medicine": i_forms.AddBilledMedicine(),
"billed_service": i_forms.AddBilledService(),
"doctors": i_forms.AddDoctors(),
"departments": i_forms.AddDepartments(),
"worksfor": i_forms.AddWorksFor(),
"treatedby": i_forms.AddTreatedBy(),
"service": i_forms.AddService(),
"medicine": i_forms.AddMedicine(),
"rooms": i_forms.AddRooms(),
"stays_in": i_forms.AddStaysIn(),
}
#error = None
id = request.args.get("id")
select = request.args.get("select")
# get insert form
form = table_to_class[select]
#get header
header = sql.get_header(select)
# get columns
columns = table_to_properties[select]
if form.validate_on_submit():
params = {}
for prop in table_to_properties[select]:
value = getattr(form, prop).data
if isinstance(value, datetime.date):
value = value.strftime('%Y-%m-%d')
params[prop] = value
print(id, params)
sql.update(select, id, params)
#jump to insert page
return redirect(url_for('insert', title='insert', select=select))
return render_template("update.html",
select=select,
form=form,
header=header,
columns=columns)
def download(u, p, o):
manifestname = manifest.getName(u)
if manifestname == '':
print('ERROR: There seems to be no Manifest file for order {number}'.format(number=o))
sql.setOrderStatus(o, 'NOMANIFEST')
else:
u += manifestname
p = os.path.join(p, manifestname)
ftp.file(u, p)
if os.path.exists(p): # also check file size here
SQL = "UPDATE orders set manifest = %s WHERE ordernumber = %s"
data = (manifestname, o)
sql.update(SQL, data)
sql.setOrderStatus(o, 'MANIFEST')
else:
print('ERROR: There is no Manifest for order {o}'.format(o=o))
def f():
f = ['ps.id_product = p.id', 'pr.id_prop = ps.id_prop1', 'pr.name = "ETA"']
q = sql.get_f('pr.val1', ['prop_set ps', 'param pr'], f)
q = '(%s)' % sql.limit(q, 0, 1)
q = sql.update('eta', 'product p') % q
db.execute(q)
f = ['s.id_product = p.id', 'pr.id_prop = s.id_store', 'pr.name = "ETA"']
q = sql.get_f('pr.val1', ['store s', 'param pr'], f)
q = sql.order(q, 'pr.val1')
q = '(%s)' % sql.limit(q, 0, 1)
fu = sql.get_f('id', 'store s', 's.id_product = p.id')
fu = sql.exists(fu)
q = sql.update('eta', 'product p', filter = fu) % q
db.execute(q)
db.commit()
def enter_pdf(name, productId, pdf_path):
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
value = dict()
value["pdf_path"] = pdf_path
company_name = vendor_name2company_name(name)
condition = dict()
condition['company'] = company_name
condition['commodity_id'] = productId
# 生成SQL语句
query = sql.update("provide", value, condition)
# 使用execute方法执行SQL语句
try:
cursor.execute(query)
db.commit()
db.close()
result = total_commoditys(company_name)
return True, "录入pdf成功", result
except:
db.rollback()
db.close()
result = total_commoditys(company_name)
return False, "录入pdf失败", result
except:
return False, "无法连接数据库" , "null"
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 save_sample_plant(sample_id, plant_id, date):
if add_plant(plant_id): progress('Plant %d added' % plant_id)
if not sql.exists('samples', sample_id):
sample = {
'id': sample_id,
'created': date,
}
if sql.insert('samples', sample):
progress('Sample %d added' % sample['id'])
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
else:
# check if there is already a link between sample and plant
samples = sql.fetch_all('sample_plants', { 'sample_id': sample_id, 'plant_id': plant_id })
if samples:
for sample in samples:
if sample['plant_id'] == 1:
if sql.update('sample_plants', { 'sample_id': sample_id }, { 'plant_id': plant_id }):
progress('Updated %d sample plant_id from %d to %d' % (sql.lastrowid(), 1, plant_id))
else:
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
def save(self, afields = None):
if afields:
fields = afields
else:
fields = self.table.fields
key = self.keyfield
tab = self.table.name
exists = self.id and self.db.fetchval(sql.get_fval(key, tab, key), [self.id])
db = self.db.self()
try:
# UPDATE
if exists:
self.mode = MOD_UPD
q = sql.update(fields, tab, key)
vals = map(lambda field: self.get(field, None), fields) + [self.id]
# INSERT
else:
self.mode = MOD_INS
self.newid(db)
q = sql.insert(fields, tab)
vals = map(lambda field: self.get(field, None), fields)
db.execute(q, vals, commit = True)
finally:
pass
async def rps(ctx, player_choice):
"""Play rock, paper, scissors against Botboy (eg. !rps r)"""
# See if player is already in database. If not, create their entry.
member = str(ctx.message.author)
query = sql.select(rps_table, condition={"DiscordName":member})
# Store all results from query in a list of tuples
results = c.execute(query).fetchall()
if len(results) == 0:
# Create entry
default_values = [member,0,0,0]
c.execute(sql.insert(rps_table, default_values))
# Result matrix - columns: player's choice / rows: bot's choice
result_matrix = [['draw','user','bot'],['bot','draw','user'],['user','bot','draw']]
choices = ['rock','paper','scissors']
alt_choices = ['r','p','s']
# Bot makes a choice
bot_choice_index = random.randint(0,2)
log.debug("rps - player input: {0}".format(player_choice))
log.debug("rps - bot's random number: {0}".format(bot_choice_index))
# Try getting index from players choice
try:
player_choice_index = choices.index(player_choice)
except:
try:
player_choice_index = alt_choices.index(player_choice)
except:
await ctx.send("ERROR: must enter 'rock' (or r), 'paper' (or p), 'scissors' (or s)")
return
# Determine result from matrix
result = result_matrix[bot_choice_index][player_choice_index]
column_update = {}
if result == "user":
winner = "You win!"
column_update = {"Wins":"Wins+1"}
elif result == "draw":
winner = "It's a draw ¯\_(ツ)_/¯"
column_update = {"Draws":"Draws+1"}
elif result == "bot":
winner = "Better luck next time!"
column_update = {"Losses":"Losses+1"}
# Update database
c.execute(sql.update(rps_table, column_update, {"DiscordName":member}))
# Create response
response = "You chose: {0} \nBotBoy chose: {1} \n{2}".format(choices[player_choice_index], choices[bot_choice_index], winner)
query = sql.select(rps_table, condition={"DiscordName":member})
results = c.execute(query).fetchone()
wins = results[1]
draws = results[2]
losses = results[3]
win_percentage = (wins/(wins+draws+losses))*100
response += "\nWins: " + str(wins) + "\nDraws: " + str(draws) + "\nLosses: " + str(losses) + "\nWin %: " + str(win_percentage) + "%"
# Say it
await ctx.send(response)
conn.commit()
def select_proxy():
myIP = query_ip()
if myIP == None:
raise Exception('本机IP获取失败！')
while True:
ip_info = sql.get()
if not ip_info:
raise Exception('代理获取失败，请更新代理池')
proxies = {'http': '{}:{}'.format(ip_info[0], ip_info[1])}
# print(f"proxies={proxies}") # proxies={'http': '60.13.42.87:9999'}
xIP = query_ip(proxies, ip=ip_info[0]) # 查询代理IP
if xIP == None or xIP == myIP:
sql.update(ip_info[0], 'STATUS', '失效')
print('无效代理 或 IP查询失败，继续测试下一条IP\n')
else:
sql.update(ip_info[0], 'STATUS', '有效代理')
print('测试通过！\n')
return proxies
def row_update(self, id):
cell_st_index = (len(self.table.titles)) * id
cell_end_index = cell_st_index + len(self.table.titles)
if self.state:
self.state = 0 # disabled
values = [x.get() for x in self.table.vars[id]]
for cell in self.table.cells[cell_st_index: cell_end_index]:
cell.config(state=DISABLED)
sql.update(self.table.tablename, values, id)
image = self.get_image(os.path.join(self.img_dir, 'update.gif'))
self.btn_update.config(image=image)
self.btn_update.img = image
else:
self.state = 1 # enabled
image = self.get_image(os.path.join(self.img_dir, 'apply.gif'))
self.btn_update.config(image=image)
self.btn_update.img = image
for cell in self.table.cells[cell_st_index: cell_end_index]:
cell.config(state=NORMAL)
def set(self, key, value, paramno = 1):
field = 'val' + str(paramno)
d = self.__data
if key in d:
q = sql.update([field], 'param', filter = ['name = %s', 'id_prop = %s'])
db.execute(q, [value, key, self.idp], commit = True)
else:
q = sql.insert(['id_prop', 'name', field], 'param')
db.execute(q, [self.idp, key, value], commit = True)
d[key] = [None] * 4
d[key] = list(d[key])
d[key][paramno - 1] = value
def f():
f = ['CASE']
curdef = getcurdef(site.config)[5:]
rate = float(site.config('CURRENCY', 2))
field = 'IFNULL(price_out, price * %.5f)' % rate
for key in site.config.getlist('CURR_'):
currency = key[5:]
rate = float(site.config(key))
f.append('WHEN currency = "%s" THEN %s * %.5f' % (currency, field, rate))
f.append('END')
f = "\n".join(f)
q = sql.update('price', 'product') % f
db.execute(q)
f = ['CASE']
f.append('WHEN price BETWEEN 200 AND 900 THEN FLOOR((price + 4.99999) / 5) * 5')
f.append('WHEN price > 900 THEN ROUND(price + 5, -1)')
f.append('ELSE price')
f.append('END')
f = "\n".join(f)
q = sql.update('price', 'product') % f
db.execute(q)
def consumer(change_id, title):
# make query to wikipedia by title
queryRequestUrlForPage = "http://en.wikipedia.org/w/api.php?action=query&format=json&titles=" + urllib2.quote(title) + "&prop=redirects|revisions"
queryResponseForPage = json.load(urllib2.urlopen(queryRequestUrlForPage))
d = {}
d['page'] = queryResponseForPage['query']['pages'][queryResponseForPage['query']['pages'].keys()[0]]
# revision of a certain page may be missing
# ignore case with missing revisions
if not 'revisions' in d['page']:
pass
else:
revision_id = d['page']['revisions'][0]['revid']
# reference for parameters: http://www.mediawiki.org/wiki/API:Revisions
queryRequestUrlForRevision = "http://en.wikipedia.org/w/api.php?action=query&format=json&prop=revisions&rvprop=content|flags|ids|comment|timestamp|sha1&revids=" + str(revision_id)
queryResponseForRevision = json.load(urllib2.urlopen(queryRequestUrlForRevision))
d['revision'] = queryResponseForRevision['query']['pages'][queryResponseForRevision['query']['pages'].keys()[0]]['revisions'][0]
# update DB
update(d)
page_id = d['page']['pageid']
updatePagelinks(page_id, title)
# delete row from waiting list
try:
cnx = connection.MySQLConnection(user='******', password='', host='localhost', database='wikidb')
cursor = cnx.cursor()
sqlDelete = "DELETE FROM ChangeTable WHERE change_id = %s"
cursor.execute(sqlDelete, (str(change_id), ))
cnx.commit()
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
cnx.close()
def get_proxies():
curTime = TIME(UnixTime=TIME().NowUnix) # 获得当前时间
sql.start()
if sql.insert(ip='000.000.0.000',
port='8888',
addr='爬取时间',
time=curTime.normal()):
fill_proxypool() # 第一次爬取代理网站
else:
time_info = sql.get(ip='000.000.0.000')[6]
lastTime = TIME(NormalTime=time_info)
diffTime = (curTime.unix() - lastTime.unix()) / 60 # 相差多少分钟
# 距上次爬取超过30分钟，则更新代理池
if diffTime > 30:
print('代理池需要更新，请稍后...')
fill_proxypool()
sql.update('000.000.0.000', 'TIME', curTime.normal())
else:
print('代理池无需更新')
proxy = select_proxy()
sql.close()
return proxy
def __process_obj(self, frame_cnt, object_cnt):
print('processing object...')
camera_id = 0
frame_id = frame_cnt
object_id = object_cnt
object_class = random.randint(0,
2) #0:VEHICLE,1:PEDESTRIAN,2:MOTORCYCLE
object_type = 'bus'
object_colr = 'green'
vehicle_license = 'B12345'
person_name = 'jack'
person_gender = random.randint(0, 1) #0:MALE,1:FEMALE
person_age = random.randint(5, 70)
left = random.randint(0, 1000)
top = random.randint(0, 1000)
width = random.randint(20, 200)
height = random.randint(20, 200)
create_time = int(time.time())
status = random.randint(0, 3)
query="INSERT INTO `t_objdet_camera0_his`"\
"(`camera_id`, `frame_id`, `object_id`, "\
"`object_class`, `object_type`, `object_colr`, "\
"`vehicle_license`,"\
"`person_name`, `person_gender`, `person_age`, "\
"`left`, `top`, `width`, `height`, "\
"`create_time`, `status`) "\
"VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
params = [camera_id, frame_id, object_id,\
object_class, object_type, object_colr,\
vehicle_license,\
person_name, person_gender, person_age,\
left,top,width,height,\
create_time, status]
update(query, tuple(params))
time.sleep(0.01)
print('object processed')
def action_edit(self):
"""
edit and save data to database
:return:
"""
if self.edit_status:
# make row editable
self.edit_status = False
self.btn_undo.setDisabled(False)
self.set_bgr_image(self.btn_edit, 'apply.jpg')
self.parent.set_row_editable(self.tpos)
self.prev_data = self.parent.get_row_values(self.tpos)
else:
# saving to database, make row disabled
self.edit_status = True
self.btn_undo.setDisabled(True)
self.set_bgr_image(self.btn_edit, 'edit.png')
self.parent.set_row_disabled(self.tpos)
self.row_data = self.parent.get_row_values(self.tpos)
if not sql.update(self.parent.table, self.row_data,
self.prev_data[0]):
self.parent.restore_row(self.tpos, self.prev_data)
def update_process(user):
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 查询字段
value = {}
value["phone_number"] = user.phone_number
value["password"] = sha256_crypt.hash(user.password)
# 更新条目
condition = {}
condition["name"] = user.name
# 生成SQL语句
query = sql.update("users", value, condition)
try:
# 使用execute方法执行SQL语句
if cursor.execute(query):
db.commit()
return "Successfully update"
else:
return "Update failed"
except:
return "Error while update"
except:
return "Unable to connect to DB"
def main(argv):
argparser = argparse.ArgumentParser(description='')
argparser.add_argument('files', nargs='+')
args = argparser.parse_args(argv)
for fn in args.files:
if isdir(fn): continue
# read in file
print 'opening %s' % fn
f = open(fn, 'r')
lines_raw = f.readlines()
raw_id = sql.fetch_all('raws', {'filename': basename(fn)})[0]['id']
progress("Found %d of %s" % (raw_id, fn))
# preprocess
lines = []
try:
line_nr = 0
for line in lines_raw:
line_nr += 1
line = line.rstrip('\r\n')
line = re.split(r'\t|;', line)
line = preprocess_line(line)
lines.append(line)
except:
print "%d: %s" % (line_nr, line)
raise
# save!
line_nr = 0
try:
for line in lines:
line_nr += 1
phenotype = format_line(line) # create a readable program
# add the actual phenotype
phenotype_id = None
phenotype_q_params = dict(phenotype.items() + {
'entity_id': -12345,
'filename': basename(fn)
}.items())
del phenotype_q_params['attribute']
del phenotype_q_params['value']
phenotype_q_params['pp.plant_id'] = phenotype_q_params.pop(
'sample_id')
q = """
select phenotypes.id from phenotypes
join phenotype_raws pr on pr.phenotype_id = phenotypes.id
join raws r on r.id = pr.raw_id
left join phenotype_plants pp on pp.phenotype_id = phenotypes.id
where """
q += ' and '.join(
['%s=%s' % (k, '%s') for k in phenotype_q_params.keys()])
sql_phenotype = sql.fetch_all(None, phenotype_q_params, q)
if len(sql_phenotype) == 1:
if sql.update('phenotypes', {'id': sql_phenotype[0]['id']},
{'entity_id': phenotype['entity_id']}):
phenotype_id = sql.lastrowid()
progress('Added %d to phenotype' % phenotype_id)
except:
progress("%d: %s" % (line_nr, line))
raise
sql.commit()
#!/usr/bin/python
import sql
from config import DATABASE
db = sql.SqlDatabase(DATABASE)
with db.cursor() as sql:
for enum_id in sql.selectAllSingle(
"SELECT enum_id FROM enum WHERE field != 'role' AND field != 'user'"
):
enum = sql.selectAllDict(
"SELECT * FROM enum_entry WHERE enum_id=:enum_id", enum_id=enum_id)
enum.sort(key=lambda x: x['label'])
order = 0
for value in enum:
sql.update(
"UPDATE enum_entry SET `order`=:order WHERE entry_id=:entry_id",
entry_id=value['entry_id'],
order=order)
order += 1
def handle(request):
info = []
#
# NEW JOB REQUESTED
#
if request[0] == 'n':
print "Got a request for a new job"
data = sql.fetch("SELECT * from openjobs WHERE jobtype > -1 AND tid = (SELECT max(tid));")[0]
# Build the query to move job to pending table
pending_q = ("INSERT INTO pendingjobs(tid, jobtype, jobdesc, jobdata) VALUES (%s, %s, '%s', '%s');" %
(data[0], data[1], data[2], data[3]))
# Build info with delimeters for transit
for el in data:
el = str(el)
info.append(el)
info.append("^")
info.pop(-1) # Remove the last item, as its a spare delimeter
# Move job to pending table
print "> Moving job with table ID [" + str(info[0]) + "] to the pending jobs table"
res = sql.update(pending_q)
if res == -1:
print ">> There was an error moving job to the pending table! Changes reverted"
elif res == 1:
print ">> Job moved to pending table"
# Remove job from open table
print "> Removing job with table ID [" + str(info[0]) + "] from open jobs";
res1 = sql.update("delete from openjobs where tid = %s;" % str(info[0]))
if res1 == -1:
print ">> There was an error removing the job from the open table! Changes reverted"
elif res == 1:
print ">> Job removed from open table"
#
# UPDATE TO JOB REQUESTED
#
if request[0] == 'u':
# EXPECTING : u, tid, type, desc, data
# If sorting : data = item,item,item;ORIGINAL_DESC;ORIGINAL_DATA
# Insert data into closed
print "Got a request to update job"
print "> Moving job with table ID [" + str(request[1]) + "] to the closed table"
sql.update("INSERT INTO closedjobs(tid, jobtype, jobdesc, jobdata) VALUES (%s, %s, '%s', '%s');" %
(request[1], request[2], request[3], request[4]))
# Remove item from pending
print "> Removing job with table ID [" + str(request[1]) + "] from the pending jobs";
res1 = sql.update("delete from pendingjobs where tid = %s;" % str(request[1]))
if res1 == -1:
print ">> There was an error removing the job from the pending table! Changes reverted"
elif res1 == 1:
print ">> Job removed from pending table"
# Thank the client
info.append("skynet thanks you for your service")
#
# REPORT REQUESTED
#
if request[0] == 'r':
info.append("REQ FOR REPORT")
print "Got a request for a report"
#
# JOIN AND SEND DATA
#
reply = ''.join(info).replace('\t', ' ')
reply += '\t'
return reply
def update(self, data, **kw):
q = {}
q.update(kw)
q['_where'] = self.where(q)
return self.db.query(sql.update(self.table, data, **q))
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 = ["", "", "", "", "", "", "", "", ""]
row = list(row)
for i in range(8):
if row[i] == None:
async def update_sr(battle_tag):
"""Does something like this already exist???"""
sr = str(await owh.get_sr(battle_tag))
log.info("Updating {} to SR: {}".format(battle_tag, sr))
uquery = sql.update(overwatch_table, {"SR":sr}, condition={"BattleTag":battle_tag})
c.execute(uquery)
def acao_update(tabela, colunas):
bold_underline = '\033[1m \033[4m'
end_bold_underline = '\033[0m'
id_tabela = 'id_' + tabela
# da um select e printa todos os dados da tabela, para o usuário ter a referencia do código da linha que irá dar o update
print(
f'\n{bold_underline}A tabela que você deseja dar update possui os seguintes dados: {end_bold_underline}'
)
colunas_query = colunas_all(tabela)
query = f'SELECT {colunas_query} FROM {tabela}'
colunas_vetor = colunas_query.split(', ')
df_all = select(query, colunas_vetor)
if not df_all.empty:
print(df_all.to_string(index=False))
else:
print('Essa tabela está vazia, não é possível fazer update!\n')
input('Pressione enter para continuar\n')
return 'Tabelas'
# se a tabela for da beirada, irá pedir o código da chave primária. Se for tabela do meio, irá pedir as chaves que são estrangeiras E primárias.
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 dar update: '
))
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 dar update: '
))
condition.append(value)
# verifica se a tabela que deve ser feito o update possui chave estrangeira. Se possuir, irá verificar se a tabela está vazia. Se estiver vazia, não será possível realizar o update. Se não estiver vazia, irá printar os códigos de referência.
tabela_estrangeira(colunas_vetor, tabela, id_tabela, 'o update!')
# faz o input dos valores
colunas_selecionadas_vetor = colunas.split(', ')
print('Insira os valores atualizados: ')
values = []
for col in colunas_selecionadas_vetor:
if col == id_tabela:
# em colunas da beirada, não é possível alterar o identificador
print(
f'{col.upper()}: {bold_underline} Você não possui permissão para alterar o identificador{end_bold_underline}'
)
else:
value = input(f'{col.upper()}: ')
values.append(value)
# monta os valores da query
count = 0
query = f'UPDATE {tabela} SET '
for col in colunas_selecionadas_vetor:
if col == id_tabela:
continue
else:
query = query + f'{col} = \'{values[count]}\', '
count += 1
# monta o where da query
query = query[:-2]
count = 0
if id_tabela in colunas_vetor:
query = query + f' WHERE id_{tabela} = {condition[count]}'
else:
query = query + 'WHERE '
for item in colunas_vetor:
query = query + f'{item} = {condition[count]} AND '
count += 1
query = query[:-5]
# tenta fazer o update
try:
update(query)
print('Update realizado com sucesso!')
verif = str(
input(
f'\n\n Deseja dar update em outro registro desta mesma tabela? [Y/N] '
))
if (verif == 'Y' or verif == 'y'):
acao_update(tabela, colunas)
else:
input('\nPressione enter para continuar\n')
return 'Tabelas'
# se não conseguir, trata os erros
except psycopg2.Error as e:
if e.pgcode == '22007':
print(
f'\nVocê inseriu um campo de data com o formato errado. Insira novamente no formato {bold_underline}dd/mm/aaaa{end_bold_underline}.'
)
input('\nPressione enter para continuar\n')
acao_update(tabela, colunas)
elif 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_update(tabela, colunas)
elif e.pgcode == '22001':
print(
f'\nVocê inseriu caracteres demais em um campo. Insira novamente.'
)
input('\nPressione enter para continuar\n')
acao_update(tabela, colunas)
else:
print('Ocorreu algum erro! Retornando para o menu de tabela!\n')
input('\nPressione enter para continuar\n')
return 'Tabelas'
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
print '<tr>'
for key in keys:
print '<th>{}</th>'.format(key)
print '<th>Update</th><th>Delete</th>'
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)
if fields['ViewerTypeOld'] != 'P' or fields['ViewerTypeOld'] != 'B':
message = sql.update(db, cursor, 'PremiumViewer', fields, keys_p, ['ViewerID'], ignore=ignore_update)
######## If DELETE button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Delete":
message = sql.delete(db, cursor, table, fields, ['ViewerID'])
####### GENERATE AND EXECUTE SEARCH QUERY ##########################################################################
# Search Viewer from Viewers using ViewerID and ignoring other fields
fields = dict.fromkeys(keys + address_keys + ['StartDateOld'])
message = "";
for key in fields:
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 = ["", "", "", "", "", "", "", "", "", "", "", "", ""]
monitor = True
else:
monitor = False
while True:
val = str(ser.readline().decode().strip('\r\n'))
valA = val.split("/")
#upd(valA)
if (monitor == False):
#print(val, end="\r", flush=True)
#data.append(print(val,end="\r",flush=True))
for v in valA:
sleep(3)
if len(v) > 0:
x = update(v)
print(x)
#print(v)
#Data.append(v)
#i+=1
#print(i)
#print(v)
#print(i)
#if(i == 2):
#s=""
#for i in range(Data):
# s+=str(i)
#print(s)
#break
#break
#print(Data)
