def main():
pygame.init()
screen_size = [SCREEN_WIDTH, SCREEN_HEIGHT]
screen = pygame.display.set_mode(screen_size)
pygame.display.set_caption(GAME_NAME)
done = False
clock = pygame.time.Clock()
#TEST
db.create_connection(DB_PATH)
c = db.connect(DB_PATH)
b = Bridge("b1", 1, 1, 1, 1, 1, 1, 1, 1)
db.add_bridge(c, b)
#/TEST
while not done:
for event in pygame.event.get():
if event.type == pygame.QUIT:
done = True
screen.fill((0, 0, 0))
clock.tick(60)
pygame.display.flip()
pygame.quit()
def home():
x = random.randint(-100, 100)
y = random.randint(-100, 100)
statement_str = generate_statement_string(2)
tree = BinTree.build_tree(statement_str)
statement_result = BinTree.solve_tree(tree, x, y)
form = forms.TrueOrFalseForm(flask.request.form)
if form.validate_on_submit():
if not flask_login.current_user.is_anonymous:
if form.choice.data == form.hidden.data:
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "UPDATE users SET score = score + 2 WHERE id = %s"
cursor.execute(sql, flask_login.current_user.get_id())
connection.commit()
flask.flash('Correct! +2 points!', 'success')
else:
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "UPDATE users SET score = score - 1 WHERE id = %s"
cursor.execute(sql, flask_login.current_user.primary_id)
connection.commit()
flask.flash('Incorrect! -1 points!', 'error')
else:
if form.choice.data == form.hidden.data:
flask.flash('Correct!', 'success')
else:
flask.flash('Incorrect!', 'error')
elif form.errors:
for item in form.errors.items():
flask.flash(item, 'error')
return flask.render_template('home.html', x_value=str(x), y_value=str(y), statement=statement_str,
result=str(statement_result), form=form)
def show(category_name):
category = create_connection().execute(
'SELECT id, name FROM categories where name = ?', (category_name,)
).fetchone()
items = create_connection().execute(
'SELECT id, title FROM items where category = ?', (category[0],)
).fetchall()
return render_template('categories/show.jinja2', category=category, items=items)
def delete(category_name):
if request.method == 'POST':
db = create_connection()
db.execute('DELETE FROM categories where name = ?', (category_name,))
db.commit()
return redirect(url_for('categories.index'))
category = create_connection().execute(
'SELECT id, name FROM categories where name = ?', (category_name,)
).fetchone()
return render_template('categories/delete.jinja2', category=category)
def login():
if not flask_login.current_user.is_anonymous:
flask.flash('Already logged in!', 'error')
return flask.redirect('/')
login_form = forms.LoginForm(prefix='login_form')
signup_form = forms.SignupForm(prefix='signup_form')
if signup_form.register.data and signup_form.validate_on_submit():
with db.create_connection() as connection:
with connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE username=%s OR email=%s"
cursor.execute(sql, (signup_form.username.data, signup_form.email.data))
result = cursor.fetchone()
if result:
flask.flash('Username or email already exist!', 'error')
return flask.redirect('/login')
with connection.cursor() as cursor:
sql = "INSERT INTO users (username, email, password, score) VALUES (%s, %s, SHA1(%s), 0)"
cursor.execute(sql, (signup_form.username.data, signup_form.email.data, signup_form.password.data))
connection.commit()
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE username=%s AND password=SHA1(%s)"
cursor.execute(sql, (signup_form.username.data, signup_form.password.data))
result = cursor.fetchone()
if flask_login.login_user(load_user(result['id'])):
flask.flash('User created and logged in!', 'success')
return flask.redirect('/')
else:
flask.flash('User created but not logged in.')
elif signup_form.errors:
for item in signup_form.errors.items():
flask.flash(item, 'error')
if login_form.login.data and login_form.validate_on_submit():
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE username=%s AND password=SHA1(%s)"
cursor.execute(sql, (login_form.username.data, login_form.password.data))
result = cursor.fetchone()
if result:
if flask_login.login_user(load_user(result['id']), remember=login_form.remember_me.data):
flask.flash('Logged in!', 'success')
return flask.redirect('/')
else:
flask.flash('Sorry, something went wrong.', 'error')
else:
flask.flash('Invalid username or password.', 'error')
elif login_form.errors:
for item in login_form.errors.items():
flask.flash(item, 'error')
return flask.render_template('login.html', login_form=login_form, signup_form=signup_form)
def home():
x = random.randint(-100, 100)
y = random.randint(-100, 100)
if flask_login.current_user.is_anonymous:
statement_str = generate_statement_string(2)
else:
statement_str = generate_statement_string(
flask_login.current_user.difficulty)
tree = BinTree.build_tree(statement_str)
statement_result = BinTree.solve_tree(tree, x, y)
form = forms.TrueOrFalseForm(flask.request.form)
if form.validate_on_submit():
if not flask_login.current_user.is_anonymous:
difficulty = flask_login.current_user.difficulty
if form.choice.data == form.hidden.data:
with db.create_connection() as connection, connection.cursor(
) as cursor:
sql = "UPDATE users SET score = score + %s WHERE id = %s"
cursor.execute(
sql, (difficulty, flask_login.current_user.primary_id))
connection.commit()
flask.flash('Correct! +{0} points!'.format(difficulty),
'success')
else:
with db.create_connection() as connection, connection.cursor(
) as cursor:
sql = "UPDATE users SET score = score - %s WHERE id = %s"
cursor.execute(
sql,
(difficulty / 2, flask_login.current_user.primary_id))
connection.commit()
flask.flash(
'Incorrect! -{0} points!'.format(difficulty / 2),
'error')
else:
if form.choice.data == form.hidden.data:
flask.flash('Correct!', 'success')
else:
flask.flash('Incorrect!', 'error')
elif form.errors:
for item in form.errors.items():
flask.flash(item, 'error')
return flask.render_template('home.html',
x_value=str(x),
y_value=str(y),
statement=statement_str,
result=str(statement_result),
form=form)
def keepAlive(self):
if self.connection is None:
return
try:
log('sending keep alive... ', False, True)
t0 = time.time()
db.execute_query(self.connection, 'select * from dummy', [])
t1 = time.time()
log('ok: %s ms' % (str(round(t1 - t0, 3))), True)
except dbException as e:
log('Trigger autoreconnect...')
try:
conn = db.create_connection(self.server)
if conn is not None:
self.connection = conn
log('Connection restored automatically')
else:
log('Some connection issue, give up')
self.connection = None
except:
log('Connection lost, give up')
# print disable the timer?
self.connection = None
def login():
"""
Endpoint: 'api/login'
EXAMPLE Parameter http://127.0.0.1:5000/api/login
Verb: GET
Parameter: user_token, name, email
Output: { 'drink_id': [], 'drink_name' = [], 'drink_rating' = []}
"""
try:
data = request.get_json()
user_token = data['user_token']
name = data['name']
email = data['email']
sql_check = f"""select user_token from account
where user_token = '{user_token}'
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_check)
if not output:
sql_insert = f"""INSERT INTO account VALUES('{user_token}', '{name}', '{email}', {1})
"""
db.execute_query(conn, sql_insert)
else:
sql_update = f"""UPDATE account set status = 1 where user_token = '{user_token}'
"""
db.execute_query(conn, sql_update)
conn.close()
return dumps({'is_success': 200})
except:
return dumps({'is_success': 500})
def drinks_sold():
"""
Endpoint: '/drinks_sold'
EXAMPLE Parameter http://127.0.0.1:5000/api/drink_sold_where?drink_id=1
Verb: GET
Parameter: drink_id
Output: { 'shop_name' : [], 'price' : [] }
"""
drink_id = request.args.get('drink_id')
sql_query = f"""select shop.name, shop.town, menu.price, shop.picture_file, shop.id from menu
inner join shop on menu.shop_id = shop.id
where menu.drink_id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
drink_sold_where_dict = {
'shop_name': [],
'prices': [],
'pictures': [],
'ids': []
}
for row in output:
drink_sold_where_dict['shop_name'].append(f"{row[0]} {row[1]}")
drink_sold_where_dict['prices'].append(str(row[2]).format('.2f'))
drink_sold_where_dict['pictures'].append(row[3])
drink_sold_where_dict['ids'].append(row[4])
conn.close()
return dumps(drink_sold_where_dict)
def update_ratings():
"""
Endpoint: '/update_ratings'
EXAMPLE Parameter http://127.0.0.1:5000/api/update_ratings?rating=3.0&drink_id=1
Verb: POST
Parameter: rating, drink_id in json dict
Output: { 'old_rating' : curr_rating, 'new_rating' : new_rating}
"""
# returns none if no input
data = request.get_json()
rating = data['rating']
drink_id = data['drink_id']
# query the database for current rating
sql_query = f"""select rating from drink where id = {drink_id}"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
curr_rating = output[0][0]
# NOTE: need to include num of ratings to determien average
new_rating = round((curr_rating + float(rating)) / 2.0, 2)
update_post_description = f"""update drink set rating = {new_rating} WHERE id = {drink_id}"""
db.execute_query(conn, update_post_description)
# test change
# select_users = "SELECT * FROM drink"
# out = db.execute_read_query(conn, select_users)
conn.close()
return dumps({
'old_rating': curr_rating,
'new_rating': new_rating,
# 'output' : out,
})
def search_shops():
"""
Endpoint: '/search_shops'
EXAMPLE Parameter http://127.0.0.1:5000/api/api/search_shop?search_term=""
Verb: GET
Parameter: search_term
Output: { 'shop_name': [], 'shop_town' = [], 'shop_city' = [], 'shop_country': []}
"""
search_term = request.args.get('search_term')
if search_term:
sql_query = f"""select * from shop
where shop.name ilike '%{search_term}%'
"""
else:
sql_query = f"""select * from shop
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
shops_dict = {'shop_ids': [], 'shop_names': [], 'shop_pics': []}
for row in output:
shops_dict['shop_ids'].append(row[0])
shops_dict['shop_names'].append(f"{row[1]} {row[2]}")
shops_dict['shop_pics'].append(row[-1])
conn.close()
return dumps(shops_dict)
def search_drinks():
"""
Endpoint: 'api/search_drinks'
EXAMPLE Parameter http://127.0.0.1:5000/api/api/search_drinks?search_term=""
Verb: GET
Parameter: search_term
Output: { 'drink_id': [], 'drink_name' = [], 'drink_rating' = []}
"""
search_term = request.args.get('search_term')
if search_term:
sql_query = f"""select * from drink
where name ilike '%{search_term}%'
"""
else:
sql_query = f"""select * from drink
"""
conn = db.create_connection()
output = db.execute_read_query(conn, sql_query)
drinks_dict = {
'drink_ids': [],
'drink_names': [],
'drink_pictures': [],
'drink_ratings': []
}
for row in output:
drinks_dict['drink_ids'].append(row[0])
drinks_dict['drink_names'].append(row[1])
drinks_dict['drink_pictures'].append(row[2])
drinks_dict['drink_ratings'].append(row[3])
conn.close()
return dumps(drinks_dict)
def get_schedule(callback_query):
weekday = callback_query.data.split(':')[1]
what_week = callback_query.data.split(':')[2]
addition = 0 if what_week == 'current' else 1
week_num = int(redis.get('current_week').decode('utf8')) + addition
connection = db.create_connection()
try:
schedule = db.get_or_create_schedule(connection, weekday, redis, callback_query, week_num)
kb = telebot.types.InlineKeyboardMarkup()
for i in schedule:
print(i)
text = f'{i.num_object}) {i.object} {i.auditory}'
btn = telebot.types.InlineKeyboardButton(text=text, callback_data=f'get_info:{i.slug}:{what_week}')
kb.row(btn)
btn = telebot.types.InlineKeyboardButton(text='Назад', callback_data=f'weekdays:{what_week}')
kb.row(btn)
try:
bot.edit_message_text(
f'Вы выбрали {weekday.capitalize()}. Можете нажать на предмет, чтобы получить более подробную информацию',
callback_query.message.chat.id, message_id=callback_query.message.message_id,
reply_markup=kb)
except ApiException:
pass
except exceptions.MpeiBotException as e:
bot.answer_callback_query(callback_query.id, e.message, show_alert=True)
def add_interval(params):
"""Добавление интервала в базу данных"""
params_start = local_to_utc(params.start)
params_end = local_to_utc(params.end)
if params_start.minute % 15 == 0 and params_end.minute % 15 == 0:
with db.create_connection(params.path) as con:
cur = con.cursor()
insert_list = []
while params_start < params_end:
cur.execute(
"SELECT start_interval FROM Slots WHERE start_interval == (?)",
[params_start])
interval_list = cur.fetchall()
if len(interval_list) > 0:
assert len(interval_list) == 1
interval_tuple = interval_list[0]
assert len(interval_tuple) == 1
interval = interval_tuple[0]
finish_interval = params_start + timedelta(minutes=15)
print('interval ({} - {}) already exist'.format(
utc_to_local_format(interval),
utc_to_local_format(finish_interval.isoformat())))
else:
insert_list.append((params_start, ))
params_start += timedelta(minutes=15)
cur.executemany("INSERT INTO Slots (start_interval) VALUES (?)",
insert_list)
else:
mes = 'Введите интервал кратный 15 минутам'
print(mes)
return mes
def create():
db = create_connection()
if request.method == 'POST':
title = request.form['title']
description = request.form['description']
category = request.form['category']
errors = []
if not title:
errors.append('Title is required.')
elif not description:
errors.append('Description is required.')
elif not category:
errors.append('Category is required.')
elif not g.user:
errors.append('You must be logged in to add an item.')
if not errors:
db.execute(
'insert into items (title, description, category, owner) values (?, ?, ?, ?)',
(
title,
description,
category,
g.user[0],
))
db.commit()
return redirect(url_for('item.show', item_title=title))
flash(errors)
categories = db.execute('select id, name from categories').fetchall()
return render_template('items/create.jinja2', categories=categories)
def moreimgs():
""" Read list of json files or return one specific for specific time """
hour_back1 = request.args.get('hour_back1', default=1, type=int)
hour_back2 = request.args.get('hour_back2', default=0, type=int)
object_of_interest = request.args.get('object_of_interest', type=None)
#print("object_of_interest: " + str(object_of_interest)[1:-1])
cam = request.args.get('cam', default=0, type=int)
if hour_back1 != '':
hour_back1 = int(hour_back1)
else:
hour_back1 = 0 # default value: 60 min back
if hour_back2 != '':
hour_back2 = int(hour_back2)
else:
hour_back2 = 1 # default value: 60 min back
print(
"cam: {}, hour_back1:{}, hour_back2:{}, object_of_interest: {}".format(
cam, hour_back1, hour_back2, object_of_interest))
conn = db.create_connection(SQLITE_DB, ipaddress)
rows = db.select_last_frames(conn,
cam=cam,
time1=hour_back1,
time2=hour_back2,
obj=object_of_interest)
return Response(json.dumps(rows), mimetype='text/plain')
def plot_followers(database):
def pop_std(x):
return x.std(ddof=0)
con = db.create_connection(database)
df = pd.read_sql_query("SELECT * FROM user", con)
df_followers = df.groupby(df.party, as_index=False).agg(
{'followers_count': ['mean', 'std', 'mad']})
df_followers.columns = ['party', 'mean', 'std', 'mad']
df_followers = df_followers.reindex(columns=df_followers.columns)
df_followers.T.to_csv("followers_count.csv")
_, ax = plt.subplots()
ax.errorbar(df_followers['party'],
df_followers['mean'],
yerr=df_followers['std'],
fmt='o',
color='black',
ecolor='lightgray',
elinewidth=3,
capsize=0)
# ax.set_yscale('log')
ymax = df_followers['mean'].max() + df_followers['std'].max() + 1000
ax.set_ylim([0, ymax])
ax.set_xlabel('Party')
ax.set_ylabel('Followers Count')
ax.set_title('Mean and std of followers per party')
plt.savefig('followers_per_party.png')
def add_multi_urls():
conn = db.create_connection(r"cat5test.db")
cursor = conn.cursor()
update_queries = []
insert_queries = []
while True:
for row in cursor.execute(
'SELECT * FROM categories WHERE last_update IS NULL ORDER BY id DESC LIMIT 5'
):
try:
items = get_page_urls(str(row[6]))
print(len(items))
for item in items:
insert_queries.append(
(item, row[1], row[2], row[3], row[4], row[5]))
update_queries.append([str(row[0])])
except:
print('error ID ' + str(row[0]))
continue
update_queries = []
insert_queries = []
cursor.executemany(
"UPDATE categories SET last_update = date('now') WHERE id = (?)",
update_queries)
cursor.executemany(
"INSERT INTO items (url,lvl1,lvl2,lvl3,lvl4,lvl5) VALUES (?,?,?,?,?,?)",
insert_queries)
conn.commit()
def register():
if request.method == 'POST':
email = request.form['email']
name = request.form['name']
password = request.form['password']
db = create_connection()
error = None
if not email:
error = 'Email is required.'
elif not password:
error = 'Password is required.'
elif not name:
error = 'Name is required.'
elif db.execute(
'SELECT id FROM users WHERE email = ?', (email,)
).fetchone() is not None:
error = 'Email {0} is already registered.'.format(email)
if error is None:
# input is valid, register the user, redirect to login
db.execute(
'INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)',
(email, name, hash_pass(password))
)
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('register.jinja2')
def gen_array_of_imgs(cam, delta=10, currentime=int(time.time() * 1000)):
time1 = currentime - delta
time2 = currentime + delta
conn = db.create_connection(SQLITE_DB)
rows = db.select_frame_by_time(conn, cam, time1, time2)
x = json.dumps(rows)
return x
def login():
# Validate login info against db
if request.method == 'POST':
email = request.form['email']
password = request.form['password']
db = create_connection()
error = None
user = db.execute(
'SELECT id, password_hash FROM users WHERE email = ?', (email,)
).fetchone()
if user is None:
error = 'No user associated with that email.'
elif not check_pass(password, user[1]):
error = 'Incorrect login information.'
if error is None:
session.clear()
session['user_id'] = user[0]
return redirect(url_for('index'))
# Send some feedback to the user about what went wrong
flash(error)
return render_template('login.jinja2')
def adduser():
try:
conn = db.create_connection(config.DBFile())
if request.form['name'] and request.form['tag'] and request.form[
'email'] and request.form['phone']:
retval = db.new_user(conn, request.form['tag'],
request.form['email'], request.form['phone'],
request.form['name'])
if retval == -1:
return render_template(
'message.html',
message=_('Are you sure you\'re not already registered?'))
elif retval == -2:
return render_template(
'message.html',
message=_('Something went wrong, try again later'))
else:
return render_template('message.html',
message=_('Welcome, ') +
request.form['tag'])
else:
return render_template('message.html',
message=_('Missing some items...'))
except:
return render_template(
'message.html',
message=_('Something wrong happened!<br />Already registered?'))
def find_colocations(subject):
with db.create_connection() as conn:
colocations = []
for visit in subject.visits:
colocations.extend(compare_visit(conn, visit))
return colocations
def list():
"""Gets commit history
Returns:
(list): List containing commit history, including commit_id, build_date, build_logs and url
"""
conn = db.create_connection(r"commit_history")
return db.select_all(conn)
def test_delete_by_time(self):
time.sleep(2)
conn = db.create_connection(DB_LOCATION)
print("Test delete records n minutes before")
predicate = "'-1 seconds'"
db.delete_frames_later_then(conn,predicate)
rows = db.select_all_stats(conn)
self.assertEqual(len(rows),0);
def serialize_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = create_connection().execute(
'SELECT * FROM users WHERE id = ?', (user_id, )).fetchone()
def test_select_frame_by_time(self):
conn = db.create_connection(DB_LOCATION)
# get time 1 second before creation
time1 = now - datetime.timedelta(seconds = 1)
# get time 1 second after creation
time2 = now + datetime.timedelta(seconds = 1)
ls = db.select_frame_by_time(conn, 0 , time1, time2)
self.assertEqual(len(ls), 1)
print(json.dumps(ls,indent=4))
def profile(name):
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE username=%s"
cursor.execute(sql, (name))
result = cursor.fetchone()
if not result:
flask.flash('No such user!')
return flask.redirect('/')
return flask.render_template('profile.html', score=result['score'], username=name)
def gen_params(cam=0, hours=1, currentime=time.time()):
"""Parameters streaming generator function."""
time1 = currentime - hours * 60 * 60
print("time1: {} now: {}".format(time1, currentime))
conn = db.create_connection(SQLITE_DB)
ls = db.select_statistic_by_time(conn, cam, time1, currentime)
ret = json.dumps(ls) # , indent = 4)
logger.debug(ret)
return ret
def add_test_dataset_into_db(title='test_dataset'):
tmp_dir, tmp_files = create_test_files_pack(title, 10)
with create_connection() as conn:
for i, f in enumerate(tmp_files):
execute_query(
conn,
f'INSERT INTO datasets (id, title, filename) VALUES ("{i}", "{title}", "{f}");'
)
return tmp_dir, tmp_files
def test_insert(self):
conn = db.create_connection(DB_LOCATION)
day = "{date:%Y-%m-%d}".format(date=now)
print("Current day: " + day)
time = "{time:%Y-%m-%d %H:%M:%S}".format(time=now)
print("Current time: " + time)
hashcode = 12345467890 * r.randint(1, 5000)
numpy_array = np.random.rand(100,100)
print("numpy array: {} ".format(numpy_array) )
db.insert_frame(conn, hashcode=hashcode, date=day, time=time, type="car" , numpy_array=numpy_array, x_dim=100, y_dim=100,cam=0)
def profile(name):
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE username=%s"
cursor.execute(sql, (name))
result = cursor.fetchone()
if not result:
flask.flash('No such user!')
return flask.redirect('/')
return flask.render_template('profile.html',
score=result['score'],
username=name)
def load_user(user_id):
try:
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE id=%s"
cursor.execute(sql, (user_id))
result = cursor.fetchone()
if result:
return User(result['username'], result['id'])
return None
except:
return None
def populate_db(db_file, quotes_file):
with open(quotes_file, 'r') as f:
quotes = f.readlines()
conn = create_connection(db_file)
cur = conn.cursor()
for quote in quotes:
try:
cur.execute(f"INSERT INTO quotes(text) VALUES(?);", (quote, ))
conn.commit()
except Error as e:
logger.error(e)
def leaderboard():
with db.create_connection() as connection, connection.cursor() as cursor:
sql = "SELECT * FROM users ORDER BY score DESC"
cursor.execute(sql)
user_dict = cursor.fetchall()
return flask.render_template('leaderboard.html', user_dict=user_dict)
def main():
conn = db.create_connection()
db.create_schema(conn)
db.add_some_data(conn)
txt = input("Enter a keyword. ")
db.find_some_data(conn, txt)
