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 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 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 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 __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 addConnection(self, driver, dbName, user, password, host, port):
"""
Public method to add a database connection.
@param driver name of the Qt database driver (string)
@param dbName name of the database (string)
@param user user name (string)
@param password password (string)
@param host host name (string)
@param port port number (integer)
@return SQL error object (QSqlError)
"""
err = QSqlError()
self.__class__.cCount += 1
db = QSqlDatabase.addDatabase(
driver.upper(), "Browser{0:d}".format(self.__class__.cCount))
db.setDatabaseName(dbName)
db.setHostName(host)
db.setPort(port)
if not db.open(user, password):
err = db.lastError()
db = QSqlDatabase()
QSqlDatabase.removeDatabase("Browser{0:d}".format(
self.__class__.cCount))
self.connections.refresh()
return err
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
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 create(db=QSqlDatabase()):
sql = """CREATE TABLE treeparam (
id INTEGER NOT NULL PRIMARY KEY,
pageid INTEGER,
fieldid INTEGER,
position INTEGER)"""
QSqlQuery(sql, db)
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 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 __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 create(db=QSqlDatabase()):
sql = """CREATE TABLE statistics (
id INTEGER NOT NULL PRIMARY KEY,
pageid INTEGER,
showed INTEGER,
chart TEXT,
fieldid INTEGER,
subfieldid INTEGER,
items TEXT,
period TEXT,
color INTEGER)"""
QSqlQuery(sql, 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()
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 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 __init__(self, libraryPath, parent=None, db=QSqlDatabase()):
super(desuplayerModel, self).__init__(parent, db)
self.setTable('tracks')
self.setEditStrategy(QSqlTableModel.OnFieldChange)
self.select()
self._lock_edits = True
for index, name in enumerate(headers):
self.setHeaderData(index, Qt.Horizontal, name)
self.libraryPath = libraryPath
self.sortColumn = -1
self.sortOrder = None
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 create(self, db=QSqlDatabase()):
db.transaction()
sql = "CREATE TABLE %s (\
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
value TEXT, icon BLOB)" % self.name
QSqlQuery(sql, db)
query = QSqlQuery(db)
query.prepare("INSERT INTO sections (name, letter)\
VALUES (?, ?)")
query.addBindValue(self.name)
query.addBindValue(self.letter)
query.exec_()
db.commit()
def __init__(self, db = QSqlDatabase(), dangerousMode = False):
'''
Constructor: Creates a QSqlUpgradeManager with a connection to db.
If db is invalid the default db connection is used.
If dangerousMode is set to True, then any redundant columns and tables
in the database will be deleted.
'''
self._db = db
self._query = QSqlQuery(db)
self._dangerousMode = dangerousMode
# TODO: Perform schema upgrades first
self._handleDBObjects()
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 create(db=QSqlDatabase()):
db.transaction()
sql = """CREATE TABLE settings (
title CHAR NOT NULL UNIQUE,
value CHAR)"""
QSqlQuery(sql, db)
for key, value in CollectionSettings.Default.items():
query = QSqlQuery(db)
query.prepare("""INSERT INTO settings (title, value)
VALUES (?, ?)""")
query.addBindValue(key)
query.addBindValue(str(value))
query.exec_()
db.commit()
def __init__(self, parent=None, db=QSqlDatabase(), *args, **kwargs):
super().__init__(parent, db, *args, **kwargs)
self.current_power_hour_id = None
self.setTable("tracks")
self.setEditStrategy(QSqlTableModel.OnFieldChange)
self.setHeaderData(self.Columns.url, Qt.Horizontal, "URL")
self.setHeaderData(self.Columns.title, Qt.Horizontal, "Title")
self.setHeaderData(self.Columns.length, Qt.Horizontal, "Duration")
self.setHeaderData(self.Columns.start_time, Qt.Horizontal,
"Start Time")
self.setHeaderData(self.Columns.full_song, Qt.Horizontal, "Full Song?")
self.setSort(self.Columns.position, Qt.AscendingOrder)
self.dataChanged.connect(self._load_track_info_if_new_url_given)
self.select()
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 run(self): # 2. Implement run()
ui = Ui_MainWindow()
window = QMainWindow()
version = self.build_settings['version']
ui.setupUi(window)
window.setWindowTitle("HotelManagementSystem v" + version)
#Setup Charts for isolated UI editing
ui.chartView = QtChart.QChartView(window)
ui.chartView_2 = QtChart.QChartView(window)
ui.gridLayout_3 = QtWidgets.QGridLayout(ui.groupBox_2)
ui.gridLayout_3.addWidget(ui.chartView, 0, 0, 1, 1)
ui.gridLayout_4 = QtWidgets.QGridLayout(ui.groupBox_3)
ui.gridLayout_4.addWidget(ui.chartView_2, 0, 0, 1, 1)
window.showMaximized()
#Database connection, instead of sqlite3
db = QSqlDatabase('QSQLITE')
db.setDatabaseName(self.get_resource('hotel.db'))
model = QSqlTableModel(self.app, db)
#Threading
thrd = QThreadPool().globalInstance()
thrd.setExpiryTimeout(5)
hlist = ['Reserv. ID','Customer ID','Room #','From','To','Discount','Extension','Net Total']
worker = TableWorker(update_table("CurrentReservation", hlist, ui.current_res, db, model)) #We pass a function for the worker to execute
thrd.tryStart(worker)
#Setup Signals
ui.newRes.triggered.connect(lambda: self.new_res_dialog(db, ui, model))
ui.newRoom.triggered.connect(lambda: self.new_room_dialog(db))
ui.newService.triggered.connect(lambda: self.new_srv_dialog(db))
ui.newCustomer.triggered.connect(lambda: self.new_customer_dialog(db))
ui.cancelRes.triggered.connect(lambda: self.new_cancel_dialog(window, db,
ui.current_res.currentIndex(),
thrd, model, hlist, ui.current_res))
#TODO Add new dialog for adding/deleting services to a current Reservation
ui.current_res.doubleClicked.connect(lambda: self.new_addservice_dialog(window, db,
ui.current_res.currentIndex(), thrd))
return self.app.exec_() # 3. End run() with this line
def create(self, db=QSqlDatabase()):
in_transaction = db.transaction()
cross_ref = ('country', 'period', 'emitent', 'ruler', 'unit', 'mint',
'series')
if self.name in cross_ref:
sql = "CREATE TABLE %s (\
id INTEGER PRIMARY KEY,\
parentid INTEGER,\
value TEXT, icon BLOB)" % self.table_name
else:
sql = "CREATE TABLE %s (\
id INTEGER PRIMARY KEY,\
value TEXT, icon BLOB)" % self.table_name
QSqlQuery(sql, db)
query = QSqlQuery(db)
if self.name in cross_ref:
sql = "INSERT INTO sections (name, letter, parent, sort)\
VALUES (?, ?, ?, ?)"
else:
sql = "INSERT INTO sections (name, letter, sort)\
VALUES (?, ?, ?)"
query.prepare(sql)
query.addBindValue(self.name)
query.addBindValue(self.letter)
if self.name in cross_ref:
if self.name == 'country':
query.addBindValue('region')
else:
query.addBindValue('country')
query.addBindValue(int(self.sort))
query.exec_()
if in_transaction:
db.commit()
def create(db=QSqlDatabase()):
db.transaction()
sql = """CREATE TABLE fields (
id INTEGER NOT NULL PRIMARY KEY,
title TEXT,
enabled INTEGER)"""
QSqlQuery(sql, db)
fields = CollectionFieldsBase()
for field in fields:
query = QSqlQuery(db)
query.prepare("""INSERT INTO fields (id, title, enabled)
VALUES (?, ?, ?)""")
query.addBindValue(field.id)
query.addBindValue(field.title)
enabled = field in fields.userFields
query.addBindValue(int(enabled))
query.exec_()
db.commit()
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()
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 __open(self):
self.__db = QSqlDatabase().addDatabase('QSQLITE')
self.__db.setDatabaseName(DATABASE_NAME)
return self.__db.open()
