TRiBByX
8/9/2018 - 9:31 AM

Generic Read and Execute_sql database functions

A generic read function that can handle dynamic querying. And an Execute_sql function that executes and returns result from the sql.

def read(self, conds=True, lim=-1, table='vulnerabilities', *argv, **kwargs):
        #region docstring
        """
        Returns a database pull from a specified talbe 
        with argv as columns, kwargs as
        conditions and lim as limit.
        lim set to -1 as standard. (This is because limit might
        be needed i specific pulls, but if set to -1 doesnt do
        anything.)

        SELECT *argv FROM table
        WHERE **kwargs
        limit(lim)

        example:
            database.read(False, -1, 'csvs', 'filename')

        Columns:
        _______________________________________
        asset_id, host_name, ip, tag_name, cvss,
        exploits, severity, scan_id, started,
        category_name, title, summary, client.
        """
        #endregion
        
        if conds is True:
            select = ''
            for argument in argv: select += '{argument}, '.format(argument=argument)
            conditionals = ''
            for key, value in kwargs.items():
                if type(value) == int:
                    conditionals += '{key} = {value} and '.format(key=key, value=value)
                if type(value) == tuple:
                    cond = ' '.join(value)
                    conditionals += '{cond} and '.format(cond=cond)
                else:
                    conditionals += '''{key} = '{value}' and '''.format(key=key, value=value)

            conditionals = conditionals[:-4]
            sql = '''
                SELECT {select} FROM {table}
                WHERE {conditionals}
                limit({lim});
            '''.format(table=table, select=select[:-2], conditionals=conditionals, lim=lim)
        else:
            select = ''
            for argument in argv: select += '{argument}, '.format(argument=argument)
            sql = '''
                SELECT {select} FROM {table}
                limit({lim});
            '''.format(select=select[:-2], lim=lim, table=table)
        result = self.execute_sql(sql)
        if len(result) < 1:
            result = None
        return result
def execute_sql(self, sql, bindings=[]):
  """
  Executes given sql.
  Arguments:
  -- sql: a string of sql
  -- bindings: if sql needs data. (leave empty if not needed)
  """
  
  try:
      if len(bindings) > 0: # This check is important, if no argvs are given, this will break.
          dat = self.conn.execute(sql, bindings)
      else:
          dat = self.conn.execute(sql)
      self.conn.commit() # Mostly there for the CUD of CRUD, but doesn't seem to break read.
  except Error as e:
      raise e
  return list(dat)