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