root/livinglogic.python.orasql/orasql.py @ 15:65da029f888b

Revision 15:65da029f888b, 6.2 KB (checked in by Walter Doerwald <walter@…>, 15 years ago)

Fix and enhance docstring.

Line 
1#!/usr/bin/env python
2# -*- coding: iso-8859-1 -*-
3
4## Copyright 2004 by LivingLogic AG, Bayreuth/Germany.
5## Copyright 2004 by Walter Dörwald
6##
7## All Rights Reserved
8##
9## Permission to use, copy, modify, and distribute this software and its documentation
10## for any purpose and without fee is hereby granted, provided that the above copyright
11## notice appears in all copies and that both that copyright notice and this permission
12## notice appear in supporting documentation, and that the name of LivingLogic AG or
13## the author not be used in advertising or publicity pertaining to distribution of the
14## software without specific, written prior permission.
15##
16## LIVINGLOGIC AG AND THE AUTHOR DISCLAIM ALL WARRANTIES WITH REGARD TO THIS SOFTWARE,
17## INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO EVENT SHALL
18## LIVINGLOGIC AG OR THE AUTHOR BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL
19## DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER
20## IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR
21## IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
22
23
24"""
25<par>This module contains utilities for working with
26<link href="http://www.computronix.com/utilities.shtml#Oracle"><app>cx_Oracle</app></link>:</par>
27<ulist>
28<item>It allows calling procedure with keyword arguments (via the
29<pyref class="Proc"><class>Proc</class></pyref> class).</item>
30<item>Query results will be put into <pyref class="Record"><class>Record</class></pyref>
31objects, where database fields are accessible as object attributes.</item>
32</ulist>
33"""
34
35
36import datetime
37
38
39from cx_Oracle import *
40
41
42class Proc:
43    """
44    A <class>Proc</class> object is a wrapper for calling a specific Oracle
45    procedure with keyword arguments.
46    """
47    _ora2cx = {
48        "number": NUMBER,
49        "varchar2": STRING
50    }
51   
52    def __init__(self, name):
53        """
54        Create a <class>Proc</class> object. <arg>name</arg> is the name of the
55        Oracle procedure.
56        """
57        self.name = name
58        self._realargs = None
59
60    def __repr__(self):
61        return "Proc(%r)" % self.name
62
63    def _calcrealargs(self, cursor):
64        if self._realargs is None:
65            self._realargs = ({}, [])
66            cursor.execute("select lower(argument_name) as name, lower(in_out) as in_out, lower(data_type) as datatype from user_arguments where lower(object_name)=lower(:name) order by sequence", name=self.name)
67            for (i, record) in enumerate(cursor):
68                self._realargs[1].append((record.name, record.datatype, "in" in record.in_out, "out" in record.in_out))
69                self._realargs[0][record.name] = (i, record.datatype, "in" in record.in_out, "out" in record.in_out)
70
71    def __call__(self, cursor, **kwargs):
72        """
73        Call the procedure with keyword arguments <arg>kwargs</arg>. <arg>cursor</arg>
74        must be a <module>cx_Oracle</module> cursor. This will return a
75        <pyref class="Record"><class>Record</class></pyref> object containing the result
76        of the call.
77        """
78        self._calcrealargs(cursor)
79        realargs = [None]*len(self._realargs[1])
80        for (key, value) in kwargs.iteritems():
81            (pos, datatype, isin, isout) = self._realargs[0][key.lower()]
82            if isinstance(value, unicode):
83                value = value.encode("iso-8859-1")
84            realargs[pos] = value
85
86        # Replace out parameters with variables
87        for (pos, (name, datatype, isin, isout)) in enumerate(self._realargs[1]):
88            if isout:
89                var = cursor.var(self._ora2cx[datatype])
90                var.setvalue(0, realargs[pos])
91                realargs[pos] = var
92
93        result = Record()
94        for (i, value) in enumerate(cursor.callproc(self.name, realargs)):
95            result[self._realargs[1][i][0]] = value
96        return result
97
98
99class LLProc(Proc):
100    def __call__(self, cursor, **kwargs):
101        args = {}
102        for (key, value) in kwargs.iteritems():
103            if key == "user":
104                key = "c_user"
105            else:
106                key = "p_%s" % key
107            args[key] = value
108
109        result = Record()
110        for (key, value) in Proc.__call__(self, cursor, **args).iteritems():
111            if key == "c_user":
112                key = "user"
113            elif key.startswith("p_"):
114                key = key[2:]
115            else:
116                raise ValueError("unknown parameter name %r in result" % key)
117            result[key] = value
118        return result
119
120
121class Record(dict):
122    """
123    A <class>Record</class> is a subclass of <class>dict</class> that is used
124    for storing results of database queries. Both item and attribute access (i.e.
125    <method>__getitem__</method> and <method>__getattr__</method>) are available.
126    Field names are case insensitive.
127    """
128    def __getitem__(self, name):
129        return dict.__getitem__(self, name.lower())
130
131    def __setitem__(self, name, value):
132        dict.__setitem__(self, name.lower(), value)
133
134    def __delitem__(self, name):
135        dict.__delitem__(self, name.lower())
136
137    def __getattr__(self, name):
138        return self.__getitem__(name)
139
140    def __setattr__(self, name, value):
141        self.__setitem__(name, value)
142
143    def __delattr__(self, name):
144        self.__delitem__(name)
145
146    def __repr__(self):
147        return "%s.%s(%s)" % (self.__class__.__module__, self.__class__.__name__, ", ".join(["%s=%r" % item for item in self.iteritems()]))
148
149
150class Connection(Connection):
151    def cursor(self):
152        return Cursor(self)
153
154
155def connect(*args, **kwargs):
156    return Connection(*args, **kwargs)
157
158
159class Cursor(Cursor):
160    """
161    A subclass of <module>cx_Oracle</module>s cursor class. All database results
162    will be returned as <pyref class="Record"><class>Record</class> objects.
163    """
164    def makerecord(self, row, type=Record):
165        record = type()
166        for (i, field) in enumerate(row):
167            if isinstance(field, TIMESTAMP):
168                field = datetime.datetime(field.year, field.month, field.day, field.hour, field.minute, field.second, field.fsecond)
169            elif isinstance(field, DATETIME):
170                field = datetime.datetime(field.year, field.month, field.day, field.hour, field.minute, field.second)
171            record[self.description[i][0]] = field
172        return record
173
174    def fetchone(self, type=Record):
175        row = super(Cursor, self).fetchone()
176        if row is not None:
177            row = self.makerecord(row, type)
178        return row
179
180    def fetchmany(self, rows=0, type=Record):
181        sup = super(Cursor, self)
182        return [ self.makerecord(row, type) for row in sup.fetchmany(rows)]
183
184    def fetchall(self, type=Record):
185        sup = super(Cursor, self)
186        return [ self.makerecord(row, type) for row in sup.fetchall()]
187
188    def fetch(self, type=Record):
189        while True:
190            yield self.makerecord(self.next(), type)
191
192    def __iter__(self):
193        return self.fetch()
Note: See TracBrowser for help on using the browser.