Creating Static Page Database

#models/dashboard/pagedb.py
import os, psycopg2

class Pagedb():
  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 PAGES(
      ID TEXT,
      TITLE TEXT,
      CONTENT TEXT,
      CATDATE DATE,
      CATTIME TIME,
      AUTHOR TEXT
    )'''

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

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

    self.cursor.execute("INSERT INTO PAGES (ID, TITLE, CONTENT, CATDATE, CATTIME, AUTHOR) VALUES %s ", (page,))
  
    self.conn.commit()
    self.conn.close()

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

    sql = "UPDATE PAGES SET ID = %s, TITLE = %s, CONTENT = %s, CATDATE = %s, CATTIME = %s, AUTHOR = %s WHERE ID = '"+page[0]+"'"
    self.cursor.execute(sql, page)

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

  def select(self, amount=0, id=0, page=0):
    self.set_conection()
    
    if id:
      SQL = "SELECT * FROM PAGES WHERE ID = %s LIMIT 1"
      self.cursor.execute(SQL, (id,))
      result = self.cursor.fetchone()
    elif page:
      SQL = "SELECT * FROM PAGES ORDER BY CATDATE DESC, CATTIME DESC OFFSET %s ROWS FETCH NEXT %s ROWS ONLY"
      self.cursor.execute(SQL, (amount*page, amount))
      result = self.cursor.fetchall()
    else:
      SQL = "SELECT * FROM PAGES ORDER BY CATDATE DESC, CATTIME DESC LIMIT %s"
      self.cursor.execute(SQL, (amount,))
      result = self.cursor.fetchall()
    
    self.conn.close()
    return result

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

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

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

#controllers/dashboard/page.py
import config, copy, uuid, datetime
from flask import render_template, session, redirect, request
from lib import Lib
from models.dashboard.pagedb import Pagedb

class Page():
  def __init__(self):
    self.lib = Lib()
    self.pagedb = Pagedb()

  def get_post_page(self):
    vdict = copy.deepcopy(config.vdict)
    vdict['blog_title'] = 'បង្កើត​ទំព័រ​មាតិកា'
    vdict['datetime'] = self.lib.get_timezone()

    if (request.method == "POST") and ('logged-in' in session):
      title = request.form['fpage-title']
      if not title:
        title = 'unknown'
        
      content = request.form['fcontent']
      date = request.form['fpage-date']
      time = request.form['fpage-time']
      author = session['logged-in']

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

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

      id = str(uuid.uuid4().int)
      if 'edit' in session:
        self.pagedb.update(session['edit'], title, content, date, time, author)
        session.pop('edit', None)
      else:
        self.pagedb.insert(id, title, content, date, time, author)

      vdict['pages'] = self.pagedb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['pages'], 2)

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

    elif 'logged-in' in session:
      if 'edit' in session:
        session.pop('edit', None)
        
      vdict['pages'] = self.pagedb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['pages'], 2)
      return render_template('dashboard/page.html', data=vdict)
    else:
      return redirect('/login/')

  def delete(self, id):
    if 'logged-in' in session:
      self.pagedb.delete(id)
      return redirect('/dashboard/page/')

    return render_template('login.html', data=vdict)

  def edit(self, id):
    vdict = copy.deepcopy(config.vdict)
    vdict['blog_title'] = 'កែតំរូវ​មាតិកា'

    if 'logged-in' in session:
      vdict['pages'] = self.pagedb.select(vdict['dashboard_max_post'])
      vdict['thumbs'] = self.lib.get_thumbs(vdict['pages'], 2)
      vdict['page'] = self.pagedb.select(id=id)
      date = (vdict['page'][3]).strftime('%d/%m/%Y')
      time = (vdict['page'][4]).strftime('%H:%M:%S')
      vdict['datetime'] = (date, time)

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

    return render_template('login.html', data=vdict)

  def load(self, page):
    if 'logged-in' in session:
      vdict = copy.deepcopy(config.vdict)
      vdict['pages'] = self.pagedb.select(vdict['dashboard_max_post'], page=page)
      vdict['thumbs'] = self.lib.get_thumbs(vdict['pages'], 2)

      new_list = []
      for page in vdict['pages']:
        new_page = list(page)
        new_page[3] = page[3].strftime('%d/%m/%Y') 
        new_page[4] = page[4].strftime('%H:%M:%S') 
        new_list.append(new_page)

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

GitHub: "https://github.com/Sokhavuth/E-Learning
Heroku: https://khmerweb-elearning.herokuapp.com/