def main():
rs.login()
conn = db.db_connect(SQLVars.DATABASE.value)
exists = db.check_if_table_exists(conn, SQLVars.TICKERS.value)
if not exists:
sql_create_tickers_table = """ CREATE TABLE IF NOT EXISTS existing_tickers (
ticker text,
first_dt text
) """
db.execute_query(conn, sql_create_tickers_table)
current_symbols = db.get_col_from_table(conn, TickersColumns.TICKER.value,
SQLVars.TICKERS.value)
current_symbols = [
i[getIndexOfEnum('ticker', TickersColumns)] for i in current_symbols
]
stocks_to_check = ['IPO' + chr(i + ord('a')).upper() for i in range(26)]
stocks_to_check_filtered = list(
set(stocks_to_check) - set(current_symbols))
curr_date = datetime.datetime.today().strftime('%Y-%m-%d')
new_tickers = []
for symbol in stocks_to_check_filtered:
quotes = rs.get_quote(symbol)
if quotes:
new_tickers.append((symbol, curr_date))
order_by_dollars(symbol, 1000)
db.insert_into_table(conn, SQLVars.TICKERS.value, new_tickers)
rs.logout()
def edit_item():
if request.method == 'GET':
asin = request.args.get('asin')
name, description, images, price, browse_node_id = execute_query("""
SELECT name, description, images, price, browse_node_id FROM items
WHERE asin = %s
""", (asin,))[0]
item = {
'name': name,
'description': description,
'images': ','.join(images),
'price': price,
'asin': asin,
'browse_node_id': browse_node_id
}
return render_template('edit_item.html', item=item)
elif request.method == 'POST':
if session.get('email') is None:
return redirect('/login')
data = request.form
images = "{"+data['images']+"}"
query = execute_query("""UPDATE items SET
(name, images, price, description)
= (%s,%s,%s,%s)
WHERE asin = %s""",
(data['name'],
images,
data['price'],
unescape(data['description']),
data['asin'],))
return redirect('/search_category?category='+data['browse_node_id'])
def send_message():
"""
Stores the message, time of sending and username of author in database.
:request: {
"username": str,
"text": str
}
:return: dict of execution status
:rtype: {"ok": bool}
"""
data = request.json
username = data["username"]
text = data["text"]
connection = create_connection("data.sqlite3")
select_user_id = f"SELECT id FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user_id, 0,
{'username': username})
data_dict = {'text': text, 'id': query_data[0]}
new_message = f"INSERT INTO messages (text, time, user_id) " \
f"VALUES (:text, strftime('%s','now'), :id)"
execute_query(connection, new_message, data_dict)
connection.close()
return {'ok': True}
def edit_item():
if request.method == 'GET':
asin = request.args.get('asin')
name, description, images, price, browse_node_id = execute_query(
"""
SELECT name, description, images, price, browse_node_id FROM items
WHERE asin = %s
""", (asin, ))[0]
item = {
'name': name,
'description': description,
'images': ','.join(images),
'price': price,
'asin': asin,
'browse_node_id': browse_node_id
}
return render_template('edit_item.html', item=item)
elif request.method == 'POST':
if session.get('email') is None:
return redirect('/login')
data = request.form
images = "{" + data['images'] + "}"
query = execute_query(
"""UPDATE items SET
(name, images, price, description)
= (%s,%s,%s,%s)
WHERE asin = %s""", (
data['name'],
images,
data['price'],
unescape(data['description']),
data['asin'],
))
return redirect('/search_category?category=' + data['browse_node_id'])
def sign_up_user():
"""
Register user.
Confirms whether login and password are in range and whether user exist.
Hashes and encrypts password using :func:`codec`.
Stores user's data in database.
request: {
"username": str,
"password": str
}
:return: dict of execution status
:rtype: {
"login_out_of_range": bool,
"password_out_of_range": bool,
"ok": bool
}
"""
username = request.authorization.username
password = request.authorization.password
# Make sure credentials are in range.
if len(username) not in range(4, 20, 1):
return {"login_out_of_range": True}
elif len(password) not in range(4, 20, 1):
return {"login_out_of_range": False, "password_out_of_range": True}
connection = create_connection("data.sqlite3")
select_user = f"SELECT id FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
# If user isn't registered, encrypt password and store in database.
if query_data is None:
password_hash = codec(password, 1)
password_hash = Binary(password_hash)
data_dict = {'username': username, 'password_hash': password_hash}
create_user = f"INSERT INTO users (username, password_hash, registered)" \
f"VALUES (:username, :password_hash, strftime('%s','now'))"
execute_query(connection, create_user, data_dict)
else:
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': False
}
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': True
}
def etl(endpoint, limit=500):
"""
arguments:
endpoint - Pipedrive endpoint to retreive data from
limit - the amount of data to retrieve in this batch
returns output message for failure/success
"""
if endpoint not in [*JSON_KEYS]:
print('{} is not a valid endpoint. Please try again.'.format(endpoint))
return False
start = db.execute_query(
conn,
"select start_nbr from endpoints where endpoint_nme = '{}';".format(
endpoint))
if start == -1: return False
start = start[0][0] # unpack tuple
# read max rows from most recently stored data
json_data, next_start = get_data(endpoint, start=start, limit=limit)
if (start == next_start) or (json_data is None):
print('No new data in {}. Check back later.'.format(endpoint))
return False
if endpoint == 'users': table = 'employees'
else: table = endpoint
table_cols = db.execute_query(
conn,
"select column_name from information_schema.columns where table_name = '{}';"
.format(table))
name_updates = [col_name for i in table_cols for col_name in i]
data = json_to_df(
json_data, # json data
JSON_KEYS[endpoint], # data names from json
name_updates # new column names for df
)
# write data to database (will rollback if not successful)
write_data = db.execute_values(conn, data, table)
if write_data == -1: return False
# increment tracker to new start
update_endpoint = db.execute_query(
conn,
"update endpoints set start_nbr = {pos}, update_dtm = current_timestamp where endpoint_nme = '{endpoint}'"
.format(pos=next_start, endpoint=endpoint))
if update_endpoint == -1: return False
print('Loaded {} data ({} to {}) to database.'.format(
endpoint, start, next_start))
return True
def mudar_senha(connection):
id = input('ID: ')
user = read_query(connection,
f"SELECT name FROM USERS WHERE ID = {id};")[0][0]
ConsoleLogger.log(f"Mudando senha do usuario: {user}")
nova_senha = input("Nova senha: ")
if nova_senha:
query = f"UPDATE users SET password = '******' WHERE id = {id};"
execute_query(connection, query)
ConsoleLogger.log("Senha mudada.")
def check_tables(tables):
"""
:param tables: table to check the existence of
:return: if the table is not present on the database it will be built during this process with relevant .sql file
"""
for t in tables:
if database.missing_table(t):
buildsql = open(f'buildTools/{t}.sql').read().strip()
database.execute_query(buildsql)
print(f'Table missing: building {t}:{dt.now()}')
else:
print(f'{t} present')
def ban(username, args, flag=1):
"""
Blocks user to log in.
Depending on the flag parameter, this function can block or unblock user.
Only moderators and administrators can execute this command
:param username: who executed the command
:param args: list with usernames to block
:param flag: switch, 1 - ban user, 0 - unban user
:return: dict of execution status
:rtype: {
'ok': bool
'result': str
}
"""
# Verify executer of permissions.
role = get_permissions(username)
if not role:
return {'ok': False, 'result': "User doesn't exist"}
elif role[0] not in (2, 3):
return {'ok': False, 'result': "You don't have permissions"}
all_usernames = get_registered()
all_usernames = sum(all_usernames, ())
# Find unregistered users.
if not all(username in all_usernames for username in args):
return {'ok': False, 'result': 'Not all users exist'}
connection = create_connection("data.sqlite3")
# Ban user.
if flag:
ban_users = f"UPDATE users " \
f"SET is_banned = 1 " \
f"WHERE username IN ({','.join(['?'] * len(args))})" \
f"AND role = 1"
execute_query(connection, ban_users, args)
result = 'Only users were banned<br>'
# Unban user.
else:
unban_users = f"UPDATE users " \
f"SET is_banned = 0 " \
f"WHERE username IN ({','.join(['?'] * len(args))})"
execute_query(connection, unban_users, args)
result = 'Users were unbanned<br>'
connection.close()
return {'ok': True, 'result': result}
def insert_subs(id,subfile):
with open(subfile, "r") as ins:
array = []
for line in ins:
array.append(line)
for idx, val in enumerate(array):
if "-->" in val:
line = val.split(" --> ")
time_start_shot = line[0].split(":")
starttime = int(float(time_start_shot[0]))*3600 + int(float(time_start_shot[1]))*60 + int(float(time_start_shot[2].split(",")[0]))
time_stop_shot = line[1].split(":")
endtime = int(float(time_stop_shot[0]))*3600 + int(float(time_stop_shot[1]))*60 + int(float(time_stop_shot[2].split(",")[0]))
execute_query('INSERT INTO subtitles ( episode, start_time, end_time, content) values ( \'{0}\',\'{1}\',\'{2}\',\"{3}\")'.format(id,datetime.timedelta(seconds=starttime),datetime.timedelta(seconds=endtime),array[idx+1].replace('"','')))
def new_phone_who_dis():
'''
Question 9
Update the phone number of a guest.
'''
guestID = int(input('Please enter guest ID'))
new_number = input('please enter new phone number: ')
s = "{" + new_number + "}"
query_string = f"update guest\
set phonenum = '{s}'\
where guestid = {guestID};"
database.execute_query(query_string)
return None
def messages_overflow():
"""Keeps last 300 messages in database every 10 minutes."""
if os.path.exists('./data.sqlite3'):
connection = create_connection("data.sqlite3")
delete_old_messages = "DELETE from messages WHERE id not in " \
"(SELECT id FROM messages ORDER BY time DESC LIMIT 300)"
execute_query(connection, delete_old_messages)
connection.close()
Timer(600, messages_overflow).start()
def update_database_object_from_api(obj, object_type, where_data):
object_data = obj.AsDict()
table_name = twitter_config.object_table_map[object_type.lower()]
object_data = object_data_dict_item_id(object_data)
valid_columns = filter_valid_columns(object_data, table_name)
update_query = database.generate_update_query(
table_name=table_name,
columns=valid_columns,
data=object_data,
where_data=where_data
)
database.execute_query(update_query)
def auth_user():
"""
Verifies user exists, password matches and whether user is banned.
request: {
"username": str,
"password": str
}
:return: dict of execution status
:rtype: {
"exist": bool,
"match": bool,
"banned": bool
}
"""
username = request.authorization.username
password = request.authorization.password
connection = create_connection("data.sqlite3")
select_user = f"SELECT password_hash, is_banned FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
if query_data is None:
connection.close()
return {'exist': False}
# Decrypt password hash.
password_hash = codec(query_data[0], 0)
# Compare entered password and hash from database.
if not check_password(password.encode(), password_hash):
connection.close()
return {'exist': True, 'match': False}
# Check if user is banned.
elif query_data[1] == 1:
connection.close()
return {'exist': True, 'match': True, 'banned': True}
set_online = f"UPDATE users " \
f"SET is_active = 1 " \
f"WHERE username LIKE :username"
execute_query(connection, set_online, {'username': username})
connection.close()
return {'exist': True, 'match': True, 'banned': False}
def create_highway_map(bbox, width, height):
x1, y1, x2, y2 = bbox.split(",")
cursor = db.execute_query(
"SELECT ST_Transform(linestring,3857), tags->'highway' FROM ways WHERE tags?'highway' AND ST_Xmin(ST_Transform(bbox,3857))>"
+ x1 + " AND ST_Xmax(ST_Transform(bbox,3857))<" + x2 +
" AND ST_Ymin(ST_Transform(bbox,3857))>" + y1 +
" AND ST_Ymax(ST_Transform(bbox,3857))<" + y2 + ";")
highway = []
coordinates = []
highway_color = {}
x1, y1, x2, y2 = float(x1), float(y1), float(x2), float(y2)
width, height = int(width), int(height)
for row in cursor:
highway.append(row[1])
coord_ajout = row[0]
normalize = [(width - (x2 - point.x) * width / (x2 - x1),
((y2 - point.y) * height / (y2 - y1)))
for point in coord_ajout]
coordinates.append(normalize)
image_highway = Image(width, height)
for x, i in enumerate(coordinates):
image_highway.draw_linestring(i, (0, 0, 0, 1))
image_highway.save("tuiles/" + bbox + ".png")
cursor.close()
db.close_connection()
def create_bill():
'''
Question 8
Consider creating a simple bill for a guest stating the property type, host, address, amount paid and payment type.
'''
print('Enter the guestID for the bill: ')
guestID = -1
while guestID < 1:
print('Please enter numerical value for desired option:')
try:
guestID = int(input())
except:
print('Invalid input format')
query_string = f"select property.buildingtype, host.firstname,host.middleinitial,host.lastname, address.addressl1,address.addressl2, address.city,address.province,address.country,address.postalcode, payment.amount, payment.typeofpayment from payment\
inner join payedfor\
on payment.paymentID = payedfor.paymentID\
inner join booking\
on payedfor.bookingid = booking.bookingid\
inner join property\
on booking.propertyid = property.propertyid\
inner join hasaddress\
on property.propertyid = hasaddress.propertyID\
inner join address\
on hasaddress.addressid = address.addressId\
inner join host\
on payment.receiverID = host.hostID\
where booking.guestID = {guestID};"
result = database.execute_query(query_string)
print('This is the output of the create bill')
for row in result:
print(row)
def get_all_the_corona_spreaders():
'''
Question 1
Give the details of all the guests who rented properties.
Display the columns as guest name, rentaltype, rentalprice, signingdate, branch, payment type and payment status.
Sort by the payment type in ascending order and signing date in descending order.
'''
query_string = "SELECT guest.firstname, guest.middleinitial, guest.lastname, property.buildingtype,property.price, booking.startdate, operatingin.branchid, payment.typeofpayment, payment.status from guest\
inner join hasbooking\
on hasbooking.guestid = guest.guestid\
inner join booking\
on hasbooking.bookingid = booking.bookingid\
inner join property\
on property.propertyid = booking.propertyid\
inner join operatingin\
on operatingin.propertyid = property.propertyid\
inner join payedfor\
on payedfor.bookingid = booking.bookingid\
inner join payment\
on payment.paymentid = payedfor.paymentid\
ORDER by payment.typeofpayment asc, booking.startdate desc;"
result = database.execute_query(query_string)
for row in result:
print(row)
def new_item():
if 'username' not in session:
return redirect('/login')
if request.method == 'POST':
data = request.form
user_email = session.get('email')
success = False
while success is False:
asin = ''.join(
random.choice(string.ascii_uppercase + string.digits
) for x in xrange(16))
query = """INSERT INTO items (name,
description,
price,
images,
browse_node_id,
posted_by,
asin)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
variables = [data['name'],
data['description'],
data['price'],
data['images'].split(','),
data['browse_node_id'],
user_email, asin, ]
success = execute_query(query, variables)
return redirect('/search_category?category='+data['browse_node_id'])
else:
return render_template("items_form.html")
def database_query():
if request.method == 'GET':
return render_template("query.html")
else:
query = request.form.get('query')
list = execute_query(query)
return jsonify({'results': list})
def list_available_properties():
query_string = "select property.* from property\
where availability = 'available'"
result = database.execute_query(query_string)
for row in result:
print(row)
def query_candidates(doc):
min = MinHash(num_perm=128)
keyword = doc.keyword.split(",")
for k in keyword:
time.sleep(2)
# print(k)
trans_text = translate_yandex(str(k), src="vi",
dest="en").encode("utf-8")
print(trans_text)
min.update(trans_text)
# result = forest.query(min, 3)
result = lsh.query(min)
result = ",".join(result)
if not result:
print(doc.title)
print("----------------------------------------------")
print("Not found")
print("\n")
else:
docs = mydb.execute_query(
"SELECT id, keyword, title FROM english WHERE id IN (" + result +
")")
titles = [
Article(id=item[0], keyword=item[1], title=item[2])
for item in docs
]
print(doc.title)
print("----------------------------------------------")
for i in titles:
print(i.title)
print("\n")
def xml_leaf_nodes():
list = execute_query("""SELECT browse_node_name, browse_node_id
FROM browse_nodes
WHERE leaf = TRUE
""")
nodes_dict = [{'name': x[0], 'id': x[1]} for x in list]
return dicttoxml.dicttoxml({"leaf_nodes": nodes_dict})
def new_item():
if 'username' not in session:
return redirect('/login')
if request.method == 'POST':
data = request.form
user_email = session.get('email')
success = False
while success is False:
asin = ''.join(
random.choice(string.ascii_uppercase + string.digits)
for x in xrange(16))
query = """INSERT INTO items (name,
description,
price,
images,
browse_node_id,
posted_by,
asin)
VALUES (%s, %s, %s, %s, %s, %s, %s)"""
variables = [
data['name'],
data['description'],
data['price'],
data['images'].split(','),
data['browse_node_id'],
user_email,
asin,
]
success = execute_query(query, variables)
return redirect('/search_category?category=' + data['browse_node_id'])
else:
return render_template("items_form.html")
def xml_leaf_nodes():
list = execute_query("""SELECT browse_node_name, browse_node_id
FROM browse_nodes
WHERE leaf = TRUE
""")
nodes_dict = [{'name': x[0], 'id': x[1]} for x in list]
return dicttoxml.dicttoxml({"leaf_nodes": nodes_dict})
def search(keywords):
"""
Serch for a given keyword in a node list
"""
raw_query =\
"SELECT tags->'name', ST_X(geom), ST_Y(geom), ST_Z(geom) " +\
"FROM Nodes " + \
"WHERE tags->'name' LIKE %s "
# Pour chaque keyword passé en paramètre, on rajoute un "OR" à la recherche
complete_query = raw_query + \
''.join(list(map(lambda _: " OR tags->'name' LIKE %s ", keywords[1:])))
# Exec
cursor = db.execute_query(
complete_query,
*keywords
)
# Affichage
print("Searched name \tX_coord\tY_coord\tZ_coord\t".expandtabs(30))
for row in cursor:
print('\t'.join([str(el) for el in row]).expandtabs(30))
cursor.close()
db.close_connection()
def execute_select(cls, filters=None, order_by=None):
query = "SELECT id, name, abbreviate FROM national_rank "
query += cls.prepare_where(filters) + cls.prepare_order(order_by)
result = execute_query(query)
return [NationalRank(*row) for row in result]
def rent():
data = [("Rent view",
("Date_Posted", "Rent_ID", "Building_No", "Street_No",
"Locality_Name", "City", "State", "Pin_Code", "Facing", "BHK",
"Parking", "Tenant_Type", "Monthly_Rent", "Advance_Amount"), [])]
database.other_query(
"create view buy_rent as select date_posted, Rent_ID, Building_No, Street_No, Locality_Name, City, State, Pin_Code, Facing, BHK, Parking, Tenant_Type, Monthly_Rent, Advance_amount from property, rent where property_id=rent_id and property_status='Unsold';"
)
data[0][2].extend(database.execute_query("SELECT * FROM buy_rent;"))
database.other_query("drop view buy_rent;")
if request.method == 'POST':
buyer_id = request.form['buyer_id']
rent_id = request.form['rent_id']
results = db.session.query(Rent).all()
if buyer_id == id1:
for r in results:
if r.rent_id == rent_id:
return redirect(
url_for('pay', plot_id=rent_id, amt=r.advance_amount))
else:
flash("Please enter correct client id")
return redirect(url_for('rent'))
return render_template("rent.html",
data=data,
enumerate=enumerate,
isinstance=isinstance,
datetime=datetime,
date_format=date_format)
def execute_select(cls, filters=None, order_by=None):
query = """SELECT
p.id, p.last_name, p.first_name, p.PIN, p.rating, p.is_active,
c.id, c.name,
c2.id, c2.name, c2.code,
r.id, r.name, r.abbreviate,
nr.id, nr.name, nr.abbreviate
FROM player p
LEFT JOIN city c ON p.city_id=c.id
LEFT JOIN country c2 ON c.country_id=c2.id
LEFT JOIN `rank` r ON p.rank_id=r.id
LEFT JOIN national_rank nr ON p.national_rank_id=nr.id"""
query += cls.prepare_where(filters, 'p')
query += cls.prepare_order(order_by or [['p.rating', False]])
result = execute_query(query)
players = []
for row in result:
player_values = row[:6]
city_values = row[6:8]
country_values = row[8:11]
rank_values = row[11:14]
national_rank_values = row[14:]
country = Country(*country_values)
city = City(*city_values, country=country)
rank = Rank(*rank_values)
national_rank = NationalRank(*national_rank_values)
player = Player(*player_values, city=city, rank=rank, national_rank=national_rank)
players.append(player)
return players
def get_subs(id,scene_filename):
with open(scene_filename, "r") as ins:
array = []
for line in ins:
array.append(line)
vals = []
target_file = open(id+'_scenes_subs.srt', 'w')
target_file.truncate()
for idx, val in enumerate(array):
if "-->" in val:
line = val.split(" --> ")
time_start_shot = line[0].split(":")
starttime = int(float(time_start_shot[0]))*3600 + int(float(time_start_shot[1]))*60 + int(float(time_start_shot[2]))
time_stop_shot = line[1].split(":")
endtime = int(float(time_stop_shot[0]))*3600 + int(float(time_stop_shot[1]))*60 + int(float(time_stop_shot[2]))
subtitles = execute_query('SELECT ID, content, alchemyapi FROM subtitles WHERE episode = \'{0}\' and start_time > \'{1}\' and end_time < \'{2}\''.format(id,datetime.timedelta(seconds=starttime),datetime.timedelta(seconds=endtime)))
target_file.write(array[idx+1].split("\n")[0])
target_file.write(' --> ')
chunks = ['']
ids = [[]]
chunk_index = 0
for line in subtitles:
chunks[chunk_index] += u' {0} '.format(line[1])
ids[chunk_index].append(str(line[0]))
for chunk_ids, chunk in zip(ids,chunks):
get_enrichment(chunk_ids,chunk,array[idx+1],target_file)
target_file.write('\n')
target_file.close()
def buy_house():
data = [("House view", ("Date_Posted", "House_ID", "House_No", "Street_No",
"Locality_Name", "City", "State", "Pin_Code",
"Facing", "BHK", "Parking", "Total_Cost"), [])]
database.other_query(
"create view buy_house as select date_posted, House_ID, House_No, Street_No, Locality_Name, City, State, Pin_Code, Facing, BHK, Parking, Total_Cost from property, house where property_id=house_id and property_status='Unsold';"
)
data[0][2].extend(database.execute_query("SELECT * FROM buy_house;"))
database.other_query("drop view buy_house;")
if request.method == 'POST':
buyer_id = request.form['buyer_id']
house_id = request.form['house_id']
results = db.session.query(House).all()
if buyer_id == id1:
for r in results:
if r.house_id == house_id:
return redirect(
url_for('pay', plot_id=house_id, amt=r.total_cost))
else:
flash("Please enter correct client id")
return redirect(url_for('buy_house'))
return render_template("buy_house.html",
data=data,
enumerate=enumerate,
isinstance=isinstance,
datetime=datetime,
date_format=date_format)
def get_subs(id, scene_filename):
with open(scene_filename, "r") as ins:
array = []
for line in ins:
array.append(line)
vals = []
target_file = open(id + '_scenes_subs.srt', 'w')
target_file.truncate()
for idx, val in enumerate(array):
if "-->" in val:
line = val.split(" --> ")
time_start_shot = line[0].split(":")
starttime = int(float(time_start_shot[0])) * 3600 + int(
float(time_start_shot[1])) * 60 + int(float(
time_start_shot[2]))
time_stop_shot = line[1].split(":")
endtime = int(float(time_stop_shot[0])) * 3600 + int(
float(time_stop_shot[1])) * 60 + int(float(time_stop_shot[2]))
subtitles = execute_query(
'SELECT ID, content, alchemyapi FROM subtitles WHERE episode = \'{0}\' and start_time > \'{1}\' and end_time < \'{2}\''
.format(id, datetime.timedelta(seconds=starttime),
datetime.timedelta(seconds=endtime)))
target_file.write(array[idx + 1].split("\n")[0])
target_file.write(' --> ')
chunks = ['']
ids = [[]]
chunk_index = 0
for line in subtitles:
chunks[chunk_index] += u' {0} '.format(line[1])
ids[chunk_index].append(str(line[0]))
for chunk_ids, chunk in zip(ids, chunks):
get_enrichment(chunk_ids, chunk, array[idx + 1], target_file)
target_file.write('\n')
target_file.close()
def database_query():
if request.method == 'GET':
return render_template("query.html")
else:
query = request.form.get('query')
list = execute_query(query)
return jsonify({'results': list})
def execute_select(cls, filters: Dict = None, order_by: Dict = None):
query = """SELECT
t.id, t.name, t.PIN, t.date_start, t.date_end, t.is_ranked,
c.id, c.name,
c2.id, c2.name, c2.code
FROM tournament t
LEFT JOIN city c ON t.city_id=c.id
LEFT JOIN country c2 ON c.country_id=c2.id"""
query += cls.prepare_where(filters, 't')
query += cls.prepare_order(order_by or [['t.date_start', False]])
result = execute_query(query)
tournaments = []
for row in result:
tournament_values = row[:6]
city_values = row[6:8]
country_values = row[8:]
country = Country(*country_values)
city = City(*city_values, country=country)
tournament = Tournament(*tournament_values, city=city)
tournaments.append(tournament)
return tournaments
def execute_delete(cls, ids: List[str]) -> None:
if not ids:
return
query_ids = ', '.join([str(id) for id in ids])
query = f'DELETE FROM {cls.table_name} WHERE id IN ({query_ids})'
return execute_query(query)
def category():
try:
category = request.args.get('category')
except AttributeError:
category = 'ROOT'
list = execute_query("""SELECT * FROM get_subcategories(%s)
ORDER BY name""", (category,))
return render_template("list.html", list=list)
def invoice(order_id):
wb = openpyxl.load_workbook("templates/prototype.xlsx", read_only=False)
sheet = wb.get_sheet_by_name("Invoice")
# fetch data from database (id, customer_id, date, total)
order = execute_query("""SELECT * FROM "Order" WHERE "id"={}""".format(order_id))[0]
# set INVOICE# = order_id
sheet['F5'].value = order[0]
# set date
sheet['H5'].value = order[2]
# set customer_id
sheet['F8'].value = order[1]
# fetch customer info (id, name, telephone, email, address_id, discount, vat)
customer_info = execute_query("""SELECT * FROM "Customer" WHERE "id"={}""".format(order[1]))[0]
# fetch address info (id, street, city, postcode)
address_info = execute_query("""SELECT * FROM "Address" WHERE "id"={}""".format(customer_info[4]))[0]
# set billing info
sheet['C8'].value = customer_info[1]
sheet['C9'].value = address_info[1]
sheet['C10'].value = f'{address_info[2]}, {address_info[3]}'
sheet['C11'].value = customer_info[2]
sheet['C12'].value = customer_info[3]
sheet['C13'].value = customer_info[6]
# fetch OrderItems (product_id, quantity, price)
order_items_info = execute_query(
"""SELECT product_id, quantity, price FROM "OrderItem" WHERE "order_id"={}""".format(order[0]))
# for each item fill the corresponding cells
current_row = 16
for i in range(len(order_items_info)):
product_info = \
execute_query("""SELECT name, price FROM "Product" WHERE "id"={}""".format(order_items_info[i][0]))[0]
sheet[f'A{current_row}'].value = product_info[0]
sheet[f'E{current_row}'].value = order_items_info[i][1]
sheet[f'F{current_row}'].value = customer_info[5]
sheet[f'G{current_row}'].value = product_info[1]
sheet[f'H{current_row}'].value = order_items_info[i][2]
current_row += 1
# save excel file to invoice folder
wb.save(f"invoice/invoice_{order_id}.xlsx")
wb.close()
def delete_item():
asin = request.form.get('asin')
if session.get('email') is None:
return redirect('/login')
query = execute_query("DELETE FROM items WHERE asin = %s", (asin, ))
if query:
return jsonify({'response': 'True'})
else:
return jsonify({'response': 'False'})
def delete_item():
asin = request.form.get('asin')
if session.get('email') is None:
return redirect('/login')
query = execute_query("DELETE FROM items WHERE asin = %s", (asin,))
if query:
return jsonify({'response': 'True'})
else:
return jsonify({'response': 'False'})
def get_subs(bbc_id):
subtitles = execute_query('SELECT ID, content, alchemyapi FROM subtitles WHERE episode = \'{0}\''.format(bbc_id))
chunks = ['']
ids = [[]]
chunk_index = 0
for line in subtitles:
chunks[chunk_index] += u' {0} '.format(line[1])
ids[chunk_index].append(str(line[0]))
for chunk_ids, chunk in zip(ids,chunks):
get_enrichment(chunk_ids,chunk,bbc_id)
def insert_database_object_from_api(obj, object_type):
object_data = obj.AsDict() if type(obj) != dict else obj
table_name = twitter_config.object_table_map[object_type.lower()]
object_data = object_data_dict_item_id(object_data)
valid_columns = filter_valid_columns(object_data, table_name)
insert_query = database.generate_insert_query(
table_name=table_name,
columns=valid_columns,
data=object_data
)
try:
database.execute_query(insert_query)
except pymssql.IntegrityError:
pass
except Exception as e:
print(insert_query)
print(object_data)
print(valid_columns)
print(e)
def xml_category_tree():
category = request.args.get('category')
if category is None:
query = execute_query("""SELECT browse_node_name,
browse_node_id,
children_tree
FROM browse_nodes
WHERE child_of = 'ROOT'
ORDER BY browse_node_name
""")
else:
query = execute_query("""SELECT browse_node_name,
browse_node_id,
children_tree
FROM browse_nodes
WHERE browse_node_id = %s
ORDER BY browse_node_name
""", (category,))
list = []
for x in query:
list.append({'name': x[0], 'id': x[1], 'tree': x[2]})
return dicttoxml.dicttoxml({'categories': list})
def xml_items_list():
category = request.args.get('category')
list = execute_query("""SELECT name, description, images, price, asin
FROM items
WHERE browse_node_id = %s
""", (category,))
items_dict = [{'name': x[0],
'description': x[1],
'image_URLs': x[2],
'price': x[3],
'id': x[4]}
for x in list]
return dicttoxml.dicttoxml({'items': items_dict})
import json
from alchemyapi import AlchemyAPI
from database import execute_query
alchemyapi = AlchemyAPI()
CHUNK_SIZE = 500 #words
programmes = execute_query('SELECT * FROM programmes')
for programme in programmes:
subtitles = execute_query('SELECT ID, content, alchemyapi FROM subtitles WHERE episode = \'{0}\''.format(programme[0]))
chunks = ['']
ids = [[]]
chunk_index = 0
for line in subtitles:
if line[2] == None: # ignores the line if it has already been annotated by AlchemyAPI (Dit kan je weghalen fab)
chunks[chunk_index] += u' {0} '.format(line[1])
ids[chunk_index].append(str(line[0]))
if len(chunks[chunk_index].split(' ')) >= CHUNK_SIZE:
chunk_index += 1
chunks.append('')
ids.append([])
print chunks
if chunks[0]:
print 'Going to annotate programme {0}, made {1} chunks of {2} words each.'.format(programme[0],len(chunks),CHUNK_SIZE)
for chunk_ids, chunk in zip(ids,chunks):
print chunk
get_enrichment(chunk_ids,chunk)
