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)