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 get_comments(id):
db = get_db()
db.row_factory = dict_factory
cur = db.cursor()
result = []
fillable = ['id', 'comment', 'user']
comments = cur.execute(
'select id, comment, user_id from comments'
).fetchall()
print(comments)
for comment in comments:
p = dict()
for field in fillable:
value = comment.get(field)
if field == 'user':
value = db.execute(
'select * from users where id = ?',
(comment.get('user_id'), )
).fetchone()
p[field] = value
result.append(p)
return result
def create(username, password, name, email, kind):
usr = User()
usr.username = username
usr.password = password
usr.name = name
usr.email = email
usr.kind = kind
# Make sure kind is valid
if not User.valid_kind(kind):
return False
db = get_db()
# Check for reapeated username
if db.users.find({"username": username}).count() > 0:
return False
id = db.users.insert({
"username": username,
"password": password,
"name": name,
"email": email,
"kind": kind
})
usr.id = id
return usr
def login():
if request.method == "POST":
errors = dict()
db = get_db()
fillable = ['email', 'password']
for field in fillable:
if not request.form[field]:
errors[field] = 'Поле обязательное'
user = db.execute('SELECT * FROM users WHERE email = ?',
(request.form['email'], )).fetchone()
if (user is None) or (user and not check_password_hash(
user['password'], request.form['password'])):
errors['email'] = 'Неверные Логин или Пароль'
errors['password'] = '******'
flash(errors)
if len(errors) == 0:
session.clear()
session['user'] = user['id']
return redirect(url_for('news.home'))
return render_template('auth/login.html')
def query(params=None):
cursor = get_db().recipes.find(params)
out = list()
for doc in cursor:
recipe = Recipe(db_recipe=doc)
out.append(recipe)
return out
def query(params=None):
cursor = get_db().inventory.find(params)
out = list()
for doc in cursor:
recipe = Item(db_item=doc)
out.append(recipe)
return out
def query(params=None):
cursor = get_db().materials.find(params)
out = list()
for doc in cursor:
recipe = Material(db_material=doc)
out.append(recipe)
return out
def create(name, desc, detail, img_url, cost, ingredients, src, time,
category):
in_list = list()
for val in ingredients:
mat = Material.get_from_id(val['material-id'])
if not mat:
return False
try:
val['quantity'] = int(val['quantity'])
except:
return False
val['name'] = mat.name
val['units'] = mat.units
in_list.append(val)
id = get_db().recipes.insert({
"name": name,
"desc": desc,
"detail": detail,
"img_url": img_url,
"cost": cost,
"ingredients": in_list,
"src": src,
"time": time,
"category": category.lower()
})
return id
def downvote():
db = get_db()
_username = request.form['username']
_password = request.form['password']
_post_title = request.form['title']
if (_username == "" and _password == ""):
return Response(json.dumps({"message": "Provide login information"}),
status=404,
content_type="application/json")
login_id = db.execute(
'SELECT id FROM users WHERE username = ? and password = ?',
(_username, _password)).fetchone()
if login_id is None:
return Response(json.dumps(
{"message": "Create an account to upvote and downvote"}),
status=404,
content_type="application/json")
post_id = db.execute('SELECT id FROM posts WHERE title = ?',
(_post_title, )).fetchone()
if post_id is None:
return Response(json.dumps({"message": "Post not fund"}),
status=404,
content_type="application/json")
db.execute('UPDATE posts SET downvotes=downvotes+1 WHERE id = ?',
(post_id))
db.commit()
return Response(status=201)
def sendMessage():
db = get_db()
_userfrom = request.form['userfrom']
_userto = request.form['userto']
_message = request.form['messagecontent']
_flag = request.form['flag']
if _userfrom == "":
return Response(json.dumps({"message": "No user from"}),
status=404,
content_type="application/json")
if _userto == "":
return Response(json.dumps({"message": "No user to"}),
status=404,
content_type="application/json")
if _message == "":
return Response(json.dumps({"message": "No message"}),
status=404,
content_type="application/json")
else:
db.execute(
'INSERT INTO messages (userfrom,userto,messagecontent,flag) VALUES (?,?,?,?)',
(_userfrom, _userto, _message, _flag))
db.commit()
return Response(status=201)
def load_logged_in_user():
user = session.get('user')
if user is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM users WHERE id = ?',
(user, )).fetchone()
def delete_post():
db = get_db()
_post_title = request.form['title']
_username = request.form['username']
_password = request.form['password']
if _post_title == "":
return Response(json.dumps({"message": "Please provide title"}),
status=404,
content_type="application/json")
if _username == "":
return Response(json.dumps({"message": "Please provide username"}),
status=404,
content_type="application/json")
if _password == "":
return Response(json.dumps({"message": "Please provide password"}),
status=404,
content_type="application/json")
db.execute('DELETE FROM posts where title = ?', (_post_title, ))
db.commit()
return Response(json.dumps({"message": "Post deleted successfully"}),
status=201,
content_type="application/json")
def get_last_table_id(table):
db = get_db()
query = 'SELECT max(id) FROM {}'.format(table)
row = db.execute(query).fetchone()
return row[0]
def update(self):
db = get_db()
cls = type(self)
db.execute(cls.get_update_query(), self.get_update_values())
db.commit()
def deleteAcc():
db = get_db()
if request.method == 'POST':
_username = request.form['username']
_password = request.form['password']
validUser = db.execute(
'SELECT userName FROM users WHERE userName=? AND password=?', (
_username,
_password,
)).fetchone()
if _username == "" and _password == "":
return Response(json.dumps({"message": "Provide Information"}),
status=404,
content_type="application/json")
if validUser is None:
return Response(json.dumps({"message": "No account to delete"}),
status=404,
content_type="application/json")
else:
db.execute("DELETE from users WHERE username=? AND password=?",
(_username, _password))
db.commit()
return Response(status=201)
def updateEmail():
db = get_db()
_username = request.form['username']
_password = request.form['password']
_new_email = request.form['email']
if (_username == "" and _password == ""):
# error case 1
return Response(json.dumps({"message": "Provided information"}),
status=404,
content_type="application/json")
if (_new_email == ""):
# error case 1
return Response(json.dumps(
{"message": "Enter a new email for account"}),
status=404,
content_type="application/json")
login_id = db.execute(
'SELECT id FROM users WHERE username = ? and password = ?',
(_username, _password)).fetchone()
if login_id is None:
# error case 2
return Response(json.dumps({"message": "No account to update email"}),
status=404,
content_type="application/json")
db.execute('UPDATE users SET email = ? WHERE username = ?',
(_new_email, _username))
db.commit()
return Response(status=201)
def signup():
db = get_db()
if request.method == 'POST':
_username = request.form['username']
_password = request.form['password']
_email = request.form['email']
_karma = 0
if _username == "" and _password == "" and _email == "":
return Response(json.dumps(
{"message": "Error in creating your account"}),
status=404,
content_type="application/json")
if '@' not in _email:
return Response(json.dumps({"message": "Not proper Email"}),
status=404,
content_type="application/json")
else:
db.execute(
"INSERT INTO users(userName,email,password,karma) VALUES(?,?,?,?)",
(_username, _email, _password, _karma))
db.commit()
return Response(status=201)
def voteSegregation():
db = get_db()
_post_title = request.form['title']
_post_community = request.form['community']
if _post_title == "" and _post_community == "":
return Response(json.dumps(
{"message": "Provide a title and community"}),
status=404,
content_type="application/json")
if _post_title == "":
return Response(json.dumps({"message": "Provide a title"}),
status=404,
content_type="application/json")
if _post_community == "":
return Response(json.dumps({"message": "Provide a community"}),
status=404,
content_type="application/json")
db.execute(
"SELECT upvotes, downvotes FROM posts WHERE title = ? AND community = ?",
(_post_title, _post_community))
return Response(json.dumps({
"message":
"Upvotes and downvotes of the post retrieved successfully"
}),
status=201,
content_type="application/json")
def retrieve_existing_post():
"""
Posts can be retrieved by anyone accessing the site; login not required.
Post title will be search for and values will be retrieved based on the ID
of the search field
"""
db = get_db()
_post_title = request.form['title']
_post_community = request.form['community']
if _post_title == "" and _post_community == "":
return Response(json.dumps(
{"message": "Provide a title and community"}),
status=404,
content_type="application/json")
if _post_title == "":
return Response(json.dumps({"message": "Provide a title"}),
status=404,
content_type="application/json")
if _post_community == "":
return Response(json.dumps({"message": "Provide a community"}),
status=404,
content_type="application/json")
db.execute("SELECT community, title, text, dt FROM posts WHERE title = ?",
(_post_title, ))
return Response(json.dumps({"message": "Post retrieved successfully"}),
status=201,
content_type="application/json")
def login(username, password):
db = get_db()
cursor = db.users.find({"username": username, "password": password})
if cursor.count() is 1:
user = User(cursor[0])
return create_session(user), user
else:
return False, None
def post_list(game_id):
db = get_db()
posts = db.execute("SELECT posts.id AS post_id, username, users.id, title, body FROM posts JOIN users ON user_id=users.id WHERE game_id = ?", str(game_id)).fetchall()
game = db.execute("SELECT * FROM games WHERE id = ?", str(game_id)).fetchone()
return render_template("layouts/posts.html", posts=posts, game=game)
def app():
"""Create and configure a new app instance for each test."""
# create a temporary file to isolate the database for each test
db_fd, db_path = tempfile.mkstemp()
# create the app with common test config
app = create_app({"TESTING": True, "DATABASE": db_path})
# create the database and load test data
with app.app_context():
init_db()
get_db().executescript(_data_sql)
yield app
# close and remove the temporary database
os.close(db_fd)
os.unlink(db_path)
def test_accounts_updateEmail(client, app):
# Add user for delete testing
with app.app_context():
get_db().execute(
'INSERT INTO users (username, email, password, karma) VALUES (?, ?, ?, ?);',
("accounts_updateEmail", "*****@*****.**",
"accounts_updateEmail", 0))
get_db().execute(
'INSERT INTO users (username, email, password, karma) VALUES (?, ?, ?, ?);',
("accounts_updateEmail_2", "accounts_2@updateEmail_2.com",
"accounts_updateEmail_2", 0))
get_db().commit()
url = "/accounts/updateEmail"
# test a valid POST request
valid_data = {
"username": "******",
"email": "*****@*****.**",
"password": "******"
}
assert client.post(url, data=valid_data).status_code == 201
# test that the new email was inserted into the database
with app.app_context():
assert (get_db().execute(
"SELECT * FROM users WHERE username = '******' and email = '*****@*****.**'"
).fetchone() is not None)
def test_posts_delete(client, app):
# Add user and post for posts/delete testing
with app.app_context():
get_db().execute(
'INSERT INTO users (username, email, password, karma) VALUES (?, ?, ?, ?);',
("posts_delete", "*****@*****.**", "posts_delete", 0))
get_db().execute(
'INSERT INTO posts (title, community, text, username, url, dt, upvotes, downvotes) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
("posts_delete", "posts_delete", "posts_delete", "posts_delete",
"posts_delete.com", datetime.now(), 0, 0))
get_db().commit()
url = "/posts/delete"
# test a valid POST request
valid_data = {
"title": "posts_delete",
"username": "******",
"password": "******"
}
assert client.post(url, data=valid_data).status_code == 201
# test that the post was deleted from the database
with app.app_context():
assert (get_db().execute(
"SELECT * FROM posts WHERE title = 'posts_delete'").fetchone() is
None)
def query_items(material=None):
query = dict()
if material is not None:
query['material'] = material
cursor = get_db().inventory.find(query)
results = list()
for doc in cursor:
results.append(Item(doc))
return results
def test_import(client, app):
data = {'name': 'Michigan kangaroos'}
response = client.post('/import', json=data)
assert response.status_code == 200
with app.app_context():
db = get_db()
team = db.execute('SELECT * FROM teams WHERE id = 1').fetchone()
assert team['name'] == data['name']
def get_from_id(id):
try:
res = get_db().users.find_one({'_id': ObjectId(id)})
if res is not None:
return User(res)
else:
return False
except:
return False
def get_from_id(id):
try:
res = get_db().inventory.find_one({'_id': ObjectId(id)})
except:
return False
if res is not None:
return Item(db_item=res)
else:
return False
def profile(user_id):
db = get_db()
user = db.execute("SELECT username FROM users WHERE id = ?", str(user_id)).fetchone()
posts = db.execute("SELECT posts.title, body, games.title AS game, games.id FROM posts JOIN games ON posts.game_id=games.id WHERE posts.user_id = ?", str(user_id)).fetchall()
return render_template("layouts/profile.html", posts=posts, user=user)
def query_id(id):
try:
res = get_db().recipes.find_one({'_id': ObjectId(id)})
if res is not None:
return Recipe(res)
else:
return None
except:
return None
