X-Git-Url: https://harishankar.org/repos/?p=biaweb_qt.git;a=blobdiff_plain;f=biaweb_db.py;h=c1d5e60db222804c0eac91012849a2982f8a0dc2;hp=c2761f5885fc74a0d2c6ab6e601dd7454f111dfe;hb=df54af4f6bf7afa6b89f5fa1f07177313aa5cc1f;hpb=b517d55320f54925d862aca9dedb363b6d3b156d diff --git a/biaweb_db.py b/biaweb_db.py index c2761f5..c1d5e60 100644 --- a/biaweb_db.py +++ b/biaweb_db.py @@ -6,6 +6,8 @@ import os import os.path import time +import biaweb_strings as bws + # function to get the template text for a particular template def get_template_text (dbname, template_name): try: @@ -20,6 +22,19 @@ def get_template_text (dbname, template_name): except sqlite3.Error: return False +# function to update the template +def update_template (dbname, template_name, template_str): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("UPDATE templates SET template_content=? WHERE template_name=?", + (template_str, template_name)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + # function to retrieve the templates from the database def get_templates (dbname): try: @@ -192,6 +207,52 @@ def get_configuration (dbname): except sqlite3.Error: return False +# Function to get articles from a category or all articles (inner join with categories to get category stub) +def site_articles (dbname, catid = None): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + if catid is None: + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY cdate DESC;") + else: + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + WHERE articles.cid=? ORDER BY cdate DESC;", (catid,)) + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + +# Function to get the latest articles (inner join with categories to get category stub) +def site_latest_articles (dbname, num_arts): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY cdate DESC LIMIT ?;", (num_arts,)) + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + +# Function to get the best rated articles (inner join with categories to get category stub) +def site_get_bestrated (dbname): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY rating DESC LIMIT 5;") + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + # Function to get list of articles (either full list or just in a category def get_articles (dbname, category_id=None): try: @@ -233,6 +294,8 @@ def create_db (dbname, site_title, site_url, keywords, description, copyright, try: conn = sqlite3.connect (dbname) c = conn.cursor () + + # create the tables c.execute ("CREATE TABLE IF NOT EXISTS \ categories (cid INTEGER PRIMARY KEY, \ name TEXT, desc TEXT, \ @@ -248,203 +311,17 @@ def create_db (dbname, site_title, site_url, keywords, description, copyright, c.execute ("CREATE TABLE IF NOT EXISTS \ templates (template_name TEXT, template_content);") - template_main = """ - - -
-${category_desc}
-Title | -Created on | -Rated | -