Welcome, guest | Sign In | My Account | Store | Cart

Similar to some other recipes on this site, this script allows you to access results from queries by attribute (eg, row.field_name), by key (eg, row['field_name']) or by Index (eg, row[0] or row[:2], etc.). It is different from other recipes in that it is both light-weight and offers a variety of access methods with very little overhead. Improvements could be made in fetching the query results (ie, right now I just fetchall()), but I'll leave that up to you.

Python, 86 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#
# Author: Kevin T. Ryan (same as left, separated with dots AT gmail.com)
# Date: 2008-Feb-19 @ 12:19:24 AM

# I first checked out (which are all very good - I suggest you check them out
# as they might suit your needs better):

#   - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/528939
#   - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/163605
#   - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81252
#   - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/52293

# While each provided the dictionary type interface that I was looking for,
# they were not quite as light-weight as I wanted (although dtuple might be -
# I didn't fetch the source for that one), or they didn't have the terribly
# simple interface that I wanted (ie, row.field_name or row['field_name'] or
# row[0] - whatever I preferred at the moment).  Although the
# "light-weightedness" of my approach might be questionable as I do a
# "fetchall" when no results are passed in - which sort of defeats the purpose
# a -little- bit.  I digress.  As such, I came up with the attached.  The
# recipe below only stores one copy of the fields which is initialized on
# __init__.  It does this in the same fashion that recipe 52293 does.  It also
# wraps each result row sort of like recipe 163605, but instead of storing a
# dictionary of the fields for every row it allows the "row" class to delegate
# back to the parent (ie, results class) by using __getattr__ judiciously.

# It also acts as an iterator for your results by defining __iter__() and
# next() methods.

# Usage:

#   conn = <connect method>
#   curs = conn.cursor()
#   curs.execute("<your SQL statement here")
#   for row in results(curs): # note, fetches all the rows 1st
#       print row.field_name
#       - OR -
#       print row['field_name']
#       - OR -
#       print row[0]
#       - OR -
#       print row[:3]

# Note that all of the above access methods are possible thanks to the pure
# goodness of Python :)  Also, you could easily improve how the data is
# fetched if you are building large queries - just delegate in each next()
# call if you need to fetch more records instead of fetching everything at the
# beginning of the call.  Finally, you could also make each row look more like
# a dictionary if you wanted to - add "has_key" method (k in
# self.parents.columns), "keys" method (self.parents.columns.keys()), etc.

class row(object):
    def __init__(self, parent, current_row):
        self.parent = parent
        self.current_row = current_row
    def __getattr__(self, name):
        try:
            return self.parent.get_item(self.current_row, name)
        except:
            raise AttributeError, "That field doesn't appear to be in the results row"
    def __getitem__(self, key):
        return self.__getattr__(key)

class results(object):
    def __init__(self, curs, results=None):
        if results is None:
            results = curs.fetchall() # too inefficient?
        self.rows = results
        # Start row at -1 so that when iteration commences (probably the only way this
        # should be called), it will update the row to point to the beginning of the results.
        self.row = row(self, -1)
        self.columns = dict( [(d[0],pos) for pos,d in enumerate(curs.description)] )
    def get_item(self, row, column):
        if column in self.columns:
            return self.rows[row][self.columns[column]]
        else:
            return self.rows[row][column] # Assumed to be a numeric reference or a slice
    def __iter__(self):
        return self
    def next(self):
        self.row.current_row += 1
        if self.row.current_row >= len(self.rows):
            raise StopIteration
        return self.row
    def __len__(self):
        return len(self.rows)

You may want to use this if you want to access results from queries in a variety of ways (as shown above) and you want a solution with very little overhead. The only additional information stored is really the one dictionary of column names. It also allows you to easily iterate through results (see comments above). Hope you enjoy.

Created by Kevin Ryan on Mon, 18 Feb 2008 (PSF)
Python recipes (4591)
Kevin Ryan's recipes (3)

Required Modules

  • (none specified)

Other Information and Tasks