def test_get_close_db(app): with app.app_context(): db = get_db() assert db is get_db() with pytest.raises(sqlite3.ProgrammingError) as e: db.execute('SELECT 1') assert 'closed' in str(e.value)
def app(): db_fd, db_path = tempfile.mkstemp() app = create_app({ 'TESTING': True, 'DATABASE': db_path, }) with app.app_context(): init_db() get_db().executescript(_data_sql) yield app os.close(db_fd) os.unlink(db_path)
def show_today(): db = get_db() todos = db.execute( ' SELECT i.id, note, due_date, done, user_id' ' FROM item i JOIN user u ON i.user_id = u.id ' ' WHERE i.due_date IS (?) AND user_id = (?)', (datetime.today().strftime('%Y-%m-%d'), g.user['id']) ).fetchall() return render_template('todo/today.html', todos = todos)
def show_report(): db = get_db() dates = db.execute( ' SELECT i.due_date, COUNT(*) count_all, SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) count_done, ' ' ROUND((1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100, 1) AS percentage ' ' FROM item i JOIN user u ON i.user_id = u.id ' ' WHERE i.user_id = (?) ' ' GROUP BY due_date ' ' ORDER BY due_date DESC', (g.user['id'], )).fetchall() return render_template('reports/list.html', dates=dates)
def apex_charts_daily_data(): db = get_db() today = date.today().strftime("%Y-%m-%d") data = db.execute( ' SELECT (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 percentage ' ' FROM item i JOIN user u ON i.user_id = u.id WHERE i.due_date = (?) AND i.user_id = (?)', (today, g.user['id'])).fetchone() data = dict(data) data["percentage"] = round(data["percentage"], 1) return jsonify(data)
def apex_charts_data(): db = get_db() data = db.execute( ' SELECT i.due_date, (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 percentage ' ' FROM item i JOIN user u ON i.user_id = u.id WHERE i.user_id = (?) GROUP BY due_date ', (g.user['id'], )).fetchall() data = dict(data) new_data = {} for k, v in zip(data.keys(), data.values()): new_data[k] = round(v, 1) return jsonify(new_data)
def test_register(client, app): assert client.get('/auth/register').status_code == 200 response = client.post('/auth/register', data={ 'username': '******', 'password': '******' }) assert 'http://localhost/auth/login' == response.headers['Location'] with app.app_context(): assert get_db().execute( "select * from user where username = '******'", ).fetchone() is not None
def export_to_csv(): date_from = request.form['date_from'] date_to = request.form['date_to'] db = get_db() dates = db.execute( ' SELECT i.due_date, COUNT(*) count_all, SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) count_done, ' ' (1.0 * SUM(CASE WHEN done==1 THEN 1 ELSE 0 end) / COUNT(*)) * 100 AS percentage ' ' FROM item i JOIN user u ON i.user_id = u.id ' ' WHERE i.user_id = (?) AND (i.due_date BETWEEN (?) AND (?))' ' GROUP BY due_date ' ' ORDER BY due_date DESC', (g.user['id'], date_from, date_to)).fetchall() io_file = io.StringIO() csv_writer = csv.writer(io_file) csv_writer.writerows(dates) output = make_response(io_file.getvalue()) output.headers["Content-Disposition"] = "attachment; filename=export.csv" output.headers["Content-type"] = "text/csv" return output
def create(): if request.method == 'POST': note = request.form['note'] due_date = request.form['due_date'] error = None if not note: error = 'Note is required.' if not due_date: error = 'Due date is required.' if error is not None: flash(error) else: db = get_db() db.execute( 'INSERT INTO item (note, due_date, user_id)' ' VALUES (?, ?, ?)', (note, due_date, g.user['id']) ) db.commit() return redirect(url_for('todo.index')) return render_template('todo/create.html')
def undone(id): db = get_db() db.execute('UPDATE item SET done = ? WHERE id = ?', (0, id)) db.commit() return redirect(url_for('todo.show_today'))
def delete(id): db = get_db() db.execute('DELETE FROM item WHERE id = ?', (id, )) db.commit() return redirect(url_for('todo.index'))
def index(): db = get_db() todos = db.execute( 'SELECT i.id, note, due_date, done, user_id' ' FROM item i JOIN user u ON i.user_id = u.id WHERE user_id = (?)', (g.user['id'],)).fetchall() return render_template('todo/index.html', todos = todos)