def get_log(id):
sql = "SELECT * FROM logs WHERE id = %s"
cursor.execute(sql, (id, ))
result = cursor.fetchone()
for row in result:
print(row)
def delete(cmt_id):
if request.method == 'GET':
cursor.execute("SELECT msg_id FROM comment where cmt_id = %s;", (cmt_id,))
m = cursor.fetchone()
cursor.execute("DELETE FROM comment where cmt_id = %s;", (cmt_id,))
conn.commit()
flash('Delete Success!')
return redirect(url_for('comment.show', msg_id=m[0]))
def edit(cmt_id):
m = None
if request.method == 'GET':
cursor.execute("SELECT * FROM comment where cmt_id = %s;", (cmt_id,))
m = cursor.fetchone()
return render_template('comment/edit.html', m=m, cmt_id=cmt_id)
if request.method == 'POST':
content = request.form['content']
cursor.execute("UPDATE comment SET content = %s where cmt_id = %s;", (content, cmt_id))
conn.commit()
cursor.execute("SELECT msg_id FROM comment where cmt_id = %s;", (cmt_id,))
m = cursor.fetchone()
flash('Edit Success!')
return redirect(url_for('comment.show', msg_id=m[0]))
return render_template('comment/edit.html', m=m, cmt_id=cmt_id)
def show(pin):
cursor.execute("SELECT * FROM parking WHERE pin = " + pin)
result = cursor.fetchone()
print(url_for('index'))
print(url_for('login'))
print(url_for('login', next='/'))
print(url_for('profile', username='******'))
return jsonify(result)
def get_log(digit):
sql = ("SELECT * FROM logs WHERE id = %s")
cursor.execute(sql, (digit, ))
result = cursor.fetchone()
for row in result:
print(row)
def fetch_newest():
sql_get = "SELECT name FROM post_info ORDER BY ID DESC limit 1"
try:
cursor.execute(sql_get)
results = cursor.fetchone()
for row in results:
name_id = row
except:
print("Unable to find data")
def get_letter(id, letter):
sql = ("SELECT * FROM logs WHERE id = %s")
cursor.execute(sql, (id, ))
result = cursor.fetchone()
if (ord(letter) > 96 and ord(letter) < 123):
x = int(ord(letter) - 96)
return (result[x + 10])
elif (ord(letter) > 47 and ord(letter) < 58):
x = int(ord(letter) - 47)
return (result[x])
def book_update(id):
#Update a book
sql = "UPDATE books SET title = %s WHERE id = %s;"
cursor.execute(sql, (request.json["title"], id))
connection.commit()
sql = "SELECT * FROM books WHERE id = %s"
cursor.execute(sql, (id,))
book = cursor.fetchone()
return jsonify(book)
def book_create():
#Create a new book
sql = "INSERT INTO books (title) VALUES (%s);"
cursor.execute(sql, (request.json["title"],))
connection.commit()
sql = "SELECT * FROM books ORDER BY ID DESC LIMIT 1"
cursor.execute(sql)
book = cursor.fetchone()
return jsonify(book)
def book_delete(id):
sql = "SELECT * FROM books WHERE id = %s;"
cursor.execute(sql, (id,))
book = cursor.fetchone()
if book:
sql = "DELETE FROM books WHERE id = %s;"
cursor.execute(sql, (id,))
connection.commit()
return jsonify(book)
def unlike(cmt_id):
if request.method == 'GET':
user_id = session['logged_id']
cursor.execute(
"DELETE FROM like_cmt where cmt_id = %s AND user_id = %s;",
(cmt_id, user_id))
conn.commit()
cursor.execute("SELECT msg_id FROM comment WHERE cmt_id = %s",
(cmt_id, ))
c = cursor.fetchone()
return redirect(url_for('comment.show', msg_id=c[0]))
def get_log(id):
sql = ("SELECT * FROM logs WHERE id = %s")
cursor.execute(sql, (id,))
result = cursor.fetchone()
if result != None:
for row in result:
print(row)
return row
else:
print(f"There is no log {id} recorded!")
def insert(self):
try:
cursor.execute(
"INSERT INTO users (name, email, password) VALUES(%s, %s, %s) RETURNING id",
(self.name, self.email, self.__password))
connection.commit()
self.id = cursor.fetchone()[0]
return True
except (Exception, psycopg2.Error) as error:
connection.rollback()
print("Error while connecting to PostgreSQL", error)
return False
def like(cmt_id):
if request.method == 'GET':
user_id = session['logged_id']
c_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(
"INSERT INTO like_cmt(cmt_id, user_id,c_time) VALUES(%s,%s,%s);",
(cmt_id, user_id, c_time))
conn.commit()
cursor.execute("SELECT msg_id FROM comment WHERE cmt_id = %s",
(cmt_id, ))
c = cursor.fetchone()
return redirect(url_for('comment.show', msg_id=c[0]))
def login():
error = None
if session.get('logged_in'):
return redirect(url_for('show_entries'))
if request.method == 'POST':
email = request.form['email'].strip()
password = request.form['password'].strip()
email = email.lower()
cursor.execute("SELECT password,user_id FROM users where email = %s;",
(email, ))
u = cursor.fetchone()
if u is None:
error = "The user doesn\'t exsit.Please register first."
else:
cursor.execute(
"SELECT user_id FROM users WHERE email = %s AND password = crypt(%s, password);",
(email, password))
u = cursor.fetchone()
if u is None:
error = "Your password is wrong.Try it again."
else:
session['logged_in'] = True
session['logged_email'] = email
session['logged_id'] = u[0]
return redirect(url_for('show_entries'))
'''
elif bcrypt.check_password_hash(u[0], password):
session['logged_in'] = True
session['logged_email'] = email
session['logged_id'] = u[1]
return redirect(url_for('show_entries'))
else:
error = "Your password is wrong.Try it again."
'''
return render_template('login.html', error=error)
def find(cls, email):
try:
cursor.execute(
"SELECT id, name, email, password FROM users WHERE email = %s",
(email, ))
record = cursor.fetchone()
if record:
return cls(*record)
else:
return None
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL:", error)
return None
def save(self):
"""return True if save else False (already have entry)"""
# check if have entry already
cursor.execute("SELECT COUNT(*) FROM towed_bike WHERE bike_id = %s AND time = %s", (self.bike_id, self.time))
if(cursor.fetchone()[0] > 0):
return False
self.save_picture()
cursor.execute("INSERT INTO towed_bike (bike_id, location, time,picture_url, picture_fileName, picture_filePath) VALUES (%s, %s, %s, %s, %s,%s)",
(self.bike_id, self.location, self.time, self.picture_url, self.picture_fileName, self.picture_filePath))
db.commit()
return True
def show(msg_id):
user_id = session['logged_id']
if request.method == 'GET':
cursor.execute("SELECT * FROM message where msg_id = %s;", (msg_id,))
m = cursor.fetchone()
cursor.execute("SELECT * FROM comment where msg_id = %s;", (msg_id,))
cs = cursor.fetchall()
if cs is None:
cs = ()
cs = list(cs)
for i, comment in enumerate(cs):
comment = list(comment)
cursor.execute("SELECT nickname FROM users where user_id = %s", (user_id,))
u = cursor.fetchone()
comment.append(u[0])
cursor.execute("SELECT * FROM like_cmt where cmt_id = %s AND user_id = %s", (comment[0], user_id))
like = cursor.fetchone()
if like is not None:
like_flag = 1
else:
like_flag = 0
comment.append(like_flag)
cs[i] = comment
return render_template('comment/show.html', m=m, cs=cs)
def edit(msg_id):
m = None
if request.method == 'GET':
cursor.execute("SELECT * FROM message where msg_id = %s;", (msg_id, ))
m = cursor.fetchone()
return render_template('message/edit.html', m=m, msg_id=msg_id)
if request.method == 'POST':
content = request.form['content']
cursor.execute("UPDATE message SET content = %s where msg_id = %s;",
(content, msg_id))
conn.commit()
flash('Edit Success!')
return redirect(url_for('show_entries'))
return render_template('message/edit.html', m=m, msg_id=msg_id)
def search(self):
# print("%s" % (self.input.get()))
cursor.execute(query, {"title_to_find": "%" + self.input.get() + "%"})
while True:
self.film_searched = cursor.fetchone()
if self.film_searched == None:
break
else:
# print(films['title'])
# self.list_films_searched.append(films['title'])
result = tkinter.Label(self.root,
text=self.film_searched["title"])
result.film = self.film_searched
result.bind("<Button>", self.show_one_moovie)
result.grid(column=1)
def save_picture(self):
"""store picture localy and init filePath & fileName"""
try:
res = requests.get(self.picture_url)
except HTTPError as err:
print(err)
exit()
except ConnectionError as err:
print(err)
exit()
except:
print("Unexpected error:", sys.exc_info()[0])
exit()
cursor.execute("SELECT MAX(ID) FROM towed_bike")
index = cursor.fetchone()[0]
index = 1 if not index else index + 1
with open(f'./src/pictures/{index}.jpg', 'wb') as f:
f.write(res.content)
self.picture_fileName = f'{index}.jpg'
self.picture_filePath = path.abspath(f'./src/pictures/{index}.jpg')
def register():
if request.method == 'POST':
error = None
email = request.form['email'].strip()
nickname = request.form['nickname'].strip()
password = request.form['password'].strip()
password2 = request.form['password2'].strip()
email = email.lower()
if email == "" or nickname == "" or password == "" or password2 == "":
error = 'Please input all the information'
elif password2 != password:
error = 'The password is not repeated correctly'
elif len(password) < 6:
error = 'The password has at least 6 characters'
elif not re.match(
r'^[0-9a-zA-Z_]{0,19}@' + '[0-9a-zA-Z]{1,15}\.[com,cn,net]',
email):
error = 'Please input the right email'
cursor.execute("SELECT * FROM users where email = %s;", (email, ))
u = cursor.fetchone()
if u is not None:
error = 'The email has already exsit'
if error is not None:
return render_template('register.html', error=error)
else:
#password = bcrypt.generate_password_hash(password)
cursor.execute("INSERT INTO users(email,nickname,password) VALUES(%s,%s,crypt(%s, gen_salt('bf', 8)));", \
(email, nickname, password))
conn.commit()
flash('Register Success!')
return redirect(url_for('users.login'))
return render_template('register.html')
def profile():
if request.method == 'GET':
user_id = session["user_id"]
select_user = '******'
# update user
cursor.execute(select_user, (user_id, ))
result = cursor.fetchone()
db.commit()
return render_template('profile.html', user=result)
if request.method == 'POST':
name = request.form['name']
phone = request.form['phone_number']
email = request.form['email_id']
user_id = session["user_id"]
hash = sha256_crypt.hash(str(request.form['password']))
update_user = "******"
params = (name, email, phone, hash, user_id)
# update user
cursor.execute(update_user, params)
db.commit()
user_id = cursor.lastrowid
flash('Profile updated', 'success')
return redirect(url_for('profile'))
def rate(self, q, user):
if self.rating == q:
return False
dt = datetime.datetime.now()
try:
if self.id:
cursor.execute(
"UPDATE calificaciones SET calificacion = %s, updated_at = %s WHERE id = %s",
(q, dt, self.id))
connection.commit()
else:
cursor.execute(
"INSERT INTO calificaciones (user_id, impresora_id, calificacion, created_at, updated_at) VALUES(%s, %s, %s, %s, %s) RETURNING id",
(user.id, self.printer_id, q, dt, dt))
connection.commit()
self.id = cursor.fetchone()[0]
return True
except (Exception, psycopg2.Error) as error:
connection.rollback()
print("Error while connecting to PostgreSQL", error)
return False
def calculate(champions, attr, question, answer):
cursor.execute(f"""SELECT weight FROM weights WHERE attr = "{attr}" """)
weight = cursor.fetchone()[0]
cursor.execute(
"SELECT champion, `yes`, `probably`, `unknown`, `maybe`, `no`, (`yes` + `probably` + `unknown` + `maybe` + `no`) as total FROM attributes WHERE attr = ?",
[attr])
rows = cursor.fetchall()
for row in rows:
champion, yes, prob, unknown, maybe, no, total = row
if champion not in champions:
champions[champion] = 50
distribution = [0.5, 0.2, 0.05]
# Something like z distribution but I failed probability :(
minus_two = row[max(1, answer.value - 2)] * distribution[2]
minus_one = row[max(1, answer.value - 1)] * distribution[1]
middle = row[answer.value] * distribution[0]
plus_two = row[min(1, answer.value + 2)] * distribution[1]
plus_one = row[min(1, answer.value + 1)] * distribution[2]
score = sum([minus_one, minus_two, middle, plus_one, plus_two
]) / total * weight
champions[champion] += score
def has_ratings(cls, user):
cursor.execute(
"SELECT EXISTS(SELECT id FROM calificaciones WHERE calificaciones.user_id = %s)",
(user.id, ))
return cursor.fetchone()[0]
def getLatitude(MMSI):
# Check to see if the membership ID already exists in the database
cursor.execute('SELECT latitude FROM location_tracking')
check = str(cursor.fetchone()[0])
print(check)
def find_user(username, password):
sql = ("select * from users where username = %s and password = %s")
cursor.execute(sql, (username, password,))
user = cursor.fetchone()
return user
def book_show(id):
#Return a single book
sql = "SELECT * FROM books WHERE id = %s;"
cursor.execute(sql, (id,))
book = cursor.fetchone()
return jsonify(book)
def rating_users(cls):
cursor.execute(
"SELECT COUNT(DISTINCT calificaciones.user_id) FROM calificaciones GROUP BY calificaciones.user_id"
)
return cursor.fetchone()[0]
