示例#1
0
    def query(self, tables=None, condition=None, fields=[], querymappings=None, grouping=[], ordering=[],
              nomapping=False):
        """Execute a SELECT on the AS/400 turning the results in a list of dicts.

        In fields you can give a list of fields you are interested in. If fields is left empty the engine
        generates a list of field on it own by consulting the field mapping database in from fields.MAPPINGDIR.
        """

        if isinstance(tables, StringType):
            tables = [tables]
        if isinstance(fields, StringType):
            fields = [fields]
        if isinstance(grouping, StringType):
            grouping = [grouping]
        if isinstance(ordering, StringType):
            ordering = [ordering]
        if not querymappings and len(fields) != 1 and not nomapping:
            querymappings = dict()
            for table in tables:
                querymappings.update(MAPPINGDIR.get(table, {}))
        if not fields:
            fields = querymappings.keys()
        if not fields:
            raise RuntimeError("can't deduce field names")

        querystr = "SELECT %s FROM %s" % (','.join(fields),
                                          ','.join([self._get_tablename(x) for x in tables]))
        if condition:
            querystr += ' WHERE %s' % condition
        if grouping:
            querystr += ' GROUP BY %s' % (','.join(grouping), )
        if ordering:
            querystr += ' ORDER BY %s' % (','.join(ordering), )

        return self._execute_query(querystr, querymappings, fields)
示例#2
0
    def query(self, tables=None, condition=None, fields=None, querymappings=None,
              grouping=None, ordering=[], limit=None):
        r"""Execute a SELECT on the AS/400 turning the results in a list of dicts.

        In fields you can give a list of fields you are interested in. If fields is left empty the engine
        generates a list of field on it own by consulting the field mapping database in from
        fields.MAPPINGDIR.

        >>> MoftSconnection().query('ALK00', condition="LKLFSN=4034544") #doctest: +ELLIPSIS
        [{'lager': 100, 'versandart': '013', ...}]

        To suppress mapping provide querymappings={} and fields=[].
        >>> MoftSconnection().query(tables=['XPN00'], condition="PNSANR=2255")
        [{'satznummer': 2255, 'preis': Decimal('16.10')}]
        >>> MoftSconnection().query(tables=['XPN00'], condition="PNSANR=2255",
        ... fields=['PNSANR', 'PNPRB'], querymappings={})
        [(2255, Decimal('16.10'))]

        To get only certain fields give a list of fieldnames in fields=[...].
        >>> MoftSconnection().query(tables=['XPN00'], condition="PNSANR=2255", fields=['PNPRB'])
        [(Decimal('16.10'),)]

        Joins are straightforward if used with condition="<expression>":
        >>> MoftSconnection().query(['XPN00', 'XPR00'], condition="PNSANR=PRSANR and PNSANR=2255",
        ... fields=['PRDTVO', 'PNPRB'])
        [{'preis': Decimal('16.10'), 'gueltig_ab_date': datetime.date(2004, 12, 16)}]

        Aggregate functions can be created by using the "grouping" keyword:
        >>> sorted(MoftSconnection().query('XLF00', fields=['LFARTN', 'SUM(LFMGLP)'], grouping=['LFARTN'],
        ... condition="LFLGNR=3"))
        [('65166/01', '0'), ('65198', '0'), ('76095', '0'), ('76102', '0'), ('ED76095', '0')]

        If desired "querymappings" can be used to return a list of dicts:
        >>> sorted(MoftSconnection().query('XLF00', fields=['LFARTN', 'SUM(LFMGLP)'], grouping=['LFARTN'],
        ... condition="LFLGNR=3", querymappings={'LFARTN': 'artnr',
        ... 'SUM(LFMGLP)': 'menge'})) #doctest: +ELLIPSIS
        [{'menge': '0', 'artnr': '65166/01'}, {'menge': '0', 'artnr': '65198'}, ...]

        We also should be - to a certain degree - be Unicode aware:
        >>> MoftSconnection().query(u'XKD00', u"KDKDNR LIKE '%18287'")[0]['ort'].encode('utf8')
        'G\xc3\xbcnzburg'
        """

        # fixup sloppy parameter passing
        if isinstance(tables, basestring):
            tables = [tables]
        if isinstance(grouping, basestring):
            grouping = [grouping]
        if not grouping:
            grouping = []
        if isinstance(ordering, basestring):
            ordering = [ordering]
        if isinstance(fields, basestring):
            fields = [fields]

        if not ordering:
            ordering = []
        if not fields:
            fields = []

        if querymappings == {} and not fields:
            raise RuntimeError("Please give fieldnames.")

        if querymappings is None and len(fields) != 1:
            querymappings = {}
            for table in tables:
                querymappings.update(MAPPINGDIR.get(table, {}))

        if not fields:  # decuce fieldnames from querymappings
            fields = querymappings.keys()
        if not fields:  # still nothing found
            raise RuntimeError("can't deduce field names, check fields.py")

        querystr = ["SELECT %s FROM %s" % (','.join(fields),
                                          ','.join([self._get_tablename(x) for x in tables]))]
        if condition:
            querystr.append('WHERE %s' % condition)
        if grouping:
            querystr.append('GROUP BY %s' % (','.join(grouping), ))
        if ordering:
            querystr.append('ORDER BY %s' % (','.join(ordering), ))
        if limit:
            querystr.append('FETCH FIRST %d ROWS ONLY' % limit)

        return self._execute_query(' '.join(querystr), querymappings, fields)