Example #1
0
def fileDelete():
    files_to_delete = request.form.getlist("files")
    deeb_data = query_db(
        "select fname, fhash from audiofiles where fname in ({})".format(
            ",".join(['?'] * len(files_to_delete))), tuple(files_to_delete))

    # Delete from DB
    deeb = get_db()
    cur = deeb.cursor()
    cur.execute(
        "DELETE FROM audiofiles WHERE fname in ({})".format(",".join(
            ['?'] * len(files_to_delete))), tuple(files_to_delete))
    deeb.commit()
    close_db()

    # Delete hash folder that holds all files
    for dat in deeb_data:
        shutil.rmtree(os.path.join(UPLOAD_FOLDER, 'test', dat['fhash']), True)

    retnames = []
    deeb_data = query_db("select fname from audiofiles")
    for dat in deeb_data:
        retnames.append(dat["fname"])

    return jsonify(names=retnames)
Example #2
0
def suche():
    form = SearchForm(request.form)
    if request.method == 'POST':
        results = []
        if request.form['name']:
            name = "%" + request.form['name'] + "%"
            results += query_db(
                "SELECT DISTINCT A.Name, A.Datum, A.Uhrzeit, A.Dirigent, P.Künstlername \
                                 FROM Aufführung_von A INNER JOIN singen s ON A.Datum=s.Datum AND A.Uhrzeit=s.Uhrzeit \
                                                       INNER JOIN Sänger P ON s.SozNr = P.Soznr \
                                                       WHERE A.Name LIKE ?",
                (name, ))
        elif request.form['dirigent']:
            dirigent = "%" + request.form['dirigent'] + "%"
            results += query_db(
                "SELECT * FROM Aufführung_von WHERE Dirigent LIKE ?",
                (dirigent, ))
        elif request.form['datum']:
            datum = request.form['datum']
            results += query_db(
                "SELECT * FROM Aufführung_von WHERE Datum LIKE ?", (datum, ))
        elif request.form['saenger']:
            saenger = "%" + request.form['saenger'] + "%"
            results += query_db(
                "SELECT DISTINCT A.Name, A.Datum, A.Uhrzeit, A.Dirigent, P.Künstlername \
                                 FROM Aufführung_von A INNER JOIN singen s ON A.Datum=s.Datum AND A.Uhrzeit=s.Uhrzeit \
                                                       INNER JOIN Sänger P ON s.SozNr = P.Soznr \
                                                       WHERE P.Künstlername LIKE ?",
                (saenger, ))
        return render_template("ergebnisse.html", results=results)

    return render_template("suche.html", form=form)
def catalog_import():
    query_db("DELETE FROM ps_pack", [], "new", commit=True)

    for t in [
            'pack', 'image', 'image_lang', 'image_shop', 'image_type',
            'manufacturer', 'manufacturer_lang', 'manufacturer_shop',
            'attribute', 'attribute_group', 'attribute_group_lang',
            'attribute_group_shop', 'attribute_impact', 'attribute_lang',
            'attribute_shop', 'feature', 'feature_shop', 'feature_lang',
            'feature_value', 'feature_value_lang', 'product',
            'product_attachment', 'product_attribute',
            'product_attribute_combination', 'product_attribute_image',
            'product_attribute_shop', 'product_carrier', 'product_lang',
            'product_sale', 'product_shop', 'product_supplier', 'product_tag',
            'product_country_tax', 'supplier', 'supplier_lang',
            'supplier_shop', 'supply_order', 'supply_order_detail', 'category',
            'category_group', 'category_lang', 'category_product',
            'category_shop', 'stock_available', "stock_mvt_reason",
            "stock_mvt_reason_lang", 'product', 'product_sale',
            'product_comment', 'product_comment_criterion',
            'product_comment_criterion_category',
            'product_comment_criterion_lang',
            'product_comment_criterion_product', 'product_comment_grade',
            'product_comment_report', 'product_comment_usefulness'
    ]:
        copy1t1("psNew2_" + t, "ps_" + t, [], clear_new_table=True)
Example #4
0
def download(channelid):
    f = StringIO()
    writer = csv.writer(f,
                        quotechar='"',
                        quoting=csv.QUOTE_ALL,
                        lineterminator="\n")

    if channelid == 'channels' and current_user.is_authenticated:
        writer.writerow([
            'Channel Id', 'Channel name', 'Published at', 'subscriberCount',
            'subscriberChange', 'viewCount', 'viewChange', 'videoCount',
            'videoChange', 'commentCount', 'commentChange'
        ])
        downloadFileName = 'channels' + '-' + datetime.datetime.now().strftime(
            '%y%m%d') + '.csv'
        for channel in query_db(
                'select user_channel.channelid,channel.title,channel.publish_at,channel.subscriberCount,channel.viewCount,channel.videoCount,channel.commentCount,channel.subscriberChange,channel.viewChange,channel.videoChange,channel.commentChange from user_channel left outer join channel on user_channel.channelid = channel.channelid where user_channel.userid = ?',
            [current_user.id]):
            writer.writerow([
                channel['channelid'], channel['title'], channel['publish_at'],
                channel['subscriberCount'], channel['subscriberChange'],
                channel['viewCount'], channel['viewChange'],
                channel['videoCount'], channel['videoChange'],
                channel['commentCount'], channel['commentChange']
            ])
    else:
        newdata = query_db('select * from channel where channelid = ?',
                           (channelid, ), True)
        if newdata:
            downloadFileName = newdata['title'] + '-' + datetime.datetime.now(
            ).strftime('%y%m%d') + '.csv'
        else:
            downloadFileName = channelid + '-' + datetime.datetime.now(
            ).strftime('%y%m%d') + '.csv'
        if youtubechecker.check_channelid(channelid):
            writer.writerow([
                'Date', 'subscriberCount', 'subscriberChange', 'viewCount',
                'viewChange', 'videoCount', 'videoChange', 'commentCount',
                'commentChange'
            ])
            for channel in query_db(
                    'select * from channel_history where channelid = ?',
                [channelid]):
                writer.writerow([
                    channel['date'], channel['subscriberCount'],
                    channel['subscriberChange'], channel['viewCount'],
                    channel['viewChange'], channel['videoCount'],
                    channel['videoChange'], channel['commentCount'],
                    channel['commentChange']
                ])
        else:
            return 'err: channel ID'

    res = make_response()
    res.data = f.getvalue()
    res.headers['Content-Type'] = 'text/csv'
    res.headers['Content-Disposition'] = functions.rfc5987_content_disposition(
        downloadFileName)  #'attachment; filename='+ downloadFileName
    return res
Example #5
0
def cashDep(username,password):
	account_number, balance, __ = db.query_db(db.display_customer_data_query,username,password)[0]
	amt = int(input('Enter amount to deposit: '))
	final_amt = int(balance) + amt
	db.query_db(db.transfer_to_query,amt,account_number)
	db.logger.debug('Deposit successful for : {}'.format(account_number))
	print('Deposit of',amt,'Successful! Available balance is:',final_amt)
	return True
Example #6
0
def create_reply(post_id, text, img, user, ip):
    sql_string = """insert into reply(reply_post_id, user, date, reply, img_filename, img_uid, ip)
                        values (?, ?, strftime('%Y-%m-%d %H:%M', 'now', 'localtime'), ?, ?, ?, ?);"""

    filename, uid = get_filename_uid_from_img(img)
    text = make_none(text)
    params = [post_id, user, text, filename, uid, ip]
    query_db(sql_string, params)
Example #7
0
def main():
    cur_page = request.args.get("page") if request.args.get("page") else '0'
    order = request.args.get("order") if request.args.get("order") else '0'
    num = 30  # number of list
    num_range = 2  # number of around current pager item
    if not (re.match('^[0-9]+$', cur_page) and re.match('^[0-9]+$', order)):
        return 'page or order error'
    order_query = {}
    numdata = query_db('select count(*) from channel', order_query, True)
    num_all = int(numdata[0])  # all amount of data
    orderstr = settings.channel_order[int(order)]['val']
    order_query.update({'start': int(cur_page) * num, 'num': num})
    newdata = query_db(
        'select * from channel order by {} limit :start, :num'.format(
            orderstr), order_query)
    page_max = int(num_all / num)  # max page number ( 0 start)
    pager_list = [0]
    #pager_start = 0 if int(cur_page) - 2 - (0 if int(cur_page) + 2 <= page_max else page_max - int(cur_page) + 2) < 0 else int(cur_page) - 2 - (0 if int(cur_page) + 2 <= page_max else page_max - int(cur_page) + 2)
    pager_start = max(
        num_range - 1,
        int(cur_page) - num_range -
        max(int(cur_page) + num_range - page_max, 0))
    #pager_end = page_max if int(cur_page) + 2 + abs(0 if int(cur_page) - 2 > 0 else int(cur_page) - 2) > page_max else int(cur_page) + 2 + abs(0 if int(cur_page) - 2 > 0 else int(cur_page) - 2)
    pager_end = min(
        page_max - 1,
        int(cur_page) + num_range + abs(min(0,
                                            int(cur_page) - num_range)))
    pager_list.extend(list(range(pager_start, pager_end + 1)))
    pager_list.append(page_max)
    pager_list = list(dict.fromkeys(pager_list))
    #for item in newdata:

    pager = {
        'page':
        int(cur_page),
        'num':
        num,
        'page_min':
        0,
        'page_max':
        page_max,
        'page_min_ommit':
        True if len(pager_list) > 1 and pager_list[1] != 1 else False,
        'page_max_ommit':
        True if len(pager_list) > 1
        and pager_list[len(pager_list) - 2] != page_max - 1 else False,
        'num_all':
        num_all,
        'list':
        pager_list
    }
    order = {'order': int(order), 'list': settings.channel_order}
    return render_template('main.html',
                           title='Channels',
                           data=newdata,
                           pager=pager,
                           order=order)
Example #8
0
def send_notify_from_user(user_id, notify_token, category):
    if 'channel' in category:
        result = list()
        for channel in query_db(
                'select user_channel.channelid,channel.title,channel.publish_at,channel.subscriberCount,channel.viewCount,channel.videoCount,channel.commentCount,channel.subscriberChange,channel.viewChange,channel.videoChange,channel.commentChange from user_channel left outer join channel on user_channel.channelid = channel.channelid where user_channel.userid = ?',
            [user_id]):
            result.append(channel['title'] + ' 🔔: ' + functions.human_format(
                int(0 if channel['subscriberCount'] is None else
                    channel['subscriberCount'])) + ' (' +
                          str(change_format(channel['subscriberChange'])) +
                          ')' + ' 👀: ' +
                          functions.human_format(int(channel['viewCount'])) +
                          ' (' + str(change_format(channel['viewChange'])) +
                          ')' + ' 🎞: ' +
                          functions.human_format(int(channel['videoCount'])) +
                          ' (' + str(change_format(channel['videoChange'])) +
                          ')')
        n = 10
        for split_result in [
                result[idx:idx + n] for idx in range(0, len(result), n)
        ]:  # リストを10ずつ分割(長すぎると切れるため)
            send_line_notify(
                '\n' + '\n'.join(split_result),
                notify_token)  # 結果が入ったリストを改行で結合させて,LINE Notifyで結果をLINEに送信する

    if 'video' in category:
        video_result = list()
        for video in query_db(
                'select user_video.videoid,video.title,video.publish_at,video.duration,video.viewCount,video.likeCount,video.dislikeCount,video.commentCount,video.viewChange,video.likeChange,video.dislikeChange,video.commentChange from user_video left outer join video on user_video.videoid = video.videoid where user_video.userid = ?',
            [user_id]):
            video_result.append(video['title'][:15] + ' 👀: ' +
                                functions.human_format(
                                    int(0 if video['viewCount'] is None else
                                        video['viewCount'])) + ' (' +
                                str(change_format(video['viewChange'])) + ')' +
                                ' 👍: ' + functions.human_format(
                                    int(0 if video['likeCount'] is None else
                                        video['likeCount'])) + ' (' +
                                str(change_format(video['likeChange'])) + ')' +
                                ' 👎: ' + functions.human_format(
                                    int(0 if video['dislikeCount'] is None else
                                        video['dislikeCount'])) + ' (' +
                                str(change_format(video['dislikeChange'])) +
                                ')' + ' 💬: ' + functions.human_format(
                                    int(0 if video['commentCount'] is None else
                                        video['commentCount'])) + ' (' +
                                str(change_format(video['commentChange'])) +
                                ')')
        n = 10
        for split_result in [
                video_result[idx:idx + n]
                for idx in range(0, len(video_result), n)
        ]:  # リストを10ずつ分割(長すぎると切れるため)
            send_line_notify(
                '\n' + '\n'.join(split_result),
                notify_token)  # 結果が入ったリストを改行で結合させて,LINE Notifyで結果をLINEに送信する
Example #9
0
 def test_remove_duplicate_movies(self):
     with flask_app.test_request_context():
         sql = 'select count(_id) as fcount from movies'
         expected_count = 1
         for i in range(2):
             execute_sql('insert into movies(name, imdb_id) values(?, ?)', ['duplicate_movie_name_' + str(i), 'tt9999999'])
         count = query_db(sql, one=True)['fcount']
         MovieManager().remove_duplicate_movies()
         actual_count = count - query_db(sql, one=True)['fcount']
         assert actual_count == expected_count, 'actual: {0}, expected: {1}'.format(actual_count, expected_count)
         print('OK - test_remove_duplicate_movies')
Example #10
0
def archives(keyword):
    """
    展示Archives部分的内容
    :param keyword:
    :return:
    """

    select_sql = "SELECT * FROM posts WHERE ceated_at > '"+keyword+"-00-00 00:00:00 ' ORDER BY id DESC"
    rows = db.query_db(select_sql)
    select_sql = "select DISTINCT tag_name from posts_tags"
    tag = db.query_db(select_sql)
    return render_template('tags.html', keyword=keyword,tag=tag, rows=rows)
Example #11
0
def cashWdr(username, password):
    account_number, balance, __ = db.query_db(db.display_customer_data_query,
                                              username, password)[0]
    amt = int(input('Enter amount to withdraw: '))
    if amt <= int(balance):
        final_amt = int(balance) - amt
        db.query_db(db.transfer_from_query, amt, account_number)
        print('Withdrawal successful! available balance is:', final_amt)
        return True
    else:
        print('Insufficient account balance.')
        return False
Example #12
0
def push_event(ip, event=None):
    if event == None:
        event = get_event(ip)

    if event:
        assert ip == event.ip
        sql_string = """update event
            set last_event_date = ?
            where ip = ?;"""
        query_db(sql_string, [time(), ip])
    else:
        sql_string = """insert into event(ip) values (?);"""
        query_db(sql_string, [ip], one=True)
Example #13
0
def post(post_path):
    """
    展示文章
    :param post_path:
    :return:
    """
    select_sql = "select * from posts where url ='"+post_path+"'"
    content = db.query_db(select_sql, one=True)


    select_sql = "select DISTINCT tag_name from posts_tags"
    tag = db.query_db(select_sql)


    return render_template('post.html', post_path=content[4], tag=tag)
Example #14
0
def channelvideos(channelid):
    newdata = query_db('select * from channel where channelid = ?',
                       (channelid, ), True)
    result = query_db(
        'SELECT json_extract(video, "$") as video FROM channel_video WHERE channelid = ? ',
        [channelid], True)
    videos = list()
    if result:
        result = None if result['video'] is None else json.loads(
            result['video'])
        for video in result:
            detail = video.get('detail')
            snippet = detail.get('snippet') if detail != None else None
            contentDetails = detail.get(
                'contentDetails') if detail != None else None
            statistics = detail.get('statistics') if detail != None else None

            title = snippet.get('title') if snippet != None else ''
            duration = functions.duration_format(
                contentDetails.get('duration').replace('PT', '').replace(
                    'H', ':').replace('M', ':').replace(
                        'S', '')) if contentDetails != None else ''
            viewCount = statistics.get(
                'viewCount') if statistics != None else ''
            likeCount = statistics.get(
                'likeCount') if statistics != None else ''
            dislikeCount = statistics.get(
                'dislikeCount') if statistics != None else ''
            commentCount = statistics.get(
                'commentCount') if statistics != None else ''
            publishedAt = snippet.get('publishedAt').replace('Z', '').replace(
                'T', ' ') if snippet != None else ''

            videos.append({
                'videoid': video.get('videoId'),
                'title': title,
                'duration': duration,
                'viewCount': viewCount,
                'likeCount': likeCount,
                'dislikeCount': dislikeCount,
                'commentCount': commentCount,
                'publishedAt': publishedAt
            })

    return render_template('channelvideos.html',
                           title='Videos for ' + newdata['title'],
                           data=newdata,
                           videos=videos)
Example #15
0
def get_posts(since):
    sql_string = """
        select * from post where date >= date(?) order by post_id desc;
    """
    rows = query_db(sql_string, args=[since])
    posts = [make_post_lite(r) for r in rows] if rows else None
    return posts
Example #16
0
def get_replies(since):
    sql_string = """
        select * from reply where date >= date(?) order by reply_id desc;
    """
    rows = query_db(sql_string, args=[since])
    replies = make_replies(rows) if rows else None
    return replies
Example #17
0
def get_feedbacks(since):
    sql_string = """
        select * from feedback where date >= date(?) order by feedback_id desc;
    """
    rows = query_db(sql_string, args=[since])
    feedbacks = [make_feedback(row) for row in rows] if rows else None
    return feedbacks
Example #18
0
 def post(self):
     data = parser_beers.parse_args()
     styles = ','.join(
         map(lambda str: f"'{str}'", json.loads(data['style'])))
     query = f'select * from data_beers2 where data_beers2.country=? and abv between ? and ? and style in ({styles})order by average desc limit 10'
     req = query_db(query, args=(data['country'], data['min'], data['max']))
     return req
Example #19
0
 def post(self):
     data = parser_beers_volume.parse_args()
     styles = ','.join(
         map(lambda str: f"'{str}'", json.loads(data['style'])))
     query = f'''select country,sum(sumScore)/sum(nbBeer) as average from data_beers2 where abv between ? and ? and style in ({styles}) and country!="undefined" group by country order by average desc'''
     req = query_db(query, (data['min'], data['max']))
     return req
Example #20
0
def get_reports(since):
    sql_string = """
        select * from report where date >= date(?) order by report_id desc;
    """
    rows = query_db(sql_string, args=[since])
    reports = [make_report(row) for row in rows] if rows else None
    return reports
Example #21
0
def create_reply_to_reply(post_id, parent_reply_id, text, img, user, ip):
    """
    Replies and replies to replies go in the same SQLite table.

        reply_id   parent_reply_id     text
        1          null                drawing is fun!
        2          1                   what did you draw?
        3          2                   a pink elephant.
        4          1                   drawing takes too much patience.
    """
    sql_string = """insert into reply(reply_post_id, parent_reply_id, user, date, reply, img_filename, img_uid, ip)
                        values (?, ?, ?, strftime('%Y-%m-%d %H:%M', 'now', 'localtime'), ?, ?, ?, ?);"""
    filename, uid = get_filename_uid_from_img(img)
    text = make_none(text)
    params = [post_id, parent_reply_id, user, text, filename, uid, ip]
    query_db(sql_string, params)
Example #22
0
def pushtime():
    hour = "09"
    min = "15"

    if request.method == "POST":
        hour = request.form["hour"]
        min = request.form["min"]
        if re.match('^[0-9]{4}$', hour + min):
            db = get_db()
            db.execute(
                "UPDATE user set push_time = ? WHERE id = ?",
                (hour + min, current_user.id),
            )
            db.commit()
            flash('Your notify time was updated!', 'alert-success')
            if session.get("newregister") == 1:
                return redirect(url_for("gonotify"))
            #else:
            #    return redirect(url_for("account"))
        else:
            return 'err: invalid value'

    userdata = query_db('select push_time from user where id = ?',
                        (current_user.id, ), True)
    if userdata:
        hour = userdata['push_time'][:2]
        min = userdata['push_time'][2:]
    newdata = {'title': 'Notify time', 'hour': hour, 'min': min}
    return render_template('pushtime.html',
                           title=newdata['title'],
                           data=newdata)
Example #23
0
def get_board_names():
    sql_string = """
        select board_name
        from board;
    """
    rows = query_db(sql_string)
    return set(row['board_name'] for row in rows) if rows else None
Example #24
0
def login():
    username = request.form['username']
    password = request.form['password']
    action = request.form['action']
    if action == 'Login':
        user = check_password(username, password)
        if user is not None:
            session['session_id'] = user['id']
            session['user_id'] = user['id']
            session['logged_in'] = True
            session['username'] = user['username']
            session['is_admin'] = (user.get('group_id') == app.config['ADMIN_GROUP_ID'])
            flash('Welcome ' + user['username'], 'flash')
        else:
            flash('Login failed.', 'error')
    elif action =='Register':
        username = request.form['username']
        password = request.form['password']
        check = query_db('SELECT * FROM users WHERE username = ?', [username], one=True)
        if check is None:
            create_user(username, password)
            flash('You successfully registered as ' + username, 'flash')
        else:
            flash('Your username, ' + username + ', is already taken.', 'error')
    return redirect(url_for('home'))
Example #25
0
 def get_all():
     result = query_db('SELECT * from Posts ORDER BY timestamp DESC')
     posts = []
     for r in result:
         posts.append(Post(r['author_id'], r['content'], r[
                      'attachment_name'], r['timestamp']))
     return posts
Example #26
0
def login():
    if request.method == 'POST':
        # Get Form Fields
        username = request.form['username']
        password_candidate = request.form['password']
        result = query_db("SELECT * FROM users WHERE username = ?",
                          (username, ),
                          one=True)
        if result:
            # Get stored hash
            password = result['password']
            soznr = result['SozNr']
            # Compare Passwords
            if pbkdf2_sha256.verify(password_candidate, password):
                # Passed
                session['logged_in'] = True
                session['username'] = username
                session['soznr'] = soznr

                flash('You are now logged in', 'success')
                return redirect(url_for('index'))
            else:
                error = 'Invalid login'
                return render_template('login.html', error=error)
        else:
            error = 'Username not found'
            return render_template('login.html', error=error)

    return render_template('login.html')
Example #27
0
def getFileNames():
    retnames = []
    deeb_data = query_db("select fname from audiofiles")
    for dat in deeb_data:
        retnames.append(dat["fname"])

    return jsonify(names=retnames)
Example #28
0
 def get_posts_by_user_id(user_id):
     result = query_db('SELECT * from Posts WHERE author_id = ?', [user_id])
     posts = []
     for r in result:
         posts.append(Post(r['author_id'], r['content'], r[
                      'attachment_name'], r['timestamp']))
     return posts
Example #29
0
def get_itinerary(start, destination):
    # We check if the starting and ending point begins by B1 or B3
    if not start.startswith('B3') and not start.startswith('B1'):
        raise ValueError(
            f'Start name should start with either B1 or B3. Got {start}')
    if not destination.startswith('B3') and not destination.startswith('B1'):
        raise ValueError(
            f'Start name should start with either B1 or B3. Got {destination}')

    # We get all the rooms saved in the database
    rooms = query_db("SELECT name, linked_to FROM points")

    graph = Graph()
    for room in rooms:
        # We convert each room into a dictionary and we get the current room name
        room = dict(room)
        room_name = room.get("name", "")
        if room_name:
            # For each room connected to our current room
            for room_connected_and_distance in room.get("linked_to",
                                                        "").split("|"):
                # On the connected room we get the name of the room and the distance
                room_connected, distance = room_connected_and_distance.split(
                    ";")
                # We add the connection in our graph
                graph.add_edge(room_name, room_connected, int(distance))

    # We generate our dijkstra and we search ou path
    dijkstra = DijkstraSPF(graph, start)
    itinerary_list = dijkstra.get_path(destination)
    return itinerary_list
Example #30
0
def new_post():
    if request.method == "POST":
        mar = request.form.get("markdown")  # 编辑元吗
        html = request.form.get("html")  # HTML 源码
        title = request.form.get("title")  # 标题
        tag = request.form.get("tag")  # 标签
        tag_list = tag.split(',')
        user_id = session['user']
        # user_id = int(user)
        # 存储 标题信息

        sql = "INSERT INTO posts(`url`, `title`, `content_text`, `content_html`,user_id) VALUES  \
               ('%s','%s','%s','%s','%d')" % (title, title, mar, html, user_id)
        db.update_db(sql)
        # 存储标签内容
        title_selectsql = "SELECT * FROM posts WHERE title='" + title + "'"
        rows = db.query_db(title_selectsql, one=True)
        for i in tag_list:
            post_tag_sql = "INSERT INTO posts_tags(post_id,tag_name) value('%d','%s')" % (
                rows[0], i)
            db.update_db(post_tag_sql)

        return mar + "---" + html + "---" + title + "---" + str(
            tag_list) + 'l:' + str(rows[0])
    # return "错误了"
    return render_template('new.html')
Example #31
0
 def get_latest_posts(amount):
     result = query_db('SELECT * from Posts LIMIT ?', (amount,))
     posts = []
     for r in result:
         posts.append(Post(r['author_id'], r['content'], r[
                      'attachment_name'], r['timestamp']))
     return posts
Example #32
0
 def has_more_results(self, keywords, page_size=DEFAULT_PAGE_SIZE, page_number=0):
     sql = u"select _id from movies where is_active = 'true' limit 1 offset {0}".format((page_number + 1)*page_size)
     args = []
     if keywords:
         sql = u"select _id from movies where is_active = 'true' and name like ? limit 1 offset {0}".format((page_number + 1)*page_size)
         args=['%' + keywords + '%']
     result = query_db(query=sql, args=args, one=True)
     return True if result else False
Example #33
0
 def get_all_movies(self, page_size=DEFAULT_PAGE_SIZE, page_number=0, include_not_active=False):
     if page_size <= 0:
         page_size = DEFAULT_PAGE_SIZE
     if page_number < 0:
         page_number = 0
     sql = u"select * from movies" + (" where is_active = 'true' " if not include_not_active else '') + " order by ratings desc limit {0} offset {1}".format(page_size, page_number*page_size)
     result = query_db(sql)
     return self.wrap_results(result, page_number, page_size, '')
Example #34
0
def get_linked_points_of_point(point):
    if not point.startswith('B3') and not point.startswith('B1'):
        raise ValueError(
            f'Point name should start with either B1 or B3. Got {point}')
    return [
        list(row)[0] for row in query_db(
            f"SELECT name FROM points WHERE linked_to LIKE '%{point}%'")
    ]
Example #35
0
def user_timeline(username):
    """Display's a users tweets."""
    profile_user = query_db('select * from user where username = ?',
                            [username], one=True)
    if profile_user is None:
        abort(404)
    followed = False
    if g.user:
        followed = query_db('''select 1 from follower where
            follower.who_id = ? and follower.whom_id = ?''',
            [session['user_id'], profile_user['user_id']],
            one=True) is not None
    return render_template('timeline.html', messages=query_db('''
            select message.*, user.* from message, user where
            user.user_id = message.author_id and user.user_id = ?
            order by message.pub_date desc limit ?''',
            [profile_user['user_id'], PER_PAGE]), followed=followed,
            profile_user=profile_user)
Example #36
0
 def search(self, keywords, page_size=DEFAULT_PAGE_SIZE, page_number=0):
     if page_size <= 0:
         page_size = DEFAULT_PAGE_SIZE
     if page_number < 0:
         page_number = 0
     result = []
     sql = u"select * from movies where is_active = 'true' and name like ? or also_known_as like ? or plot_keywords like ? or tags like ? order by ratings desc limit {0} offset {1}".format(page_size, page_number*page_size)
     if keywords:
         result = query_db(sql, ['%' + keywords + '%', '%' + keywords + '%', '%' + keywords + '%', '%' + keywords + '%'])
     return self.wrap_results(result, page_number, page_size, keywords)
Example #37
0
def explore():
    curUser = ""
    if current_user.is_authenticated():
        curUser = db.curUsername(current_user.get_id())
    
    nodes = db.query_db("select * from nodes",[],one=False)
    
    #cur = g.db.execute('select * from nodes order by id')
    #nodes = [dict(name=row[0]) for row in cur.fetchall()]
    return render_template('explore.html', nodes=json.dumps(nodes), curUser= curUser)
Example #38
0
def score_answer(question_id, answer, test_id, score_table='scores', print_only=False):
        # get in memory map of all questions and points for displaying results
        questions = db.query_db('select * from questions')
        questions_map = dict()
        for question in questions:
            questions_map[question['id']] = (question['question'], question['points'])

        # get all keyword sets
        rows = db.query_db('select * from keywords where question_id = ?', [question_id])

        answered_words = preprocess_words(answer)

        # evaluate answered words against each set of answer keywords
        points = 0
        criteria_id = ''
        for row in rows:
            keywords = preprocess_words(row['ordered_keywords'])
            tmp_criteria_id = choose_criteria(keywords, answered_words)
            tmp_points = choose_score(question_id, tmp_criteria_id)
            # keep only the highest point value and corresponding criteria id
            if tmp_points > points:
                points = tmp_points
                criteria_id = tmp_criteria_id

        # save score to database
        fields = ('id', 'test_id', 'question_id', 'answered', 'score', 'criteria_id')
        values = (str(uuid.uuid4())[:5], test_id, question_id, answer, points, criteria_id)

        if not print_only:
            db.insert(score_table, fields=fields, values=values)
        else:
            print(points)

        # create score dict with added fields for display purposes and append to test scores list
        score_dict = dict()
        for idx, key in enumerate(fields):
            score_dict[key] = values[idx]
            if key == 'question_id':
                score_dict['question'], score_dict['points_possible'] = questions_map[question_id]

        return score_dict
Example #39
0
def search_execute():
    q = query_db('SELECT thread FROM posts WHERE message LIKE ? ORDER BY time', ['%' + request.form['query'] + '%'])
    unique_threads = list()
    for p in q:
        if p['thread'] in unique_threads:
            continue
        else:
            unique_threads.append(p['thread'])
    query = 'SELECT threads.id AS id, users.username AS author, threads.author AS user_id, \
            threads.title AS title FROM threads, users  WHERE threads.id = ? AND users.id == threads.author'
    thread_query = list()
    if len(unique_threads) > 0:
        for x in unique_threads:
            thread_query.append(query_db(query, [x], one=True))
    else:
        return jsonify(good=False)
    boilerplate = "<tr class=search_result id=thread_%d><td>%s</td><td>%s</td></tr>"
    threads = ""
    for t in thread_query:
        threads += (boilerplate % (t['id'], t['title'], t['author']))
    return jsonify(good=True,threads=threads)
Example #40
0
def timeline():
    """Shows a users timeline or if no user is logged in it will
    redirect to the public timeline.  This timeline shows the user's
    messages as well as all the messages of followed users.
    """
    if not g.user:
        return redirect(url_for('public_timeline'))
    return render_template('timeline.html', messages=query_db('''
        select message.*, user.* from message, user
        where message.author_id = user.user_id and (
            user.user_id = ? or
            user.user_id in (select whom_id from follower
                                    where who_id = ?))
        order by message.pub_date desc limit ?''',
        [session['user_id'], session['user_id'], PER_PAGE]))
Example #41
0
def login():
    """Logs the user in."""
    if g.user:
        return redirect(url_for('timeline'))
    error = None
    if request.method == 'POST':
        user = query_db('''select * from user where
            username = ?''', [request.form['username']], one=True)
        if user is None:
            error = 'Invalid username'
        elif not check_password_hash(user['pw_hash'],
                                     request.form['password']):
            error = 'Invalid password'
        else:
            flash('You were logged in')
            session['user_id'] = user['user_id']
            return redirect(url_for('timeline'))
    return render_template('login.html', error=error)
Example #42
0
def choose_score(question_id, criteria_id):
        score = 0
        row = db.query_db('select * from questions where id = ?',
                          [question_id],
                          one=True)
        possible = row['points']
        level_max = 3
        level = 0
        if criteria_id == 'criteria-1':
            level = 3
        elif criteria_id == 'criteria-2':
            level = 2
        elif criteria_id == 'criteria-3':
            level = 1
        elif criteria_id == 'criteria-4':
            level = 0
        scale = level/level_max
        score = scale * possible
        return int(math.ceil(score))
Example #43
0
def querylastdata(jianqu):
    sql = 'select * from jianqu where createtime = (select max(createtime) from jianqu where jianqu = \'%s\' )' % jianqu
    jianqu = db.query_db(sql)
    return jianqu
Example #44
0
def view_thread(thread_id):
    query = query_db('SELECT users.username AS author, users.signature AS signature, posts.author AS user_id, posts.first_post AS first_post, posts.id AS id, posts.message AS message, datetime(posts.time, "unixepoch") AS time FROM posts, users WHERE thread = ? AND users.id = posts.author ORDER BY time', [thread_id])
    topic = query_db('SELECT title,forum FROM threads WHERE id = ?', [thread_id], one=True)
    forum = query_db('SELECT name FROM forums WHERE id = ?', [topic['forum']], one=True)
    return render_template('view_thread.html', thread_id=thread_id, posts=query, thread_topic=topic['title'], forum_id=topic['forum'], forum_name=forum['name'])
Example #45
0
def queryall():
    sql = 'select * from kanshou'
    sql2 = 'select * from jianqu'
    kanshou = db.query_db(sql)
    jianqu = db.query_db(sql2)
    return kanshou,jianqu
Example #46
0
def querylastks():
    sql = 'select * from kanshou where createtime = (select max(createtime) from kanshou)'
    kanshou = db.query_db(sql)
    return kanshou
Example #47
0
def delete_all():
    sql = 'truncate table jianqu'
    if db.query_db(sql):
        return True
    else:
        return False
Example #48
0
def public_timeline():
    """Displays the latest messages of all users."""
    return render_template('timeline.html', messages=query_db('''
        select message.*, user.* from message, user
        where message.author_id = user.user_id
        order by message.pub_date desc limit ?''', [PER_PAGE]))
Example #49
0
def new_post(thread_id):
    if request.method == 'POST':
        forum_id = query_db('SELECT forum FROM threads WHERE id = ?', [thread_id], one=True)['forum']
        insert_post(forum_id, thread_id, request.form['message'], session['user_id'])
        return redirect(url_for('view_thread', thread_id=thread_id))
    return render_template('new_post.html', thread_id=thread_id)
Example #50
0
def delete_thread(thread_id):
    q = query_db('SELECT author FROM threads WHERE id = ?', [thread_id], one=True)
    pred = (q['author'] == session['user_id'])
    if pred:
        drop_thread(thread_id)
    return jsonify(delete=pred)
Example #51
0
def update_profile():
    error = ''
    flash_msg = ''
    cur_pass = request.args.get('cur_pass')
    if cur_pass is None:
        pass
    elif cur_pass == '':
        error = 'You must always provide your current password.'
    else:
        q = check_password(session['username'], cur_pass)
        if q is None:
            error = 'Current password was incorrect. No operation was executed.'
        else:
            set_query = ''
            query_params = list()
            new_pass1 = request.args.get('new_pass1')
            new_pass2 = request.args.get('new_pass2')
            old_signature = q['signature']
            new_signature = request.args.get('signature')
            coins_buy = request.args.get('buycoins')
            coins_sell = request.args.get('sellcoins')
            coins_trade_amt = request.args.get('tradecoins_amt')
            coins_trade_dest= request.args.get('tradecoins_dest')
            coins = start_coins = q['coins']
            if new_pass1 != '':
                if new_pass1 != new_pass2:
                    error += 'New passwords do not match. Password was not changed.<br>'
                else:
                    set_query += 'password = ?, '
                    query_params.append(new_pass1)
                    flash_msg += 'Password changed.<br>'
            if old_signature != new_signature:
                set_query += 'signature = ?, '
                query_params.append(new_signature)
                flash_msg += 'Signature changed.<br>'
            if coins_buy:
                if coins_buy.isdigit():
                    coins += int(coins_buy)
                    flash_msg += 'Purchased %s coins.<br>' % coins_buy
                else:
                    error += 'Could not buy coins. The value given was invalid.<br>'
            if coins_sell:
                if coins_sell.isdigit():
                    coins -= int(coins_sell)
                    flash_msg += 'Sold %s coins.<br>' % coins_sell
                else:
                    error += 'Could not sell coins. The value given was invalid.<br>'
            if coins_trade_dest:
                trade_dest = query_db('SELECT id FROM users WHERE username = ?', [coins_trade_dest], one=True)
                if trade_dest is not None:
                    if coins_trade_amt.isdigit():
                        coins -= int(coins_trade_amt)
                        query_db('UPDATE users SET coins = ? WHERE id = ?', [coins_trade_amt, trade_dest['id']])
                        flash_msg += 'Transferred %s coins to %s.<br>' % [coins_trade_amt, coins_trade_dest]
                    else:
                        error += 'Could not trade coins. The value given was invalid.<br>'
                else:
                    error += 'Could not trade coins. The recipient does not exist.<br>'
            set_query += 'coins = ? '
            if coins < 0:
                query_params.append(start_coins)
                error += 'Unable to process transaction. You do not have enough coins.<br>'
            else:
                query_params.append(coins)
            query_params.append(session['user_id'])
            if set_query != '':
                query_db('UPDATE users SET ' + set_query + 'WHERE id = ?', query_params)
    if error != '':
        flash(Markup(error), 'error')
    if flash_msg != '':
        flash(Markup(flash_msg), 'flash')
    coins = query_db('SELECT coins FROM users WHERE id = ?', [session['user_id']], one=True)['coins']
    return render_template('update_profile.html', coins=coins)
Example #52
0
def delete_post(post_id):
    q = query_db('SELECT author, first_post FROM posts WHERE id = ?', [post_id], one=True)
    pred = (q['author'] == session['user_id']) and not q['first_post']
    if pred:
        drop_post(post_id)
    return jsonify(delete=pred)
Example #53
0
def home():
    forums = None
    if session.get('logged_in'):
        forums = query_db('SELECT * FROM forums')
    return render_template('home.html', forums=forums)
Example #54
0
def load_user(id):
    g.db = db.connect_db() # Since this isn't a request we need to connect first.
    user = db.query_db('select * from users where id = ?', [id], one=True)
    g.db.close()
    return User(user['id'], user['username'], user['password'], user['name'], user['city'])
Example #55
0
def view_forum(forum_id):
    threads = query_db('SELECT threads.id AS id, threads.title AS name, threads.post_count AS post_count, users.username AS author, threads.author AS user_id FROM threads, users WHERE threads.author = users.id AND forum = ' + str(forum_id))
    forum_name = query_db('SELECT name FROM forums WHERE id = ' + str(forum_id), one=True)
    return render_template('view_forum.html', threads=threads, forum_id=forum_id, forum_name=forum_name['name'])
Example #56
0
 def get_total_count(self):
     sql = u"select count(_id) as mcount from movies where is_active = 'true'"
     result = query_db(query=sql, one=True)
     return int(result['mcount']) if result else 0
Example #57
0
File: views.py Project: bobais/ec
def list_emails():
    result = query_db('''SELECT * FROM emails''')

    return render_template('email-list.html', emails=result)