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)