def insert_record(self): sql = '''insert into livros ( pu_author_id, pu_cota , pu_type, pu_isbn , pu_obs, pu_sinopse, pu_status, pu_title, pu_volume,pu_ed_year, pu_sub_title) VALUES ((select au_id from authors where lower(au_name)=%s),%s,(select ty_id from types where lower(ty_name)=%s) ,%s,%s,%s,(select st_id from status where lower(st_nome)=%s),%s,%s,%s,%s)''' data = ( stdio.authors_process(self.pu_author_id.currentText()).lower(), self.pu_cota.text().upper(), ) data += ( self.pu_type.currentText().lower(), self.pu_isbn.text().strip(), ) data += (self.pu_obs.toPlainText(), ) data += (self.pu_sinopse.toPlainText(), ) data += (self.pu_status.currentText().lower(), ) data += (self.pu_title.text().strip(), ) data += (self.pu_volume.text(), write_record(self.pu_ed_year), self.pu_sub_title.text()) dbmain.execute_query(sql, data) ''' campos especiais ''' self.pub_id = dbmain.query_one('select max(pu_id) from livros', (True, ))[0] gl.last_id = self.pub_id ''' actualiza obs''' self.update_tags() gl.TYPE = self.pu_type.currentText() gl.STATUS = self.pu_status.currentText()
def search_data_in_table(table, field, data_to_search): sql = 'SELECT * from ' + table + ' where ' + 'lower(' + field +') = \'' + data_to_search.lower() + '\'' print(sql) if dbmain.query_one(sql, (True,)): print('existe') else: print('nao existe')
def update_tags(pub_id,tag_list): # id = livro tags_id = [] tag_max = dbmain.query_one('''Select max(ta_id)+1 as t from tags''', (True,))[0] if tag_max == None: tag_max = 1 for n in tag_list: toto = n.lower().strip() if toto != '': a = dbmain.query_one('select ta_id from tags where ta_name = %s', (toto,)) if a == None: # é nova dbmain.execute_query('insert into tags (ta_name) values(%s)', (toto, )) tags_id.append((pub_id,tag_max)) tag_max +=1 else: tags_id.append((pub_id,a[0])) sql = ''' INSERT INTO tags_reference(tags_ref_book, tags_ref_tag_id) VALUES''' + str(tags_id)[1:-1] dbmain.execute_query(sql, (True, ))
def update_special_tags(pub_id,level=1): dbmain.execute_query('DELETE FROM tags_reference ' 'WHERE tags_ref_book = %s and tags_ref_key in (select tags_special_key FROM tags_special WHERE tags_special_level=%s) ' , (pub_id,level)) tags_id = [] tag_max = dbmain.query_one('''SELECT MAX(ta_id)+1 AS t FROM tags''', (True,))[0] if tag_max is None: tag_max = 1 for n in gl.tags_special_level1_data: a = dbmain.query_one('SELECT ta_id FROM tags where lower(ta_name) = %s and tag_key = %s', (n[1].lower(),n[0])) if a == None: # é nova dbmain.execute_query('INSERT INTO tags (ta_name, tag_key) values(%s,%s)', (n[1],n[0])) tags_id.append((pub_id,tag_max, n[0],level)) tag_max +=1 else: tags_id.append((pub_id,a[0], n[0],level)) sql = ''' INSERT INTO tags_reference(tags_ref_book, tags_ref_tag_id, tags_ref_key, tags_ref_level) VALUES''' + str(tags_id)[1:-1] if not tags_id == []: dbmain.execute_query(sql, (True, ))
def attachment_save(pu_id, file_path): if file_path is not None: sql = '''select edoc_id, edoc_data, edoc_pub_id, edoc_size, edoc_file_name, edoc_file_format from edoc_data where edoc_pub_id = %s''' try: hl = dbmain.query_one(sql, (pu_id,)) a = base64.b64decode(hl[1]) print(hl[3]/1024,hl[4],hl[5]) print('-----------------------') f = open(file_path + hl[4] + '.' + hl[5], 'wb') f.write(a) except Exception as e: print(str(e))
def info_click(self): dataset = [] dataset.append(('Livros', str( dbmain.query_one('select count(pu_id) from livros', (True, ))[0]))) dataset.append( ('Autores', str( dbmain.query_one('select count(au_id) from authors', (True, ))[0]))) dataset.append(('Por categoria', '')) dataset = dataset + (dbmain.query_many( '''select types.ty_name, to_char(count(*),'999999999') as a from livros,types where types.ty_id = pu_name group by pu_name,types.ty_name order by ty_name asc''')) self.grid.setRowCount(len(dataset)) ex_grid.ex_grid_update(self.grid, { 0: ['Tabela', 's'], 1: ['Total', 'sr'] }, dataset)
def check_ISBN(self): """ impede/informa quando há um ISBN duplicado """ isbn = str(self.pu_isbn.text()) if isbn != '': a = dbmain.query_one( 'select pu_isbn from livros where pu_isbn =%s', (isbn, )) if a != None: if self.askForNew( "ISBN duplicado.", "Já existe outra publicação com este ISBN\nAdiciono mais esta? ", isbn): pass else: self.error_list.append('O ISBN está duplicado')
def edit_tags_click(self): dum = self.tags.toPlainText().rstrip(',') tags_txt = dum.split(',') tags_data = [] for n in tags_txt: toto = n.lower().strip() if toto != '': a = dbmain.query_one( 'select ta_id from tags where ta_name = %s', (toto, )) tags_data.append((a, toto)) form = tag_browser.EditRecordTags(tags_data) form.exec_() if form.flag: if form.tag_list == '': pass else: self.tags.setHtml('<font color="blue"><strong>' + form.tag_list)
def change_locals_click(self): # check souce local exists sql = '''select count(pu_cota) from livros where pu_cota = %s''' a = dbmain.query_one(sql, (self.fromEdt.text().upper(), )) flag = True if not a[0] > 0: b = QMessageBox.question( self, self.tr("Cota inistente"), self.tr("""A Cota de origem não existe! Continuar?"""), QMessageBox.StandardButtons(QMessageBox.No | QMessageBox.Yes), QMessageBox.No) if b == QMessageBox.No: flag == False if flag: sql = '''UPDATE livros set pu_cota=%s where pu_cota=%s''' dbmain.execute_query( sql, (self.toEdt.text().upper(), self.fromEdt.text().upper())) self.grid_refresh()
def load_parameters(): a = dbmain.query_one('select param_json from params where param_key=%s', ('FORM',))[0] gl.forms = json.loads(a)