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(sql, lensmaker)
 117         rows = cur. fetchall()
 118         return rows
 119
 120     def getLensByMakerPattern(self, maker, pattern):
 121         cur = self.conn.cursor()
 122         sql = """
 123         select m.maker, l.sn, l.description, l.notes, l.id from lensmaker m, lens l
 124         where l.description like ? and l.maker = ? and
 125         m.id = l.maker COLLATE NOCASE
 126         """
 127         if type(maker) == str:
 128             cur.execute("select id from lensmaker where maker = ? COLLATE NOCASE", (maker, ))
 129             lensmaker = cur.fetchone()
 130         else:
 131             lensmaker = (maker, )
 132         cur.execute(sql, ('%'+pattern+'%', ) + lensmaker)
 133         rows = cur. fetchall()
 134         return rows
 135
 136     def __del__(self):
 137         try:
 138             self.conn.commit()
 139             self.conn.close()
 140         except Exception:
 141             pass