def close(self):
if self.conn:
try:
if self._complete:
self.conn.commit()
else:
self.conn.rollback()
except Exception as e:
raise DbException(*e.args)
finally:
try:
self.conn.close()
except Exception as e:
raise DbException(*e.args)
def create_table(self, table_name):
sql = ''
table_name = self.scrub(table_name)
if table_name == 'user':
sql = 'CREATE TABLE IF NOT EXISTS {} ('\
'id INTEGER PRIMARY KEY AUTOINCREMENT,' \
'username TEXT UNIQUE, ' \
'fullname TEXT, ' \
'password TEXT, ' \
'userRole TEXT)'.format(table_name)
elif table_name == 'session':
sql = 'CREATE TABLE IF NOT EXISTS {} (' \
'id INTEGER PRIMARY KEY AUTOINCREMENT,' \
'userid INTEGER NOT NULL, ' \
'loginTime timestamp NOT NULL, ' \
'logoutTime timestamp)'.format(table_name)
elif table_name == 'setting':
sql = 'CREATE TABLE IF NOT EXISTS {} (' \
'id INTEGER PRIMARY KEY AUTOINCREMENT,' \
'setting_key TEXT, ' \
'setting_value TEXT)'.format(table_name)
try:
self.conn.execute(sql)
except OperationalError as e:
raise DbException(*e.args)
def insert(self, user):
try:
c = self.db.conn.cursor()
# this needs an appropriate table
c.execute("INSERT INTO user ('username','fullname','password','userRole') VALUES(?, ?, ?, ?)", (user.username, user.fullname, user.password, user.userRole))
self.db.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def insert(self, setting:Setting):
try:
c = self.db.conn.cursor()
# this needs an appropriate table
c.execute("INSERT INTO setting ('setting_key','setting_value') VALUES(?, ?)",
(setting.setting_key, setting.setting_value))
self.db.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def deleteById(self, id):
sql_check = 'SELECT EXISTS(SELECT 1 FROM user WHERE id=? LIMIT 1)'
sql_delete = 'DELETE FROM user WHERE id=?'.format(id,)
c = self.db.conn.execute(sql_check, (id,)) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_delete, (id,)) # we need the comma
self.db.conn.commit()
else:
raise DbException("Can't delete user (id:{}) because it's not stored in db".format(id,))
def updateWithoutPassword(self, user):
sql_check = 'SELECT EXISTS(SELECT 1 FROM user WHERE id=? LIMIT 1)'
sql_update = 'UPDATE user SET username=?, fullname=?, userRole=? WHERE id=?'
c = self.db.conn.execute(sql_check, (user.id,)) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_update, (user.username, user.fullname, user.userRole, user.id))
self.db.conn.commit()
else:
raise DbException("Can't update user (id:{}) because it's not stored in db".format(user.id,))
def insert(self, session):
try:
c = self.db.conn.cursor()
# this needs an appropriate table
c.execute(
"INSERT INTO session ('userid','loginTime','logoutTime') VALUES(?, ?, ?)",
(session.userid, session.loginTime, session.logoutTime))
self.db.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def insert_list(self, users):
try:
entries = list()
for x in users:
entries.append((x['username'], x['fullname'], x['password'], x['userRole']))
c = self.conn.cursor()
# this needs an appropriate table
c.execute("INSERT INTO user ('username','fullname','password','userRole') VALUES(?, ?, ?, ?)", entries)
self.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def insert_list(self, settings):
try:
entries = list()
for x in settings:
entries.append((x['setting_key'], x['setting_value']))
c = self.conn.cursor()
# this needs an appropriate table
c.execute("INSERT INTO setting ('setting_key','setting_value') VALUES(?, ?)", entries)
self.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def update(self, setting):
sql_check = 'SELECT EXISTS(SELECT 1 FROM setting WHERE id=? LIMIT 1)'
sql_update = 'UPDATE setting SET '\
'setting_key=?, setting_value=? '\
'WHERE id=?'
c = self.db.conn.execute(sql_check, (setting.id,)) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_update, (setting.setting_key, setting.setting_value, setting.id,))
self.db.conn.commit()
else:
raise DbException("Can't update setting (id:{}) because it's not stored in db".format(setting.id,))
def insert_list(self, sessions):
try:
entries = list()
for x in sessions:
entries.append((x['userid'], x['loginTime'], x['logoutTime']))
c = self.conn.cursor()
# this needs an appropriate table
c.execute(
"INSERT INTO session ('userid','loginTime','logoutTime') VALUES(?, ?, ?)",
entries)
self.conn.commit()
except Exception as e:
raise DbException(*e.args, **e.kwargs)
def update(self, session):
sql_check = 'SELECT EXISTS(SELECT 1 FROM session WHERE id=? LIMIT 1)'
sql_update = 'UPDATE session SET userid=?, loginTime=?, logoutTime=? WHERE id=?'
c = self.db.conn.execute(sql_check,
(session.id, )) # we need the comma
result = c.fetchone()
if result[0]:
c.execute(sql_update, (session.userid, session.loginTime,
session.logoutTime, session.id))
self.db.conn.commit()
else:
raise DbException(
"Can't update session (id:{}) because it's not stored in db".
format(session.id, ))
def find_by_condition(self, condition=None, order=None, limit=None):
try:
c = self.db.conn.cursor()
# this needs an appropriate table
sql= ""
if(condition is None or condition == ""):
sql= "SELECT * FROM user"
else :
sql= "SELECT * FROM user WHERE " + condition
if(order is not None and len(order) > 0):
sql= sql + " ORDER BY " + order
if(limit is not None and len(str(limit)) > 0):
sql= sql + " LIMIT " + str(limit)
c.execute(sql)
results = c.fetchall()
return list(map(lambda x: self.tuple_to_dict(x), results))
except Exception as e:
raise DbException(*e.args, **e.kwargs)