def addTestData(self):
data = [
("Harrison", "Senior Back-End Developer",
"*****@*****.**", "0799204524", 22),
("Joe", "Senior Front-End Developer", "*****@*****.**",
"0799001654", 21),
("Lara", "Project Manager", "*****@*****.**", "0710204524", 25),
("David", "Data Analyst", "*****@*****.**", "0799204500", 24),
("Jane", "Senior Python Developer", "*****@*****.**",
"0799200520", 23),
]
query = QSqlQuery()
query.prepare("""
INSERT INTO employee (
name,
job,
email,
phone,
age
)
VALUES (?, ?, ?, ?, ?)
""")
for name, job, email, phone, age in data:
query.addBindValue(name)
query.addBindValue(job)
query.addBindValue(email)
query.addBindValue(phone)
query.addBindValue(age)
query.exec()
query.finish()
def execute_query(query, num_columns, log, task, err_type = "Database Error", parent = None):
"""executes a query;
returns data of a SELECT statement as list of lists;
reports errors to log and QMessageBox
(using task and err_type as message building blocks)
usage example:
success, data = db_internal.execute_query(query, 1, self.log,
"generating the IPD submission number",
"IPD submission nr error", self)
"""
log.debug("\tExecuting query {}[...]...".format(query.split("\n")[0][:100]))
data = []
success = False
q = QSqlQuery()
q.exec_(query)
err_msg = error_in_query(q, task, log)
if err_msg:
if parent:
QMessageBox.warning(parent, err_type, err_msg)
else:
data = err_msg
return success, data
success = True
while q.next(): # if query has return-data, return it
row = []
for i in range(num_columns):
row.append(q.value(i))
data.append(row)
q.finish()
if data:
log.debug("\t=> {} records found!".format(len(data)))
return success, data
def create_table(self):
"""Create the database table."""
query = QSqlQuery(self.database())
if not query.exec('CREATE TABLE IF NOT EXISTS "%s" ' % self.TABLE +
'("%s" INTEGER NOT NULL PRIMARY KEY, ' % self.ID +
'"%s" TEXT NOT NULL, ' % self.KEY +
'"%s" TEXT NOT NULL);' % self.VALUE):
raise DatabaseError(query.lastError().text())
query.finish()
def delete_env(self, env: DEnvEnvironment) -> bool:
"""
Deletes Environment record in table
:param env: Reference to Environment reocrd
:return: true - delete was successful, false - delete was unsuccessful
"""
query = QSqlQuery(self._conn)
query.prepare("delete from %s where env_id = :id" % self.TABLE_NAME)
query.bindValue(":id", env.id)
result = query.exec_()
query.finish()
return result
def _insert_batch(self, query_str, fields, values):
try:
with self._lock:
q = QSqlQuery(self.db)
q.prepare(query_str)
q.addBindValue(fields)
q.addBindValue(values)
if not q.execBatch():
print(query_str)
print(q.lastError().driverText())
q.finish()
except Exception as e:
print("_insert_batch() exception:", e)
def delete_album(self, album) -> bool:
"""
Deletes a specific album
:param album: Album to delete (needs the id to be specified at least)
:return: bool - true - operation was successful, else operation unsuccessful
"""
query = QSqlQuery(self._conn)
query.prepare("delete from %s where id = :id" % self.TABLE_NAME)
query.bindValue(":id", album.id)
result = query.exec_(
) # Bound values are replaced with proper values and executed
self._dbm.debug(query)
query.finish()
return result
def create_env(self, env: DEnvEnvironment = None) -> DEnvEnvironment:
"""
Create environment record in table
:param env: - Name of environment
:return: Environment Model Object
"""
if env is not None:
query = QSqlQuery(self._conn)
query.prepare(DbConstant.INSERT_ENVIRONMENT % self.TABLE_NAME)
query.bindValue(":name", env.name)
query.exec_()
# Grab the last id before finish() is called, otherwise it is lost
last_id = query.lastInsertId()
query.finish()
return DEnvEnvironment(_id=last_id, name=env.name)
def init(self):
"""
Initialise Environment table.
It creates the table if it does not exists yet
:return: None
"""
if self.TABLE_NAME not in self._conn.tables():
query = QSqlQuery(self._conn)
a = query.exec_(DbConstant.CREATE_TABLE_ENVIRONMENT %
self.TABLE_NAME)
b = query.exec_(DbConstant.CREATE_TABLE_ENVIRONMENT_SETTING %
self.LINK_TABLE_NAME)
query.finish()
return a and b
return True
def _insert(self, query_str, columns):
try:
with self._lock:
q = QSqlQuery(self.db)
q.prepare(query_str)
for idx, v in enumerate(columns):
q.bindValue(idx, v)
if not q.exec_():
print("ERROR", query_str)
print(q.lastError().driverText())
except Exception as e:
print("_insert exception", e)
finally:
q.finish()
def update_env(self, env: DEnvEnvironment) -> bool:
"""
Update existing Environment record in table
:param env: - Reference to existing Environment record
:return: true - update was succeesful, false - update was unsuccessful
"""
query = QSqlQuery(self._conn)
query.prepare("update %s set env_name = :name where env_id = :id" %
self.TABLE_NAME)
query.bindValue(":name", env.name)
query.bindValue(":id", env.id)
result = query.exec_()
query.finish()
return result
def update(self, table, fields, values, condition, action_on_conflict="OR IGNORE"):
qstr = "UPDATE " + action_on_conflict + " " + table + " SET " + fields + " WHERE " + condition
try:
with self._lock:
q = QSqlQuery(qstr, self.db)
q.prepare(qstr)
for idx, v in enumerate(values):
q.bindValue(idx, v)
if not q.exec_():
print("update ERROR", qstr)
print(q.lastError().driverText())
except Exception as e:
print("update() exception:", e)
finally:
q.finish()
def update_album(self, album: Album) -> bool:
"""
Updates existing entry of Album record
:param album: Record with updated data
:return: bool - true if operation was successful, else false if operation was unsuccessful
"""
query = QSqlQuery(self._conn)
query.prepare("update %s set name = :name where id = :id" %
self.TABLE_NAME)
query.bindValue(":name", album.name)
query.bindValue(":id", album.id)
result = query.exec_(
) # Bound values are replaced with proper values and executed
self._dbm.debug(query)
query.finish()
return result
def create_album(self, album: Album) -> Album:
"""
Creates a new album entry into the table
:param album: Data to be inserted as an entry into the table
:return: Newly inserted album entry
"""
query = QSqlQuery(self._conn)
query.prepare("insert into %s (name) values (:name)" % self.TABLE_NAME)
query.bindValue(":name", album.name)
query.exec_(
) # Bound values are replaced with proper values and executed
self._dbm.debug(query)
# Grab the last id before finish() is called, otherwise it is lost
last_id = query.lastInsertId()
album._id = last_id
query.finish()
return album
def insert_registry(self, name, hostname) -> int:
"""
Insert registry record into table
:param name: - Name of registry
:param hostname: - Hostname of registry (e.g. localhost:5000)
:return: Last insert's id columns value (primary key value)
insert into docker_reg_registry values (0, 'Local VM', 'localhost:5000')
"""
query = QSqlQuery(self._conn)
query.prepare(DbConstant.INSERT_REGISTRY % self.TABLE_NAME)
query.bindValue(":name", name)
query.bindValue(":hostname", hostname)
query.exec_()
# Grab the last id before finish() is called, otherwise it is lost
last_id = query.lastInsertId()
query.finish()
return last_id
def _insert_batch(self, query_str, fields, values):
result=True
with self._lock:
try:
q = QSqlQuery(self.db)
q.prepare(query_str)
q.addBindValue(fields)
q.addBindValue(values)
if not q.execBatch():
print("_insert_batch() error", query_str)
print(q.lastError().driverText())
result=False
except Exception as e:
print("_insert_batch() exception:", e)
finally:
q.finish()
return result
def create_table(self):
"""Create the database table."""
query = QSqlQuery(self.database())
if not query.exec('CREATE TABLE IF NOT EXISTS "%s" ' % self.TABLE +
'("%s" INTEGER NOT NULL PRIMARY KEY, ' % self.ID +
'"%s" INTEGER UNIQUE NOT NULL, ' % self.BIB +
'"%s" TEXT NOT NULL, ' % self.FIRST_NAME +
'"%s" TEXT NOT NULL, ' % self.LAST_NAME +
'"%s" INTEGER NOT NULL, ' % self.FIELD +
'"%s" TEXT NOT NULL, ' % self.CATEGORY +
'"%s" TEXT NOT NULL, ' % self.TEAM +
'"%s" INTEGER NOT NULL, ' % self.AGE +
'"%s" INTEGER NOT NULL, ' % self.START +
'"%s" INTEGER NOT NULL, ' % self.FINISH +
'"%s" TEXT NOT NULL, ' % self.STATUS +
'"%s" TEXT NOT NULL);' % self.METADATA):
raise DatabaseError(query.lastError().text())
query.finish()
def create_env_setting(self,
env: DEnvEnvironment = None,
setting: tuple = None) -> DEsEnvSetting:
"""
Create environment setting
:param env: - Reference of environment
:param setting: - Tuple that is a name-value pair (e.g. ('DOCKER_HOST', 'tcp://localhost:2376'))
:return: Environment Setting Model Object
"""
query = QSqlQuery(self._conn)
query.prepare(DbConstant.INSERT_ENVIRONMENT_SETTING %
self.LINK_TABLE_NAME)
query.bindValue(":name", setting[0])
query.bindValue(":value", setting[1])
query.bindValue(":env_id", env.id)
success = query.exec_()
query.finish()
if success:
return DEsEnvSetting(env_id=env.id,
name=setting[0],
value=setting[1])
def _update_data(self, sql: str, bind_values: list) -> None:
"""It runs the given sql update query
The sql query placeholder should be "?". The values in the bind_values
parameter will replace "?".
:param sql: The sql query to run.
:type sql: str.
:param bind_values: The values to bind to the query placeholders.
:type bind_values: list.
"""
# Try to open the connection and handle possible errors
if not self.con.open():
# The error is shown in message box
self._display_error("")
# The query object is created
query = QSqlQuery()
# If the sql query contains placeholders
if len(bind_values) > 0:
# Prepared query is used
query.prepare(sql)
# Each given bind value is added
for val in bind_values:
# The bind value is added
query.addBindValue(val)
# The query is run
if not query.exec():
self._display_error(sql)
else:
# The query is run
if not query.exec(sql):
self._display_error(sql)
# The resources associated with the query object are freed
query.finish()
def _fetch_data(self, sql: str, bind_values: list, sel_count: int) -> list:
"""It runs the given sql select query and returns the fetched data
The sql query placeholder should be "?". The values in the bind_values
parameter will replace "?".
:param sql: The sql query to run.
:type sql: str.
:param bind_values: The values to bind to the query placeholders.
:type bind_values: list.
:param sel_count: The number of fields in select query.
:type sel_count: int.
:return: The required data.
:rtype: list.
"""
# Try to open the connection and handle possible errors
if not self.con.open():
# The error is shown in message box
self._display_error("")
# The query object is created
query = QSqlQuery()
# If the sql query contains placeholders
if len(bind_values) > 0:
# Prepared query is used
query.prepare(sql)
# Each given bind value is added
for val in bind_values:
# The bind value is added
query.addBindValue(val)
# The query is run
if not query.exec():
self._display_error(sql)
else:
# The query is run
if not query.exec(sql):
self._display_error(sql)
# All rows
rows = []
# All rows are fetched
while query.next():
# The row of data
row = []
# All selected field values are fetched
for i in range(sel_count):
# The query value
qval = query.value(i)
# The query value is appended to the row
row.append(qval)
# The row in appended to the list of rows
rows.append(row)
# The resources associated with the query object are freed
query.finish()
# The connection is closed
#self.con.close()
# The data is returned
return rows
