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)
