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)
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)