def count(num_obs, cond):
table = experiment.table_name()
select = "SELECT DISTINCT A.mix_p, COALESCE(B.cnt, 0) FROM %s A " \
"LEFT JOIN (SELECT mix_p, COUNT(*) AS cnt FROM %s " \
"WHERE num_obs=%i AND %s GROUP BY (mix_p)) B " \
"ON A.mix_p = B.mix_p " \
"ORDER BY A.mix_p" % (table, table, num_obs, cond)
return db.arbitrary_select(select)
def new_table(fields):
spec = "id INT NOT NULL AUTO_INCREMENT"
for f in fields:
spec += ", " + f[0] + " " + f[1]
spec += ", PRIMARY KEY (id)"
create_table = "CREATE TABLE %s (%s) ENGINE=InnoDB" % (exp.table_name(),
spec)
update(create_table)
def load(what="*", where=""):
request = "SELECT %s FROM %s" % (what, exp.table_name())
if len(where) > 0:
request += " WHERE %s" % where
print(request)
cnx = connect()
cursor = cnx.cursor()
cursor.execute(request)
return cursor.fetchall()
def result():
table = experiment.table_name()
select = "SELECT mix_p, AVG(pearson), AVG(sign_transform), AVG(kendall_transform), AVG(spearman_transform) " \
"FROM %s WHERE market='%s' AND num_obs=%s GROUP BY mix_p" % (table, market, num_obs)
return db.arbitrary_select(select)
def insert(values):
insert = "INSERT INTO %s (%s) VALUES (%s)" % (exp.table_name(), ",".join(
map(lambda v: v[0], values)), ",".join(map(lambda v: str(v[1]),
values)))
update(insert)
def drop_table():
try:
update("DROP TABLE %s" % exp.table_name())
except mysql.connector.errors.ProgrammingError:
print("Failed to drop table")