def init_db(path): init = not os.path.isfile(path) if init: sqlite3.connect(path).close() database = QSqlDatabase("QSQLITE") database.setDatabaseName(path) database.open() def qt_exec(sql, *args, **kwargs): statement = sql.compile(dialect=mock.dialect) # print(statement) database.exec(str(statement)) mock = create_engine('sqlite://', strategy='mock', executor=qt_exec) if init: Base.metadata.create_all(bind=mock) assert database.driver().hasFeature(QSqlDriver.EventNotifications) for tablename in Base.metadata.tables.keys(): database.driver().subscribeToNotification(tablename) def call_callbacks(name: str): print("db changed") cb = Base.metadata.tables[name].onTableChanged if cb: cb() database.driver().notification.connect(call_callbacks) return database, mock
class DBManager(QObject): ''' Manages a connection and initialising tables of an SQLite database ''' def __init__(self, DatabaseName: str): ''' Constructor: Initialises a connection to the database ''' super.__init__(self) self._schema = {} self._db = QSqlDatabase(QSqlDriver.SQLite) self._db.setDatabaseName(DatabaseName) if not self._db.open(): qCritical("Failed to establish connection with database " + DatabaseName) return self._initSchema() def _initSchema(self): ''' Initialises the table schema of the database ''' for tablename in self._db.tables(): record = self._db.record(tablename) self._schema[tablename] = record
class DataBase(QObject): def __open(self): self.__db = QSqlDatabase().addDatabase('QSQLITE') self.__db.setDatabaseName(DATABASE_NAME) return self.__db.open() def __restore(self): if not self.__open(): return False queries = open(SCHEMA).read().split(';') for query in queries: query = query.strip() if query: sql = QSqlQuery(self.__db) if not sql.exec(query): QFile(DATABASE_NAME).remove() return False return True def connect(self): return self.__open() if QFile( DATABASE_NAME).exists() else self.__restore()
def carregarTable(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): query = QSqlQueryModel(self) query.setQuery( "SELECT" " livros_id, titulo, editoras.editora," " isbn, qtde_estoque, vl_unitario, consignado" " FROM livros" " INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id" ) model = QSqlTableModel(self, db) model.setQuery(query.query()) model.select() self.ui.tableView.setModel(model) self.ui.tableView.show() # carregar combobox de editoras query2 = QSqlQueryModel(self) query2.setQuery("select(editora) from editoras") model2 = QSqlTableModel(self, db) model2.setQuery(query2.query()) model2.select() self.ui.cbEditora.setModel(model2) db.close()
def MyCustomConnection(): """ Connection to SQLite databse""" db = QSqlDatabase().addDatabase("QSQLITE") db.setDatabaseName("db\database.db") if db.open(): print("Connection Opened...") else: sys.exit(-1234)
def carregarTable(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') if db.open(): model = QSqlTableModel(self, db) model.setTable("autores") model.select() self.ui.tableView.setModel(model) self.ui.tableView.show() db.close() QSqlDatabase().removeDatabase('Litterarius.db')
def carregarTable(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): model = QSqlTableModel(self, db) model.setTable("generos") model.select() self.ui.tableView.setModel(model) self.ui.tableView.show() db.close()
class DBConnection: """Utility class. Provides functionality for opening and closing db connections for clean resource management.""" def __init__(self): self._db = QSqlDatabase().addDatabase("QSQLITE") self._db.setDatabaseName("records.db") def open_db(self): if not self._db.isOpen(): self._db.open() else: print("Feck off, the db is already open") def close_db(self): if self._db.isOpen(): self._db.close() else: print("Feck off, the db is already closed") def db(self): return self._db
def carregarComboBox(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): query = QSqlQueryModel(self) query.setQuery("SELECT" " fornecedor" " FROM fornecedores") model = QSqlTableModel(self, db) model.setQuery(query.query()) model.select() self.ui.cbxFornecedores.setModel(model) self.ui.cbxFornecedores.show() db.close()
def carregarComboBox(self): db = QSqlDatabase ().addDatabase ('QSQLITE') db.setDatabaseName ('Litterarius.db') if db.open (): query = QSqlQueryModel (self) query.setQuery ("SELECT parcelas_id FROM recebimentos WHERE vendas_id = %s" % (self.ui.txtVendaId.text())) model = QSqlTableModel (self, db) model.setQuery (query.query ()) model.select () self.ui.cbParcela.setModel (model) self.ui.cbParcela.show () db.close () QSqlDatabase ().removeDatabase ('Litterarius.db')
def carregarListView(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') if db.open(): self.model = QSqlTableModel(self, db) # self.model.setTable ("autores") # self.model.select () query = QSqlQueryModel(self) query.setQuery("select(autor) from autores") self.model.setQuery(query.query()) self.ui.lvAutor.setModel(self.model) self.ui.lvAutor.show() db.close()
def carregarTable(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): model = QSqlTableModel(self, db) model2 = QSqlTableModel(self, db) model.setTable("fornecedores") model.select() self.ui.tableView.setModel(model) self.ui.tableView.show() model2.setTable("transportadoras") model2.select() db.close() QSqlDatabase().removeDatabase('Litterarius.db')
def carregarTable(self): db = QSqlDatabase ().addDatabase ('QSQLITE') db.setDatabaseName ('Litterarius.db') if db.open (): query = QSqlQueryModel (self) query.setQuery ("SELECT vendas_id as venda, dataVenda as data," " precoVenda as preco" " FROM vendas") model = QSqlTableModel (self, db) model.setQuery (query.query ()) model.select () self.ui.tableView.setModel (model) self.ui.tableView.show () db.close () QSqlDatabase ().removeDatabase ('Litterarius.db')
def carregarListView(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') if db.open(): self.model = QSqlTableModel(self, db) # self.model.setTable ("autores") # self.model.select () query = QSqlQueryModel(self) query.setQuery("select(genero) from generos") self.model.setQuery(query.query()) self.ui.lvGenero.setModel(self.model) self.ui.lvGenero.show() self.ui.btnIncluir.clicked.connect(self.clickedIncluir) db.close()
def carregarValorParcela(self): db = QSqlDatabase ().addDatabase ('QSQLITE') db.setDatabaseName ('Litterarius.db') if db.open (): query = QSqlQuery () valores = query.exec ("SELECT vl_parcela, pago FROM recebimentos" " WHERE parcelas_id = %s" % (self.ui.cbParcela.currentText())) while query.next(): print(query.value('vl_parcela')) query.first() self.ui.txtVlrParcela.setText (str (query.value('vl_parcela'))) # self.ui.txtVlrParcela.setText (model.data[0]) # if model.data[1] == 1: # self.ui.cbxPago.setEnabled(True) # else: # self.ui.cbxPago.setEnabled(False) db.close () QSqlDatabase ().removeDatabase ('Litterarius.db')
def carregarTable(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): query = QSqlQueryModel(self) query.setQuery( "SELECT" " titulo, editoras.editora," " qtde_estoque, vl_unitario, consignado" " FROM livros" " INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id" ) model = QSqlTableModel(self, db) model.setQuery(query.query()) model.select() self.ui.tvLivros.setModel(model) self.ui.tvLivros.show() db.close()
def carregarTableByGenero(self): db = QSqlDatabase().addDatabase('QSQLITE') db.setDatabaseName('Litterarius.db') conexao = db.connectionName() if db.open(): query = QSqlQueryModel(self) query.setQuery( "SELECT" " titulo, editoras.editora," " qtde_estoque, vl_unitario, consignado" " FROM livros" " INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id" " WHERE livros_id =(" " SELECT livros_id FROM livros_generos WHERE generos_id=(" " SELECT generos_id FROM generos WHERE genero LIKE '%s'))" % (self.ui.txtPesquisar.text() + "%")) model = QSqlTableModel(self, db) model.setQuery(query.query()) model.select() self.ui.tvLivros.setModel(model) self.ui.tvLivros.show() db.close()
class DatabaseLogLite(object): """ Low load only; using SQLite To store bookmarks, configuration, etc. AB01 CFG02 """ def __init__(self): # ###### STARTUP super(DatabaseLogLite, self).__init__() self.litedb = QSqlDatabase("QSQLITE") db_file = expanduser("~/.eilat/eilat.db") rebuild = not isfile(db_file) self.litedb.setDatabaseName(db_file) self.litedb.open() if rebuild: query_mknav = ( "CREATE TABLE navigation (host TEXT NOT NULL," + " path TEXT, count INTEGER default 0, prefix char(2)," + " PRIMARY KEY (host, path))") self.litedb.exec_(query_mknav) # ###### VALIDATION # verifies database structure, not datatypes tables = self.litedb.tables() tables_ok = [k in tables for k in ['navigation']] if not all(tables_ok): raise RuntimeError("tables missing from database") fnav_ok = [ self.litedb.record('navigation').contains(k) for k in ['host', 'path', 'count', 'prefix'] ] if not all(fnav_ok): raise RuntimeError("bad structure for 'navigation' table") def model(self, prefix=None): """ recreate the model each call; opening a new window will not be needed to use the recent completions """ if prefix is None: query_nav = QSqlQuery( "select host || path from navigation " + "order by count desc", self.litedb) else: # CFG02 query_nav = QSqlQuery( "select host || path from navigation " + "where prefix = '{}' ".format(prefix) + "order by count desc", self.litedb) ret_model = QSqlQueryModel() ret_model.setQuery(query_nav) # AB01 return ret_model def store_navigation(self, host, path, prefix): """ save host, path and increase its count AB01 """ host = host.replace("'", "%27") path = path.replace("'", "%27") insert_or_ignore = ( "insert or ignore into navigation (host, path, prefix) " + "values ('{}', '{}', '{}')".format(host, path, prefix)) update = ("update navigation set count = count + 1 where " + "host = '{}' and path = '{}'".format(host, path)) self.litedb.exec_(insert_or_ignore) self.litedb.exec_(update)
import sys from PyQt5.QtCore import * from PyQt5.QtWidgets import * from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel from PyQt5.QtWidgets import QApplication, QMainWindow, QTableView db = QSqlDatabase("QSQLITE") db.setDatabaseName("chinook.sqlite") if db.open(): print("Connect to {} database sucessfully!".format(db.databaseName())) class MainWindow(QMainWindow): def __init__(self, *args, **kwargs): super(MainWindow, self).__init__(*args, **kwargs) self.setWindowTitle("View Table with SQL") container = QWidget() layout_search = QHBoxLayout() self.track = QLineEdit() self.track.setPlaceholderText("Track name...") self.track.textChanged.connect(self.update_query) self.composer = QLineEdit() self.composer.setPlaceholderText("Artist name...") self.composer.textChanged.connect(self.update_query)
class MainDatabase(QObject): ignore = pyqtSignal() def __init__(self, parent=None): super(MainDatabase, self).__init__(parent) self._database = QSqlDatabase('QSQLITE') self._databaseName = '' self._databaseQuery = '' self._model = '' self._databaseQueryResult = [] @pyqtProperty(str) def databaseName(self): return self._databaseName @databaseName.setter def databaseName(self, databaseName): self._databaseName = databaseName @pyqtProperty(str) def databaseQuery(self): return self._databaseQuery @databaseQuery.setter def databaseQuery(self, databaseQuery): self._databaseQuery = databaseQuery @pyqtProperty(QAbstractListModel, notify=ignore) def problemTypesTableModel(self): return self._model @pyqtSlot(str, str, str) @pyqtSlot(str, str) def prepareTableModel(self, columns, tableName, suffix=''): if (not columns) or (not tableName): return columns = columns.split('|') self.runQuery(columns, tableName) self._model = queryResultModel(columns, suffix) self._model.setData(self._databaseQueryResult) def runQuery(self, columns, tableName, addToQuery=''): if (not self._databaseName == ''): self._databaseQuery = 'SELECT {columns} FROM {tableName} {addToQuery}'.format(columns = ', '.join(columns), tableName = tableName, addToQuery = addToQuery) self._database.setDatabaseName(getFullPath(self._databaseName)) if self._database.open(): query = QSqlQuery(self._database) query.exec_(self._databaseQuery) self._databaseQueryResult = [] while query.next(): item = {} for i in range(len(columns)): item[columns[i]] = query.value(i) self._databaseQueryResult.append(item) @pyqtSlot(str) def prepareProblemTypeCommands(self, problemID): self.runQuery(['ReportCommands'], 'ProblemTypes', 'WHERE ID LIKE "{}"'.format(problemID)) @pyqtProperty(QVariant) def getProblemTypeCommands(self): return QVariant(parseCommandsReturnedFromDatabase(self._databaseQueryResult[0]['ReportCommands']))
from PyQt5.QtSql import QSqlDatabase, QSqlQuery from PyQt5.QtCore import * from PyQt5.QtWidgets import QApplication if __name__=='__main__': import sys app=QApplication(sys.argv) db=QSqlDatabase("QMYSQL") db.setHostName('57af503e35be5.gz.cdb.myqcloud.com') db.setDatabaseName('gstar') db.setUserName('root') db.setPassword('root123456*') db.setPort(4402) if not db.open(): print('open error') else: print ('open success!') print ('db is open', db.isOpen()) print ("db's driver name is", db.driverName()) print ("db's tables", db.tables()) query=QSqlQuery(db) query.prepare(u"SELECT * FROM material") s=query.exec_() while query.next(): print (query.value(0)) print (s)
class DB: def __init__(self): self.__db = QSqlDatabase('QODBC3') self.__db.setDatabaseName('DRIVER={SQL Server};SERVER=localhost;DATABASE=SerialTracker;Port=1433') self.__id = 0 self.__only_watching = False if not self.__db.open(): QMessageBox.critical(self, 'Ошибка', 'Не удалось подключиться к базе данных') exit(-2) self.__query = QSqlQuery(self.__db) def authorise(self, login: str, password: str) -> bool: self.__query.exec(f"exec Authorise '{login}', '{get_hash(password)}'") self.__query.next() if self.__query.value(0): self.__id = self.__query.value(0) return self.__id > 0 def get_shows(self) -> List[Show]: shows = [] if not self.__only_watching: self.__query.exec('exec GetAllShows') else: self.__query.exec(f'exec GetWatchingShow {self.__id}') while self.__query.next(): shows.append(Show(show_id=self.__query.value(0), name=self.__query.value(1), year=self.__query.value(2), timing=self.__query.value(3), description=self.__query.value(4), score=self.__query.value(5))) for show in shows: show.my_score = self.get_score(show) return shows def get_episodes(self, show_id: int) -> List[Episode]: episodes = [] self.__query.exec(f'exec GetEpisodes {show_id}') while self.__query.next(): episodes.append(Episode( episode_id=self.__query.value(0), season=self.__query.value(1), series=self.__query.value(2), score=self.__query.value(3), name=self.__query.value(4), description=self.__query.value(5) )) for episode in episodes: episode.my_score = self.get_score(episode) return episodes def set_only_watching(self, status: bool): self.__only_watching = status def episode_score_update(self, data: Episode, value: int): if value is None: self.__query.exec(f'exec ReviewEpisode {self.__id}, {data.id}') else: self.__query.exec(f'exec ReviewEpisode {self.__id}, {data.id}, {value}') def show_score_update(self, data: Show, value: int): if value is None: self.__query.exec(f'exec ReviewShow {self.__id}, {data.show_id}') else: self.__query.exec(f'exec ReviewShow {self.__id}, {data.show_id}, {value}') def get_score(self, data): if type(data) is Episode: self.__query.exec(f'exec GetWatcherEpisodeScore {self.__id}, {data.id}') else: self.__query.exec(f'exec GetWatcherShowScore {self.__id}, {data.show_id}') self.__query.next() return self.__query.value(0) if self.__query.value(0) else None def mark_status_show(self, data: Show): self.__query.exec(f'exec MarkShow {self.__id}, {data.show_id}') def get_watching_show(self): watching_show_id = [] self.__query.exec(f'exec GetWatchingShow {self.__id}') while self.__query.next(): watching_show_id.append(self.__query.value(0)) return watching_show_id def get_watched_episode(self): watched_episode_id = [] self.__query.exec(f'exec GetWatchedEpisode {self.__id}') while self.__query.next(): watched_episode_id.append(self.__query.value(1)) return watched_episode_id def get_stat_model(self): model = QSqlTableModel(None, self.__db) model.setTable('Stat') model.select() return model def registration(self, login: str, password: str): self.__query.exec(f"exec Registration '{login}', '{get_hash(password)}'") self.__query.next() return self.__query.value(0) == 1 def mark_status_episode(self, data): self.__query.exec(f'exec MarkEpisode {self.__id}, {data.id}') def is_logged(self): return self.__id > 0
conexion.commit() conexion.close() def busqueda2(id): conexion = sqlite3.connect('puntoVenta.db') consulta = conexion.cursor() datos = ("", "", "", "") dato = (id, ) sql = """SELECT nombre, precio, cantidad, uniMedida FROM Refaccion WHERE (idRefaccion = ?)""" encontro = False try: consulta.execute(sql, dato) for i in consulta: datos = (i[0], i[1], i[2], i[3]) encontro = True if encontro == False: pass return datos except: pass conexion.close() app = QApplication(sys.argv) dba = QSqlDatabase("QSQLITE") dba.setDatabaseName("puntoVenta.db") dba.open() win = VentanaPrincipal() win.show() app.exec_()
class BeerTable(QTableView): def __init__(self, model, database, parent=None): super().__init__(parent) self._parent = parent or self self._model = model self.setModel(self._model) self.setAlternatingRowColors(True) self.setSortingEnabled(True) self.setMouseTracking(True) self.viewport().setAttribute(Qt.WA_Hover, True) self.horizontalHeader().setContextMenuPolicy(Qt.CustomContextMenu) self.horizontalHeader().customContextMenuRequested.connect(self.headerMenu) self._index = None self._pos = self.cursor().pos() self._timer = QTimer(self) self._timer.timeout.connect(self.updateTimer) self._timer.start(1000) self._label = PopupLable(self) self._database = database self._db = QSqlDatabase('QSQLITE') self._db.setDatabaseName(self._database) self._q = None self.lastError = None self.beers = OrderedDict() self.beerImages = OrderedDict() self.tableFields = ['Image', 'name', 'alcohol', 'quantity', 'producer', 'country', 'overview'] self.loadBeers() def keyPressEvent(self, event): if event.key() == Qt.Key_F5: self.resizeRowsToContents() self._top_rowid = self.indexAt(self.rect().topLeft()).row() self._bottom_rowid = self.indexAt(self.rect().bottomRight()).row() elif event.key() == Qt.Key_D and event.modifiers() & Qt.ControlModifier and self.currentIndex(): rowId = self.currentIndex().row() r = [self.model().item(rowId, colId).text() for colId in [1,2,4,5,6,8,13,14]] cb = QApplication.clipboard() cb.clear(mode=cb.Clipboard ) cb.setText(chr(9).join(r), mode=cb.Clipboard) elif (event.key() == Qt.Key_C or event.key() == Qt.Key_S) and event.modifiers() & Qt.ControlModifier and self.currentIndex(): try: rowId = self.currentIndex().row() beerId = int(self.model().item(rowId, 0).text()) if beerId in self.beerImages: status = [] if self.model().item(rowId, 6).text() != bytes([110, 111, 32, 115, 99, 111, 114, 101]).decode(): status.append(self.model().item(rowId, 6).text()) if self.model().item(rowId, 8).text() != bytes([110, 111, 32, 115, 99, 111, 114, 101]).decode(): status.append(self.model().item(rowId, 8).text()) if self.model().item(rowId, 8).text() == self.model().item(rowId, 6).text(): if self.model().item(rowId, 6).text() != bytes([110, 111, 32, 115, 99, 111, 114, 101]).decode(): status = [self.model().item(rowId, 8).text()] r = [self.model().item(rowId, 1).text(), self.model().item(rowId, 2).text(), chr(32).join([ bytes([65, 66, 86, 32, 123, 125, 32, 37]).decode().format( self.model().item(rowId, 4).text()), ]+status), chr(32).join([ self.model().item(rowId, 13).text(), chr(40), self.model().item(rowId, 5).text(), chr(41)]), ] pixmap = self._pixmap(open(self.beerImages[beerId][0], ReadWriteFile.READ_BINARY).read(), None) height = pixmap.size().height() width = pixmap.size().width() p = QPixmap(width, height + 12*len(r)) height = p.size().height() width = p.size().width() with QPainter(p) as painter: painter.drawPixmap(0, 12*len(r), pixmap) brush = QBrush(QColor(0, 0, 0, 255)) painter.fillRect(0, 0, p.size().width()-2, 12*len(r), brush) font = QFont() font.setFamily(bytes([65, 114, 105, 97, 108]).decode()) font.setBold(True) font.setPixelSize(10) painter.setFont( font ) pen = QPen() pen.setColor(QColor(255, 255, 0, 255)) painter.setPen(pen) for i, info in enumerate(r, 1): painter.drawText( QPoint(2, i*10), info ) if event.key() == Qt.Key_C: QApplication.clipboard().clear() QApplication.clipboard().setPixmap(p) else: fileName, _ = QFileDialog.getSaveFileName(self, bytes([83, 97, 118, 101, 32, 73, 109, 97, 103, 101]).decode(), str(beerId), bytes([73, 109, 97, 103, 101, 32, 40, 42, 46, 106, 112, 103, 41]).decode()) print(fileName) p.save(fileName, bytes([74, 80, 71]).decode()) except Exception as err: print(err) return QTableView.keyPressEvent(self, event) def updateTimer(self): if (self.isActiveWindow() and self._pos == self.cursor().pos() and (not self._label.isVisible())): index = self.indexAt(self._pos) if index.column() == 0: i = int(self.model().data(index, Qt.DisplayRole)) for pos in self.beerImages[i]: if pos == 1: pixmap = QPixmap() pixmap.loadFromData(self.beerImages[i][pos]) self._label.setPixmap(pixmap) self._label.move(self._pos) self._label.setVisible(True) break self._pos = self.cursor().pos() def Clear(self): self.model().clear() self.model().setColumnCount(len(self.tableFields)) self.model().setHorizontalHeaderLabels(self.tableFields) def dbIter(self, sql): if self._q is not None: if self._q.exec(sql): has_next = self._q.next get_record = self._q.record qrange = range(0) if has_next(): record = get_record() record_name = record.fieldName record_value = record.value qrange = range(record.count()) yield [record_name(i) for i in qrange] yield [record_value(i) for i in qrange] while has_next(): record = get_record() record_value = record.value yield [record_value(i) for i in qrange] else: self.lastError = self.__query.lastError().text() def normText(self, value): text = '' i = 0 for c in value: i += 1 if i > 90 and c == ' ': text += c + '\n' i = 0 else: text += c return text def _pixmap(self, dataBytes, h=200, w=150): pixmap = QPixmap() pixmap.loadFromData(dataBytes) if h is None or w is None: return pixmap height = pixmap.size().height() width = pixmap.size().width() if width == 0 or height == 0: return pixmap return pixmap.scaled(w, h, Qt.IgnoreAspectRatio, Qt.SmoothTransformation) def loadBeers(self): self.beers.clear() self.Clear() if self._db.open(): self._q = QSqlQuery(self._db) sqliter = self.dbIter('SELECT [id], [name], [source_page] FROM beers') _ = sqliter.__next__() for id_, name, page in sqliter: self.beers[id_] = [page, name] self.beerImages[id_] = OrderedDict() sqliter = self.dbIter('SELECT [id], [name], [alcohol], [quantity], [producer], [country], [overview] FROM beer_info') _ = sqliter.__next__() for id_, name, alcohol, quantity, producer, country, overview in sqliter: overview = self.normText(overview) self.beers[id_].extend([alcohol, quantity, producer, country, overview]) sqliter = self.dbIter('SELECT [id], [name], [index], [image] FROM beer_images') _ = sqliter.__next__() for id_, _, index, image in sqliter: b = base64.b64decode(image.encode()) self.beerImages[id_][index] = b for rowId, id_ in enumerate(self.beers): r = [id_] + self.beers[id_][1:] self.model().appendRow(list(map(BeerItem, r))) widget = QTextEdit() widget.setText(r[-1]) index = self.model().index(rowId, 6) self.setIndexWidget(index, widget) if self.beerImages[id_]: indexs = list(self.beerImages[id_]) b = self.beerImages[id_][indexs[0]] pixmap = self._pixmap(b) label = ImageLabel() label.setPixmap(pixmap) index = self.model().index(rowId, 0) self.setIndexWidget(index, label) self._db.close() self.resizeColumnsToContents() self.resizeRowsToContents() self._q = None def closeEvent(self, event): self.Clear() def mousePressEvent(self, event): self._pos = event.pos() if event.button() == Qt.RightButton: index = self.indexAt(self._pos) if index.column() == 0: i = int(self.model().data(index, Qt.DisplayRole)) for pos in self.beerImages[i]: if pos == 1: pixmap = QPixmap() pixmap.loadFromData(self.beerImages[i][pos]) self._label.setPixmap(pixmap) self._label.move(self._pos) self._label.setVisible(True) break return QTableView.mousePressEvent(self, event) def mouseMoveEvent(self, event): pos = event.pos() index = self.indexAt(pos) if self._index is None: self.enterIndex(index) elif self._index != index: self.leaveIndex(self._index) self.enterIndex(index) else: self.moveIndex(index) self._index = index self._pos = pos return QTableView.mouseMoveEvent(self, event) def image_mouseMoveEvent(self, event): pass def event(self, event): return QTableView.event(self, event) def enterIndex(self, index): self._label.setVisible(False) def leaveIndex(self, index): self._label.setVisible(False) def moveIndex(self, index): self._label.setVisible(False) def headerMenu(self, point): globalPos = self.mapToGlobal(point) column = self.horizontalHeader().visualIndexAt(point.x()) names = ["Contains", "Starts With", "Equals...", "Does not Equal...", "Greater Than", "Less Than", "Between", "Clear filter"] menu = QMenu() for name in names: menu.addAction(name) selectedItem = menu.exec_(globalPos) if not selectedItem: return if selectedItem.text() == names[7]: self.filterClear() else: value, status = QInputDialog.getText(self, "Custom filter", "Value:") if status: if selectedItem.text() == names[0]: self.filterColumnValueContains(column, value) elif selectedItem.text() == names[1]: self.filterColumnValueStartsWith(column, value) elif selectedItem.text() == names[2]: self.filterColumnValueEqual(column, value) elif selectedItem.text() == names[3]: self.filterColumnValueEqualNo(column, value) elif selectedItem.text() == names[4]: self.filterColumnValueGreaterThan(column, value) elif selectedItem.text() == names[5]: self.filterColumnValueLessThan(column, value) elif selectedItem.text() == names[6]: pass def isDigital(self, v): return v.isdigit() or ('.' in v and v.count('.') == 1) def toDigital(self, v): if v.isdigit(): return int(v) elif '.' in v and v.count('.') == 1: return round(float(v), 6) return v def filterClear(self): for i in range(self.model().rowCount()): if self.isRowHidden(i): self.setRowHidden(i, False) def filterColumnValueContains(self, column, value): if value == str(): self.filterClear() else: value = value.lower() for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole).lower() if v is None: self.setRowHidden(rowId, True) elif v.__contains__(value): self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) def filterColumnValueStartsWith(self, column, value): if value == str(): self.filterClear() else: for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif v.startswith(value): self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) def filterColumnValueEqual(self, column, value): if value == str(): self.filterClear() elif self.isDigital(value): ivalue = self.toDigital(value) for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif isinstance(v, int): if v == ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) elif isinstance(v, float): if round(v, 6) == ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: if v == value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) self.__frozenTableView.setRowHidden(rowId, True) elif v == value: self.setRowHidden(rowId, False) self.__frozenTableView.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) self.__frozenTableView.setRowHidden(rowId, True) def filterColumnValueEqualNo(self, column, value): if value == str(): self.filterClear() elif self.isDigital(value): ivalue = self.toDigital(value) for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif isinstance(v, int): if v != ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) elif isinstance(v, float): if round(v, 6) != ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: if v != value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif v == value: self.setRowHidden(rowId, True) else: self.setRowHidden(rowId, False) def filterColumnValueGreaterThan(self, column, value): if value == str(): self.filterClear() elif self.isDigital(value): ivalue = self.toDigital(value) for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif (isinstance(v, int) or isinstance(v, float)): if v > ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: if v > value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif v > value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) def filterColumnValueLessThan(self, column, value): if value == str(): self.filterClear() elif self.isDigital(value): ivalue = self.toDigital(value) for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) self.__frozenTableView.setRowHidden(rowId, True) elif (isinstance(v, int) or isinstance(v, float)): if v < ivalue: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: if v < value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True) else: for rowId in range(self.model().rowCount()): if self.isRowHidden(rowId): continue v = self.model().item(rowId, column).data(Qt.DisplayRole) if v is None: self.setRowHidden(rowId, True) elif v < value: self.setRowHidden(rowId, False) else: self.setRowHidden(rowId, True)
import sys from PyQt5.QtCore import QSize, Qt from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel from PyQt5.QtWidgets import (QApplication, QHBoxLayout, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget) db = QSqlDatabase("QSQLITE") db.setDatabaseName("chinook.sqlite") db.open() class MainWindow(QMainWindow): def __init__(self): super().__init__() container = QWidget() layout_search = QHBoxLayout() self.track = QLineEdit() self.track.setPlaceholderText("Track name...") self.track.textChanged.connect(self.update_query) self.composer = QLineEdit() self.composer.setPlaceholderText("Artist name...") self.composer.textChanged.connect(self.update_query) self.album = QLineEdit() self.album.setPlaceholderText("Album name...") self.album.textChanged.connect(self.update_query)
class DatabaseLogLite(object): """ Low load only; using SQLite To store bookmarks, configuration, etc. AB01 CFG02 """ def __init__(self): # ###### STARTUP super(DatabaseLogLite, self).__init__() self.litedb = QSqlDatabase("QSQLITE") db_file = expanduser("~/.eilat/eilat.db") rebuild = not isfile(db_file) self.litedb.setDatabaseName(db_file) self.litedb.open() if rebuild: query_mknav = ( "CREATE TABLE navigation (host TEXT NOT NULL," + " path TEXT, count INTEGER default 0, prefix char(2)," + " PRIMARY KEY (host, path))") self.litedb.exec_(query_mknav) # ###### VALIDATION # verifies database structure, not datatypes tables = self.litedb.tables() tables_ok = [k in tables for k in ['navigation']] if not all(tables_ok): raise RuntimeError("tables missing from database") fnav_ok = [self.litedb.record('navigation').contains(k) for k in ['host', 'path', 'count', 'prefix']] if not all(fnav_ok): raise RuntimeError("bad structure for 'navigation' table") def model(self, prefix=None): """ recreate the model each call; opening a new window will not be needed to use the recent completions """ if prefix is None: query_nav = QSqlQuery( "select host || path from navigation " + "order by count desc", self.litedb) else: # CFG02 query_nav = QSqlQuery( "select host || path from navigation " + "where prefix = '{}' ".format(prefix) + "order by count desc", self.litedb) ret_model = QSqlQueryModel() ret_model.setQuery(query_nav) # AB01 return ret_model def store_navigation(self, host, path, prefix): """ save host, path and increase its count AB01 """ host = host.replace("'", "%27") path = path.replace("'", "%27") insert_or_ignore = ( "insert or ignore into navigation (host, path, prefix) " + "values ('{}', '{}', '{}')".format(host, path, prefix)) update = ( "update navigation set count = count + 1 where " + "host = '{}' and path = '{}'".format(host, path)) self.litedb.exec_(insert_or_ignore) self.litedb.exec_(update)
import datetime from PyQt5.QtSql import QSqlDatabase from peewee import * db_filename = 'logger.db' baza = SqliteDatabase(db_filename) qbaza = QSqlDatabase("QSQLITE") qbaza.setDatabaseName(db_filename) if qbaza.open(): print('Udalo sie połączyć z bazą loggera') else: print('Nie udało sie połączyć z bazą loggera') class ModelBazy(Model): class Meta: database = baza class AreaLog(ModelBazy): date = DateTimeField(default=0) user = CharField(default='') areaid = IntegerField() change = CharField() staredane = CharField(default='') nowedane = CharField()
def openConnection(self, db: QSqlDatabase): print("%s: openConnection" % os.path.basename(__file__)) if db: if not db.isOpen(): db.open()
class ApiDatabaseDialog(QDialog): def __init__(self): super().__init__() self.setMinimumSize(QSize(800, 400)) config_folder = os.path.join(os.path.expanduser("~"), '.config', 'CSV_Viewer') os.makedirs(config_folder, exist_ok=True) db_file = "apilinks.sqlite" db_path = os.path.join(config_folder, db_file) self.db = QSqlDatabase("QSQLITE") self.db.setDatabaseName(db_path) if self.db.open(): if os.path.getsize(db_path) == 0: query = QSqlQuery(db=self.db) query.exec_("CREATE TABLE links(address TEXT)") demo = "http://climatedataapi.worldbank.org/climateweb/rest/v1/country/cru/tas/year/POL.csv" query.exec_(f"INSERT INTO links VALUES ('{demo}')") self.db.commit() else: QMessageBox.warning(self, "Error", "API links database not open.") self.table = QTableView() self.model = QSqlTableModel(db=self.db) self.model.setTable('links') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.select() # set headers column_titles = {"address": "API Address"} for n, t in column_titles.items(): idx = self.model.fieldIndex(n) self.model.setHeaderData(idx, Qt.Horizontal, t) self.table.setModel(self.model) self.table.setSelectionBehavior(QtWidgets.QTableView.SelectRows) self.table.setSelectionMode(QtWidgets.QTableView.SingleSelection) self.table.setColumnWidth(0, 750) self.table.selectRow(0) self.table.setFocus() self.layout = QVBoxLayout() QBtn = QDialogButtonBox.Ok self.buttonBox = QDialogButtonBox(QBtn) style_add = self.buttonBox.style() icon = style_add.standardIcon(QStyle.SP_DialogYesButton) self.button_add = QPushButton(icon, "&Add") self.button_add.setStatusTip("Add new api link") self.button_add.clicked.connect(self.add_link) style_del = self.buttonBox.style() icon = style_del.standardIcon(QStyle.SP_DialogCloseButton) self.button_del = QPushButton(icon, "&Delete") self.button_del.setStatusTip("Delete api link") self.button_del.clicked.connect(self.del_link) self.buttonBox.accepted.connect(self.accept) self.layout.addWidget(self.table) layout_btn = QHBoxLayout() layout_btn.addWidget(self.button_add) layout_btn.addWidget(self.button_del) layout_btn.addSpacerItem(QSpacerItem(150, 10, QSizePolicy.Expanding)) layout_btn.addWidget(self.buttonBox) self.layout.addLayout(layout_btn) self.setLayout(self.layout) def closeEvent(self, event) -> None: """ Quit dialog """ self.db.close() def add_link(self): self.model.insertRows(self.model.rowCount(), 1) self.table.setFocus() self.table.selectRow(self.model.rowCount() - 1) index = self.table.currentIndex() self.table.edit(index) def del_link(self): if self.model.rowCount() > 0: index = self.table.currentIndex() self.model.removeRow(index.row()) self.model.submitAll() self.table.setRowHidden(index.row(), True) if index.row() == 0: current = 0 else: current = index.row() - 1 self.table.selectRow(current)