# BiaWeb Website content manager (c) 2010 V.Harishankar # Database handling functions import sqlite3 import os import os.path import time # function to get an article from the database def get_article (dbname, artid): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("SELECT * FROM articles WHERE aid=?;", (artid,)) conn.commit () row = c.fetchone () conn.close () return row except sqlite3.Error: return False # function to create an article def create_article (dbname, title, summary, keywords, content, catid, stub, rating): # time of creation creattime = time.time () modtime = creattime try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title, summary, keywords, content, creattime, modtime, catid, stub, rating)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to update an article def update_article (dbname, aid, title, summary, keywords, content, catid, stub, rating): # modification time only to be updated modtime = time.time () try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \ stub=?, rating=? WHERE aid=?;", (title, summary, keywords, content, modtime, catid, stub, rating, aid)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to delete an article def delete_article (dbname, artid): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("DELETE FROM articles WHERE aid=?;", (artid,)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to get a category from the database def get_category (dbname, catid): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("SELECT * FROM categories WHERE cid=?", (catid,)) conn.commit () cat = c.fetchone () conn.close () return cat except sqlite3.Error: return False # function to update a category def update_category (dbname, catid, cat_name, cat_desc, cat_stub): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;", (cat_name, cat_desc, cat_stub, catid)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to remove a category def remove_category (dbname, catid): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("DELETE FROM categories WHERE cid=?;", (catid,)) c.execute ("DELETE FROM articles WHERE cid=?;", (catid,)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to create a category def create_category (dbname, category_name, category_desc, category_stub): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);", (category_name, category_desc, category_stub)) conn.commit () conn.close () return True except sqlite3.Error: return False # function to set the configuration and update the database def set_configuration (dbname, site_title, site_url, keywords, description, copyright, num_rss, dest_path): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.executemany ("UPDATE config SET config_param=? WHERE config_name=?;", [[site_url, "Website URL"], [site_title, "Website Title"], [keywords, "Keywords"], [description, "Description"], [num_rss, "No. of RSS items"], [dest_path, "Destination path"], [copyright, "Copyright"]]) conn.commit () conn.close () return True except sqlite3.Error: return False # function to get the existing site configuration and return it as a tuple def get_configuration (dbname): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("SELECT * FROM config;") conn.commit () recs = c.fetchall () conn.close () for name, param in recs: if name == "Website URL": website_url = param elif name == "Website Title": website_title = param elif name == "Keywords": keywords = param elif name == "Description": description = param elif name == "No. of RSS items": num_rss = int (param) elif name == "Destination path": destination = param elif name == "Copyright": copyright = param return (website_url, website_title, keywords, description, num_rss, destination, copyright) 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: conn = sqlite3.connect (dbname) c = conn.cursor () if category_id == None: c.execute ("SELECT * FROM articles;") else: c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,)) conn.commit () rows = c.fetchall () conn.close () return rows except sqlite3.Error: return False # Function to get list of categories and return a (category_id, category_name) array def get_categories (dbname): try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("SELECT * FROM categories;") conn.commit () recs = c.fetchall () conn.close () return recs except sqlite3.Error: return False # function to create a new site database def create_db (dbname, site_title, site_url, keywords, description, copyright, num_rss, dest_path): try: if os.path.exists (dbname): os.remove (dbname) except OSError: return False try: conn = sqlite3.connect (dbname) c = conn.cursor () c.execute ("CREATE TABLE IF NOT EXISTS \ categories (cid INTEGER PRIMARY KEY, \ name TEXT, desc TEXT, \ stub TEXT);") c.execute ("CREATE TABLE IF NOT EXISTS \ articles (aid INTEGER PRIMARY KEY, \ title TEXT, summary TEXT, keywords TEXT, \ content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \ stub TEXT, rating NUMERIC);") c.execute ("CREATE TABLE IF NOT EXISTS \ config (config_name TEXT, config_param TEXT);") c.execute ("CREATE TABLE IF NOT EXISTS \ templates (template_name TEXT, template_content);") template_main = """
${category_desc}
Title | Created on | Rated |