dbLayer.py

   1 import sqlite3
   2
   3 dbFile = 'sqLibrary.db'
   4 bookTable = 'books'
   5
   6 bookTableCreation = '''
   7 CREATE TABLE IF NOT EXISTS books
   8     (id INTEGER PRIMARY KEY,
   9      isbn, lccn, title, subtitle, authors, edition,
  10      publisher, publish_year, publish_month, publish_location,
  11      pages, pagination, weight, last_updated);
  12
  13 CREATE TABLE IF NOT EXISTS books_deleted
  14     (id INTEGER PRIMARY KEY,
  15      isbn, lccn, title, subtitle, authors, edition,
  16      publisher, publish_year, publish_month, publish_location,
  17      pages, pagination, weight, last_updated);
  18 '''
  19
  20 columns = ['id', 'isbn', 'lccn',
  21            'title', 'subtitle', 'authors', 'edition',
  22            'publisher', 'publish year', 'publish month', 'publish location',
  23            'pages', 'pagination', 'weight', 'last updated']
  24
  25 bookTriggerCreation = '''
  26 CREATE TRIGGER IF NOT EXISTS insert_books_time AFTER INSERT ON books
  27 BEGIN
  28     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
  29 END;
  30
  31 CREATE TRIGGER IF NOT EXISTS update_books_time AFTER UPDATE ON books
  32 BEGIN
  33     UPDATE books SET last_updated = DATETIME('NOW') WHERE rowid = new.rowid;
  34 END;
  35
  36 CREATE TRIGGER IF NOT EXISTS delete_books_backup BEFORE DELETE ON books
  37 BEGIN
  38     INSERT INTO books_deleted (isbn, lccn,
  39                 title, subtitle, authors, edition,
  40                 publisher, publish_year, publish_month, publish_location,
  41                 pages, pagination, weight, last_updated)
  42             SELECT isbn, lccn,
  43                    title, subtitle, authors, edition,
  44                    publisher, publish_year, publish_month, publish_location,
  45                    pages, pagination, weight, last_updated
  46                    FROM books
  47                    WHERE rowid = old.rowid;
  48 END;
  49 '''
  50
  51 def colify(s):
  52     return s.replace(" ","_").lower()
  53
  54 # escapes strings and such
  55 def stringify(v):
  56     return '"' + str(v).strip().replace('"','""') + '"'
  57
  58 def addBook(book):
  59     conn = sqlite3.connect(dbFile)
  60     c = conn.cursor()
  61     cols = []
  62     vals = []
  63     for k,v in book.items():
  64         if v!="":
  65             cols.append(colify(k))
  66             vals.append(stringify(v))
  67
  68     query = "INSERT INTO "+bookTable+" ("+", ".join(cols)+") VALUES ("+", ".join(vals)+");"
  69     c.execute(query)
  70     conn.commit()
  71     c.close()
  72
  73 def updateBook(book, bookID):
  74     conn = sqlite3.connect(dbFile)
  75     c = conn.cursor()
  76     updates=[]
  77     for k,v in book.items():
  78         updates.append(colify(k)+"="+stringify(v))
  79     query = "UPDATE "+bookTable+" SET " +  ", ".join(updates)+" WHERE id = " +str(bookID)+";"
  80     c.execute(query)
  81     conn.commit()
  82     c.close()
  83
  84 def getBooks():
  85     conn = sqlite3.connect(dbFile)
  86     c = conn.cursor()
  87     query = "SELECT * FROM "+bookTable+";"
  88     c.execute(query)
  89     books = []
  90     for b in c:
  91         book = {}
  92         i = 0
  93         for k in columns:
  94             if b[i]!=None:
  95                 book[k]=b[i]
  96             i+=1
  97         books.append(book)
  98     c.close()
  99     return books
 100
 101 def getBookByID(bookid):
 102     conn = sqlite3.connect(dbFile)
 103     c = conn.cursor()
 104     query = "SELECT * FROM "+bookTable+" WHERE id = "+str(bookid)+";"
 105     c.execute(query)
 106     b = c.fetchone()
 107     book = {}
 108     i=0
 109     for k in columns:
 110         if b[i]!=None:
 111             book[k]=b[i]
 112         i+=1
 113     c.close()
 114     return book
 115
 116
 117 def removeBook(bookid):
 118     conn = sqlite3.connect(dbFile)
 119     c = conn.cursor()
 120     query = "DELETE FROM " +bookTable+ " WHERE id = "+str(bookid)+";"
 121     c.execute(query)
 122     conn.commit()
 123     c.close()
 124
 125 def createBooksTable():
 126     conn = sqlite3.connect(dbFile)
 127     c = conn.cursor()
 128     c.executescript(bookTableCreation)
 129     conn.commit()
 130     c.close()
 131
 132 def createTriggers():
 133     conn = sqlite3.connect(dbFile)
 134     c = conn.cursor()
 135     c.executescript(bookTriggerCreation)
 136     conn.commit()
 137     c.close()
 138
 139 createBooksTable()
 140 createTriggers()