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")