def createDB(self):
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('sports.db')
if not db.open():
QtGui.QMessageBox.critical(
None, QtGui.qApp.tr("Cannot open database"),
QtGui.qApp.tr(
"Unable to establish a database connection.\n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."), QtGui.QMessageBox.Cancel)
return False
query = QtSql.QSqlQuery()
query.exec_("create table sportsmen(id int primary key, "
"firstname varchar(20), lastname varchar(20))")
query.exec_("insert into sportsmen values(101, 'Ibrahim', 'Federer')")
query.exec_(
"insert into sportsmen values(102, 'Christiano', 'Ronaldo')")
query.exec_("insert into sportsmen values(103, 'Ussain', 'Bolt')")
query.exec_("insert into sportsmen values(104, 'Sachin', 'Tendulkar')")
query.exec_("insert into sportsmen values(105, 'Saina', 'Nehwal')")
query.exec_("insert into sportsmen values(106, 'Ibrahim', 'Ince')")
return True
def create_connection():
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(str(pathRaiz)+"/tray.db")
if not db.open():
QMessageBox.critical(
None,
QtWidgets.qApp.tr("Cannot open database"),
QtWidgets.qApp.tr(
"Unable to establish a database connection.\n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."
),
QtWidgets.QMessageBox.Cancel,
)
return False
query = QtSql.QSqlQuery(
"CREATE TABLE IF NOT EXISTS projects(PATH text NOT NULL UNIQUE)"
)
if not query.exec_():
print(query.lastError().text())
return False
return True
def set_blank_form_mode(self):
""" """
if "configure toolbar icons for adding ":
self.to_previous_win_act.setEnabled(False)
self.print_act.setEnabled(False)
self.delete_row_act.setEnabled(False)
self.cancel_act.setEnabled(True)
self.save_act.setEnabled(True)
if "configure triggers":
self.save_act.triggered.disconnect()
self.cancel_act.triggered.disconnect()
self.save_act.triggered.connect(self.insert_new_row)
self.cancel_act.triggered.connect(self.cancel_add)
if "find next id":
sql_str = "SELECT MAX(id) FROM table_1"
query = S.QSqlQuery()
query.exec_(sql_str)
query.first()
glb.list_id = str(int(query.value(0)) + 1)
if "setup blank form - show and wait":
self.header = ""
self.form_groupbox.setTitle("ID: " + glb.list_id)
self.led01.setText('')
self.led02.setText('0')
self.led03.setText('0.0')
self.led04.setText('')
self.show()
def testTableCreationAndDestruction(self):
#Test table creation and destruction
query = QtSql.QSqlQuery()
query.exec_(
"CREATE TABLE dummy(id int primary key, dummyfield varchar(20))")
query.exec_("DROP TABLE dummy")
query.clear()
def run_search_handler(self):
"""following needs to be updated for table other than table_1 """
glb.table_1_list.filter_str = self.create_table_1_where()
if not glb.table_1_list.filter_str:
notify_("Empty filter string")
return
if "get list data":
str_sqlcount = """SELECT COUNT(*) FROM table_1 {} """.format(
glb.table_1_list.filter_str) # to get total database rows
query_1 = S.QSqlQuery()
query_1.exec_(str_sqlcount)
query_1.first()
total_rows = query_1.value(0)
if not query_1.isActive():
notify_("Invalid Query", str_sqlcount)
return
if not total_rows:
notify_("Empty Query", str_sqlcount)
return
if "update table size":
glb.table_1_list.table.setRowCount(total_rows)
if "place data in table ...":
glb.table_1_list.table.setSortingEnabled(False)
#Note: need to turn sorting off while folling table, or data gets scrambled !!!
str_sqldata = """SELECT id, text_1, integer_1, real_1, date_1 FROM table_1 {} """.format(
glb.table_1_list.filter_str)
query_2 = S.QSqlQuery()
query_2.exec_(str_sqldata)
query_2.first()
for row in range(total_rows):
for col in range(glb.table_1_list.total_columns):
item = W.QTableWidgetItem(str(query_2.value(col)))
if col != 1:
item.setTextAlignment(int(Qt.AlignCenter))
glb.table_1_list.table.setItem(row, col, item)
query_2.next()
glb.table_1_list.table.sortItems(1, order=Qt.AscendingOrder)
glb.table_1_list.table.setSortingEnabled(True)
glb.table_1_list.show()
self.hide()
def sql_qt_insert(self, username=None, email=None, passw=None, data=None):
query = QtSql.QSqlQuery(self.qtsql_db)
query.prepare("""INSERT INTO u_dbtable (user_name, user_email,
user_passw, user_data) VALUES (:user_name, :user_email,
:user_passw, :user_data);""")
query.bindValue(":user_name", username)
query.bindValue(":user_email", email)
query.bindValue(":user_passw", passw) # Inserts values to the sqlite
query.bindValue(":user_data", data) # database with QtSql module.
query.exec_() # Executes the query to DB.
def updateIcon(wap_id, icon_path):
query = QtSql.QSqlQuery()
print("Setting for: " + wap_id)
print("setting to: " + icon_path)
query.prepare("UPDATE webapps SET icon_path = :icon_path WHERE id = :id;")
query.bindValue(":icon_path", icon_path)
query.bindValue(":id", wap_id)
if not query.exec_():
raise Exception("Query failed: " + query.lastError().driverText())
updateDesktopEntry(getWebapp(wap_id))
def deleteWebApp(app: WebApp):
query = QtSql.QSqlQuery()
query.prepare("DELETE FROM webapps WHERE title=:title;")
query.bindValue(":title", app.title)
assert query.exec_()
desktopEntry = desktopEntryLocation(app.title)
if os.path.isfile(desktopEntry):
os.remove(desktopEntry)
if os.path.isfile(app.icon_path) and app.icon_path != default_icon():
os.remove(app.icon_path)
def add(self):
directory = QtWidgets.QFileDialog.getExistingDirectory(
None, self.tr("Open getExistingDirectory")
)
query = QtSql.QSqlQuery()
query.prepare("INSERT INTO projects VALUES (?)")
query.addBindValue(directory)
if not query.exec_():
print(query.lastError().text())
return
self.refresh_menu()
def getWebapps():
query = QtSql.QSqlQuery("SELECT id, title, url, icon_path FROM webapps;")
apps = []
while query.next():
wap_id = query.value(0)
title = query.value(1)
url = query.value(2)
icon_path = query.value(3)
print("Getting: " + icon_path)
apps.append(WebApp(wap_id, title, url, icon_path))
return apps
def addWebApp(title, url):
query = QtSql.QSqlQuery()
query.prepare(
"INSERT INTO webapps (title, url, icon_path) VALUES (:title, :url, :icon_path);"
)
query.bindValue(":title", title)
query.bindValue(":url", url)
query.bindValue(":icon_path", "galago")
query.exec_()
apps = getWebapps()
wap = WebApp(title, url, "galago")
updateDesktopEntry(wap)
return wap
def migrate():
versionQuery = QtSql.QSqlQuery("SELECT version FROM version;")
if not versionQuery.exec_():
raise Exception("Version selection failed")
version = 0
if versionQuery.first():
version = versionQuery.value(0)
else:
versionZeroQuery = QtSql.QSqlQuery(
"INSERT INTO version (version, id) VALUES (0, 0);")
versionZeroQuery.exec_()
if version == 0:
print("Migrating from version 0 to 1")
query = QtSql.QSqlQuery(
"CREATE TABLE webapps (title TEXT PRIMARY KEY, url TEXT, icon_path TEXT);"
)
query.exec_()
updateVersion = QtSql.QSqlQuery()
updateVersion.prepare("UPDATE version SET version = :version;")
updateVersion.bindValue(":version", 1)
updateVersion.exec_()
def set_preference(key: str, value: any):
query = QtSql.QSqlQuery()
query.prepare('UPDATE preferences SET value = :value WHERE key = :key')
if value == True:
query.bindValue(':value', 'true')
elif value == False:
query.bindValue(':value', 'false')
else:
query.bindValue(':value', value)
query.bindValue(':key', key)
query.exec_()
def initDB():
path = QtCore.QStandardPaths.writableLocation(
QtCore.QStandardPaths.AppDataLocation)
if not os.path.exists(path):
os.makedirs(path)
database = QtSql.QSqlDatabase.addDatabase("QSQLITE")
database.setDatabaseName(path + "/webappifierdb.db")
if not database.open():
raise Exception("Database not working: " +
database.lastError().driverText())
query = QtSql.QSqlQuery(
"CREATE TABLE IF NOT EXISTS webapps (id TEXT PRIMARY KEY, title TEXT, url TEXT, icon_path TEXT);"
)
query.exec_()
def getWebapps():
query = QtSql.QSqlQuery("SELECT title, url, icon_path FROM webapps;")
apps = []
while query.next():
title = query.value(0)
url = query.value(1)
icon_path = query.value(2)
if icon_path != "galago" and not os.path.isfile(icon_path):
updateIcon(title, "galago")
icon_path = "galago"
if icon_path == "galago":
icon_path = default_icon()
apps.append(WebApp(title, url, icon_path))
return apps
def update_table_1_list(self):
"""new and updated records do not show, unless window updated ..."""
query_1 = S.QSqlQuery()
if "get list data":
query_1.exec_(get_table_1_count())
if not query_1.isActive():
notify_("Error", "Invalid_query")
return
query_1.first()
total_rows = query_1.value(0)
if "update table size":
self.table.setRowCount(total_rows)
if "place data in table ...":
query_2 = S.QSqlQuery()
query_2.exec_(get_table_1_rows())
query_2.first()
if not query_2.isActive():
notify_("Error", "Invalid_query")
return
self.table.setSortingEnabled(
False
) # need to disable automatic sorting when filling table!
for row in range(total_rows):
for col in range(self.total_columns):
item = W.QTableWidgetItem(str(query_2.value(col)))
if col != 1:
item.setTextAlignment(int(Qt.AlignCenter))
self.table.setItem(row, col, item)
query_2.next()
self.table.sortItems(1, order=Qt.AscendingOrder)
self.table.setSortingEnabled(True)
def get_lastfm_session() -> LastfmSession:
'''Fetch the user's Last.fm session key and username from the settings table'''
# Execute SQL to find the row that matches our criteria
query = QtSql.QSqlQuery(
'SELECT value FROM lastfm_login_info WHERE key="username"')
# Move to next row
if query.next():
# Get column id for value in settings
username = query.value(query.record().indexOf('value'))
# Execute SQL to find the row that matches our criteria
query = QtSql.QSqlQuery(
'SELECT value FROM lastfm_login_info WHERE key="session_key"')
# Move to next row
query.next()
session_key = query.value(query.record().indexOf('value'))
return LastfmSession(session_key, username)
return None
def initDB():
path = QtCore.QStandardPaths.writableLocation(
QtCore.QStandardPaths.AppDataLocation)
if not os.path.exists(path):
os.makedirs(path)
database = QtSql.QSqlDatabase.addDatabase("QSQLITE")
database.setDatabaseName(path + "/galagodb.db")
if not database.open():
raise Exception("Database not working: " +
database.lastError().driverText())
# The ID is just a clever way to ensure only one row in the version table
query = QtSql.QSqlQuery(
"CREATE TABLE IF NOT EXISTS version (version INTEGER, id INTEGER PRIMARY KEY CHECK (id = 0));"
)
query.exec_()
migrate()
def refresh_menu(self):
menu = self.contextMenu()
for action in self._path_actions:
action.deleteLater()
query = QtSql.QSqlQuery("SELECT * FROM projects")
self._path_actions = []
while query.next():
directory = query.value(0)
info = QtCore.QFileInfo(directory)
action = QtWidgets.QAction(info.fileName(), self, triggered=self.vscode)
action.setIcon(QtGui.QIcon(str(pathRaiz)+"/img/vscode.svg"))
action.setData(directory)
self._path_actions.append(action)
menu.insertActions(self._separator, self._path_actions)
def delete_row(self):
""" """
if confirm_("Update:"):
sql_str = """DELETE FROM table_1 WHERE id = "{}" """.format(
glb.list_id)
query = S.QSqlQuery()
query.exec_(sql_str)
query.first()
notify_("Delete:", "Row was deleted")
glb.menu_win.show()
self.hide()
else:
notify_("Delete:", "Delete row was canceled")
def updateIcon(title, icon):
pixmap = icon.pixmap(256, 256)
path = QtCore.QStandardPaths.writableLocation(
QtCore.QStandardPaths.AppDataLocation) + "/icons/"
if not os.path.exists(path):
os.makedirs(path)
icon_path = path + title + ".png"
pixmap.save(icon_path)
updateDesktopEntry(getWebapp(title))
query = QtSql.QSqlQuery()
query.prepare(
"UPDATE webapps SET icon_path = :icon_path WHERE title = :title;")
query.bindValue(":icon_path", icon_path)
query.bindValue(":title", title)
if not query.exec_():
raise Exception("Query failed: " + query.lastError().driverText())
def show_table_1_data(self, list_id_parameter):
"""following correct for table_1, update for a different table """
self.form_groupbox.setTitle("ID: " + str(list_id_parameter))
sql_str = """SELECT * FROM table_1 WHERE id = {}""".format(
str(list_id_parameter))
query = S.QSqlQuery()
query.exec_(sql_str)
if not query.isActive():
notify_("Invalid Query", sql_str)
return
query.first()
self.led01.setText(str(query.value(1)))
self.led02.setText(str(query.value(2)))
self.led03.setText(str(query.value(3)))
self.led04.setText(str(query.value(4)))
def save_default_preferences_to_database(media_player_preference: str):
create_preferences_table()
# Set up preferences with default values
insert_query = QtSql.QSqlQuery()
insert_query.prepare(
'INSERT INTO preferences (key, value) VALUES ("rich_presence_enabled", "false")'
)
insert_query.exec_()
insert_query.prepare(
'INSERT INTO preferences (key, value) VALUES ("is_in_mini_mode", "false")'
)
insert_query.exec_()
insert_query.prepare(
'INSERT INTO preferences (key, value) VALUES ("media_player", :value)')
insert_query.bindValue(':value', media_player_preference)
insert_query.exec_()
def update_data(self, list_id_parameter):
"""following correct for table_1, update for a different table """
#pylint: disable = no-self-use
sql_str = """UPDATE table_1 SET
text_1 = "{}",
integer_1 = "{}",
real_1 = "{}",
date_1 = "{}"
WHERE id = {} """.format(
self.led01.text(),
self.led02.text(),
self.led03.text(),
self.led04.text(),
str(list_id_parameter),
)
query = S.QSqlQuery()
query.exec_(sql_str)
def save_lastfm_session_to_database(session: LastfmSession):
# TODO: Only do this if there isn't already a table
create_lastfm_session_table()
# Insert session key
insert_query = QtSql.QSqlQuery()
insert_query.prepare(
'INSERT INTO lastfm_login_info (key, value) VALUES (:key, :value)')
insert_query.bindValue(':key', 'session_key')
insert_query.bindValue(':value', session.session_key)
insert_query.exec_()
# Insert username
insert_query.prepare(
'INSERT INTO lastfm_login_info (key, value) VALUES (:key, :value)')
insert_query.bindValue(':key', 'username')
insert_query.bindValue(':value', session.username)
insert_query.exec_()
def get_preference(key: str) -> any:
query = QtSql.QSqlQuery()
query.prepare('SELECT value FROM preferences WHERE key=:key')
query.bindValue(':key', key)
query.exec_()
if query.next():
value = query.value(query.record().indexOf('value'))
if value == 'true':
return True
if value == 'false':
return False
return value
logging.error(f'Cannot fetch value in preferences table for {key}')
def testTableInsertionAndRetrieval(self):
#Test table creation, insertion and retrieval
query = QtSql.QSqlQuery()
query.exec_("CREATE TABLE person(id int primary key, "
"firstname varchar(20), lastname varchar(20))")
query.exec_("INSERT INTO person VALUES(101, 'George', 'Harrison')")
query.prepare("INSERT INTO person (id, firstname, lastname) "
"VALUES (:id, :firstname, :lastname)")
query.bindValue(":id", 102)
query.bindValue(":firstname", "John")
query.bindValue(":lastname", "Lennon")
query.exec_()
lastname = ''
query.exec_("SELECT lastname FROM person where id=101")
self.assertTrue(query.isActive())
query.next()
lastname = query.value(0)
self.assertEqual(lastname, 'Harrison')
def createRelationalTables():
query = QtSql.QSqlQuery()
query.exec_(
"create table employee(id int, name varchar(20), city int, country int)"
)
query.exec_("insert into employee values(1, 'Espen', 5000, 47)")
query.exec_("insert into employee values(2, 'Harald', 80000, 49)")
query.exec_("insert into employee values(3, 'Sam', 100, 41)")
query.exec_("create table city(id int, name varchar(20))")
query.exec_("insert into city values(100, 'San Jose')")
query.exec_("insert into city values(5000, 'Oslo')")
query.exec_("insert into city values(80000, 'Munich')")
query.exec_("create table country(id int, name varchar(20))")
query.exec_("insert into country values(41, 'USA')")
query.exec_("insert into country values(47, 'Norway')")
query.exec_("insert into country values(49, 'Germany')")
def createConnection():
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
if not db.open():
QtGui.QMessageBox.critical(
None, QtGui.qApp.tr("Cannot open database"),
QtGui.qApp.tr("Unable to establish a database connection.\n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\nClick Cancel to exit."),
QtGui.QMessageBox.Cancel, QtGui.QMessageBox.NoButton)
return False
query = QtSql.QSqlQuery()
query.exec_("create table person(id int primary key, "
"firstname varchar(20), lastname varchar(20))")
query.exec_("insert into person values(101, 'Danny', 'Young')")
query.exec_("insert into person values(102, 'Christine', 'Holand')")
query.exec_("insert into person values(103, 'Lars', 'Gordon')")
query.exec_("insert into person values(104, 'Roberto', 'Robitaille')")
query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')")
return True
def insert_new_row(self):
""" """
if self.validate_data():
if self.confirm_update():
sql_str = """INSERT INTO table_1 VALUES
("{}","{}", "{}", "{}", "{}") """.format(
glb.list_id, self.led01.text(), self.led02.text(),
self.led03.text(), self.led04.text())
query = S.QSqlQuery()
query.exec_(sql_str)
query.first()
else:
notify_("Save Canceled", "Any input was discarded")
self.cancel_add()
self.set_default_form_mode()
glb.menu_win.show()
glb.table_1_list.hide()
self.hide()
else:
notify_("Data Invalid:", self.invalid_msg)
