def upsert()

in asfpy/sqlite.py [0:0]

• 18 lines of code
• 9 McCabe index (conditional complexity)

    def upsert(self, table: str, document: dict, **target):
        """
        Performs an upsert in a table with unique constraints. Insert if not present, update otherwise.
        @param table: The table to upsert into
        @param document: The document to insert/update (depending on whether it exists)
        @param target: Target search key/value parameters to look for existing document.
        """
        # Always have the target identifier as part of the row
        if not target:
            raise AsfpyDBError("UPSERTs must have at least one defined target value for locating where to upsert")
        k, v = next(iter(target.items()))
        document[k] = v

        # table: foo
        # bar: 1
        # baz: 2
        # INSERT INTO foo (bar,baz) VALUES (?,?) ON CONFLICT (bar) DO UPDATE SET (bar=?, foo=?) WHERE bar=?,(1,2,1,2,1,)
        if self.upserts_supported:
            items = document.items()  # Use the same ordering for keys/values
            variables = ", ".join("`%s`" % uk for uk, uv in items)
            questionmarks = ", ".join(['?'] * len(items))
            upserts = ", ".join("`%s` = ?" % uk for uk, uv in items)

            statement = f'INSERT INTO {table} ({variables}) VALUES ({questionmarks}) ON CONFLICT({k}) DO UPDATE SET {upserts} WHERE {k} = ?;'
            # insert values, update values, and the unique constraint value
            values = ([uv for uk, uv in items] * 2) + [v]
            self.runc(statement, *values)
        # Older versions of sqlite do not support 'ON CONFLICT', so we'll have to work around that...
        else:
            try:  # Try to insert
                self.insert(table, document)
            except sqlite3.IntegrityError: # Conflict, update instead
                self.update(table, document, **target)