def get_tables(): ret = {} logger = logging.getLogger("extract_schema") logger.info("creating cursor") cur = conn.cursor() try: cur.execute("show tables;") for row in cur: name = row[1] logger.info("Found table %s", name) cur2 = conn.cursor() cur2.execute(f"describe table {name};") col = [] for row2 in cur2: cname = row2[0] type = row2[1] col.append((cname, type)) logger.debug("Found column %s (%s)", cname, type) cur2.close() ret[name] = col finally: cur.close() return ret
def train(currency='USD'): sql_command = ''' select date, rate_nb from exchange_rates where currency_pair_id = (select id from currency_pairs where currency = '{}') order by date;'''.format(currency) data = pd.read_sql(sql_command, conn) lendf = len(data) train_df = data[:int(lendf * 0.995)] test_df = data[int(lendf * 0.995):] arima = ARIMAC(data, train_df, test_df, 'rate_nb') arima.getBestParamsARIMA() rmse, results = arima.modelTrain() print(results) print('Best order = {}'.format(arima.order)) print('RMSE = {}'.format(rmse)) cursor = conn.cursor() cursor.execute('''insert into arima_params (p, d, q, currency, date, rmse) values ({}, {}, {}, '{}', '{}', {}) on conflict do nothing''' .format(arima.order[0], arima.order[1], arima.order[2], currency, str(date.today()), rmse)) conn.commit() cursor.close() plt.figure(figsize=(14, 5)) plt.plot(data['date'], data['rate_nb']) plt.plot(results['date'], results['predicted']) plt.plot(results['date'], results['rate_nb']) plt.show()
def dump_tokens(): c = conn.cursor() res = c.execute(""" SELECT * FROM tokens; """) # 2D list of lists return c.fetchall()
def main(scale, schema, runs, warm, end): queries = load_queries() logger.info("Loaded %d queries", len(queries)) db = "SNOWFLAKE_SAMPLE_DATA" if schema else "TCPH_SCHEMALESS" schema_name = f"TPCH_SF{scale}" schema_id = "SCHEMA" if schema else "SCHEMALESS" with conn_vw(name = f'TEMPORARY_{schema_id}_SF{scale}', size='MEDIUM') as conn: logger.info("Running on database %s", db) cur = conn.cursor() cur.execute(f"USE DATABASE {db};") logger.info("Running on schema %s", db) cur.execute(f"USE SCHEMA {schema_name};") logger.info("Disabling result set cache!") cur.execute("ALTER SESSION SET USE_CACHED_RESULT = FALSE;") cur.close() filename = f"results_{schema_id}_SF{scale}.csv" filepath = os.path.join("results", filename) logger.info("Writing results to %s", filepath) with open(filepath, "w") as f: header = ["Query"]+[f"Run {i+1}" for i in range(runs)] + ["Average", "Standard Deviation"] write_row(f, header) for q in queries[:end]: timings, avg, std = time_query(q, conn, runs=runs, warmups=warm) timings = list(timings) timings += [avg / 1000.0, std / 1000.0] timings = [f"{q.num}"] + [f"{x:.06f}" for x in timings] write_row(f, timings)
def main(scale): logger = logging.getLogger("create_schemaless") tables = get_tables() logger.info("Creating schemaless version of scale factor %d", scale) schema_name = f"TPCH_SF{scale}" with conn_vw(name='temporary2') as conn: logger.info("Creating schema %s", schema_name) cur = conn.cursor() cur.execute("use database TCPH_SCHEMALESS;") cur.execute(f"create or replace schema {schema_name}") cur.execute(f"use schema {schema_name}") logger.info("Creating stage TMP") cur.execute(f"CREATE OR REPLACE STAGE TCPH_SCHEMALESS.{schema_name}.TMP") logger.info(f"Creating export file format") cur.execute(f"CREATE OR REPLACE FILE FORMAT TCPH_SCHEMALESS.{schema_name}.export TYPE = 'JSON' COMPRESSION = 'AUTO' ENABLE_OCTAL = FALSE ALLOW_DUPLICATE = FALSE STRIP_OUTER_ARRAY = FALSE STRIP_NULL_VALUES = TRUE IGNORE_UTF8_ERRORS = FALSE;") for tbl, cols in tables.items(): table_name = f"{tbl}_RAW" logger.info("Creating table %s", table_name) cur.execute(f"CREATE OR REPLACE TABLE \"{table_name}\" (\"SRC\" VARIANT);") stage_path = f"@TMP/{tbl}/" logger.info("Copying data for table %s to temp files %s", tbl, stage_path) cur.execute(f"copy into {stage_path} from (select object_construct(*) from \"SNOWFLAKE_SAMPLE_DATA\".\"{schema_name}\".\"{tbl}\") file_format = (format_name = export)") logger.info("Copying data from temp path %s into actual table", stage_path) cur.execute(f"copy into {table_name} from {stage_path} file_format = (format_name = export)") logger.info("Deleting temp files from %s", stage_path) cur.execute(f"rm {stage_path}") cur.close()
def dump_tokens(): c = conn.cursor() res = c.execute(""" SELECT * FROM tokens; """) # 2D list of lists return c.fetchall()
def get_by_username(username): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE userName= %s" cur.execute(sql_string, (username, )) info = cur.fetchone() conn.close() return info
def prune_temp_request(): """ delete temporary requests tables. Should be called at app startup """ c = conn.cursor() res = c.execute(""" DELETE FROM temp_request(userid,reqsec) """) conn.commit() return True
def get_email(email): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE email= %s" cur.execute(sql_string, (email, )) info = cur.fetchone() conn.close() return info
def prune_temp_request(): """ delete temporary requests tables. Should be called at app startup """ c = conn.cursor() res = c.execute(""" DELETE FROM temp_request(userid,reqsec) """) conn.commit() return True
def get_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE userID= %s" cur.execute(sql_string, (user_id, )) info = cur.fetchone() conn.close() return info[1]
def delete_review(place_id): conn.connect() cur = conn.cursor() sql_string = "DELETE FROM review WHERE placeID = %s" cur.execute(sql_string, (place_id, )) conn.commit() cur.close() conn.close()
def block_user(user_id): conn.connect() cur = conn.cursor() sql_string = "UPDATE users SET isBlock = 1 WHERE userID = %s " cur.execute(sql_string, (user_id, )) conn.commit() cur.close() conn.close()
def post_review(place_id, user_id, review, rating): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO review (placeID, userID, reviewDate, review,rating) VALUES(%s, %s,now(), %s, %s)" cur.execute(sql_string, (place_id, user_id, review, rating)) conn.commit() cur.close() conn.close()
def approve_place(place_id): conn.connect() cur = conn.cursor() sql_string = "UPDATE places SET approved = 1 WHERE placeID = %s " cur.execute(sql_string, (place_id, )) conn.commit() cur.close() conn.close()
def signup(username, email, password): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO users (userName, email, password) VALUES(%s, %s, %s)" cur.execute(sql_string, (username, email, password)) conn.commit() cur.close() conn.close()
def get_place_review(place_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM review WHERE placeID =%s ORDER BY reviewDate DESC" cur.execute(sql_string, (place_id, )) info = cur.fetchall() cur.close() conn.close() return info
def get_review_by_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM review WHERE userID =%s ORDER BY reviewDate DESC" cur.execute(sql_string, (user_id, )) info = cur.fetchall() cur.close() conn.close() return info
def get_to_approve(): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE approved = false ORDER BY postedDate DESC" cur.execute(sql_string, ) info = cur.fetchall() cur.close() conn.close() return info
def get_place_by_id(place_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE placeID = %s" cur.execute(sql_string, (place_id, )) info = cur.fetchone() cur.close() conn.close() return info
def get_place_by_name(name): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE name = %s" cur.execute(sql_string, (name, )) info = cur.fetchone() cur.close() conn.close() return info
def get_all_place_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT placeID,image_location FROM places WHERE userID =%s ORDER BY postedDate DESC" cur.execute(sql_string, (user_id, )) info = cur.fetchall() cur.close() conn.close() return info
def run_query(q: query, conn): #q.build_args() sql = q.get_sql() cur = conn.cursor() start = time.time() cur.execute(sql) diff = time.time() - start cur.close() return diff
def new_place(user_id, name, address, website, phone, image_loc, description): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO places (userID, name, address, website, phone, image_location, description, postedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, now())" cur.execute( sql_string, (user_id, name, address, website, phone, image_loc, description)) conn.commit() cur.close() conn.close()
def get_place_by_name1(name): name = '%' + name + '%' conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE name LIKE %s" cur.execute(sql_string, (name, )) info = cur.fetchall() cur.close() conn.close() return info
def main(scale, typed): tables = get_tables() cur = conn.cursor() schema_name = f"TPCH_SF{scale}" logger.info("Using schema %s", schema_name) cur.execute(f"USE DATABASE TCPH_SCHEMALESS;") cur.execute(f"USE SCHEMA {schema_name};") for tbl, cols in tables.items(): col_sels = [] for col, type in cols: type_str = "" if typed: type_str = f"::{type}" col_sels.append(f"src:{col}{type_str} as {col}") col_sel = ",\n\t".join(col_sels) sql = f"""select {col_sel} from {tbl}_RAW""" with open(os.path.join("views", f"{tbl}.sql"), "w") as f: f.write(sql) logger.info("Creating view %s with sql %s", tbl, sql) cur.execute(f"CREATE OR REPLACE VIEW {schema_name}.{tbl} AS {sql}") cur.close()
from connection import conn import logging import os logger = logging.getLogger("extract_queries") logger.info("creating cursor") cur = conn.cursor() try: cur.execute("show views;") for row in cur: name = row[1] text = row[7] logger.info("Found view %s", name) if "_" not in name: # we only want raw queries! # extract sql statement as_idx = text.index("as ") select_stmt = text[as_idx + 3:] logger.debug("%s SQL: %s", name, select_stmt) # save query to file filename = f"{name}.sql" with open(os.path.join("queries", filename), "w") as f: f.write(select_stmt) logger.info("saved sql to %s", filename) finally: cur.close()
def get_access_pair(userid): """ return ( acc_key, acc_secret ) for provided userid or None """ c = conn.cursor() c.execute(GET_ACCESS_PAIR, (userid, )) return c.fetchone()
def delete_token(userid): """ delete token with provided userid """ c = conn.cursor() c.execute(DELETE_TOKEN, (userid, )) conn.commit() return c.rowcount == 1
def get_request_pair(userid): """ return ( userid, req_secret ) pair for provided userid or None """ c = conn.cursor() c.execute(GET_REQUEST_PAIR, (userid, )) return c.fetchone()
def delete_unverified_request(userid): """ Delete an user temporary request returns always True """ c = conn.cursor() res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid,)) conn.commit() return True
def save_unverified_request( userid, req_secret ): """ save unverified request keys """ c = conn.cursor() res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid)) conn.commit() return True
def get_request_pair(userid): """ return ( userid, req_secret ) pair for provided userid or None """ c = conn.cursor() c.execute(GET_REQUEST_PAIR, (userid,)) return c.fetchone()
def delete_token(userid): """ delete token with provided userid """ c = conn.cursor() c.execute(DELETE_TOKEN, (userid,)) conn.commit() return c.rowcount == 1
def get_access_pair(userid): """ return ( acc_key, acc_secret ) for provided userid or None """ c = conn.cursor() c.execute(GET_ACCESS_PAIR, (userid,)) return c.fetchone()
def save_access_tokens(userid, req_secret, acc_key, acc_secret): c = conn.cursor() c.execute(SAVE_ACCESS_TOKENS, (userid, req_secret, acc_key, acc_secret, userid)) conn.commit() return c.rowcount == 1
def save_unverified_request(userid, req_secret): """ save unverified request keys """ c = conn.cursor() res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid)) conn.commit() return True
def delete_unverified_request(userid): """ Delete an user temporary request returns always True """ c = conn.cursor() res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid, )) conn.commit() return True