def select_infor(proj_id, dataset_id, libname="user_information"):
sql = """select `name`, `type`, `width`, `float_width`, `varlabels`, `valuelabels`, `formats` from `data_information` where project_id={} and dataset_id={}""".format(
proj_id, dataset_id)
res = base_model(libname).connect()
query_result = res.select_sql(sql)
res.close()
return query_result
def select_tableinfor(proj_id, dataset_id, libname="user_information"):
sql = """select datatable_name from dataset where proj_id={} and dataset_id={}""".format(
proj_id, dataset_id)
res = base_model(libname).connect()
query_result = res.select_sql(sql)
res.close()
return query_result
def create_data_table(vartypes, width, valuetypes, formats, varnames, filename, libname="data"):
sql = """CREATE TABLE `{}` (""".format(filename)
for i in range(len(varnames)):
if valuetypes[i] == "FLOAT":
num = width[i].split(".")
s = "`{}` {}({},{}) DEFAULT NULL".format(varnames[i], valuetypes[i], num[0], num[1])
elif valuetypes[i] == "DATETIME":
s = "`{}` {} DEFAULT NULL".format(varnames[i], valuetypes[i])
elif valuetypes[i] == "DATE":
s = "`{}` {} DEFAULT NULL".format(varnames[i], valuetypes[i])
elif valuetypes[i] == "VARCHAR":
print width[i], type(width[i])
s = "`{}` {}({}) DEFAULT NULL".format(varnames[i], valuetypes[i], str(int(width[i])+10))
else:
s = "`{}` {}({}) DEFAULT NULL".format(varnames[i], valuetypes[i], width[i])
if i < len(varnames) - 1:
sql = sql + s + ","
elif i == len(varnames) - 1:
sql = sql + s
sql = sql + ") ENGINE=InnoDB DEFAULT CHARSET=UTF8"
res = base_model(libname).connect()
res.adu_sql(sql)
res.close()
def create_information_tables(tablename, libname="information"):
sql = """CREATE TABLE `{}` (
`name` VARCHAR (255) DEFAULT NULL,
`type` VARCHAR (255) DEFAULT NULL,
`width` INT (5) DEFAULT NULL,
`float_width` INT (5) DEFAULT NULL,
`varlabels` text DEFAULT NULL,
`valuelabels` text DEFAULT NULL,
`formats` VARCHAR(255) DEFAULT NULL,
`missing_value` VARCHAR(255) DEFAULT NULL,
`theme` VARCHAR(255) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;""".format(tablename)
res = base_model(libname).connect()
res.adu_sql(sql)
res.close()
def __init__(self, libname="user_information"):
self.libname = libname
self.res = base_model(libname).connect()
# user_table, proj_table, dataset_table, datainfor_table
self.table = Config().get_content("user_proj")
def __init__(self, libname="user_information"):
self.libname = libname
self.res = base_model(libname).connect()
def __init__(self, libname="data"):
self.libname = libname
self.res = base_model(libname).connect()
def select_data(tablename, libname="data"):
sql = """select * from {}""".format(tablename)
res = base_model(libname).connect()
query_result = res.select_sql(sql)
res.close()
return query_result
