def getTable(self, table, idCol, newCols=None, matchCols=None, data=None, commit=False, 
                 comparisonOperator='=', checkIfExisting=False, insertIfNotMatched=True, 
                 returnMatches=True, returnExistenceStatus=False):
        # generate SQL
        # note: comparison by = will never match NULL fields
        # use 'IS NOT DISTINCT FROM' to match nulls, but this is not indexed and verrrrry slooooow
        if not data or not newCols or not matchCols:
            # nothing can be done, just return
            return () # place breakpoint here to debug
        isOracle = self.product == "orcl"
        isMSSql = self.product == "mssql"
        isPostgres = self.product == "postgres"
        isSQLite = self.product == "sqlite"
        newCols = [newCol.lower() for newCol in newCols]
        matchCols = [matchCol.lower() for matchCol in matchCols]
        returningCols = []
        if idCol: # idCol is the first returned column if present
            returningCols.append(idCol.lower())
        for matchCol in matchCols:
            if matchCol not in returningCols: # allow idCol to be specified or default assigned
                returningCols.append(matchCol)
        colTypeFunctions = self.columnTypeFunctions(table)
        colDeclarations = self.tableColDeclaration.get(table)
        try:
            colTypeCast = tuple(colTypeFunctions[colName][0] for colName in newCols)
            colTypeFunction = [colTypeFunctions[colName][1] for colName in returningCols]
            if returnExistenceStatus:
                colTypeFunction.append(self.pyBoolFromDbBool) # existence is a boolean
        except KeyError as err:
            raise XPDBException("xpgDB:MissingColumnDefinition",
                                _("Table %(table)s column definition missing: %(missingColumnName)s"),
                                table=table, missingColumnName=str(err)) 
        rowValues = []
        rowLongValues = []  # contains None if no parameters, else {} parameter dict
        if isOracle:
            longColValues = {}
        else:
            longColValues = []
        for row in data:
            colValues = []
            for col in row:
                if isinstance(col, bool):
                    if isOracle or isMSSql or isSQLite:
                        colValues.append('1' if col else '0')
                    else:
                        colValues.append('TRUE' if col else 'FALSE')
                elif isinstance(col, int):
                    colValues.append(str(col))
                elif isinstance(col, float):
                    if _ISFINITE(col):
                        colValues.append(str(col))
                    else:  # no NaN, INF, in SQL implementations (Postgres has it but not IEEE implementation)
                        colValues.append('NULL')
                elif isinstance(col, Decimal):
                    if col.is_finite():
                        colValues.append(str(col))
                    else:  # no NaN, INF, in SQL implementations (Postgres has it but not IEEE implementation)
                        colValues.append('NULL')
                elif isinstance(col, (datetime.date, datetime.datetime)) and self.product == "orcl":
                    colValues.append("DATE '{:04}-{:02}-{:02}'".format(col.year, col.month, col.day))
                elif isinstance(col, datetime.datetime) and (isMSSql or isSQLite):
                    colValues.append("'{:04}-{:02}-{:02} {:02}:{:02}:{:02}'".format(col.year, col.month, col.day, col.hour, col.minute, col.second))
                elif isinstance(col, datetime.date) and (isMSSql or isSQLite):
                    colValues.append("'{:04}-{:02}-{:02}'".format(col.year, col.month, col.day))
                elif col is None:
                    colValues.append('NULL')
                elif isinstance(col, _STR_BASE) and len(col) >= 4000 and (isOracle or isMSSql):
                    if isOracle:
                        colName = "col{}".format(len(colValues))
                        longColValues[colName] = col
                        colValues.append(":" + colName)
                    else:
                        longColValues.append(col)
                        colValues.append("?")
                else:
                    colValues.append(self.dbStr(col))
            if not rowValues and isPostgres:  # first row
                for i, cast in enumerate(colTypeCast):
                    if cast:
                        colValues[i] = colValues[i] + cast
            rowColValues = ", ".join(colValues)
            rowValues.append("(" + rowColValues + ")" if not isOracle else rowColValues)
            if longColValues:
                rowLongValues.append(longColValues)
                if isOracle:
                    longColValues = {} # must be new instance of dict
                else:
                    longColValues = []
            else:
                rowLongValues.append(None)
        values = ", \n".join(rowValues)
        
        _table = self.dbTableName(table)
        _inputTableName = self.tempInputTableName
        if self.product == "postgres":
            # insert new rows, return id and cols of new and existing rows
            # use IS NOT DISTINCT FROM instead of = to compare NULL usefully
            sql = [(('''
WITH row_values (%(newCols)s) AS (
  VALUES %(values)s
  )''' + (''', insertions AS (
  INSERT INTO %(table)s (%(newCols)s)
  SELECT %(newCols)s
  FROM row_values v''' + ('''
  WHERE NOT EXISTS (SELECT 1 
                    FROM %(table)s x 
                    WHERE %(match)s)''' if checkIfExisting else '') + '''
  RETURNING %(returningCols)s
) ''' if insertIfNotMatched else '') + '''
(''' + (('''
   SELECT %(x_returningCols)s %(statusIfExisting)s
   FROM %(table)s x JOIN row_values v ON (%(match)s) ''' if checkIfExisting else '') + ('''
) UNION ( ''' if (checkIfExisting and insertIfNotMatched) else '') + ('''
   SELECT %(returningCols)s %(statusIfInserted)s
   FROM insertions''' if insertIfNotMatched else '')) + '''
);''') %        {"table": _table,
                 "idCol": idCol,
                 "newCols": ', '.join(newCols),
                 "returningCols": ', '.join(returningCols),
                 "x_returningCols": ', '.join('x.{0}'.format(c) for c in returningCols),
                 "match": ' AND '.join('x.{0} {1} v.{0}'.format(col, comparisonOperator) 
                                    for col in matchCols),
                 "values": values,
                 "statusIfInserted": ", FALSE" if returnExistenceStatus else "",
                 "statusIfExisting": ", TRUE" if returnExistenceStatus else ""
                 }, None, True)]
        elif self.product == "mysql":
            sql = [("CREATE TEMPORARY TABLE %(inputTable)s ( %(inputCols)s );" %
                        {"inputTable": _inputTableName,
                         "inputCols": ', '.join('{0} {1}'.format(newCol, colDeclarations[newCol])
                                                for newCol in newCols)}, None, False),
                   ("INSERT INTO %(inputTable)s ( %(newCols)s ) VALUES %(values)s;" %     
                        {"inputTable": _inputTableName,
                         "newCols": ', '.join(newCols),
                         "values": values}, None, False)]
            if insertIfNotMatched:
                if checkIfExisting:
                    _where = ('WHERE NOT EXISTS (SELECT 1 FROM %(table)s x WHERE %(match)s)' %
                              {"table": _table,
                               "match": ' AND '.join('x.{0} {1} i.{0}'.format(col, comparisonOperator) 
                                                     for col in matchCols)})
                    _whereLock = (", %(table)s AS x READ" % {"table": _table})
                else:
                    _where = "";
                    _whereLock = ""
                sql.append( ("LOCK TABLES %(table)s WRITE %(whereLock)s" %
                             {"table": _table,
                              "whereLock": _whereLock}, None, False) )
                sql.append( ("INSERT INTO %(table)s ( %(newCols)s ) SELECT %(newCols)s FROM %(inputTable)s i %(where)s;" %     
                                {"inputTable": _inputTableName,
                                 "table": _table,
                                 "newCols": ', '.join(newCols),
                                 "where": _where}, None, False) )
            elif returnMatches or returnExistenceStatus:
                sql.append( ("LOCK TABLES %(table)s READ" %
                             {"table": _table}, None, False) )
            # don't know how to get status if existing
            if returnMatches or returnExistenceStatus:
                sql.append( ("SELECT %(returningCols)s %(statusIfExisting)s from %(inputTable)s JOIN %(table)s ON ( %(match)s );" %
                                {"inputTable": _inputTableName,
                                 "table": _table,
                                 "newCols": ', '.join(newCols),
                                 "match": ' AND '.join('{0}.{2} = {1}.{2}'.format(_table,_inputTableName,col) 
                                            for col in matchCols),
                                 "statusIfExisting": ", FALSE" if returnExistenceStatus else "",
                                 "returningCols": ', '.join('{0}.{1}'.format(_table,col)
                                                            for col in returningCols)}, None, True) )
            sql.append( ("DROP TEMPORARY TABLE %(inputTable)s;" %
                         {"inputTable": _inputTableName}, None, False) )
        elif self.product == "mssql":
            sql = [("CREATE TABLE #%(inputTable)s ( %(inputCols)s );" %
                        {"inputTable": _inputTableName,
                         "inputCols": ', '.join('{0} {1}'.format(newCol, colDeclarations[newCol])
                                                for newCol in newCols)}, None, False)]
            # break values insertion into 1000's each
            def insertMSSqlRows(i, j, params):
                sql.append(("INSERT INTO #%(inputTable)s ( %(newCols)s ) VALUES %(values)s;" %     
                        {"inputTable": _inputTableName,
                         "newCols": ', '.join(newCols),
                         "values": ", ".join(rowValues[i:j])}, params, False))
            iMax = len(rowValues)
            i = 0
            while (i < iMax):
                for j in range(i, min(i+1000, iMax)):
                    if rowLongValues[j] is not None:
                        if i > j:
                            insertMSSqlRows(i, j, None)
                        insertMSSqlRows(j, j+1, rowLongValues[j])
                        i = j + 1
                        break
                if i < j+1 and i < iMax:
                    insertMSSqlRows(i, j+1, None)
                    i = j+1
            if insertIfNotMatched:
                sql.append(("MERGE INTO %(table)s USING #%(inputTable)s ON (%(match)s) "
                            "WHEN NOT MATCHED THEN INSERT (%(newCols)s) VALUES (%(values)s);" %
                            {"inputTable": _inputTableName,
                             "table": _table,
                             "newCols": ', '.join(newCols),
                             "match": ' AND '.join('{0}.{2} = #{1}.{2}'.format(_table,_inputTableName,col) 
                                        for col in matchCols),
                             "values": ', '.join("#{0}.{1}".format(_inputTableName,newCol)
                                                 for newCol in newCols)}, None, False))
            if returnMatches or returnExistenceStatus:
                sql.append(# don't know how to get status if existing
                       ("SELECT %(returningCols)s %(statusIfExisting)s from #%(inputTable)s JOIN %(table)s ON ( %(match)s );" %
                            {"inputTable": _inputTableName,
                             "table": _table,
                             "newCols": ', '.join(newCols),
                             "match": ' AND '.join('{0}.{2} = #{1}.{2}'.format(_table,_inputTableName,col) 
                                        for col in matchCols),
                             "statusIfExisting": ", 0" if returnExistenceStatus else "",
                             "returningCols": ', '.join('{0}.{1}'.format(_table,col)
                                                        for col in returningCols)}, None, True))
            sql.append(("DROP TABLE #%(inputTable)s;" %
                         {"inputTable": _inputTableName}, None, False))
        elif self.product == "orcl":
            sql = [("CREATE GLOBAL TEMPORARY TABLE %(inputTable)s ( %(inputCols)s )" %
                        {"inputTable": _inputTableName,
                         "inputCols": ', '.join('{0} {1}'.format(newCol, colDeclarations[newCol])
                                                for newCol in newCols)}, None, False)]
            # break values insertion into 1000's each
            def insertOrclRows(i, j, params):
                sql.append(("INSERT INTO %(inputTable)s ( %(newCols)s ) %(values)s" %     
                        {"inputTable": _inputTableName,
                         "newCols": ', '.join(newCols),
                         "values": "\nUNION ALL".join(" SELECT {} FROM dual ".format(r)
                                                      for r in rowValues[i:j])}, params, False))
            iMax = len(rowValues)
            i = 0
            while (i < iMax):
                for j in range(i, min(i+1000, iMax)):
                    if rowLongValues[j] is not None:
                        if j > i:
                            insertOrclRows(i, j, None)
                        insertOrclRows(j, j+1, rowLongValues[j])
                        i = j + 1
                        break
                if i < j+1 and i < iMax:
                    insertOrclRows(i, j+1, None)
                    i = j+1
            if insertIfNotMatched:
                sql.append(("MERGE INTO %(table)s USING %(inputTable)s ON (%(match)s) "
                            "WHEN NOT MATCHED THEN INSERT (%(newCols)s) VALUES (%(values)s)" %
                            {"inputTable": _inputTableName,
                             "table": _table,
                             "newCols": ', '.join(newCols),
                             "match": ' AND '.join('{0}.{2} = {1}.{2}'.format(_table,_inputTableName,col) 
                                        for col in matchCols),
                             "values": ', '.join("{0}.{1}".format(_inputTableName,newCol)
                                                 for newCol in newCols)}, None, False))
            if returnMatches or returnExistenceStatus:
                sql.append(# don't know how to get status if existing
                       ("SELECT %(returningCols)s %(statusIfExisting)s from %(inputTable)s JOIN %(table)s ON ( %(match)s )" %
                            {"inputTable": _inputTableName,
                             "table": _table,
                             "newCols": ', '.join(newCols),
                             "match": ' AND '.join('{0}.{2} = {1}.{2}'.format(_table,_inputTableName,col) 
                                        for col in matchCols),
                             "statusIfExisting": ", 0" if returnExistenceStatus else "",
                             "returningCols": ', '.join('{0}.{1}'.format(_table,col)
                                                        for col in returningCols)}, None, True))
            sql.append(("DROP TABLE %(inputTable)s" %
                         {"inputTable": _inputTableName}, None, False))
        elif self.product == "sqlite":
            sql = [("CREATE TEMP TABLE %(inputTable)s ( %(inputCols)s );" %
                        {"inputTable": _inputTableName,
                         "inputCols": ', '.join('{0} {1}'.format(newCol, colDeclarations[newCol])
                                                for newCol in newCols)}, None, False)]
            # break values insertion into 1000's each
            def insertSQLiteRows(i, j, params):
                sql.append(("INSERT INTO %(inputTable)s ( %(newCols)s ) VALUES %(values)s;" %     
                        {"inputTable": _inputTableName,
                         "newCols": ', '.join(newCols),
                         "values": ", ".join(rowValues[i:j])}, params, False))
            iMax = len(rowValues)
            i = 0
            while (i < iMax):
                for j in range(i, min(i+500, iMax)):
                    if rowLongValues[j] is not None:
                        if j > i:
                            insertSQLiteRows(i, j, None)
                        insertSQLiteRows(j, j+1, rowLongValues[j])
                        i = j + 1
                        break
                if i < j+1 and i < iMax:
                    insertSQLiteRows(i, j+1, None)
                    i = j+1
            if insertIfNotMatched:
                if checkIfExisting:
                    _where = ('WHERE NOT EXISTS (SELECT 1 FROM %(table)s x WHERE %(match)s)' %
                              {"table": _table,
                               "match": ' AND '.join('x.{0} {1} i.{0}'.format(col, comparisonOperator) 
                                                     for col in matchCols)})
                else:
                    _where = "";
                sql.append( ("INSERT INTO %(table)s ( %(newCols)s ) SELECT %(newCols)s FROM %(inputTable)s i %(where)s;" %     
                                {"inputTable": _inputTableName,
                                 "table": _table,
                                 "newCols": ', '.join(newCols),
                                 "where": _where}, None, False) )
            if returnMatches or returnExistenceStatus:
                sql.append(# don't know how to get status if existing
                       ("SELECT %(returningCols)s %(statusIfExisting)s from %(inputTable)s JOIN %(table)s ON ( %(match)s );" %
                            {"inputTable": _inputTableName,
                             "table": _table,
                             "newCols": ', '.join(newCols),
                             "match": ' AND '.join('{0}.{2} = {1}.{2}'.format(_table,_inputTableName,col) 
                                        for col in matchCols),
                             "statusIfExisting": ", 0" if returnExistenceStatus else "",
                             "returningCols": ', '.join('{0}.{1}'.format(_table,col)
                                                        for col in returningCols)}, None, True))
            sql.append(("DROP TABLE %(inputTable)s;" %
                         {"inputTable": _inputTableName}, None, False))
            if insertIfNotMatched and self.syncSequences:
                sql.append( ("update sqlite_sequence "
                             "set seq = (select seq from sqlite_sequence where name = '%(table)s') " 
                             "where name != '%(table)s';" % 
                              {"table": _table}, None, False) )
        if TRACESQLFILE:
            with io.open(TRACESQLFILE, "a", encoding='utf-8') as fh:
                fh.write("\n\n>>> accession {0} table {1} sql length {2} row count {3}\n"
                         .format(self.accessionId, table, len(sql), len(data)))
                for sqlStmt, params, fetch in sql:
                    fh.write("\n    " + sqlStmt + "\n     {}".format(params if params else ""))
        tableRows = []
        for sqlStmt, params, fetch in sql:
            if params and isOracle:
                self.cursor.setinputsizes(**dict((name,oracleNCLOB) for name in params))
            result = self.execute(sqlStmt,commit=commit, close=False, fetch=fetch, params=params)
            if fetch and result:
                tableRows.extend(result)

        if TRACESQLFILE:
            with io.open(TRACESQLFILE, "a", encoding='utf-8') as fh:
                fh.write("\n\n>>> accession {0} table {1} result row count {2}\n{3}\n"
                         .format(self.accessionId, table, len(tableRows), '\n'.join(str(r) for r in tableRows)))
        return tuple(tuple(None if colValue == "NULL" or colValue is None else
                           colTypeFunction[i](colValue)  # convert to int, datetime, etc
                           for i, colValue in enumerate(row))
                     for row in tableRows)
dataset/ETHPy150Open Arelle/Arelle/arelle/plugin/xbrlDB/SqlDb.py/SqlDbConnection.getTable/swapped operands of `>`