Adding User Crud Functionality

#controllers/dashboard/user.py
import config, copy, lib, datetime
from flask import render_template, session, request, redirect
from models.userdb import Userdb

class User():
  def __init__(self):
    self.userdb = Userdb()
    self.lib = lib.Lib()

  def signup(self):
    vdict = copy.deepcopy(config.vdict)
    vdict['site_title'] = "ទំព័រ​អ្នក​ប្រើប្រាស់"
    vdict['datetime'] = self.lib.get_timezone()
    session['page'] = 0

    if request.method == "POST":
      username = request.form['fusername']
      content = request.form['fcontent']
      password = request.form['fpassword']
      role = request.form['frole']
      date = request.form['fdate']
      time = request.form['ftime']
      email = request.form['femail']

      if not email:
        vdict['message'] = 'ចាំបាច់​ត្រូវ​មាន​ E-MAIL!'
        return render_template('dashboard/user.html', data=vdict)

      if (self.userdb.check_email(email)) and (not ('edit-id' in session)):
        vdict['message'] = 'E-MAIL នេះ​ត្រូវ​បាន​គេ​យក​ទៅ​ប្រើប្រាស់​ហើយ។'
        return render_template('dashboard/user.html', data=vdict)

      if 'logged-in' in session:
        author_id = session['author-id']
        author_role = self.userdb.check_author(author_id)
        author = author_role[1]
      else:
        author = 'root'

      try:
        date = datetime.datetime.strptime(date, "%d/%m/%Y")
      except ValueError:
        vdict['message'] = 'ទំរង់​កាលបរិច្ឆេទ​មិន​ត្រឹមត្រូវ!'
        return render_template('dashboard/user.html', data=vdict)

      try:
        time = datetime.datetime.strptime(time, "%H:%M:%S")
      except ValueError:
        vdict['message'] = 'ទំរង់​ពេល​វេលា​មិន​ត្រឹមត្រូវ!'
        return render_template('dashboard/user.html', data=vdict)

      if 'edit-id' in session:
        if author_role[4] == 'Admin':
          id = session['edit-id']
          self.userdb.update(username, email, password, role, content, date, time, author, id)
          session.pop('edit-id', None)
      else:
        if author_role[4] == 'Admin':
          self.userdb.insert(username, email, password, role, content, date, time, author)

      vdict['users'] = self.userdb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['users'], 5, type='user')
      return render_template('dashboard/user.html', data=vdict)

    elif 'logged-in' in session:
      vdict['users'] = self.userdb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['users'], 5, type='user')
      return render_template('dashboard/user.html', data=vdict)
    else:
      return redirect('/login/')

  def edit(self, id):
    vdict = copy.deepcopy(config.vdict)
    vdict['blog_title'] = 'កែតំរូវ​អ្នក​ប្រើប្រាស់'
    session['edit-id'] = id

    if 'logged-in' in session:
      vdict['users'] = self.userdb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['users'], 5, type='user')
      vdict['user'] = self.userdb.select(id=id)
      date = (vdict['user'][6]).strftime('%d/%m/%Y')
      time = (vdict['user'][7]).strftime('%H:%M:%S')
      vdict['datetime'] = (date, time)

      return render_template('dashboard/user.html', data=vdict)

    return redirect('/login/')

  def delete(self, id):
    author_id = session['author-id']
    author_role = self.userdb.check_author(author_id)
    if author_role[4] == 'Admin':
      self.userdb.delete(id)

    return redirect('/dashboard/user/signup/')

  def load(self):
    if 'logged-in' in session:
      vdict = copy.deepcopy(config.vdict)
      session['page'] += 1
      vdict['users'] = self.userdb.select(vdict['dashboard_max_post'], page=session['page'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['users'], 5, type="user")

      new_list = []
      for user in vdict['users']:
        new_user = list(user)
        new_user[6] = user[6].strftime('%d/%m/%Y') 
        new_user[7] = user[7].strftime('%H:%M:%S') 
        new_list.append(new_user)

      vdict['users'] = new_list
      return vdict
    else:
      return render_template('login.html', data=vdict)

#models/userdb.py
import os, psycopg2

class Userdb():
  def __init__(self):
    self.create_table()

  def set_conection(self):
    if 'DYNO' in os.environ:
      DATABASE_URL = os.environ['DATABASE_URL']
      self.conn = psycopg2.connect(DATABASE_URL, sslmode='require')
      self.cursor = self.conn.cursor()
    else: 
      self.conn = psycopg2.connect(
        database="postgres", 
        user="postgres", 
        password="sokhavuth", 
        host="localhost", 
        port="5432"
      )

      self.cursor = self.conn.cursor()

  def create_table(self):
    self.set_conection()
    
    SQL = '''CREATE TABLE IF NOT EXISTS USERS(
      ID SERIAL PRIMARY KEY,
      USERNAME VARCHAR(320),
      EMAIL VARCHAR(320),
      PASSWORD VARCHAR(320),
      ROLE TEXT,
      CONTENT TEXT,
      CDATE DATE,
      CTIME TIME,
      AUTHOR TEXT
    )'''

    self.cursor.execute(SQL)
    self.conn.commit()
    self.conn.close() 

  def insert(self, *user):
    self.set_conection()

    self.cursor.execute("INSERT INTO USERS (USERNAME, EMAIL, PASSWORD, ROLE, CONTENT, CDATE, CTIME, AUTHOR) VALUES %s ", (user,))
  
    self.conn.commit()
    self.conn.close()

  def select(self, amount=5, id='', page=0):
    self.set_conection()

    if id:
      SQL = "SELECT * FROM USERS WHERE ID=%s"
      self.cursor.execute(SQL, (id,))
      result = self.cursor.fetchone()
    elif page:
      SQL = "SELECT * FROM USERS ORDER BY ID DESC OFFSET %s ROWS FETCH NEXT %s ROWS ONLY"
      self.cursor.execute(SQL, (amount*page, amount))
      result = self.cursor.fetchall()
    else:
      SQL = "SELECT * FROM USERS ORDER BY CDATE DESC, CTIME DESC LIMIT %s"
      self.cursor.execute(SQL, (amount,))
      result = self.cursor.fetchall()

    self.conn.close()
    return result

  def check_user(self, *user):
    self.set_conection()

    SQL = "SELECT * FROM USERS WHERE EMAIL = %s AND PASSWORD = %s LIMIT 1"
    self.cursor.execute(SQL, (user))
    result = self.cursor.fetchone()
    
    self.conn.close()
    return result

  def check_email(self, email):
    self.set_conection()

    SQL = "SELECT EMAIL, PASSWORD FROM USERS WHERE EMAIL = %s LIMIT 1"
    self.cursor.execute(SQL, (email,))
    result = self.cursor.fetchone()
    
    self.conn.close()
    return result

  def check_author(self, id):
    self.set_conection()

    SQL = "SELECT * FROM USERS WHERE ID = %s LIMIT 1"
    self.cursor.execute(SQL, (id,))
    result = self.cursor.fetchone()
    
    self.conn.close()
    return result

  def delete(self, id):
    self.set_conection()

    SQL = "DELETE FROM USERS WHERE ID = %s"
    self.cursor.execute(SQL, (id,))

    self.conn.commit()
    self.conn.close()

  def update(self, *user):
    self.set_conection()

    sql = "UPDATE USERS SET USERNAME = %s, EMAIL = %s, PASSWORD = %s, ROLE = %s, CONTENT = %s, CDATE = %s, CTIME = %s, AUTHOR = %s WHERE ID = %s"
    self.cursor.execute(sql, user)

    self.conn.commit()
    self.conn.close()

  def search(self, query):
    self.set_conection()
  
    sql = "SELECT * from USERS WHERE"
    sql += " EMAIL LIKE '%"+query+"%'"
    sql += " OR CONTENT LIKE '%"+query+"%'"
    sql += " ORDER BY CATDATE DESC, CATTIME DESC LIMIT 20"

    self.cursor.execute(sql)
    
    result = self.cursor.fetchall()
    return result

<!--templates/dashboard/index.html-->
{% extends 'dashboard/index.html' %}
  
{% block head %}
{{ super() }}
<link href="/static/styles/dashboard/user.css" rel="stylesheet">
<script src="/static/scripts/dashboard/user.js"></script>
{% endblock %}

{% block content %}
  <form id='signup' action='/dashboard/user/signup/' method="POST" >
    {% if 'user' in data %}
    <input id='user-title' value="{{ data['user'][1] }}" name="fusername" type="text" placeholder="ឈ្មោះ​អ្នក​ប្រើប្រាស់" required />
    <textarea name="fcontent" id="editor" >{{ data['user'][5] }}</textarea>
    {% else: %}
    <input id='user-title' value="" name="fusername" type="text" placeholder="ឈ្មោះ​អ្នក​ប្រើប្រាស់" required />
    <textarea name="fcontent" id="editor" ></textarea>
    {% endif %}
    <div id="bottombar">
      <input id="submit" class="bottom-widget" type="submit" value="ចុះ​ឈ្មោះ">
      {% if 'user' in data %}
      <input id="user-password" value="{{ data['user'][3] }}" class="bottom-widget" type="password" name="fpassword" />
      <select id="user-role" class="bottom-widget" name="frole" >
        <option>Author</option>
        <option>Admin</option>
      </select>
      <script>$("#user-role").val("{{data['user'][4]}}").change();</script>
      <input id="user-date" value="{{ data['datetime'][0] }}" class="bottom-widget user-date" type="text" name="fdate" required />
      <input id="user-time" value="{{ data['datetime'][1] }}" class="bottom-widget user-time" type="text" name="ftime" required />
      <input type='email' value="{{ data['user'][2] }}" placeholder="E-MAIL" id="user-author" class="user-time" name="femail" required />
      {% else %}
      <input id="user-password" value="" class="bottom-widget" type="password" name="fpassword" />
      <select id="user-role" class="bottom-widget" name="frole" >
        <option>Author</option>
        <option>Admin</option>
      </select>
      <input id="user-date" value="{{ data['datetime'][0] }}" class="bottom-widget user-date" type="text" name="fdate" required />
      <input id="user-time" value="{{ data['datetime'][1] }}" class="bottom-widget user-time" type="text" name="ftime" required />
      <input type='email' placeholder="E-MAIL" id="user-author" class="user-time" name="femail" required />
      {% endif %}
    </div>
  </form>
  <div id="message">{{ data['message'] }}</div>
  <script src="/static/scripts/ckeditor/config.js"></script>
{% endblock %}
  
{% block item_listing %}
    <ul id="item-listing" class="item-listing region">
      {% if 'users' in data %}
      {% for v in range(data['users']|length) %}
      <li class="user">
        <a class="thumbnail" href="/user/{{ data['users'][v][0] }}">
          <img src="{{data['thumbs'][v]}}" />
        </a>
        <div class='title'>
          <a href="/user/{{ data['users'][v][1] }}">{{ data['users'][v][1] }}</a>
          <div>{{ data['users'][v][4] }}</div>
          <span>{{ data['users'][v][6].strftime('%d/%m/%Y') }}</span>
        </div>
        <div class="crud">
          <div class="user">{{ data['users'][v][8] }}</div>
          <a href='/dashboard/user/edit/{{ data["users"][v][0] }}'><img src="/static/images/edit.png" /></a>
          <a href='/dashboard/user/delete/{{ data["users"][v][0] }}'><img src="/static/images/delete.png" /></a>
        </div>
      </li>
      {% endfor %}
      {% endif %}
    </ul>
    <div id="load-more" class="load-more region">
      <img onclick="user.load_items('/dashboard/user/load/', 'users')" src="/static/images/load-more.png" />
    </div>
{% endblock %}

//static/scritps/dashboard/user.js
class User{
  load_items(url){
    $('#load-more img').attr('src', '/static/images/loading.gif');
    $.get(url, function(data, status){
      if(status === "success"){
        user.listing_users(data)
      }else{
        alert('Fail to connect to server.');
      }
    });
  }
  listing_users(data){
    var html = '';
    for(var v=0; v<data['users'].length; v++){
      html += '<li class="user">';
      html += `<a class="thumbnail" href="/user/${ data['users'][v][0] }"><img src="${data['thumbs'][v]}" /></a>`;
      html += `<div class='title'>`;
      html += `<a href="/user/${ data['users'][v][0] }">${ data['users'][v][1] }</a>`;
      html += `<div>${ data['users'][v][4] }</div>`
      html += `<span>${ data['users'][v][6] }</span>`;
      html += `</div>`;
      html += `<div class="crud">`;
      html += `<div class="user">${ data['users'][v][8] }</div>`;
      html += `<a href='/dashboard/user/delete/${ data["users"][v][0] }'><img src="/static/images/delete.png" /></a>`;
      html += `<a href='/dashboard/user/edit/${ data["users"][v][0] }'><img src="/static/images/edit.png" /></a>`;
      html += `</div>`;
      html += `</li>`;
    }
  
    $('#item-listing').append(html);
    $('#load-more img').attr('src', '/static/images/load-more.png')
  }
}//end class

const user = new User();

GitHub: "https://github.com/Sokhavuth/ETV
Heroku: https://khmerweb-etv.herokuapp.com/