def request_delete(self, id):
query = """DELETE FROM users
WHERE id=?"""
con, cur = get_connection_cursor(self.name)
cur.execute(query, (id, ))
con.commit()
con.close()
def request_update(self, id, user, post):
query = """UPDATE posts
SET title=?, text=?, coords=?
WHERE id=? AND user_id=?"""
con, cur = get_connection_cursor(self.name)
cur.execute(query, (post.title, post.text, post.coords, id, user.id))
con.commit()
con.close()
def request_delete(self, id, user):
query = """DELETE FROM posts
WHERE id=? AND user_id=?"""
con, cur = get_connection_cursor(self.name)
cur.execute(query, (id, user.id))
# return message?
con.commit()
con.close()
def request_update(self, id, name, password):
query = """UPDATE users
SET username=?, password=?
WHERE id=?"""
con, cur = get_connection_cursor(self.name)
cur.execute(query, (name, password, id))
con.commit()
con.close()
def get_all(self):
query = """Select id, username, password
FROM users"""
con, cur = get_connection_cursor(self.name)
results = cur.execute(query).fetchall()
res = list()
for elem in results:
res.append(
user.User(id=elem[0], username=elem[1], password=elem[2]))
con.close()
return res
def get_by_name(self, name):
query = """SELECT id, username, password
FROM users
WHERE username = ?"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (name, )).fetchone()
if result is None:
con.close()
return None
con.close()
return user.User(id=result[0], username=result[1], password=result[2])
def get_by_category(self, category):
query = """SELECT * FROM posts
WHERE category=?"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (category, ))
if result is None:
con.close()
return None
con.close()
return post.Post(id=result[0],
title=result[1],
text=result[2],
created=result[3],
coords=result[4],
user_id=result[5])
def request_create(self, p):
p.created = datetime.datetime.now()
query = """INSERT INTO posts(author, created, category, score, text, title, type, upvotePercentage, views, url)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """
con, cur = get_connection_cursor(self.name)
params = p.author.id, p.created, p.category, p.score, p.text, p.title, p.type, p.upvotePercentage, \
p.views, p.url
result = cur.execute(query, params)
if not result.rowcount > 0:
con.close()
return None
p.id = result.lastrowid
con.commit()
con.close()
return p
def request_create(self, name, password):
found = self.get_by_name(name)
if not (found is None):
return None
query = """INSERT INTO users(username, password)
VALUES (?, ?)"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (name, password))
if not result.rowcount > 0:
con.close()
return None
new_user = user.User(id=result.lastrowid,
username=name,
password=password)
con.commit()
con.close()
return new_user
def get_by_id(self, id):
query = """SELECT *
FROM posts JOIN users ON posts.user_id=users.id
WHERE posts.id = ?"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (id, )).fetchone()
if result is None:
con.close()
return None
con.close()
author = {'username': result[7], 'id': result[6]}
return post.Post(id=result[0],
title=result[1],
text=result[2],
created=result[3],
coords=result[4],
user_id=result[5],
author=author)
def get_all(self):
query = """SELECT *
FROM posts JOIN users ON posts.user_id = users.id"""
con, cur = get_connection_cursor(self.name)
results = cur.execute(query).fetchall()
res = list()
for elem in results:
author = {'username': elem[7], 'id': elem[6]}
res.append(
post.Post(id=elem[0],
title=elem[1],
text=elem[2],
created=elem[3],
coords=literal_eval(elem[4]),
user_id=elem[5],
author=author))
con.close()
return res
def get_by_username(self, username):
query = """SELECT * FROM posts JOIN users ON posts.user_id=users.id
WHERE user_id=(SELECT id from users WHERE username=?)"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (username, )).fetchall()
if result is None:
con.close()
return None
con.close()
res = list()
for elem in result:
author = {'username': elem[7], 'id': elem[6]}
res.append(
post.Post(id=elem[0],
title=elem[1],
text=elem[2],
created=elem[3],
coords=elem[4],
user_id=elem[5],
author=author))
return res
def get_all(self):
query = """SELECT P.id,
P.author as author_id,
U.username as author_name,
P.category,
P.score,
P.text,
P.title,
P.type,
P.upvotePercentage,
P.views,
P.url, P.created
FROM posts P
LEFT JOIN users U on P.author = U.id"""
con, cur = get_connection_cursor(self.name)
results = cur.execute(query).fetchall()
res = list()
for elem in results:
res.append(self.elem_to_post(elem))
con.close()
return res
def get_by_id(self, id):
query = """SELECT P.id,
P.author as author_id,
U.username as author_name,
P.category,
P.score,
P.text,
P.title,
P.type,
P.upvotePercentage,
P.views,
P.url, P.created
FROM posts P
LEFT JOIN users U on P.author = U.id
WHERE P.id = ?"""
con, cur = get_connection_cursor(self.name)
result = cur.execute(query, (id,)).fetchone()
if result is None:
con.close()
return None
con.close()
return self.elem_to_post(result)
def request_create(self, local_post):
query = """INSERT INTO posts(title, text, created, coords, user_id)
VALUES (?, ?, ?, ?, (SELECT id FROM users WHERE username = ?))"""
con, cur = get_connection_cursor(self.name)
tuple_args = (local_post.title, local_post.text, datetime.now(),
local_post.coords, local_post.author['username'])
result = cur.execute(query, tuple_args)
if not result.rowcount > 0:
con.close()
return None
coords = literal_eval(local_post.coords)
dict_args = {
'id': result.lastrowid,
'title': local_post.title,
'text': local_post.text,
'created': datetime.now(),
'coords': coords,
'author': local_post.author
}
new_post = post.Post(**dict_args)
con.commit()
con.close()
return new_post
