Python: it’s been done before
I’ve learned that with Python, if something takes more than a few lines to write, there’s a very good chance that there’s another way to do it but with one or two lines of code. Chances are someone else has already come across a similar problem, and written a module to solve it – and it’s just a matter of importing their module and reusing it.
The other day I blogged about some functions that query a database and return a list or dictionary. Today I was browsing further in the python docs and learned a much simpler way of achieving the same effect, using named tuples:
>>> from collections import namedtuple
>>> def tableTuples(source, connection, name = None):
curs = connection.cursor()
curs.execute('SELECT * FROM (%s)' % (source))
query_fields = [desc[0] for desc in curs.description]
Row = namedtuple(name or source, query_fields)
return [Row(*row) for row in curs]
>>> emp = tableTuples('EMP', orcl)
>>> emp[0]
EMP(EMPNO=7369, ENAME='SMITH', JOB='CLERK', MGR=7902,
HIREDATE=datetime.datetime(1980, 12, 17, 0, 0),
SAL=800.0, COMM=None, DEPTNO=20)
>>> emp[1].ENAME
'ALLEN'
One limitation is that the query must provide names for each column (easy enough with aliases). This limitation is surmountable in Python 2.7 and 3.1, wherein namedtuples support a rename attribute.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.