def fill_sample_n_label():
for theme_id in range(1, 41):
# deal with sample_n_table
sample_n_table = "sample_%d" % theme_id
sql1= "select id from %s" % sample_n_table
sample_id_list = sql_utils.exe(sql1)
# print sample_id_list
# deal with img_theme_class
sql2= "select class_id from img_theme_class where theme_id = %d" % theme_id
class_id_list = sql_utils.exe(sql2)
# print class_id_list
# deal with sample_n_label_table
sample_n_label_table = "sample_%d_label" % theme_id
sql3 = "insert into " + sample_n_label_table + "(sample_id, class_id) value(%s, %s)"
params_all = []
for sample_id_dict in sample_id_list:
for class_id_dict in class_id_list:
sample_id = sample_id_dict["id"]
class_id = class_id_dict["class_id"]
params_all.append( (sample_id, class_id) )
size = len(params_all)
batch = 10000
batch_num = int(math.ceil(float(size)/batch))
for i in range(batch_num):
params = params_all[i*batch: min((i+1)*batch, size)]
# print params
sql_utils.exe_many(sql3, params)
def update_by_id(table_name, columns, id):
# deal with columns to update
if columns is None or len(columns) == 0:
print("Columns is None or len(columns) <= 1")
return
update_columns = ""
for key, value in columns.iteritems():
update_columns += key + "=" + sql_utils.sql_value_format(value) + ","
update_columns = update_columns.strip(',')
# deal with sql
sql = "update %s set %s where id=%s" % (table_name, update_columns, id)
sql_utils.exe(sql)
def insert(table_name, columns):
if columns is None or len(columns) == 0:
print("Columns is None or len(columns) == 0")
return
keys = ""
values = ""
for key, value in columns.iteritems():
keys = keys + key + ","
if isinstance(value, str):
values += "\'" + value + "\',"
elif sql_utils.is_number(str(value)):
values += str(value) + ","
else:
print("Input is not valid")
return
keys = keys.strip(',')
values = values.strip(',')
sql = "insert into %s(%s) value(%s)" % (table_name, keys, values)
sql_utils.exe(sql)
def update_where_condition(table_name, columns, where_conditions=None):
# deal with columns to update
update_columns = ""
if columns is None or len(columns) == 0:
print("Columns is None or len(columns) == 0")
return
else:
for key, value in columns.iteritems():
update_columns += key + "=" + sql_utils.sql_value_format(
value) + ","
update_columns = update_columns.strip(',')
# deal with where condition
where_clause = ""
if where_conditions is not None and len(where_conditions) >= 1:
where_clause = "where " + where_conditions[0]
for i in range(1, len(where_conditions)):
where_clause += " and " + where_conditions[i]
# deal with sql
sql = "update %s set %s %s" % (table_name, update_columns, where_clause)
sql = sql.strip()
sql_utils.exe(sql)
def check_sample_n_label():
for theme_id in range(1, 41):
# deal with sample_n_table
sample_n_table = "sample_%d" % theme_id
sql1= "select id from %s" % sample_n_table
sample_id_list = sql_utils.exe(sql1)
# deal with img_theme_class
sql2= "select class_id from img_theme_class where theme_id = %d" % theme_id
class_id_list = sql_utils.exe(sql2)
# print class_id_list
# deal with sample_n_label_table
sample_n_label_table = "sample_%d_label" % theme_id
sql3 = "select id from %s" % sample_n_label_table
label_id_list = sql_utils.exe(sql3)
if len(sample_id_list) * len(class_id_list) == len(label_id_list):
print "===========", True
else:
print "===========", False
def create_table(table_name, column_list):
column_entries = ""
if column_list is None or len(column_list) == 0:
return
else:
for column in column_list:
column_clause = column.column_name + " " + column.column_type
if column.not_null is True:
column_clause += " NOT NULL"
if column.auto_inc is True:
column_clause += " AUTO_INCREMENT"
if column.primary_key is True:
column_clause += " PRIMARY KEY"
if column.default is not None:
column_default = sql_utils.sql_value_format(column.default)
column_clause += " DEFAULT " + column_default
column_entries += column_clause + ","
print column_entries
column_entries = column_entries.strip(",")
print column_entries
sql = "CREATE TABLE %s(%s) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8" % (
table_name, column_entries)
sql_utils.exe(sql)
def alter_sample_n():
for theme_id in range(1, 41):
table_name = "sample_%d" % theme_id
sql1 = "ALTER TABLE %s CHANGE sample_id id INT(10) unsigned AUTO_INCREMENT" % table_name
print sql1
sql_utils.exe(sql1)
sql2 = "ALTER TABLE %s CHANGE sample_path path VARCHAR(500) NOT NULL" % table_name
print sql2
sql_utils.exe(sql2)
sql3 = "ALTER TABLE %s CHANGE sample_label label LONGTEXT" % table_name
print sql3
sql_utils.exe(sql3)
def drop_table(table_name):
sql = "drop table %s" % table_name
sql_utils.exe(sql)
def truncate_sample_n_label():
sql = "truncate table sample_1_label"
sql_utils.exe(sql)
def clean_sample_n_label_last():
for theme_id in range(1, 41):
table_name = "sample_%d_label" % theme_id
sql1 = "update %s set label_user_id=0,label_data='',check_user_id=0,check_data='',status=0,update_time=0" % table_name
sql_utils.exe(sql1)
def alter_int_to_bigint():
for theme_id in range(2, 41):
table_name = "sample_%d_label" % theme_id
sql1 = "ALTER TABLE %s MODIFY update_time BIGINT NOT NULL DEFAULT '0'" % table_name
sql_utils.exe(sql1)
def select_by_id(table_name, id):
sql = "select * from %s where id=%s" % (table_name, id)
return sql_utils.exe(sql)
def select_all(table_name):
sql = "select * from %s" % table_name
return sql_utils.exe(sql)