Example #1
0
    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()
Example #2
0
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
Example #3
0
    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()
Example #4
0
 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
Example #5
0
 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
Example #7
0
 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)
Example #8
0
 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
Example #9
0
    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()
Example #10
0
    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
Example #11
0
    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()
Example #12
0
 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
Example #13
0
 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
Example #15
0
    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
Example #16
0
    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()
Example #17
0
    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])
Example #18
0
    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()
Example #19
0
    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