def delete(cls, process_id, table_param, reg_id): """ Delete a register in db based on the id field of the process model, obtained from a request's process_id url parameter. Args: process_id (str): id field of the process model, obtained from a request's process_id url parameter (processs/<process_id>). Returns: res (int): the deleted register id field """ # sanitize the input string and limit its length table_name = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_name) try: res = db.session.query(register_model).filter_by( id=reg_id).one() except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error # perform register delete db.session.delete(res) try: db.session.commit() except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error return reg_id
def create(cls, **register): """ Create a register in a process' table Args: register (dict): dict containing the fields of the new register, obtained from json in the body of the request. Returns: res (dict): the newly created register model """ # sanitize the input string and limit its length table_name = sanitize_str(register['table'], 256) Base.prepare(db.engine, reflect=False) #register_base = Base.classes.test_table register_base = eval("Base.classes." + table_name) # set the new values from the values array register_model = register_base(**register['values']) # update the register try: db.session.add(register_model) db.session.commit() new_id = register_model.id ##db.session.expunge_all() ##db.session.close() res = as_dict(register_model) except SQLAlchemyError as e: error = str(e) print("Error : ", error) res = {'error_c': error} return res
def update(cls, **register): """ Update a register in db based on a json from a request's body parameter. Args: register (dict): dict containing the register values table_param (dict): name of the table Returns: register_model (model): the updated model """ table_name = sanitize_str(register['table'], 256) Base.prepare(db.engine, reflect=True) register_model = eval("Base.classes." + table_name) # perform query model = db.session.query(register_model).filter_by( id=register['reg_id']).one() # set the new values from the values array for property, value in register['values'].items(): setattr(model, property, value) # update the register try: db.session.commit() ##db.session.expunge_all() ##db.session.close() except SQLAlchemyError as e: error = str(e) print("Error : ", error) res['register'] = {'error_e': error} return as_dict(model)
def delete(cls, process_id, table_param): """ Delete a table from a process. Args: process_id (str): id field of the process. table_param (str): name of the table Returns: res (str): table deleted confirmation message """ # query a process model # TODO: filter by userid and column,value #update metadata and tables Base.prepare(db.engine, reflect=True) # sanitize the input string and limit its length table_param = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_param) # TODO: verify that the table is in the tables array of the current process # delete the table try: register_model.__table__.drop(db.engine) reflect_prepare(db, Base) return table_param + " table deleted" except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error
def read(cls, process_id, table_param): """ Performs a query to a process table. Args: process_id (str): id field of the process model. table_param (str): name of the table Returns: res (dict): the requested process table. """ # query a process model # TODO: filter by userid and column,value # sanitize the input string and limit its length table_param = sanitize_str(table_param, 256) # query a table try: # TODO: query table by name from process tables array #ptable.read_all(int(process_param)) proc = as_dict(Process.query.filter_by(id=process_id).one()) res_list = json.loads(proc["tables"]) except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error # search for the name in the keys of elements of the tables array. try: return next(x for x in res_list if table_param in x["name"]) except StopIteration: raise ValueError("No matching record found") return None
def read(cls, process_id, table_param, reg_id): """ Performs a query to a process table register. Args: process_id (str): id field of the process model. table_param (str): name of the table Returns: res (model): the requested process table. """ table_param = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_param) # perform query res = db.session.query(register_model).filter_by(id=reg_id).one() return res
def read_all(cls, process_id, table_param): """ Query all registers of the process table register. Args: process_id (str): id field of the process model. table_param (str): name of the table Returns: res (list): the requested list of registers. """ # generate list of registers # TODO: filter by column,value # TODO: validate if the table is in the process tables array table_name = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_name) # perform query res = db.session.query(register_model).all() return [as_dict(c) for c in res]
def parse_sqlalchemy_column_type(self, input_str): # sanitize the input string and limit its length translated_input = sanitize_str(input_str, 256) valid_types = [ translated_input == "BigInteger", translated_input == "Boolean", translated_input == "Date", translated_input == "DateTime", translated_input == "Enum", translated_input == "Float", translated_input == "Integer", translated_input == "Interval", translated_input == "LargeBinary", translated_input == "MatchType", translated_input == "Numeric", translated_input == "PickleType", translated_input == "SchemaType", translated_input == "SmallInteger", translated_input == "String", translated_input == "Text", translated_input == "Time", translated_input == "Unicode", translated_input == "UnicodeText" ] if any(valid_types): return eval(translated_input) else: return Integer
class NewModel(Base): """ Map the columns to a list of register constructor arguments adn create a statement to be executed by the controller""" table_name = sanitize_str(table_param, 256) __tablename__ = table_name __table_args__ = {'extend_existing': True} id = Column(Integer, primary_key=True) def __init__(self, **kwargs): # extract kwargs into class attributes for property, value in kwargs.items(): setattr(self, property, value) # set the table self.meta_table = db.metadata.tables[self.table] #print(db.metadata.tables) def create_stmt(self): return insert(self.meta_table).values(self.values) def update_stmt(self): return update(self.meta_table).where( self.meta_table.c.id == self.reg_id).values(self.values) def __repr__(self): return str(self.table) @classmethod def create(cls, **register): """ Create a register in a process' table Args: register (dict): dict containing the fields of the new register, obtained from json in the body of the request. Returns: res (dict): the newly created register model """ # sanitize the input string and limit its length table_name = sanitize_str(register['table'], 256) Base.prepare(db.engine, reflect=False) #register_base = Base.classes.test_table register_base = eval("Base.classes." + table_name) # set the new values from the values array register_model = register_base(**register['values']) # update the register try: db.session.add(register_model) db.session.commit() new_id = register_model.id ##db.session.expunge_all() ##db.session.close() res = as_dict(register_model) except SQLAlchemyError as e: error = str(e) print("Error : ", error) res = {'error_c': error} return res @classmethod def read(cls, process_id, table_param, reg_id): """ Performs a query to a process table register. Args: process_id (str): id field of the process model. table_param (str): name of the table Returns: res (model): the requested process table. """ table_param = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_param) # perform query res = db.session.query(register_model).filter_by(id=reg_id).one() return res @classmethod def read_all(cls, process_id, table_param): """ Query all registers of the process table register. Args: process_id (str): id field of the process model. table_param (str): name of the table Returns: res (list): the requested list of registers. """ # generate list of registers # TODO: filter by column,value # TODO: validate if the table is in the process tables array table_name = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_name) # perform query res = db.session.query(register_model).all() return [as_dict(c) for c in res] @classmethod def update(cls, **register): """ Update a register in db based on a json from a request's body parameter. Args: register (dict): dict containing the register values table_param (dict): name of the table Returns: register_model (model): the updated model """ table_name = sanitize_str(register['table'], 256) Base.prepare(db.engine, reflect=True) register_model = eval("Base.classes." + table_name) # perform query model = db.session.query(register_model).filter_by( id=register['reg_id']).one() # set the new values from the values array for property, value in register['values'].items(): setattr(model, property, value) # update the register try: db.session.commit() ##db.session.expunge_all() ##db.session.close() except SQLAlchemyError as e: error = str(e) print("Error : ", error) res['register'] = {'error_e': error} return as_dict(model) @classmethod def delete(cls, process_id, table_param, reg_id): """ Delete a register in db based on the id field of the process model, obtained from a request's process_id url parameter. Args: process_id (str): id field of the process model, obtained from a request's process_id url parameter (processs/<process_id>). Returns: res (int): the deleted register id field """ # sanitize the input string and limit its length table_name = sanitize_str(table_param, 256) register_model = eval("Base.classes." + table_name) try: res = db.session.query(register_model).filter_by( id=reg_id).one() except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error # perform register delete db.session.delete(res) try: db.session.commit() except SQLAlchemyError as e: error = str(e) print("Error : ", error) return error return reg_id