def readSQL(sql_text, params=None, named=False, check_unique=False):
if params is None:
params = []
query = QSqlQuery(
db_connection()
) # TODO reimplement via ExecuteSQL() call in order to get rid of duplicated code
query.setForwardOnly(True)
if not query.prepare(sql_text):
logging.error(
f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec():
logging.error(
f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
if query.next():
res = readSQLrecord(query, named=named)
if check_unique and query.next():
return None # More then one record in result when only one expected
return res
else:
return None
def setValue(self, key, value):
set_query = QSqlQuery(self.db)
set_query.prepare(
"INSERT OR REPLACE INTO settings(id, name, value) "
"VALUES((SELECT id FROM settings WHERE name=:key), :key, :value)")
set_query.bindValue(":key", key)
set_query.bindValue(":value", value)
if not set_query.exec():
logging.fatal(
f"Failed to set settings key='{key}' to value='{value}'")
self.db.commit()
def getValue(self, key, default=None):
get_query = QSqlQuery(self.db)
get_query.setForwardOnly(True)
get_query.prepare("SELECT value FROM settings WHERE name=:key")
value = default
get_query.bindValue(":key", key)
if not get_query.exec():
if not default:
logging.fatal(f"Failed to get settings for key='{key}'")
return value
if get_query.next():
value = get_query.value(0)
return value
def executeSQL(sql_text, params=[], forward_only=True, commit=False):
db = db_connection()
query = QSqlQuery(db)
query.setForwardOnly(forward_only)
if not query.prepare(sql_text):
logging.error(
f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'"
)
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec():
logging.error(
f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'"
)
return None
if commit:
db.commit()
return query