def insert_rows(self, table, rows, target_fields=None, commit_every=1000):
        """
        A generic way to insert a set of tuples into a table,
        the whole set of inserts is treated as one transaction
        Changes from standard DbApiHook implementation:

        - Oracle SQL queries in cx_Oracle can not be terminated with a semicolon (`;`)
        - Replace NaN values with NULL using `numpy.nan_to_num` (not using
          `is_nan()` because of input types error for strings)
        - Coerce datetime cells to Oracle DATETIME format during insert

        :param table: target Oracle table, use dot notation to target a
            specific database
        :type table: str
        :param rows: the rows to insert into the table
        :type rows: iterable of tuples
        :param target_fields: the names of the columns to fill in the table
        :type target_fields: iterable of str
        :param commit_every: the maximum number of rows to insert in one transaction
            Default 1000, Set greater than 0.
            Set 1 to insert each row in each single transaction
        :type commit_every: int
        """
        if target_fields:
            target_fields = ', '.join(target_fields)
            target_fields = '({})'.format(target_fields)
        else:
            target_fields = ''
        conn = self.get_conn()
        cur = conn.cursor()
        if self.supports_autocommit:
            cur.execute('SET autocommit = 0')
        conn.commit()
        i = 0
        for row in rows:
            i += 1
            lst = []
            for cell in row:
                if isinstance(cell, basestring):
                    lst.append("'" + str(cell).replace("'", "''") + "'")
                elif cell is None:
                    lst.append('NULL')
                elif type(cell) == float and \
                        numpy.isnan(cell):  # coerce numpy NaN to NULL
                    lst.append('NULL')
                elif isinstance(cell, numpy.datetime64):
                    lst.append("'" + str(cell) + "'")
                elif isinstance(cell, datetime):
                    lst.append("to_date('" +
                               cell.strftime('%Y-%m-%d %H:%M:%S') +
                               "','YYYY-MM-DD HH24:MI:SS')")
                else:
                    lst.append(str(cell))
            values = tuple(lst)
            sql = 'INSERT /*+ APPEND */ ' \
                  'INTO {0} {1} VALUES ({2})'.format(table,
                                                     target_fields,
                                                     ','.join(values))
            cur.execute(sql)
            if i % commit_every == 0:
                conn.commit()
                self.log.info('Loaded %s into %s rows so far', i, table)
        conn.commit()
        cur.close()
        conn.close()
        self.log.info('Done loading. Loaded a total of %s rows', i)
def insert_rows(self, table, rows, target_fields=None, commit_every=1000):
        """
        A generic way to insert a set of tuples into a table,
        the whole set of inserts is treated as one transaction
        Changes from standard DbApiHook implementation:

        - Oracle SQL queries in cx_Oracle can not be terminated with a semicolon (`;`)
        - Replace NaN values with NULL using `numpy.nan_to_num` (not using
          `is_nan()` because of input types error for strings)
        - Coerce datetime cells to Oracle DATETIME format during insert

        :param table: target Oracle table, use dot notation to target a
            specific database
        :type table: str
        :param rows: the rows to insert into the table
        :type rows: iterable of tuples
        :param target_fields: the names of the columns to fill in the table
        :type target_fields: iterable of str
        :param commit_every: the maximum number of rows to insert in one transaction
            Default 1000, Set greater than 0.
            Set 1 to insert each row in each single transaction
        :type commit_every: int
        """
        if target_fields:
            target_fields = ', '.join(target_fields)
            target_fields = '({})'.format(target_fields)
        else:
            target_fields = ''
        conn = self.get_conn()
        cur = conn.cursor()
        if self.supports_autocommit:
            cur.execute('SET autocommit = 0')
        conn.commit()
        i = 0
        for row in rows:
            i += 1
            lst = []
            for cell in row:
                if isinstance(cell, basestring):
                    lst.append("'" + str(cell).replace("'", "''") + "'")
                elif cell is None:
                    lst.append('NULL')
                elif type(cell) == float and \
                        numpy.isnan(cell):  # coerce numpy NaN to NULL
                    lst.append('NULL')
                elif isinstance(cell, numpy.datetime64):
                    lst.append("'" + str(cell) + "'")
                elif isinstance(cell, datetime):
                    lst.append("to_date('" +
                               cell.strftime('%Y-%m-%d %H:%M:%S') +
                               "','YYYY-MM-DD HH24:MI:SS')")
                else:
                    lst.append(str(cell))
            values = tuple(lst)
            sql = 'INSERT /*+ APPEND */ ' \
                  'INTO {0} {1} VALUES ({2})'.format(table,
                                                     target_fields,
                                                     ','.join(values))
            cur.execute(sql)
            if i % commit_every == 0:
                conn.commit()
                self.log.info('Loaded %s into %s rows so far', i, table)
        conn.commit()
        cur.close()
        conn.close()
        self.log.info('Done loading. Loaded a total of %s rows', i)
def insert_rows ( self , table , rows , target_fields = None , commit_every = 1000 ) : if target_fields : target_fields = ', ' . join ( target_fields ) target_fields = '({})' . format ( target_fields ) else : target_fields = '' conn = self . get_conn ( ) cur = conn . cursor ( ) if self . supports_autocommit : cur . execute ( 'SET autocommit = 0' ) conn . commit ( ) i = 0 for row in rows : i += 1 lst = [ ] for cell in row : if isinstance ( cell , basestring ) : lst . append ( "'" + str ( cell ) . replace ( "'" , "''" ) + "'" ) elif cell is None : lst . append ( 'NULL' ) elif type ( cell ) == float and numpy . isnan ( cell ) : # coerce numpy NaN to NULL lst . append ( 'NULL' ) elif isinstance ( cell , numpy . datetime64 ) : lst . append ( "'" + str ( cell ) + "'" ) elif isinstance ( cell , datetime ) : lst . append ( "to_date('" + cell . strftime ( '%Y-%m-%d %H:%M:%S' ) + "','YYYY-MM-DD HH24:MI:SS')" ) else : lst . append ( str ( cell ) ) values = tuple ( lst ) sql = 'INSERT /*+ APPEND */ ' 'INTO {0} {1} VALUES ({2})' . format ( table , target_fields , ',' . join ( values ) ) cur . execute ( sql ) if i % commit_every == 0 : conn . commit ( ) self . log . info ( 'Loaded %s into %s rows so far' , i , table ) conn . commit ( ) cur . close ( ) conn . close ( ) self . log . info ( 'Done loading. Loaded a total of %s rows' , i )
A generic way to insert a set of tuples into a table,
        the whole set of inserts is treated as one transaction
        Changes from standard DbApiHook implementation:

        - Oracle SQL queries in cx_Oracle can not be terminated with a semicolon (`;`)
        - Replace NaN values with NULL using `numpy.nan_to_num` (not using
          `is_nan()` because of input types error for strings)
        - Coerce datetime cells to Oracle DATETIME format during insert

        :param table: target Oracle table, use dot notation to target a
            specific database
        :type table: str
        :param rows: the rows to insert into the table
        :type rows: iterable of tuples
        :param target_fields: the names of the columns to fill in the table
        :type target_fields: iterable of str
        :param commit_every: the maximum number of rows to insert in one transaction
            Default 1000, Set greater than 0.
            Set 1 to insert each row in each single transaction
        :type commit_every: int
A generic way to insert a set of tuples into a table the whole set of inserts is treated as one transaction Changes from standard DbApiHook implementation :