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