- ''' Obtain rows from a table.
- Attribute sql_dir: dictionary with the following key: value
- 'SELECT': list or tuple of fields to retrieve) (by default all)
- 'FROM': string of table name (Mandatory)
- 'WHERE': dict of key:values, translated to key=value (key is null) AND ... (Optional)
- 'WHERE_NOT': dict of key:values, translated to key<>value (key is not null) AND ... (Optional)
- 'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional)
- 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional)
- 'LIMIT': limit of number of rows (Optional)
- 'ORDER_BY': list or tuple of fields to order
- Return: a list with dictionaries at each row
- '''
- #print sql_dict
- select_= "SELECT " + ("*" if 'SELECT' not in sql_dict else ",".join(map(str,sql_dict['SELECT'])) )
- #print 'select_', select_
- from_ = "FROM " + str(sql_dict['FROM'])
- #print 'from_', from_
- where_and = ""
- where_or = ""
- w=sql_dict.get('WHERE')
- if w:
- where_and = " AND ".join(map(self.__tuple2db_format_where, w.iteritems() ))
- w=sql_dict.get('WHERE_NOT')
- if w:
- if where_and: where_and += " AND "
- where_and += " AND ".join(map(self.__tuple2db_format_where_not, w.iteritems() ) )
- w=sql_dict.get('WHERE_OR')
- if w:
- where_or = " OR ".join(map(self.__tuple2db_format_where, w.iteritems() ))
- if where_and and where_or:
- if sql_dict.get("WHERE_AND_OR") == "AND":
- where_ = "WHERE " + where_and + " AND (" + where_or + ")"
+ """ Obtain rows from a table.
+ :param SELECT: list or tuple of fields to retrieve) (by default all)
+ :param FROM: string with table name (Mandatory)
+ :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional)
+ If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
+ If value is None, it will produce 'key is null'
+ If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
+ keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
+ The special keys "OR", "AND" with a dict value is used to create a nested WHERE
+ If a list, each item will be a dictionary that will be concatenated with OR
+ :param LIMIT: limit the number of obtianied entries (Optional)
+ :param ORDER_BY: list or tuple of fields to order, add ' DESC' to each item if inverse order is required
+ :return: a list with dictionaries at each row, raises exception upon error
+ """
+ # print sql_dict
+ cmd = "SELECT "
+ if 'SELECT' in sql_dict:
+ if isinstance(sql_dict['SELECT'], (tuple, list)):
+ cmd += ",".join(map(str, sql_dict['SELECT']))