def CreatePerObjectClassTable(self, classes):
        '''
    	Saves object keys and classes to a SQL table
    	'''
        p = Properties.getInstance()
        if p.class_table is None:
            raise ValueError('"class_table" in properties file is not set.')

        index_cols = dbconnect.UniqueObjectClause()
        class_cols = dbconnect.UniqueObjectClause() + ', class, class_number'
        class_col_defs = dbconnect.object_key_defs() + ', class VARCHAR (%d)'%(max([len(c.label) for c in self.classBins])+1) + ', class_number INT'

        # Drop must be explicitly asked for Classifier.ScoreAll
        db = dbconnect.DBConnect.getInstance()
        db.execute('DROP TABLE IF EXISTS %s'%(p.class_table))
        db.execute('CREATE TABLE %s (%s)'%(p.class_table, class_col_defs))
        db.execute('CREATE INDEX idx_%s ON %s (%s)'%(p.class_table, p.class_table, index_cols))
        for clNum, clName in enumerate(self.perClassObjects.keys()):
            for obj in self.perClassObjects[clName]:
                query = ''.join(['INSERT INTO ',p.class_table,' (',class_cols,') VALUES (',str(obj[0]),', ',str(obj[1]),', "',clName,'", ',str(clNum+1),')'])
                db.execute(query)

        if p.db_type.lower() == 'mysql':
            query = ''.join(['ALTER TABLE ',p.class_table,' ORDER BY ',p.image_id,' ASC, ',p.object_id,' ASC'])
            db.execute(query)
            db.Commit()
Ejemplo n.º 2
0
def create_perobject_class_table(classifier, classNames):
    '''
    classifier: generalclassifier object
    classNames: list/array of class names
    RETURNS: Saves table with columns Table Number, Image Number, Object Number, class number, class name to a pre defined
    table in the database (the class number is the predicted class)
    '''
    nClasses = len(classNames)

    if p.class_table is None:
        raise ValueError('"class_table" in properties file is not set.')

    index_cols = UniqueObjectClause()
    class_cols = UniqueObjectClause() + ', class_number, class'
    class_col_defs = object_key_defs() + ', class VARCHAR (%d)'%(3) + ', class_number INT'


    # Drop must be explicitly asked for Classifier.ScoreAll
    print('Drop table...')
    db.execute('DROP TABLE IF EXISTS %s'%(p.class_table))
    print('Create table...')
    db.execute('CREATE TABLE %s (%s)'%(p.class_table, class_col_defs))
    print('Create index...')
    db.execute('CREATE INDEX idx_%s ON %s (%s)'%(p.class_table, p.class_table, index_cols))

    print('Getting data...')
    number_of_features = len(db.GetColnamesForClassifier())
    wheres = _where_clauses(p, dm, None)
    data = []
    for idx, where_clause in enumerate(wheres):
        data = db.execute('SELECT %s, %s FROM %s '
                          '%s WHERE %s'
                          %(UniqueObjectClause(p.object_table),
                            ",".join(db.GetColnamesForClassifier()), p.object_table,'', where_clause),
                          silent=(idx > 10))
        #data.extend(result)
        #print('Getting predictions...')
        cell_data, object_keys = processData(data)

        predicted_classes = classifier.Predict(cell_data)
        #print('Writing to database...')
        if len(object_keys.shape) > 2:
            expr = 'CASE '+ ''.join(["WHEN TableNumber=%d AND ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], object_keys[ii][2], predicted_classes[ii] )
                for ii in range(0, len(predicted_classes))])+ " END"
            expr2 = 'CASE '+ ''.join(["WHEN TableNumber=%d AND ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], object_keys[ii][2],
                classNames[predicted_classes[ii] - 1]) for ii in range(0, len(predicted_classes))])+ " END"
        elif len(object_keys.shape) == 2:
            expr = 'CASE '+ ''.join(["WHEN ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], predicted_classes[ii] )
                for ii in range(0, len(predicted_classes))])+ " END"
            expr2 = 'CASE '+ ''.join(["WHEN ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], classNames[predicted_classes[ii] - 1])
                for ii in range(0, len(predicted_classes))])+ " END"
        db.execute('INSERT INTO %s (%s) SELECT %s, %s, %s FROM %s'%(p.class_table, class_cols, index_cols, expr, expr2, p.object_table),
            silent=True)
        print(idx)
    db.Commit()
Ejemplo n.º 3
0
def create_perobject_class_table(classifier, classNames):
    '''
    classifier: generalclassifier object
    classNames: list/array of class names
    RETURNS: Saves table with columns Table Number, Image Number, Object Number, class number, class name to a pre defined
    table in the database (the class number is the predicted class)
    '''
    nClasses = len(classNames)

    if p.class_table is None:
        raise ValueError('"class_table" in properties file is not set.')

    index_cols = UniqueObjectClause()
    class_cols = UniqueObjectClause() + ', class_number, class'
    class_col_defs = object_key_defs() + ', class VARCHAR (%d)'%(3) + ', class_number INT'


    # Drop must be explicitly asked for Classifier.ScoreAll
    print('Drop table...')
    db.execute('DROP TABLE IF EXISTS %s'%(p.class_table))
    print('Create table...')
    db.execute('CREATE TABLE %s (%s)'%(p.class_table, class_col_defs))
    print('Create index...')
    db.execute('CREATE INDEX idx_%s ON %s (%s)'%(p.class_table, p.class_table, index_cols))

    print('Getting data...')
    number_of_features = len(db.GetColnamesForClassifier())
    wheres = _where_clauses(p, dm, None)
    data = []
    for idx, where_clause in enumerate(wheres):
        data = db.execute('SELECT %s, %s FROM %s '
                          '%s WHERE %s'
                          %(UniqueObjectClause(p.object_table),
                            ",".join(db.GetColnamesForClassifier()), p.object_table,'', where_clause),
                          silent=(idx > 10))
        #data.extend(result)
        #print('Getting predictions...')
        cell_data, object_keys = processData(data)

        predicted_classes = classifier.Predict(cell_data)
        #print('Writing to database...')
        if len(object_keys.shape) > 2:
            expr = 'CASE '+ ''.join(["WHEN TableNumber=%d AND ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], object_keys[ii][2], predicted_classes[ii] )
                for ii in range(0, len(predicted_classes))])+ " END"
            expr2 = 'CASE '+ ''.join(["WHEN TableNumber=%d AND ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], object_keys[ii][2],
                classNames[predicted_classes[ii] - 1]) for ii in range(0, len(predicted_classes))])+ " END"
        elif len(object_keys.shape) == 2:
            expr = 'CASE '+ ''.join(["WHEN ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], predicted_classes[ii] )
                for ii in range(0, len(predicted_classes))])+ " END"
            expr2 = 'CASE '+ ''.join(["WHEN ImageNumber=%d AND ObjectNumber=%d THEN '%s'"%(
                object_keys[ii][0], object_keys[ii][1], classNames[predicted_classes[ii] - 1])
                for ii in range(0, len(predicted_classes))])+ " END"
        db.execute('INSERT INTO %s (%s) SELECT %s, %s, %s FROM %s'%(p.class_table, class_cols, index_cols, expr, expr2, p.object_table),
            silent=True)
        print(idx)
    db.Commit()
    def CreatePerObjectClassTable(self, classes):
        '''
    	Saves object keys and classes to a SQL table
    	'''
        p = Properties.getInstance()
        if p.class_table is None:
            raise ValueError('"class_table" in properties file is not set.')

        index_cols = dbconnect.UniqueObjectClause()
        class_cols = dbconnect.UniqueObjectClause() + ', class, class_number'
        class_col_defs = dbconnect.object_key_defs(
        ) + ', class VARCHAR (%d)' % (
            max([len(c.label)
                 for c in self.classBins]) + 1) + ', class_number INT'

        # Drop must be explicitly asked for Classifier.ScoreAll
        db = dbconnect.DBConnect.getInstance()
        db.execute('DROP TABLE IF EXISTS %s' % (p.class_table))
        db.execute('CREATE TABLE %s (%s)' % (p.class_table, class_col_defs))
        db.execute('CREATE INDEX idx_%s ON %s (%s)' %
                   (p.class_table, p.class_table, index_cols))
        for clNum, clName in enumerate(self.perClassObjects.keys()):
            for obj in self.perClassObjects[clName]:
                query = ''.join([
                    'INSERT INTO ', p.class_table, ' (', class_cols,
                    ') VALUES (',
                    str(obj[0]), ', ',
                    str(obj[1]), ', "', clName, '", ',
                    str(clNum + 1), ')'
                ])
                db.execute(query)

        if p.db_type.lower() == 'mysql':
            query = ''.join([
                'ALTER TABLE ', p.class_table, ' ORDER BY ', p.image_id,
                ' ASC, ', p.object_id, ' ASC'
            ])
            db.execute(query)
            db.Commit()