labpack/databases/sql.py
def export(self, sql_table, merge_rule='skip', coerce=False):

        '''
            a method to export all the records in table to another sql table

        :param sql_table: class object with sql table methods
        :param merge_rule: string with name of rule to adopt for pre-existing records
        :param coerce: boolean to enable migration even if table schemas don't match
        :return: string with exit message

        NOTE:   available merge rules include: overwrite, skip and upsert
        '''

        title = '%s.export' % self.__class__.__name__

        # validate sql client
        # TODO reduce method list requirements for other jsonified databases
        method_list = ['list', 'create', 'read', 'update', 'delete', 'remove', 'export', 'exists', '_construct_inserts', '_parse_columns', '_compare_columns', 'table', 'session', 'table_name', 'database_name']
        for method in method_list:
            if getattr(sql_table, method, None) == None:
                from labpack.parsing.grammar import join_words
                raise ValueError(
                    '%s(sql_table=...) must be a client object with %s methods.' % (title, join_words(method_list)))

        # verbosity
        export_name = self.table_name
        import_name = sql_table.table_name
        self.printer('Migrating %s table in %s database to %s table in %s database' % (
        export_name, self.database_name, import_name, sql_table.database_name), flush=True)

        # determine differences between tables
        export_columns = self._parse_columns()
        import_columns = sql_table._parse_columns()
        add_columns, remove_columns, rename_columns, retype_columns, resize_columns = self._compare_columns(
            import_columns, export_columns)
        if remove_columns or retype_columns or resize_columns:
            if not coerce:
                raise ValueError(
                    "Migration from %s to %s prevented because schemas don't match and data could be lost." % (
                    export_name, import_name))

        # define upsert reconstructor
        def _reconstruct_upsert(update_kwargs):
            record_details = {}
            current_details = record_details
            for key, value in update_kwargs.items():
                record_key = key
                record_value = value
                record_segments = record_key.split('.')
                for i in range(len(record_segments)):
                    segment = record_segments[i]
                    if i + 1 < len(record_segments):
                        if segment not in record_details.keys():
                            current_details[segment] = {}
                        current_details = current_details[segment]
                    else:
                        if isinstance(record_value, bytes):
                            current_details[segment] = pickle.loads(record_value)
                        else:
                            current_details[segment] = record_value
                current_details = record_details
            return record_details

        # migrate records from old to new
        list_statement = self.table.select()
        count = 0
        added = 0
        skipped = 0
        upserted = 0
        overwritten = 0
        for record in self.session.execute(list_statement).fetchall():
            record_details = self._reconstruct_record(record)
            record_id = record_details['id']
            if not sql_table.exists(record_id):
                create_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                insert_statement = sql_table.table.insert().values(**create_kwargs)
                sql_table.session.execute(insert_statement)
                added += 1
            elif merge_rule == 'overwrite':
                sql_table.delete(record_id)
                create_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                insert_statement = sql_table.table.insert().values(**create_kwargs)
                sql_table.session.execute(insert_statement)
                overwritten += 1
            elif merge_rule == 'skip':
                skipped += 1
            elif merge_rule == 'update':
                update_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                update_details = _reconstruct_upsert(update_kwargs)
                sql_table.update(update_details)
                upserted += 1
            count = added + overwritten + skipped + upserted
            if not count % 10:
                self.printer('.', flush=True)

        # handle verbosity
        self.printer(' done.')

        # report outcome
        plural = ''
        skip_insert = ''
        overwrite_insert = ''
        upsert_insert = ''
        if added != 1:
            plural = 's'
        if skipped > 0:
            skip_plural = ''
            if skipped > 1:
                skip_plural = 's'
            skip_insert = ' %s record%s skipped to avoid overwrite.' % (str(skipped), skip_plural)
        if overwritten > 0:
            overwrite_plural = ''
            if overwritten > 1:
                overwrite_plural = 's'
            overwrite_insert = ' %s record%s overwritten.' % (str(overwritten), overwrite_plural)
        if upserted > 0:
            upsert_plural = ''
            if upserted > 1:
                upsert_plural = 's'
            upsert_insert = ' %s record%s updated.' % (str(upserted), upsert_plural)
        exit_msg = '%s record%s added to %s.%s%s%s' % (
        str(added), plural, import_name, skip_insert, overwrite_insert, upsert_insert)
        self.printer(exit_msg)

        return count

            a method to export all the records in table to another sql table

        :param sql_table: class object with sql table methods
        :param merge_rule: string with name of rule to adopt for pre-existing records
        :param coerce: boolean to enable migration even if table schemas don't match
        :return: string with exit message

        NOTE:   available merge rules include: overwrite, skip and upsert
        
a method to export all the records in table to another sql table
a method to export all the records in table to another sql table
def export(self, sql_table, merge_rule='skip', coerce=False):
        title = '%s.export' % self.__class__.__name__
        method_list = ['list', 'create', 'read', 'update', 'delete', 'remove', 'export', 'exists', '_construct_inserts', '_parse_columns', '_compare_columns', 'table', 'session', 'table_name', 'database_name']
        for method in method_list:
            if getattr(sql_table, method, None) == None:
                from labpack.parsing.grammar import join_words
                raise ValueError(
                    '%s(sql_table=...) must be a client object with %s methods.' % (title, join_words(method_list)))
        export_name = self.table_name
        import_name = sql_table.table_name
        self.printer('Migrating %s table in %s database to %s table in %s database' % (
        export_name, self.database_name, import_name, sql_table.database_name), flush=True)
        export_columns = self._parse_columns()
        import_columns = sql_table._parse_columns()
        add_columns, remove_columns, rename_columns, retype_columns, resize_columns = self._compare_columns(
            import_columns, export_columns)
        if remove_columns or retype_columns or resize_columns:
            if not coerce:
                raise ValueError(
                    "Migration from %s to %s prevented because schemas don't match and data could be lost." % (
                    export_name, import_name))
        def _reconstruct_upsert(update_kwargs):
            record_details = {}
            current_details = record_details
            for key, value in update_kwargs.items():
                record_key = key
                record_value = value
                record_segments = record_key.split('.')
                for i in range(len(record_segments)):
                    segment = record_segments[i]
                    if i + 1 < len(record_segments):
                        if segment not in record_details.keys():
                            current_details[segment] = {}
                        current_details = current_details[segment]
                    else:
                        if isinstance(record_value, bytes):
                            current_details[segment] = pickle.loads(record_value)
                        else:
                            current_details[segment] = record_value
                current_details = record_details
            return record_details
        list_statement = self.table.select()
        count = 0
        added = 0
        skipped = 0
        upserted = 0
        overwritten = 0
        for record in self.session.execute(list_statement).fetchall():
            record_details = self._reconstruct_record(record)
            record_id = record_details['id']
            if not sql_table.exists(record_id):
                create_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                insert_statement = sql_table.table.insert().values(**create_kwargs)
                sql_table.session.execute(insert_statement)
                added += 1
            elif merge_rule == 'overwrite':
                sql_table.delete(record_id)
                create_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                insert_statement = sql_table.table.insert().values(**create_kwargs)
                sql_table.session.execute(insert_statement)
                overwritten += 1
            elif merge_rule == 'skip':
                skipped += 1
            elif merge_rule == 'update':
                update_kwargs = self._construct_inserts(record, import_columns, rename_columns, retype_columns,
                                                        resize_columns)
                update_details = _reconstruct_upsert(update_kwargs)
                sql_table.update(update_details)
                upserted += 1
            count = added + overwritten + skipped + upserted
            if not count % 10:
                self.printer('.', flush=True)
        self.printer(' done.')
        plural = ''
        skip_insert = ''
        overwrite_insert = ''
        upsert_insert = ''
        if added != 1:
            plural = 's'
        if skipped > 0:
            skip_plural = ''
            if skipped > 1:
                skip_plural = 's'
            skip_insert = ' %s record%s skipped to avoid overwrite.' % (str(skipped), skip_plural)
        if overwritten > 0:
            overwrite_plural = ''
            if overwritten > 1:
                overwrite_plural = 's'
            overwrite_insert = ' %s record%s overwritten.' % (str(overwritten), overwrite_plural)
        if upserted > 0:
            upsert_plural = ''
            if upserted > 1:
                upsert_plural = 's'
            upsert_insert = ' %s record%s updated.' % (str(upserted), upsert_plural)
        exit_msg = '%s record%s added to %s.%s%s%s' % (
        str(added), plural, import_name, skip_insert, overwrite_insert, upsert_insert)
        self.printer(exit_msg)
        return count
def export ( self , sql_table , merge_rule = 'skip' , coerce = False ) : title = '%s.export' % self . __class__ . __name__ method_list = [ 'list' , 'create' , 'read' , 'update' , 'delete' , 'remove' , 'export' , 'exists' , '_construct_inserts' , '_parse_columns' , '_compare_columns' , 'table' , 'session' , 'table_name' , 'database_name' ] for method in method_list : if getattr ( sql_table , method , None ) == None : from labpack . parsing . grammar import join_words raise ValueError ( '%s(sql_table=...) must be a client object with %s methods.' % ( title , join_words ( method_list ) ) ) export_name = self . table_name import_name = sql_table . table_name self . printer ( 'Migrating %s table in %s database to %s table in %s database' % ( export_name , self . database_name , import_name , sql_table . database_name ) , flush = True ) export_columns = self . _parse_columns ( ) import_columns = sql_table . _parse_columns ( ) add_columns , remove_columns , rename_columns , retype_columns , resize_columns = self . _compare_columns ( import_columns , export_columns ) if remove_columns or retype_columns or resize_columns : if not coerce : raise ValueError ( "Migration from %s to %s prevented because schemas don't match and data could be lost." % ( export_name , import_name ) ) def _reconstruct_upsert ( update_kwargs ) : record_details = { } current_details = record_details for key , value in update_kwargs . items ( ) : record_key = key record_value = value record_segments = record_key . split ( '.' ) for i in range ( len ( record_segments ) ) : segment = record_segments [ i ] if i + 1 < len ( record_segments ) : if segment not in record_details . keys ( ) : current_details [ segment ] = { } current_details = current_details [ segment ] else : if isinstance ( record_value , bytes ) : current_details [ segment ] = pickle . loads ( record_value ) else : current_details [ segment ] = record_value current_details = record_details return record_details list_statement = self . table . select ( ) count = 0 added = 0 skipped = 0 upserted = 0 overwritten = 0 for record in self . session . execute ( list_statement ) . fetchall ( ) : record_details = self . _reconstruct_record ( record ) record_id = record_details [ 'id' ] if not sql_table . exists ( record_id ) : create_kwargs = self . _construct_inserts ( record , import_columns , rename_columns , retype_columns , resize_columns ) insert_statement = sql_table . table . insert ( ) . values ( ** create_kwargs ) sql_table . session . execute ( insert_statement ) added += 1 elif merge_rule == 'overwrite' : sql_table . delete ( record_id ) create_kwargs = self . _construct_inserts ( record , import_columns , rename_columns , retype_columns , resize_columns ) insert_statement = sql_table . table . insert ( ) . values ( ** create_kwargs ) sql_table . session . execute ( insert_statement ) overwritten += 1 elif merge_rule == 'skip' : skipped += 1 elif merge_rule == 'update' : update_kwargs = self . _construct_inserts ( record , import_columns , rename_columns , retype_columns , resize_columns ) update_details = _reconstruct_upsert ( update_kwargs ) sql_table . update ( update_details ) upserted += 1 count = added + overwritten + skipped + upserted if not count % 10 : self . printer ( '.' , flush = True ) self . printer ( ' done.' ) plural = '' skip_insert = '' overwrite_insert = '' upsert_insert = '' if added != 1 : plural = 's' if skipped > 0 : skip_plural = '' if skipped > 1 : skip_plural = 's' skip_insert = ' %s record%s skipped to avoid overwrite.' % ( str ( skipped ) , skip_plural ) if overwritten > 0 : overwrite_plural = '' if overwritten > 1 : overwrite_plural = 's' overwrite_insert = ' %s record%s overwritten.' % ( str ( overwritten ) , overwrite_plural ) if upserted > 0 : upsert_plural = '' if upserted > 1 : upsert_plural = 's' upsert_insert = ' %s record%s updated.' % ( str ( upserted ) , upsert_plural ) exit_msg = '%s record%s added to %s.%s%s%s' % ( str ( added ) , plural , import_name , skip_insert , overwrite_insert , upsert_insert ) self . printer ( exit_msg ) return count
a method to export all the records in table to another sql table
a method to export all the records in table to another sql table
'''
            a method to export all the records in table to another sql table

        :param sql_table: class object with sql table methods
        :param merge_rule: string with name of rule to adopt for pre-existing records
        :param coerce: boolean to enable migration even if table schemas don't match
        :return: string with exit message

        NOTE:   available merge rules include: overwrite, skip and upsert
        ''' # validate sql client # TODO reduce method list requirements for other jsonified databases # verbosity # determine differences between tables # define upsert reconstructor # migrate records from old to new # handle verbosity # report outcome
string with exit message
NOTE:   available merge rules include: overwrite, skip and upsert
string with exit message NOTE : available merge rules include : overwrite skip and upsert
self
sql_table
class object with sql table methods
class object with sql table methods
merge_rule
string with name of rule to adopt for pre-existing records
string with name of rule to adopt for pre - existing records
coerce
boolean to enable migration even if table schemas don't match
boolean to enable migration even if table schemas don ' t match