def import_csv(directory): """ Import an entire directory of CSV files (requires authentication) """ instruments = [] with open("instruments.json", "r") as instruments_f: instruments = json.load(instruments_f) queue = [] for instrument in instruments: if len(queue) < 50: filename = instrument["symbol"] + ".csv" if filename in os.listdir(directory): queue.append(instrument["symbol"]) else: data = read_csv(queue, directory) for datum in data: with sqlite.create_connection(db_file) as conn: sqlite.insert(conn, datum[0], datum[1], datum[2], datum[3]) queue = [] if len(queue) > 0: data = read_csv(queue, directory) for datum in data: with sqlite.create_connection(db_file) as conn: sqlite.insert(conn, datum[0], datum[1], datum[2], datum[3])
def main(): print("Beginning execution...") t1 = time.time() ######## PUT CODE BELOW ######## if len(sys.argv) > 1: # Initialize database and collect instruments. if '0' in sys.argv[1]: print("Initiating database...") init(db_file) print("Initialization complete.") print("Collecting instruments. Expected time: 1 minute") collect_instruments("instruments.json") print("Instrument collection complete.") # Collect price and popularity data. if '1' in sys.argv[1]: print("Collecting data. Expected time: 5 minutes") collect_data("instruments.json") print("Data collection complete.") # Compose the index or update weights if '2' in sys.argv[1]: print("Composing Index.") index.collect_index(db_file) print("Index Composition complete.") else: print("Updating weights.") index.update_weights(db_file) print("Weights updated.") # Collect the value of the index print("Valuing Index.") index.collect_index_value(db_file) print("Index valuation complete") print("Generating HTML.") html.generate_template(db_file) print("Generated HTML.") ######## TEST CODE ######## convert(sqlite.create_connection(db_file)) comp_ids(sqlite.create_connection(db_file)) ######## PUT CODE ABOVE ######## dt = round(time.time() - t1) m, s = divmod(dt, 60) h, m = divmod(m, 60) print("Execution lasted", h, "hours", m, "minutes", s, "seconds.")
def were_scores_reported(week): conn = sqlite.create_connection() df = pd.read_sql_query("select * from matchup_info", conn) conn.close() reported = len(df[df['week'] == week]) > 0 return reported
def process_queue(file, queue): """ Process a queue of stock ids by fetching recent price and popularity data :param file: SQLite database file path :type file: string :param queue: queue of ids to be processed :type queue: list """ authentication.login(username=os.environ.get("ROBIN_USER"), password=os.environ.get("ROBIN_PASS")) pop = stocks.get_popularity_by_ids(queue, errors=False) price = stocks.get_quotes_by_ids(queue, errors=False) while pop.get("detail") or price[0].get("detail"): if pop.get("detail"): cooldown = helper.parse_throttle_res(pop.get("detail")) print("API Throttling... waiting {} seconds.".format(cooldown)) time.sleep(cooldown) elif price.get("detail"): cooldown = helper.parse_throttle_res(price.get("detail")) print("API Throttling... waiting {} seconds.".format(cooldown)) time.sleep(cooldown) # recollect data after cooldown pop = stocks.get_popularity_by_ids(queue, errors=False) authentication.login(username=os.environ.get("ROBIN_USER"), password=os.environ.get("ROBIN_PASS")) price = stocks.get_quotes_by_ids(queue, errors=False) results = {} for res in pop['results']: if res is not None: ins = helper.id_from_url(res["instrument"]) if not results.get(ins): results[ins] = {} results[ins]["pop"] = res['num_open_positions'] for res in price: if res is not None: ins = helper.id_from_url(res["instrument"]) if not results.get(ins): results[ins] = {} results[ins]["price"] = res["last_trade_price"] bad_keys = [] for instrument, res in results.items(): if not res.get("price"): print("Failed to fetch price for ", instrument) bad_keys.append(instrument) for k in bad_keys: del results[k] with sqlite.create_connection(file) as conn: sqlite.index_insert_many(conn, results)
def generateProfile(): ask("Welcome to Student assistant , i hope i will be helpful to you , i want to do some configuration , can you help me please ? " ) ask("we will use the terminel to avoid mistakes !") ask("First ! can you entry your name , please ? ") fname = input() print("your last name : ") lname = input() ask("nice to meet you " + fname) ask("how old are you " + fname + " ? ") age = int(input()) if age <= 18: ask(" what is your school name " + fname + " ?") schoolName = input() else: ask(" what is your university name " + fname + " ?") univName = input() ask("what's year you are study in ? ") print("ex : cpi2") degree = input() ask("what is your group ? ") print("ex : g1") gr = input() speak("what do you want to name me ?") vsname = input() speak("Thank you " + fname + " Enjoy !") sq.create_connection(fname + lname + ".sqlite") sq.create_student_table(fname + lname + ".sqlite") sq.insertStudent(fname + lname + ".sqlite", fname, lname, age, univName, degree) cfg = open("data/main.cfg", "w") config.add_section('database') config.set('database', 'dbname', fname + lname + ".sqlite") config.add_section('assistantInfo') config.set('assistantInfo', 'name', vsname) config.add_section('user') config.set('user', 'firstname', fname) config.set('user', 'lastname', lname) config.set('user', 'degree', degree) config.set('user', 'group', g) config.write(cfg) cfg.close()
def generate_template(db_file, tim=time.time()): date = datetime.fromtimestamp(tim).strftime("%m-%d-%Y") labels = [] data = [] stock_data = [] env = Environment(loader=FileSystemLoader('templates')) template = env.get_template('template.html') tim1 = tim - ((tim - 14400) % 86400) + 9 * 3600 tim2 = min(tim, tim1 + 86400 - 7 * 3600) conn = sqlite.create_connection(db_file) values = index.get_values(conn, tim1, tim2) for v in values: labels.append(1000 * v[0]) data.append(round(v[1], 2)) day_change = ("0.00", "0.00") if len(values) > 0: day_change = (values[-1][1] - values[0][1], 100 * (values[-1][1] - values[0][1]) / values[0][1]) day_change = ('%+.2f' % day_change[0], '%+.2f' % day_change[1]) index_1 = index.get_composition(conn, tim1) index_2 = index.get_composition(conn, tim2) for i1 in index_1: i = 0 while i < len(index_2): if index_2[i]['id'] == i1['id']: i1['price2'] = index_2[i]['price'] index_2.pop(i) else: i += 1 for i in index_1: if i.get("price2"): ticker = stocks.get_instrument_by_id(i.get('id'))['symbol'] p_change = round(100 * (i['price2'] - i['price']) / i['price'], 2) v_change = (i['price2'] * i['weight']) - (i['price'] * i['weight']) stock_data.append((ticker, '{:.2f}'.format(i['price']), '{:.2f}'.format(i['price2']), '%+.2f' % p_change, '%+.2f' % v_change)) output = template.render(date=date, data=json.dumps(data), labels=labels, stocks=stock_data, day_change=day_change) with open(output_dir + '/' + date + ".html", 'w') as f: f.write(output)
def collect_index(file, t1=(time.time() - (time.time() % 3600) - 604800), t2=(time.time())): """ Compose the index and set weights in the SQLite DB :param t1: Earlier Unix Epoch :type t1: Unix Epoch :param t2: Later Unix Epoch :type t2: Unix Epoch """ with sqlite.create_connection(file) as conn: data = compose_index(conn, get_value(conn), t1, t2) updates(conn, data)
def update_weights(file, date=time.time()): """ Update the latest stock weights using the day's composition :param file: Database file :type file: SQLite Database file :param time: Unix Epoch :type time: integer """ conn = sqlite.create_connection(file) comp = get_composition(conn, date - ((date - 14400) % 86400) + 9 * 3600) for c in comp: c["tim"] = date updates(conn, comp)
def collect_index_value(file, time=time.time()): """ Value the index given a time :param file: Database file :type file: SQLite Database file :param time: Unix Epoch :type time: integer """ conn = sqlite.create_connection(file) with conn: value = value_index(conn, time) print(value) sqlite.insert_value(conn, time, value)
else: if previous_img_link != row['crypto_values'][0]: imgopen = imaginador.imgOpener(row['crypto_values'][0]) previous_img_link = row['crypto_values'][0] (resultadoImagem, virgula) = imaginador.imgDiscover(imgopen, row['crypto_values'][1:]) valor_com_ponto = imaginador.recognize(resultadoImagem, virgula) row['crypto_values'] = valor_com_ponto row['card_name'] = card_name # for i in html_extract: # print(i.values()) return html_extract conn = sqlite.create_connection('valores.db') if conn is not None: sqlite.create_table(conn) else: print("Error! cannot create the database connection.") listofcards = ['Gilded Goose', 'Brazen Borrower', 'Liliana, the Last Hope'] for card in listofcards: liga_url, scg = crawlerv2.cardToLink(card) table = crawler_ligamagic(card, liga_url) sqlite.insert_values(conn, table) sqlite.show_data(conn)
from cleaning import clean import sqlite path = 'c:/py/BRICS-EDA/' if __name__ == '__main__': df = clean.get_data(path) clean.process(df, path) conn = sqlite.create_connection(r"c:/py/BRICS-EDA/data/brics.db") # create tables if conn is not None: # import data sqlite.import_data(conn, path) print('data imported to mysql') else: print("Error! cannot create the database connection.") conn.close()
def init(db): sqlite.create_table(sqlite.create_connection(db))