def receive_record_submit(self, title, description, tags, date):
# solve single quote problems
title = solve_apostrophe(title)
description = solve_apostrophe(description)
tagsList = turn_json_array_string_into_list(tags)
date = solve_apostrophe(date)
# insert into table blocks
sql = "INSERT INTO blocks(title,description,last_edited_date) VALUES('%s','%s','%s') RETURNING id;" % (
title, description, date)
self.cur.execute(sql)
self.conn.commit()
id_of_new_block = self.cur.fetchone()[0]
# dealing with each tag
for tag in tagsList:
# find if this tag exists
sql = "SELECT * FROM tags where name = '%s';" % tag
self.cur.execute(sql)
self.conn.commit()
resTuple = self.cur.fetchone()
id_of_new_tag = -1 # declare
if resTuple: # already exists
id_of_new_tag = resTuple[0]
else: # not exists
# create a tag
sql = "INSERT INTO tags(name) VALUES('%s') RETURNING id;" % tag
self.cur.execute(sql)
self.conn.commit()
id_of_new_tag = self.cur.fetchone()[0]
# insert into tag_block_pairs
sql = "INSERT INTO tag_block_pairs(block_id,tag_id) VALUES(%s,%s);" % (
id_of_new_block, id_of_new_tag)
self.cur.execute(sql)
self.conn.commit()
def if_username_password_exist_match(self, username, password):
# check if the username exists first
if not self.if_username_exists(username):
return False
# check if matching
sql = "SELECT * FROM users WHERE name = '%s' and password = '******';" % (
solve_apostrophe(username), solve_apostrophe(password))
self.cur.execute(sql)
self.conn.commit()
resList = self.cur.fetchall()
return False if len(resList) is 0 else True
def if_username_exists(self, username):
username = solve_apostrophe(username)
sql = "SELECT * FROM users WHERE name = '%s'" % username
self.cur.execute(sql)
self.conn.commit()
resList = self.cur.fetchall()
return False if len(resList) is 0 else True
def get_all_tags(self, keyword):
keyword = solve_apostrophe(keyword)
sql = "SELECT id,name FROM tags WHERE name like '%%%s%%' ORDER BY name;" % keyword
self.cur.execute(sql)
self.conn.commit()
resList = self.cur.fetchall()
return resList
def register(self, name, email, password):
sql = "INSERT INTO users (name,email,password) VALUES ('%s','%s','%s');" \
% (solve_apostrophe(name), solve_apostrophe(email), solve_apostrophe(password))
self.cur.execute(sql)
self.conn.commit()
return