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]