db.py

   1 try:
   2     import os, sqlite3
   3 except ImportError as err:
   4     print (err)
   5     os._exit(1)
   6
   7 class DB:
   8     __instance = None
   9     def __new__(cls, *args, **kwargs):
  10         if (cls.__instance is None):
  11             cls.__instance = super(DB, cls).__new__(cls)
  12         return cls.__instance
  13
  14     def __init__(self, path):
  15         file = path + "/lensdb.db"
  16         initialize = os.path.isfile(file)
  17         self.conn = sqlite3.connect(path + "/lensdb.db")
  18         self.conn.execute("PRAGMA foreign_keys = '1'")
  19         self.conn.commit()
  20         if initialize is False:
  21            self.init_db()
  22
  23     def init_db(self):
  24         cur = self.conn.cursor()
  25         cur.execute('''drop table if exists lens;''')
  26         cur.execute('''drop table if exists lensmaker;''')
  27         cur.execute('''
  28             create table lensmaker(
  29             id integer primary key not null,
  30             maker text not null);
  31         ''')
  32         cur.execute('''
  33             create table lens(
  34             id integer primary key not null,
  35             sn int not null,
  36             maker int not null,
  37             description text not null,
  38             notes text,
  39             constraint fk_maker foreign key (maker) references lensmaker (id)
  40             on delete cascade);
  41         ''')
  42         self.conn.commit()
  43
  44     def addMaker(self, maker):
  45         cur = self.conn.cursor()
  46         cur.execute("insert into lensmaker (maker) values (?)", (maker, ))
  47         self.conn.commit()
  48
  49     def deleteMaker(self, maker):
  50         cur = self.conn.cursor()
  51         cur.execute("delete from lensmaker where maker = ?", (maker, ))
  52         self.conn.commit()
  53
  54     def addLens(self, maker, tuple):
  55         cur = self.conn.cursor()
  56         cur.execute("select id from lensmaker where maker = ?", (maker, ))
  57         lensmaker = cur.fetchone()
  58         if lensmaker is None:
  59             raise Exception(maker + ": not found")
  60         cur.execute("insert into lens (maker, sn, description, notes) values (?, ?, ?, ?)", lensmaker + tuple)
  61         self.conn.commit()
  62         cur.execute("select last_insert_rowid();")
  63         id = cur.fetchone()
  64         return id[0]
  65
  66     def updateLens(self, tuple):
  67         cur = self.conn.cursor()
  68         cur.execute("update lens set sn = ?, description = ?, notes = ? where id = ?", tuple)
  69         self.conn.commit()
  70
  71     def deleteLens(self, id):
  72         cur = self.conn.cursor()
  73         cur.execute("delete from lens where id = ?", (id, ))
  74         self.conn.commit()
  75
  76     def getMakers(self):
  77         cur = self.conn.cursor()
  78         cur.execute("select maker from lensmaker")
  79         rows = cur. fetchall()
  80         return rows
  81
  82     def getLenses(self, maker):
  83         cur = self.conn.cursor()
  84         if type(maker) == str:
  85             cur.execute("select id from lensmaker where maker = ?", (maker, ))
  86             lensmaker = cur.fetchone()
  87         else:
  88             lensmaker = (maker, )
  89         cur.execute("select sn, description, notes, id from lens where maker = ?", lensmaker)
  90         rows = cur. fetchall()
  91         return rows
  92
  93     def getLensByPattern(self, pattern):
  94         cur = self.conn.cursor()
  95         sql = """
  96         select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
  97         where l.description like ? and
  98         m.id = l.maker COLLATE NOCASE
  99         """
 100         cur.execute(sql, ('%'+pattern+'%', ))
 101         rows = cur. fetchall()
 102         return rows
 103
 104     def getLensByMaker(self, maker):
 105         cur = self.conn.cursor()
 106         sql = """
 107         select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
 108         where l.maker = ? and
 109         m.id = l.maker
 110         """
 111         if type(maker) == str:
 112             cur.execute("select id from lensmaker where maker = ? COLLATE NOCASE", (maker, ))
 113             lensmaker = cur.fetchone()
 114         else:
 115             lensmaker = (maker, )
 116         #cur.execute("select sn, description, notes, id from lens where maker = ?", lensmaker)
 117         cur.execute(sql, lensmaker)
 118         rows = cur. fetchall()
 119         return rows
 120
 121     def getLensByMakerPattern(self, maker, pattern):
 122         cur = self.conn.cursor()
 123         sql = """
 124         select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
 125         where l.description like ? and l.maker = ? and
 126         m.id = l.maker COLLATE NOCASE
 127         """
 128         if type(maker) == str:
 129             cur.execute("select id from lensmaker where maker = ? COLLATE NOCASE", (maker, ))
 130             lensmaker = cur.fetchone()
 131         else:
 132             lensmaker = (maker, )
 133         #cur.execute("select sn, description, notes, id from lens where description like ? and maker = ? COLLATE NOCASE", ('%'+pattern+'%', ) + lensmaker)
 134         cur.execute(sql, ('%'+pattern+'%', ) + lensmaker)
 135         rows = cur. fetchall()
 136         return rows
 137
 138     def __del__(self):
 139         try:
 140             self.conn.commit()
 141             self.conn.close()
 142         except Exception:
 143             pass