git.datanom.net - lensdatabase.git/blame_incremental

... / ...

Commit	Line	Data
	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