Source

pydal / pydal / dbapi.py

Jan Brohl 93180e0 

















Jan Brohl a34c12d 

Jan Brohl 93180e0 













Jan Brohl a34c12d 
Jan Brohl 93180e0 







Jan Brohl a34c12d 
Jan Brohl 93180e0 












Jan Brohl a34c12d 
Jan Brohl 93180e0 



























Jan Brohl a34c12d 


Jan Brohl 93180e0 







































































































































































































Jan Brohl a34c12d 
Jan Brohl 93180e0 








Jan Brohl a34c12d 
Jan Brohl 93180e0 























































Jan Brohl a34c12d 
Jan Brohl 93180e0 





































































































Jan Brohl a34c12d 
Jan Brohl 93180e0 



  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
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
""" Wraps DB-API V2 compliant database drivers.

LICENSE
=======

Copyright (c) 2004, Randall Smith
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
    * Neither the name of the <ORGANIZATION> nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


Usage Example:
import pydal.dbapi as dal
import pydal.dbexceptions as ex
dbmod = dal.wrapdriver('psycopg')
# (Optional) Set the datetime type you want to use.
# Defaults to Python's datetime module.  Can be 'mx', 'py', or 'native'.
dbmod.dtmod = 'py' # Python datetime module.
dbmod.paramstyle = 'qmark'
try:
    cn = dbmod.connect(host='myhost', database='mydb', user='me', password='mypw')
    cs = cn.cursor()
    query = "Select * from mytable where dtfield = ?"
    params = [dbmod.Date(2004, 7, 1)]
    cs.execute(query, params)
    result = cs.fetchall()
# all DBAPI2 exceptions from the driver are mapped to the corresponding dal 
# exceptions so it is possible to catch them in a generic way.
except ex.Error:
    pass



The following example shows how to solve timezone issues. These issues 
are in fact SQL server and driver specific and unfortunately hasn't been 
addressed in Python DBAPI spec. Below is the example for MySQLdb.

import pydal.dbapi as dal
dbmod = dal.wrapdriver('MySQLdb')

# Set local timezone. This gives side effect that all datetime.datetime 
# objects returned as a result from the query will contain this tzinfo.

dal.dbapi.dbtime.local_tzinfo = pytz.reference.Local

# The following line tells the dal that all datetime.datetime objects
# returned by driver and containing no tzinfo should be treated as
# of given timezone. This also assures that all datetime.datetime objects
# sent as a query parameters has been converted internally to this 
# timezone before sending to driver.

dal.dbtime.server_tzinfo = pytz.timezone('UTC') 

dbmod.dtmod = 'py' # Python datetime module.
dbmod.paramstyle = 'qmark'

cn = dbmod.connect(host='myhost', db='mydb', user='me', passwd='mypw')
cs = cn.cursor()

# Make sure that MySQL server will send and receive dates in UTC.

cs.execute("SET time_zone='+00:00'")

# Prepare query parameter.
# Please note that param object has tzinfo set. This is absolutely 
# necessary for internal timezone conversions to be correct.

param = datetime.datetime.now(pytz.timezone('America/Vancouver')) 

cs.execute("SELECT TIMESTAMP(?)", [ p ])

# The following line will print the correct current time in your current 
# local timezone.

print cs.fetchone()[0]

"""

__revision__ = 0.1

import pydal.dbtime as dbtime
import pydal.dbexceptions as dbexceptions
import pydal.paramstyles as paramstyles

class MWrapper(object):
    """Wraps DBAPI2 driver."""
    def __init__(self, driver, drivername):
        object.__init__(self)
        self._driver = driver
        self._drivername = drivername
        # Remove backslash if it exists from paramstyle config.
        if '\\' in paramstyles.ESCAPE_CHARS:
            paramstyles.ESCAPE_CHARS.remove('\\')
        # Although DB API 2.0 says nothing about BOOLEAN type it is a good
        # idea to convert booleans in the DB result to native Python bool
        # type. The Python bool cannot be subclassed so it is impossible
        # to find out for example if the PgBoolean or similar class is in
        # fact a boolean value. This feature is required for example by 
        # XML-RPC and JSON marshallers.
        self._convert_bool = False
        if hasattr(driver, 'BOOLEAN'):
            if driver.BOOLEAN != bool:
                self._convert_bool = True
        # Check for driver specific configuration.
        try:
            self._config = __import__('config_' + drivername, globals())
            # Run init1 in config.
            if hasattr(self._config, 'init1'):
                self._config.init1(self)
            # Set up escape and quote characters.
            if hasattr(self._config, 'escape_chars'):
                paramstyles.ESCAPE_CHARS.extend(self._config.escape_chars)
            if hasattr(self._config, 'quote_chars'):
                paramstyles.QUOTE_CHARS.extend(self._config.quote_chars)
        except ImportError:
            self._config = False
        self.__use_db_row = False # default
        # Set up module attributes.
        self.apilevel = '2.0'
        # This will change later.  It will pass thru driver's threadsafety
        # level.
        self.threadsafety = 0
        # May be changed dynamically.  Default is qmark.
        self.paramstyle = 'qmark'
        # This is the datetime types used.
        # Possible values are py, mx, native.
        # Zope types are to be added.
        self.__dtmod = 'py'
        # These use the native driver's types.
        self.DATETIME = self._driver.DATETIME
        self.STRING = self._driver.STRING
        self.BINARY = self._driver.BINARY
        self.NUMBER = self._driver.NUMBER
        self.ROWID = self._driver.ROWID
        dbexceptions._setExceptions(self)
        # Run init2 in config.
        if hasattr(self._config, 'init2'):
            self._config.init2(self)

    def __getDtMod(self):
        return self.__dtmod

    def __setDtMod(self, dtmodname):
        assert dtmodname in ('py', 'mx', 'native')
        if dtmodname == 'py':
            if not dbtime.have_datetime:
                raise Exception, 'datetime module not available.'
        elif dtmodname == 'mx':
            if not dbtime.have_mxDateTime:
                raise Exception, 'mx.DateTime module not available.'
        self.__dtmod = dtmodname

    dtmod = property(__getDtMod, __setDtMod)

    def __getUseDbRow(self):
        return self.__use_db_row

    def __setUseDbRow(self, use_db_row):
        if use_db_row:
            import db_row
            globals()['db_row'] = db_row
        self.__use_db_row = use_db_row

    use_db_row = property(__getUseDbRow, __setUseDbRow)

    # All date constructors must be consistent with the date type we have 
    # chosen.
    def Date(self, year, month, day):
        if self.dtmod == 'native':
            result = self._driver.Date(year, month, day)
        else:
            result = dbtime.construct_date(self.dtmod, year, month, day)
        return result

    def Time(self, hour, minute, second):
        if self.dtmod == 'native':
            result = self._driver.Time(hour, minute, second)
        else:
            result = dbtime.construct_time(self.dtmod, hour, minute, second)
        return result

    def Timestamp(self, year, month, day, hour, minute, second):
        if self.dtmod == 'native':
            result = self._driver.Timestamp(year, month, day, hour, minute,
                                            second)
        else:
            result = dbtime.construct_timestamp(self.dtmod, year, month, day,
                                                hour, minute, second)
        return result

    def DateFromTicks(self, ticks):
        if self.dtmod == 'native':
            result = self._driver.DateFromTicks(ticks)
        else:
            result = dbtime.construct_datefromticks(self.dtmod, ticks)
        return result

    def TimeFromTicks(self, ticks):
        if self.dtmod == 'native':
            result = self._driver.TimeFromTicks(ticks)
        else:
            result = dbtime.construct_timefromticks(self.dtmod, ticks)
        return result

    def TimestampFromTicks(self, ticks):
        if self.dtmod == 'native':
            result = self._driver.TimestampFromTicks(ticks)
        else:
            result = dbtime.construct_timestampfromticks(self.dtmod, ticks)
        return result

    def Binary(self, string):
        return self._driver.Binary(string)

    def connect(self, *args, **kwargs):
        """Return connection object."""
        return Connection(self, *args, **kwargs)

class Connection(object):
    """Wrapper for connection object."""
    def __init__(self, mwrapper, *args, **kwargs):
        object.__init__(self)
        self._mwrapper = mwrapper
        self._native_cn = mwrapper._driver.connect(*args, **kwargs)

    def close(self):
        return self._native_cn.close()

    def commit(self):
        return self._native_cn.commit()

    def rollback(self):
        return self._native_cn.rollback()

    def cursor(self):
        """Return a wrapped cursor."""
        return Cursor(self, self._native_cn)

class Cursor(object):
    """Wrapper for cursor object."""
    def __init__(self, wrapper_cn, native_cn):
        object.__init__(self)
        self._wrapper_cn = wrapper_cn
        self._mwrapper = wrapper_cn._mwrapper
        self._driver = self._mwrapper._driver
        self._drivername = self._mwrapper._drivername
        self._native_cs = native_cn.cursor()
        # arraysize should initialize at 1
        self._native_cs.arraysize = 1
        self._siface = False # This will probably go away.
        self._datetimeo = True # This will also go away.
        self.dtmod = self._mwrapper.dtmod # Takes defualt from wrapper.
        self.__use_db_row = self._mwrapper.use_db_row
        self.__paramstyle = self._mwrapper.paramstyle

    def __getDbRow(self):
        """Return value of use_db_row for cursor."""
        return self.__use_db_row

    def __setDbRow(self, use_db_row):
        """Set value of use_db_row for cursor."""
        if use_db_row:
            import db_row
            globals()['db_row'] = db_row
        self.__use_db_row = use_db_row

    use_db_row = property(__getDbRow, __setDbRow)

    def __getParamstyle(self):
        """Return value of paramstyle for cursor."""
        return self.__paramstyle

    def __setParamstyle(self, paramstyle):
        """Set value of paramstyle for cursor."""
        self.__paramstyle = paramstyle

    paramstyle = property(__getParamstyle, __setParamstyle)


    def __getDescription(self):
        return self._native_cs.description

    description = property(__getDescription)

    def __getRowCount(self):
        return self._native_cs.rowcount

    rowcount = property(__getRowCount)

    def __getArraySize(self):
        return self._native_cs.arraysize

    def __setArraySize(self, new_array_size):
        self._native_cs.arraysize = new_array_size

    arraysize = property(__getArraySize, __setArraySize)

    def setinputsizes(self, sizes):
        """Do Nothing"""
        pass

    def setoutputsize(self, size, column=None):
        """Do Nothing"""
        pass

    def execute(self, query, params=None):
        if params == None:
            return self._native_cs.execute(query)
        else:
            newquery, newparams = self.__formatQueryParams(query, params)
            return self._native_cs.execute(newquery, newparams)

    def executemany(self, query, params=None):
        # very inefficient
        if params == None:
            return self._native_cs.executemany(query)
        else:
            newparams = []
            for pset in params:
                newquery, newpset = self.__formatQueryParams(query, pset)
                newparams.append(newpset)
            return self._native_cs.executemany(newquery, newparams)

    def fetchone(self):
        """Like DBAPI2."""
        native_cs = self._native_cs
        result = native_cs.fetchone()
        # Do not format None.
        # Do not format if formatting not required.
        if result != None and self.__doFormatResults():
            new_result = self.__formatResults([result])[0]
        elif result != None:
            new_result = result
        else:
            new_result = None
        return new_result

    def fetchmany(self, size=None):
        """Like DBAPI2."""
        native_cs = self._native_cs
        if size == None:
            size = self._native_cs.arraysize
        results = native_cs.fetchmany(size)
        # Do not format None.
        # Do not format if formatting not required.
        if results != [] and self.__doFormatResults():
            new_results = self.__formatResults(results)
        elif results != []:
            new_results = results
        else:
            new_results = []
        return new_results

    def close(self):
        return self._native_cs.close()

    def fetchall(self):
        """Like DBAPI2."""
        native_cs = self._native_cs
        results = native_cs.fetchall()
        # Do not format None.
        # Do not format if formatting not required.
        if results != None and self.__doFormatResults():
            new_results = self.__formatResults(results)
        elif results != None:
            new_results = results
        else:
            new_results = None
        return new_results

    def __doFormatResults(self):
        """Check to see if there is reason to format results."""
        native_dt = self._mwrapper.dtmod == 'native'
        use_db_row = self.use_db_row
        if (native_dt) and (not use_db_row):
            return False
        else:
            return True

    def __formatResults(self, results):
        """Format result set before returning."""
        if type(results) == tuple:
            results = list(results)
        desc = self._native_cs.description
        typelist = [descitem[1] for descitem in desc]
        # initialize metarow
        if self.use_db_row:
            metarow = db_row.IMetaRow(desc)
        # Do we have a custom datetime conversion function?
        if hasattr(self._mwrapper._config, 'convertdt'):
            cdtfunc = self._mwrapper._config.convertdt
        else:
            cdtfunc = None
        # check for date types in description
        datepos = []
        boolpos = []
        if self._datetimeo:
            for i in range(len(typelist)):
                if typelist[i] == self._driver.DATETIME:
                    datepos.append(i)
                elif self._mwrapper._convert_bool and typelist[i] == self._driver.BOOLEAN:
                    boolpos.append(i)
        # loop through data to make changes
        for i in xrange(len(results)):
            set = results[i]
            # make datetime objects
            if len(datepos) > 0 or len(boolpos) > 0:
                newrow = list(set) # b/c tuple is immutable
                for rownum in datepos:
                    ##dto = newrow[rownum] # datetime object
                    dt_type = typelist[rownum]
                    ##driver = self._driver # driver
                    inputdt = newrow[rownum]
                    dtpref = self._mwrapper.dtmod
                    # don't change date if set to native
                    if self._mwrapper.dtmod != 'native':
                        newrow[rownum] = dbtime.native2pref(inputdt, dtpref,
                                                            dt_type, cdtfunc)
                    ##newrow[rownum] = self.__mkdatetime(newrow[rownum])
                for rownum in boolpos:
                    if newrow[rownum] == None:
                        continue
                    if newrow[rownum]:
                        newrow[rownum] = True
                    else:
                        newrow[rownum] = False
                set = tuple(newrow) # back to a tuple
            # db_row magic
            if self.use_db_row:
                results[i] = metarow(set)
            else:
                results[i] = set
        return results

    def __formatQueryParams(self, query, params):
        # transform datetime args to native module objects
        params = dbtime.dtsubnative(self._mwrapper.dtmod, self._driver, params)
        pstyle1 = self.paramstyle
        pstyle2 = self._driver.paramstyle
        ##print pstyle1, pstyle2
        return paramstyles.convert(pstyle1, pstyle2, query, params)

# public module functions ****************************************

def connect(*args, **kwargs):
    """Return Connection wrapper object."""
    return Connection(*args, **kwargs)

def wrapdriver(driver_name, driver_alias=None):
    """Wrap native driver."""
    if driver_alias == None:
        driver_alias = driver_name
    try:
        driver = __import__(driver_name, fromlist=[ '' ])
    except ImportError:
        raise
    # create the MWrapper instance
    mwrapper = MWrapper(driver, driver_alias)
    return mwrapper