def arr2table(arr, tablename): j = 0 strsql = "" for row in arr: i = 0 strsql = strsql + "REPLACE INTO " + tablename + " VALUES(" for val in row: if i == 0: strsql = strsql + "\'" + str(val) + "\'" else: strsql = strsql + ", \'" + str(val) + "\'" i = i + 1 strsql = strsql + ");\n" sql.exec_updsql(strsql)
def csv2table(filename, tablename): with open(filename, 'r') as f: reader = csv.reader(f) strsql = "" for row in reader: i = 0 strsql = strsql + "REPLACE INTO " + tablename + " VALUES(" for val in row: if i == 0: strsql = strsql + "\'" + str(val) + "\'" else: strsql = strsql + ", \'" + str(val) + "\'" i = i + 1 strsql = strsql + ");\n" sql.exec_updsql(strsql)
def init_eigyobi(): strsql = "" strsql = "TRUNCATE TABLE trade.eigyobi;\n" strsql = strsql + "INSERT INTO trade.eigyobi(date) SELECT DISTINCT date FROM trade.kabuka ORDER BY date;\n" sql.exec_updsql(strsql)
def imp_csv2table(filename, tablename): strsql = "LOAD DATA LOCAL INFILE \'" + filename + "\' REPLACE INTO TABLE " + tablename + " FIELDS TERMINATED BY \',\';" sql.exec_updsql(strsql)
def truncate_table(table_name): sql.exec_updsql("TRUNCATE TABLE " + table_name + ";")
def init_meigaras(): truncate_table("trade.meigaras") strsql = "REPLACE INTO trade.meigaras(code) SELECT DISTINCT code FROM trade.kabuka;\n" sql.exec_updsql(strsql)
def drop_view(view_name): sql.exec_updsql("DROP VIEW IF EXISTS " + view_name + ";")
def drop_table(table_name): sql.exec_updsql("DROP TABLE IF EXISTS " + table_name + ";")
def create_view(view_name): strsql = sql.get_sql("create_view_" + view_name) sql.exec_updsql(strsql)
def create_table_from_tmpl(tmpl_name, kws): strsql = sql.get_sql("tmpl_create_table_" + tmpl_name) for k in kws: strsql = strsql.replace("%s", k, 1) sql.exec_updsql(strsql)
def create_table(table_name): strsql = sql.get_sql("create_table_" + table_name) sql.exec_updsql(strsql)