Changeset 246:22d4205a6d2f in livinglogic.python.orasql

Show
Ignore:
Timestamp:
03/25/08 17:30:31 (12 years ago)
Author:
Walter Doerwald <walter@…>
Branch:
default
Tags:
rel-1-23
Message:

Rwrite procedure/function call (and Record class). Show datatype in Argument repr().

Wehen calling procedures/functions orasql will only pass those arguments that are
passed to the call (or variables for out parameters). Internally this is handled by
executing the call as a parameterized query calling the procedure/function
with named arguments.

FetchRecord? has been renamed to Record (and is used for the result of procedure and
function calls, which required some internal changes to FetchRecord?).

The former Record has been renamed to Args as it's only use now is collecting
arguments for procedure/function calls. (The method fromdata() has been dropped.)

The repr() output of Argument objects now shows the datatype.

Files:
4 modified

Legend:

Unmodified
Added
Removed
  • NEWS.rst

    r243 r246  
     1Changes in 1.23 (released 03/25/2008) 
     2------------------------------------- 
     3 
     4*   Calling procedures and functions has been rewritten: :mod:`ll.orasql` will 
     5    only pass those parameters to the procedure/function that are passed to the 
     6    call (or variables for out parameters). Internally this is handled by 
     7    executing the call as a parameterized query calling the procedure/function 
     8    with named arguments. 
     9 
     10*   :class:`FetchRecord` has been renamed to :class:`Record` (and is used for 
     11    the result of procedure and function calls now, which required some internal 
     12    changes to :class:`FetchRecord`). The former :class:`Record` has been renamed 
     13    to :class:`Args` as it's only use no is collecting arguments for 
     14    procedure/function calls. (The method :meth:`fromdata` has been dropped.) 
     15 
     16*   The :meth:`__repr__` output of :class:`Argument` objects now shows the 
     17    datatype. 
     18 
     19 
    120Changes in 1.22 (released 03/19/2008) 
    221------------------------------------- 
  • setup.py

    r242 r246  
    5656args=dict( 
    5757    name="ll-orasql", 
    58     version="1.22", 
     58    version="1.23", 
    5959    description="Utilities for working with cx_Oracle", 
    6060    long_description=descr, 
  • src/ll/orasql/__init__.py

    r245 r246  
    108108 
    109109 
    110 _default = object() # marker object for unset parameters 
    111  
    112  
    113 class Record(dict): 
    114     """ 
    115     A :class:`Record` is a subclass of :class:`dict` that is used for storing 
    116     results of database queries. Both item and attribute access (i.e. 
    117     :meth:`__getitem__` and :meth:`__getattr__`) are available. Field names are 
     110class Args(dict): 
     111    """ 
     112    An :class:`Args` object is a subclass of :class:`dict` that is used for 
     113    passing arguments to procedures and functions. Both item and attribute access 
     114    (i.e. :meth:`__getitem__` and :meth:`__getattr__`) are available. Names are 
    118115    case insensitive. 
    119116    """ 
     
    128125        dict.update(self, ((key.lower(), value) for (key, value) in kwargs.iteritems())) 
    129126 
    130     @classmethod 
    131     def fromdata(cls, cursor, row): 
    132         """ 
    133         This class method can be used to create a :class:`Record` object from the 
    134         database data. 
    135         """ 
    136         return cls((descr[0].lower(), cursor._decode(field, descr[1] in (BLOB, BINARY))) for (descr, field) in itertools.izip(cursor.description, row)) 
    137  
    138127    def __getitem__(self, name): 
    139128        return dict.__getitem__(self, name.lower()) 
     
    174163 
    175164 
    176 class FetchRecord(tuple): 
    177     """ 
    178     A :class:`FetchRecord` is a subclass of :class:`tuple` that is used for 
    179     storing results of database fetches. Both item and attribute access (i.e. 
     165class RecordMaker(object): 
     166    def __init__(self, cursor): 
     167        self._readlobs = cursor.readlobs 
     168        self._encoding = cursor.connection.encoding 
     169        self._index2name = [d[0].lower() for d in cursor.description] 
     170        self._isblob = tuple(descr[1] in (BLOB, BINARY) for descr in cursor.description) 
     171 
     172    def _decode(self, value, isblob): 
     173        if isinstance(value, LOB) and (self._readlobs is True or (isinstance(self._readlobs, (int, long)) and value.size() <= self._readlobs)): 
     174            value = value.read() 
     175        if isinstance(value, str) and not isblob: 
     176            value = value.decode(self._encoding) 
     177        return value 
     178 
     179    def __call__(self, *row): 
     180        row = tuple(self._decode(*args) for args in itertools.izip(row, self._isblob)) 
     181        return Record(self._index2name, row) 
     182 
     183 
     184class Record(tuple): 
     185    """ 
     186    A :class:`Record` is a subclass of :class:`tuple` that is used for storing 
     187    results of database fetches. Both item and attribute access (i.e. 
    180188    :meth:`__getitem__` and :meth:`__getattr__`) are available. Field names are 
    181189    case insensitive. 
    182190    """ 
    183191 
    184     @classmethod 
    185     def fromfetch(cls, recordmaker, args): 
    186         record = tuple.__new__(cls, args) 
    187         record._maker = recordmaker 
     192    def __new__(cls, names, values): 
     193        record = tuple.__new__(cls, values) 
     194        record._index2name = names 
     195        record._name2index = dict(itertools.izip(names, itertools.count())) 
    188196        return record 
    189197 
    190198    def __getitem__(self, arg): 
    191199        if isinstance(arg, basestring): 
    192             arg = self._maker._name2index[arg.lower()] 
     200            arg = self._name2index[arg.lower()] 
    193201        return tuple.__getitem__(self, arg) 
    194202 
    195203    def __getattr__(self, name): 
    196204        try: 
    197             index = self._maker._name2index[name.lower()] 
     205            index = self._name2index[name.lower()] 
    198206        except KeyError: 
    199207            raise AttributeError("'%s' object has no attribute %r" % (self.__class__.__name__, name)) 
     
    204212        Return an iterator over field names 
    205213        """ 
    206         return iter(self._maker._index2name) 
     214        return iter(self._index2name) 
    207215 
    208216    def keys(self): 
     
    210218        Return a list of field names 
    211219        """ 
    212         return self._maker._index2name[:] 
     220        return self._index2name[:] 
    213221 
    214222    def items(self): 
     
    216224        Return a list of (field name, field value) tuples. 
    217225        """ 
    218         return [(key, tuple.__getitem__(self, index)) for (index, key) in enumerate(self._maker._index2name)] 
     226        return [(key, tuple.__getitem__(self, index)) for (index, key) in enumerate(self._index2name)] 
    219227 
    220228    def iteritems(self): 
     
    222230        Return an iterator over (field name, field value) tuples. 
    223231        """ 
    224         return ((key, tuple.__getitem__(self, index)) for (index, key) in enumerate(self._maker._index2name)) 
     232        return ((key, tuple.__getitem__(self, index)) for (index, key) in enumerate(self._index2name)) 
    225233 
    226234    def __xattrs__(self, mode="default"): 
     
    616624 
    617625 
    618 class RecordMaker(object): 
    619     def __init__(self, cursor): 
    620         self._name2index = dict((d[0].lower(), i) for (i, d) in enumerate(cursor.description)) 
    621         self._index2name = [d[0].lower() for d in cursor.description] 
    622         self._isblob = tuple(descr[1] in (BLOB, BINARY) for descr in cursor.description) 
    623         self._readlobs = cursor.readlobs 
    624         self._encoding = cursor.connection.encoding 
    625  
    626     def _decode(self, value, isblob): 
    627         if isinstance(value, LOB) and (self._readlobs is True or (isinstance(self._readlobs, (int, long)) and value.size() <= self._readlobs)): 
    628             value = value.read() 
    629         if isinstance(value, str) and not isblob: 
    630             value = value.decode(self._encoding) 
    631         return value 
    632  
    633     def __call__(self, *row): 
    634         row = tuple(self._decode(*args) for args in itertools.izip(row, self._isblob)) 
    635         return FetchRecord.fromfetch(self, row) 
    636  
    637  
    638626class Cursor(Cursor): 
    639627    """ 
     
    641629    support a unicode statement and unicode parameters (they will be encoded in 
    642630    the client encoding before being passed to the database). The "fetch" methods 
    643     will return records as :class:`FetchRecord` objects and string values and 
     631    will return records as :class:`Record` objects and string values and 
    644632    ``CLOB`` values, if the cursors :attr:`readlobs` attribute has the 
    645633    appropriate value) will be returned as :class:`unicode` objects (except for 
     
    654642        super(Cursor, self).__init__(connection) 
    655643        self.readlobs = (readlobs if readlobs is not None else connection.readlobs) 
     644 
     645    def _decode(self, value, isblob): 
     646        if isinstance(value, LOB) and (self.readlobs is True or (isinstance(self.readlobs, (int, long)) and value.size() <= self.readlobs)): 
     647            value = value.read() 
     648        if isinstance(value, str) and not isblob: 
     649            value = value.decode(self.connection.encoding) 
     650        return value 
    656651 
    657652    def _encode(self, value): 
     
    18511846 
    18521847    def __repr__(self): 
    1853         return "<%s.%s name=%r position=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.name, self.position, id(self)) 
     1848        return "<%s.%s name=%r position=%r datatype=%r at 0x%x>" % (self.__class__.__module__, self.__class__.__name__, self.name, self.position, self.datatype, id(self)) 
    18541849 
    18551850    def __xattrs__(self, mode="default"): 
     
    18941889                    i += 1 
    18951890 
    1896     def _getparamarray(self, cursor, *args, **kwargs): 
    1897         # Get preinitialized parameter array 
    1898         la = len(args) 
    1899         lra = len(self._argsbypos) 
    1900         if la > lra: 
    1901             raise TypeError("too many parameters for %r: %d given, %d expected" % (self, la, lra)) 
    1902         realargs = list(args) + [_default]*(lra-la) 
    1903  
    1904         # Put keyword arguments into the parameter array 
    1905         for (key, value) in kwargs.iteritems(): 
     1891    def _getargs(self, cursor, *args, **kwargs): 
     1892        queryargs = {} 
     1893 
     1894        if len(args) > len(self._argsbypos): 
     1895            raise TypeError("too many parameters for %r: %d given, %d expected" % (self, len(args), len(self._argsbypos))) 
     1896 
     1897        # Handle positional arguments 
     1898        for (arg, arginfo) in itertools.izip(args, self._argsbypos): 
     1899            queryargs[arginfo.name] = self._wraparg(cursor, arginfo, arg) 
     1900 
     1901        # Handle keyword arguments 
     1902        for (argname, arg) in kwargs.iteritems(): 
     1903            argname = argname.lower() 
     1904            if argname in queryargs: 
     1905                raise TypeError("duplicate argument for %r: %s" % (self, argname)) 
    19061906            try: 
    1907                 arginfo = self._argsbyname[key.lower()] 
     1907                arginfo = self._argsbyname[argname] 
    19081908            except KeyError: 
    1909                 raise TypeError("unknown parameter for %r: %s" % (self, key)) 
    1910             else: 
    1911                 if realargs[arginfo.position] is not _default: 
    1912                     raise TypeError("duplicate argument for %r: %s" % (self, key)) 
    1913             realargs[arginfo.position] = cursor._encode(value) 
    1914  
    1915         # Replace out parameters (and strings that are longer than the allowed 
    1916         # maximum) with variables; replace unspecified parameters with :const:`None` 
     1909                raise TypeError("unknown parameter for %r: %s" % (self, argname)) 
     1910            queryargs[arginfo.name] = self._wraparg(cursor, arginfo, arg) 
     1911 
     1912        # Add out parameters for anything that hasn't been specified 
    19171913        for arginfo in self._argsbypos: 
    1918             realarg = realargs[arginfo.position] 
    1919             if realarg is _default: 
    1920                 realarg = None 
    1921                 realargs[arginfo.position] = realarg 
    1922             if arginfo.isout or arginfo.datatype == "blob" or (isinstance(realarg, basestring) and len(realarg) >= 4000): 
    1923                 try: 
    1924                     type = self._ora2cx[arginfo.datatype] 
    1925                 except KeyError: 
    1926                     raise TypeError("can't handle parameter %s of type %s in %r" % (arginfo.name, arginfo.datatype, self)) 
    1927                 var = cursor.var(type) 
    1928                 var.setvalue(0, realarg) 
    1929                 realargs[arginfo.position] = var 
    1930         return realargs 
     1914            if arginfo.name not in queryargs and arginfo.isout: 
     1915                queryargs[arginfo.name] = self._wraparg(cursor, arginfo, None) 
     1916 
     1917        return queryargs 
     1918 
     1919    def _wraparg(self, cursor, arginfo, arg): 
     1920        arg = cursor._encode(arg) 
     1921        try: 
     1922            type = self._ora2cx[arginfo.datatype] 
     1923        except KeyError: 
     1924            raise TypeError("can't handle parameter %s of type %s in %r" % (arginfo.name, arginfo.datatype, self)) 
     1925        if type is STRING and isinstance(arg, str) and len(arg) >= 4000: 
     1926            type = CLOB 
     1927        var = cursor.var(type) 
     1928        var.setvalue(0, arg) 
     1929        return var 
     1930 
     1931    def _unwraparg(self, cursor, arginfo, arg): 
     1932        try: 
     1933            arg = arg.getvalue(0) 
     1934        except AttributeError: 
     1935            pass 
     1936        return cursor._decode(arg, arginfo.datatype == "blob") 
     1937 
     1938    def _makerecord(self, cursor, args): 
     1939        names = [] 
     1940        values = [] 
     1941        for arginfo in self._argsbypos: 
     1942            name = arginfo.name 
     1943            if name in args: 
     1944                names.append(name) 
     1945                values.append(self._unwraparg(cursor, arginfo, args[name])) 
     1946        return Record(names, values) 
    19311947 
    19321948    def iterarguments(self, connection=None): 
     
    19571973        """ 
    19581974        Call the procedure with arguments :var:`args` and keyword arguments 
    1959         :var:`kwargs`. :var:`cursor` must be a :mod:`cx_Oracle` or 
    1960         :mod:`ll.orasql` cursor. This will return a :class:`Record` object 
    1961         containing the result of the call (i.e. this record will contain all in 
    1962         and out parameters). 
     1975        :var:`kwargs`. :var:`cursor` must be a :mod:`ll.orasql` cursor. This will 
     1976        return a :class:`Record` object containing the result of the call (i.e. 
     1977        this record will contain all specified and all out parameters). 
    19631978        """ 
    19641979        self._calcargs(cursor) 
    1965  
    1966         # Get preinitialized parameter array 
    1967         realargs = self._getparamarray(cursor, *args, **kwargs) 
    19681980 
    19691981        if self.owner is None: 
     
    19711983        else: 
    19721984            name = "%s.%s" % (self.owner, self.name) 
    1973         name = cursor._encode(name) 
    1974  
    1975         return Record((self._argsbypos[i].name, cursor._decode(value, self._argsbypos[i].datatype == "blob")) for (i, value) in enumerate(cursor.callproc(name, realargs))) 
     1985 
     1986        queryargs = self._getargs(cursor, *args, **kwargs) 
     1987     
     1988        query = "begin %s(%s); end;" % (name, ", ".join("%s=>:%s" % (name, name) for name in queryargs)) 
     1989 
     1990        cursor.execute(query, queryargs) 
     1991 
     1992        return self._makerecord(cursor, queryargs) 
    19761993 
    19771994 
     
    19862003        """ 
    19872004        Call the function with arguments :var:`args` and keyword arguments 
    1988         :var:`kwargs`. :var:`cursor` must be a :mod:`cx_Oracle` or 
    1989         :mod:`ll.orasql` cursor. This will return a tuple containing the result 
    1990         and a :class:`Record` object containing the modified parameters (i.e. 
    1991         this record will contain all in and out parameters). 
     2005        :var:`kwargs`. :var:`cursor` must be an :mod:`ll.orasql` cursor. 
     2006        This will return a tuple containing the result and a :class:`Record` 
     2007        object containing the modified parameters (i.e. this record will contain 
     2008        all specified and out parameters). 
    19922009        """ 
    19932010        self._calcargs(cursor) 
    1994  
    1995         # Get preinitialized parameter array 
    1996         realargs = self._getparamarray(cursor, *args, **kwargs) 
    19972011 
    19982012        if self.owner is None: 
     
    20002014        else: 
    20012015            name = "%s.%s" % (self.owner, self.name) 
    2002         name = cursor._encode(name) 
    2003  
    2004         returnvalue = cursor._decode(cursor.callfunc(name, self._ora2cx[self._returnvalue.datatype], realargs), self._returnvalue.datatype == "blob") 
    2005         result = Record() 
    2006         for (i, value) in enumerate(realargs): 
    2007             arginfo = self._argsbypos[i] 
    2008             if arginfo.isout: 
    2009                 value = value.getvalue(0) 
    2010             result[arginfo.name] = cursor._decode(value, arginfo.datatype == "blob") 
    2011         return (returnvalue, result) 
     2016 
     2017        queryargs = self._getargs(cursor, *args, **kwargs) 
     2018 
     2019        returnvalue = "r" 
     2020        while returnvalue in queryargs: 
     2021            returnvalue += "_" 
     2022        queryargs[returnvalue] = self._wraparg(cursor, self._returnvalue, None) 
     2023 
     2024        query = "begin :%s := %s(%s); end;" % (returnvalue, name, ", ".join("%s=>:%s" % (name, name) for name in queryargs if name != returnvalue)) 
     2025 
     2026        cursor.execute(query, queryargs) 
     2027 
     2028        returnvalue = self._unwraparg(cursor, self._returnvalue, queryargs.pop(returnvalue)) 
     2029 
     2030        return (returnvalue, self._makerecord(cursor, queryargs)) 
    20122031 
    20132032 
  • test/test_orasql.py

    r234 r246  
    260260 
    261261def test_callprocedure(): 
    262     db = orasql.connect(dbname) 
    263     proc = orasql.Procedure("orasql_testprocedure") 
    264     result = proc(db.cursor(), p_in=42, p_inout=17) 
    265     assert result.p_in == 42 
    266     assert result.p_out == 17 
    267     assert result.p_inout == 40 
     262    db = orasql.connect(dbname, readlobs=True) 
     263    proc = db.getobject("orasql_testprocedure") 
     264    result = proc(db.cursor(), c_user=u"py.test", p_in=u"abcÀöÌ", p_inout=u"abc"*10000) 
     265    assert result.p_in == u"abcÀöÌ" 
     266    assert result.p_out == u"ABCÄÖÜ" 
     267    assert result.p_inout == u"ABC"*10000 + u"abcÀöÌ" 
    268268 
    269269 
    270270def test_callfunction(): 
    271     db = orasql.connect(dbname) 
    272     func = orasql.Function("orasql_testfunction") 
    273     result = func(db.cursor(), p_in=42, p_inout=17) 
    274     assert result[0] == "gurk" 
    275     assert result[1].p_in == 42 
    276     assert result[1].p_out == 17 
    277     assert result[1].p_inout == 40 
    278  
    279  
    280 def test_xfetch(): 
     271    db = orasql.connect(dbname, readlobs=True) 
     272    func = db.getobject("orasql_testfunction") 
     273    (result, args) = func(db.cursor(), c_user=u"py.test", p_in=u"abcÀöÌ", p_inout=u"abc"*10000) 
     274    assert result == u"ABCÄÖÜ" 
     275    assert args.p_in == u"abcÀöÌ" 
     276    assert args.p_out == u"ABCÄÖÜ" 
     277    assert args.p_inout == u"ABC"*10000 + u"abcÀöÌ" 
     278 
     279 
     280def test_fetch(): 
    281281    for obj in objdict: 
    282282        if isinstance(obj, orasql.Table): 
     
    284284            db = orasql.connect(dbname) 
    285285            c = db.cursor() 
    286             c.xexecute("select * from %s" % obj.name) 
     286            c.execute("select * from %s" % obj.name) 
    287287            c.readlobs = False 
    288             c.xfetchone() 
    289             c.xexecute("select * from %s" % obj.name) 
     288            c.fetchone() 
     289            c.execute("select * from %s" % obj.name) 
    290290            c.readlobs = True 
    291             c.xfetchone() 
     291            c.fetchone() 
    292292            break 
    293293