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()
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 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()
