def comp_ids(conn):
query = "SELECT id, tim FROM compositions"
comps = sqlite.execute(conn, query)
for c in comps:
tm = c[1] - c[1] % 3600
query = "UPDATE stock_data SET comp_id = ? WHERE tim >= ? AND tim < ?;"
sqlite.execute(conn, query, (c[0], tm, tm + 3600))
def convert(conn):
"""
Convert old tables to a new relational format
"""
sqlite.create_table(conn)
query = """ INSERT INTO stock_data (uid, tim, popularity, price, weight)
SELECT id, tim, popularity, price, weight FROM index_data;
"""
sqlite.execute(conn, query)
query = """ INSERT INTO compositions (tim, value)
SELECT tim, value FROM index_value;
"""
sqlite.execute(conn, query)
def get_composition(conn, date):
"""
Return the index composition at a time
:param conn: SQLite database Connection
:type conn: SQLite database connection
:param date: Unix Epoch
:type date: integer
"""
tim1 = date - (date % 3600)
tim2 = tim1 + 3600
query = "SELECT * FROM index_data WHERE tim BETWEEN {} AND {} AND NOT weight = 0".format(
tim1, tim2)
data = sqlite.execute(conn, query)
results = []
for row in data:
results.append({
"id": row[0],
"tim": row[1],
"popularity": row[2],
"price": row[3],
"weight": row[4]
})
return results
def sort_by_popularity(conn, date):
"""
Calculate and sort stock popularity at date.
Returns a sorted list of dictionaries containing {id, time, popularity, price}
:param conn: SQLite Connection
:type conn: SQLite Connection
:param date: Unix Epoch
:type date: integer
"""
output = []
tim1 = date - (date % 3600)
tim2 = tim1 + 3600
query = "SELECT * FROM index_data WHERE tim BETWEEN {} AND {} ORDER BY popularity desc".format(
tim1, tim2)
rows = sqlite.execute(conn, query)
for row in rows:
output.append({
"id": row[0],
"tim": row[1],
"popularity": row[2],
"price": row[3],
"weight": row[4]
})
return output
def updates(conn, data):
if len(data) == 0:
print("No data to update.")
return
query = "UPDATE index_data SET weight = CASE "
tim = 0
for datum in data:
tim = datum['tim']
tim1 = tim - (tim % 3600)
tim2 = tim1 + 3600
string = "WHEN (id = '" + datum['id'] + "') THEN "
string += str(datum['weight']) + " "
query = ''.join([query, string])
string = "ELSE weight END WHERE tim BETWEEN {} AND {};".format(tim1, tim2)
query = ''.join([query, string])
sqlite.execute(conn, query)
def get_values(conn, tim1, tim2):
"""
Get all index values between tim1 and tim2
:param conn: SQLite database Connection
:type conn: SQLite database connection
:param tim1: Earlier Unix Epoch
:type tim1: integer
:param tim2: Earlier Unix Epoch
:type tim2: integer
"""
query = "SELECT * FROM index_value WHERE tim BETWEEN {} AND {}".format(
tim1, tim2)
data = sqlite.execute(conn, query)
return data
def get_value(conn, date=None):
"""
Get the value of the index at a given time
:param conn: SQLite database Connection
:type conn: SQLite database connection
:param date: Unix Epoch
:type date: integer
"""
i = -1
query = "SELECT * FROM index_value"
if date is not None:
i = 0
tim1 = date - (date % 3600)
tim2 = tim1 + 3600
query = "SELECT * FROM index_value WHERE tim BETWEEN {} AND {}".format(
tim1, tim2)
data = sqlite.execute(conn, query)
value = 100
if len(data) > 0:
value = data[i][1]
return value
def value_index(conn, date):
"""
Value the index at a given date, assuring no stock is repeated
:param conn: SQLite database Connection
:type conn: SQLite Connection
:param date: Unix Epcoh
:type date: integer
"""
tim1 = date - (date % 3600)
tim2 = tim1 + 3600
query = "SELECT * FROM index_data WHERE tim BETWEEN {} AND {} AND weight != 0".format(
tim1, tim2)
data = sqlite.execute(conn, query)
value = 0
stocks = []
for datum in data:
if not datum[0] in stocks:
stocks.append(datum[0])
value += datum[3] * datum[4]
return value
Welcome to Email Cloud, you can store your email addresses in our service.
You can log into our server and get all your email addresses at any time.
============================================================================
""")
print("Please log in:")
while True:
try:
# take username and password
username = input("Username: "******"Password: "******"SELECT * FROM emailcloud WHERE username = '******' AND password = '******'"
sqlite.execute(c, command)
# SQL injection protection
# command = "SELECT * FROM emailcloud WHERE username = ? AND password = ?"
# c.execute(command, (username, password))
# fetch all the results
result = c.fetchall()
if result == None or result == []:
print("Invalid username password pair!")
else:
print("Your profile data is listed in the following: \n",
str(result))
except KeyboardInterrupt:
print("\nexit")
break