Package logilab-common-0 ::
Package 36 ::
Package 1 ::
Module db
|
|
1 """Wrappers to get actually replaceable DBAPI2 compliant modules and
2 database connection whatever the database and client lib used.
3
4 Currently support:
5
6 - postgresql (pgdb, psycopg, psycopg2, pyPgSQL)
7 - mysql (MySQLdb)
8 - sqlite (pysqlite2, sqlite, sqlite3)
9
10 just use the `get_connection` function from this module to get a
11 wrapped connection. If multiple drivers for a database are available,
12 you can control which one you want to use using the
13 `set_prefered_driver` function.
14
15 Additional helpers are also provided for advanced functionalities such
16 as listing existing users or databases, creating database... Get the
17 helper for your database using the `get_adv_func_helper` function.
18
19 :copyright: 2002-2008 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
20 :contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr
21 :license: General Public License version 2 - http://www.gnu.org/licenses
22 """
23 __docformat__ = "restructuredtext en"
24
25 import sys
26 import re
27 from warnings import warn
28
29 from logilab.common.deprecation import obsolete
30 try:
31 from mx.DateTime import DateTimeType, DateTimeDeltaType, strptime
32 HAS_MX_DATETIME = True
33 except:
34 HAS_MX_DATETIME = False
35
36 __all__ = ['get_dbapi_compliant_module',
37 'get_connection', 'set_prefered_driver',
38 'PyConnection', 'PyCursor',
39 'UnknownDriver', 'NoAdapterFound',
40 ]
41
43 """raised when a unknown driver is given to get connexion"""
44
46 """Raised when no Adpater to DBAPI was found"""
47 - def __init__(self, obj, objname=None, protocol='DBAPI'):
48 if objname is None:
49 objname = obj.__name__
50 Exception.__init__(self, "Could not adapt %s to protocol %s" %
51 (objname, protocol))
52 self.adapted_obj = obj
53 self.objname = objname
54 self._protocol = protocol
55
56
58 """Imports the first module found in 'drivers' for 'driver'
59
60 :rtype: tuple
61 :returns: the tuple module_object, module_name where module_object
62 is the dbapi module, and modname the module's name
63 """
64 if not driver in drivers:
65 raise UnknownDriver(driver)
66 imported_elements = imported_elements or []
67 for modname in drivers[driver]:
68 try:
69 if not quiet:
70 print >> sys.stderr, 'Trying %s' % modname
71 module = __import__(modname, globals(), locals(), imported_elements)
72 break
73 except ImportError:
74 if not quiet:
75 print >> sys.stderr, '%s is not available' % modname
76 continue
77 else:
78 raise ImportError('Unable to import a %s module' % driver)
79 if not imported_elements:
80 for part in modname.split('.')[1:]:
81 module = getattr(module, part)
82 return module, modname
83
84
85
86
88 """A simple connection wrapper in python to decorated C-level connections
89 with additional attributes
90 """
92 """Wraps the original connection object"""
93 self._cnx = cnx
94
95
97 """Wraps cursor()"""
98 return self._cnx.cursor()
99
101 """Wraps commit()"""
102 return self._cnx.commit()
103
105 """Wraps rollback()"""
106 return self._cnx.rollback()
107
109 """Wraps close()"""
110 return self._cnx.close()
111
113 return getattr(self._cnx, attrname)
114
116 """A simple connection wrapper in python, generating wrapper for cursors as
117 well (useful for profiling)
118 """
120 """Wraps the original connection object"""
121 self._cnx = cnx
122
126
127
128
130 """A simple cursor wrapper in python (useful for profiling)"""
133
135 """Wraps close()"""
136 return self._cursor.close()
137
138 - def execute(self, *args, **kwargs):
139 """Wraps execute()"""
140 return self._cursor.execute(*args, **kwargs)
141
143 """Wraps executemany()"""
144 return self._cursor.executemany(*args, **kwargs)
145
147 """Wraps fetchone()"""
148 return self._cursor.fetchone(*args, **kwargs)
149
151 """Wraps execute()"""
152 return self._cursor.fetchmany(*args, **kwargs)
153
155 """Wraps fetchall()"""
156 return self._cursor.fetchall(*args, **kwargs)
157
159 return getattr(self._cursor, attrname)
160
161
162
163
165 """Base class for all DBAPI adpaters"""
166 UNKNOWN = None
167
168 - def __init__(self, native_module, pywrap=False):
169 """
170 :type native_module: module
171 :param native_module: the database's driver adapted module
172 """
173 self._native_module = native_module
174 self._pywrap = pywrap
175
176
177 for typecode in ('STRING', 'BOOLEAN', 'BINARY', 'DATETIME', 'NUMBER',
178 'UNKNOWN'):
179 try:
180 setattr(self, typecode, getattr(self, typecode))
181 except AttributeError:
182 print 'WARNING: %s adapter has no %s type code' % (self, typecode)
183
184 - def connect(self, host='', database='', user='', password='', port=''):
185 """Wraps the native module connect method"""
186 kwargs = {'host' : host, 'port' : port, 'database' : database,
187 'user' : user, 'password' : password}
188 cnx = self._native_module.connect(**kwargs)
189 return self._wrap_if_needed(cnx, user)
190
192 """Wraps the connection object if self._pywrap is True, and returns it
193 If false, returns the original cnx object
194 """
195 if self._pywrap:
196 cnx = PyConnection(cnx)
197 try:
198 cnx.logged_user = user
199 except AttributeError:
200
201 cnx = SimpleConnectionWrapper(cnx)
202 cnx.logged_user = user
203 return cnx
204
206 return getattr(self._native_module, attrname)
207
208 - def process_value(self, value, description, encoding='utf-8', binarywrap=None):
209
210 typecode = description[1]
211 assert typecode is not None, self
212 if typecode == self.STRING:
213 if isinstance(value, str):
214 return unicode(value, encoding, 'replace')
215 elif typecode == self.BOOLEAN:
216 return bool(value)
217 elif typecode == self.BINARY and not binarywrap is None:
218 return binarywrap(value)
219 elif typecode == self.UNKNOWN:
220
221
222 if isinstance(value, str):
223 return unicode(value, encoding, 'replace')
224
225
226
227
228
229
230
231
232 return value
233
234
235
236
238 """Simple PGDB Adapter to DBAPI (pgdb modules lacks Binary() and NUMBER)
239 """
240 - def __init__(self, native_module, pywrap=False):
241 DBAPIAdapter.__init__(self, native_module, pywrap)
242 self.NUMBER = native_module.pgdbType('int2', 'int4', 'serial',
243 'int8', 'float4', 'float8',
244 'numeric', 'bool', 'money')
245
246 - def connect(self, host='', database='', user='', password='', port=''):
247 """Wraps the native module connect method"""
248 if port:
249 warn("pgdb doesn't support 'port' parameter in connect()", UserWarning)
250 kwargs = {'host' : host, 'database' : database,
251 'user' : user, 'password' : password}
252 cnx = self._native_module.connect(**kwargs)
253 return self._wrap_if_needed(cnx, user)
254
255
257 """Simple Psycopg Adapter to DBAPI (cnx_string differs from classical ones)
258 """
259 - def connect(self, host='', database='', user='', password='', port=''):
260 """Handles psycopg connexion format"""
261 if host:
262 cnx_string = 'host=%s dbname=%s user=%s' % (host, database, user)
263 else:
264 cnx_string = 'dbname=%s user=%s' % (database, user)
265 if port:
266 cnx_string += ' port=%s' % port
267 if password:
268 cnx_string = '%s password=%s' % (cnx_string, password)
269 cnx = self._native_module.connect(cnx_string)
270 cnx.set_isolation_level(1)
271 return self._wrap_if_needed(cnx, user)
272
273
275 """Simple Psycopg2 Adapter to DBAPI (cnx_string differs from classical ones)
276 """
277
278
279 UNKNOWN = 705
280
281 - def __init__(self, native_module, pywrap=False):
282 from psycopg2 import extensions
283 self.BOOLEAN = extensions.BOOLEAN
284 DBAPIAdapter.__init__(self, native_module, pywrap)
285 self._init_psycopg2()
286
288 """initialize psycopg2 to use mx.DateTime for date and timestamps
289 instead for datetime.datetime"""
290 psycopg2 = self._native_module
291 if hasattr(psycopg2, '_lc_initialized'):
292 return
293 psycopg2._lc_initialized = 1
294
295 if HAS_MX_DATETIME:
296 from psycopg2 import extensions
297 extensions.register_type(psycopg2._psycopg.MXDATETIME)
298 extensions.register_type(psycopg2._psycopg.MXINTERVAL)
299 extensions.register_type(psycopg2._psycopg.MXDATE)
300 extensions.register_type(psycopg2._psycopg.MXTIME)
301
302
303
304
305
306
307
308
309
310
311
312
314 """Simple pyPgSQL Adapter to DBAPI
315 """
316 - def connect(self, host='', database='', user='', password='', port=''):
317 """Handles psycopg connexion format"""
318 kwargs = {'host' : host, 'port': port or None,
319 'database' : database,
320 'user' : user, 'password' : password or None}
321 cnx = self._native_module.connect(**kwargs)
322 return self._wrap_if_needed(cnx, user)
323
324
326 """Emulates the Binary (cf. DB-API) function"""
327 return str
328
330
331 return getattr(self._native_module, attrname)
332
333
334
335
337 """Simple pysqlite2 Adapter to DBAPI
338 """
339
340 BINARY = 'XXX'
341 STRING = 'XXX'
342 DATETIME = 'XXX'
343 NUMBER = 'XXX'
344 BOOLEAN = 'XXX'
345
346 - def __init__(self, native_module, pywrap=False):
347 DBAPIAdapter.__init__(self, native_module, pywrap)
348 self._init_pysqlite2()
349
351 """initialize pysqlite2 to use mx.DateTime for date and timestamps"""
352 sqlite = self._native_module
353 if hasattr(sqlite, '_lc_initialized'):
354 return
355 sqlite._lc_initialized = 1
356
357
358 from StringIO import StringIO
359 def adapt_bytea(data):
360 return data.getvalue()
361 sqlite.register_adapter(StringIO, adapt_bytea)
362 def convert_bytea(data):
363 return StringIO(data)
364 sqlite.register_converter('bytea', convert_bytea)
365
366
367 def convert_boolean(ustr):
368 if ustr.upper() in ('F', 'FALSE'):
369 return False
370 return True
371 sqlite.register_converter('boolean', convert_boolean)
372 def adapt_boolean(bval):
373 return str(bval).upper()
374 sqlite.register_adapter(bool, adapt_boolean)
375
376
377
378 from decimal import Decimal
379 def adapt_decimal(data):
380 return str(data)
381 sqlite.register_adapter(Decimal,adapt_decimal)
382
383 def convert_decimal(data):
384 return Decimal(data)
385 sqlite.register_converter('decimal',convert_decimal)
386
387
388 if HAS_MX_DATETIME:
389 def adapt_mxdatetime(mxd):
390 return mxd.strftime('%Y-%m-%d %H:%M:%S')
391 sqlite.register_adapter(DateTimeType, adapt_mxdatetime)
392 def adapt_mxdatetimedelta(mxd):
393 return mxd.strftime('%H:%M:%S')
394 sqlite.register_adapter(DateTimeDeltaType, adapt_mxdatetimedelta)
395
396 def convert_mxdate(ustr):
397 return strptime(ustr, '%Y-%m-%d %H:%M:%S')
398 sqlite.register_converter('date', convert_mxdate)
399 def convert_mxdatetime(ustr):
400 return strptime(ustr, '%Y-%m-%d %H:%M:%S')
401 sqlite.register_converter('timestamp', convert_mxdatetime)
402 def convert_mxtime(ustr):
403 try:
404 return strptime(ustr, '%H:%M:%S')
405 except:
406
407 return strptime(ustr, '%Y-%m-%d %H:%M:%S')
408 sqlite.register_converter('time', convert_mxtime)
409
410
411
412 - def connect(self, host='', database='', user='', password='', port=None):
413 """Handles sqlite connexion format"""
414 sqlite = self._native_module
415
416 class PySqlite2Cursor(sqlite.Cursor):
417 """cursor adapting usual dict format to pysqlite named format
418 in SQL queries
419 """
420 def _replace_parameters(self, sql, kwargs):
421 if isinstance(kwargs, dict):
422 return re.sub(r'%\(([^\)]+)\)s', r':\1', sql)
423
424 return re.sub(r'%s', r'?', sql)
425
426 def execute(self, sql, kwargs=None):
427 if kwargs is None:
428 self.__class__.__bases__[0].execute(self, sql)
429 else:
430 final_sql = self._replace_parameters(sql, kwargs)
431 self.__class__.__bases__[0].execute(self, final_sql , kwargs)
432
433 def executemany(self, sql, kwargss):
434 if not isinstance(kwargss, (list, tuple)):
435 kwargss = tuple(kwargss)
436 self.__class__.__bases__[0].executemany(self, self._replace_parameters(sql, kwargss[0]), kwargss)
437
438 class PySqlite2CnxWrapper:
439 def __init__(self, cnx):
440 self._cnx = cnx
441
442 def cursor(self):
443 return self._cnx.cursor(PySqlite2Cursor)
444 def __getattr__(self, attrname):
445 return getattr(self._cnx, attrname)
446 cnx = sqlite.connect(database, detect_types=sqlite.PARSE_DECLTYPES)
447 return self._wrap_if_needed(PySqlite2CnxWrapper(cnx), user)
448
449 - def process_value(self, value, description, encoding='utf-8', binarywrap=None):
450 if not binarywrap is None and isinstance(value, self._native_module.Binary):
451 return binarywrap(value)
452 return value
453
454
456 """Simple sqlite Adapter to DBAPI
457 """
458 - def __init__(self, native_module, pywrap=False):
459 DBAPIAdapter.__init__(self, native_module, pywrap)
460 self.DATETIME = native_module.TIMESTAMP
461
462 - def connect(self, host='', database='', user='', password='', port=''):
463 """Handles sqlite connexion format"""
464 cnx = self._native_module.connect(database)
465 return self._wrap_if_needed(cnx, user)
466
467
468
469
471 """Simple mysql Adapter to DBAPI
472 """
473 BOOLEAN = 'XXX'
474
475 - def __init__(self, native_module, pywrap=False):
476 DBAPIAdapter.__init__(self, native_module, pywrap)
477 self._init_module()
478
480 """initialize mysqldb to use mx.DateTime for date and timestamps"""
481 natmod = self._native_module
482 if hasattr(natmod, '_lc_initialized'):
483 return
484 natmod._lc_initialized = 1
485
486 if HAS_MX_DATETIME:
487 from MySQLdb import times
488 from mx import DateTime as mxdt
489 times.Date = times.date = mxdt.Date
490 times.Time = times.time = mxdt.Time
491 times.Timestamp = times.datetime = mxdt.DateTime
492 times.TimeDelta = times.timedelta = mxdt.TimeDelta
493 times.DateTimeType = mxdt.DateTimeType
494 times.DateTimeDeltaType = mxdt.DateTimeDeltaType
495
496 - def connect(self, host='', database='', user='', password='', port=None,
497 unicode=True, charset='utf8'):
498 """Handles mysqldb connexion format
499 the unicode named argument asks to use Unicode objects for strings
500 in result sets and query parameters
501 """
502 kwargs = {'host' : host or '', 'db' : database,
503 'user' : user, 'passwd' : password,
504 'use_unicode' : unicode}
505
506 if port:
507 kwargs['port'] = int(port)
508 cnx = self._native_module.connect(**kwargs)
509 if unicode:
510 if charset.lower() == 'utf-8':
511 charset = 'utf8'
512 cnx.set_character_set(charset)
513 return self._wrap_if_needed(cnx, user)
514
515 - def process_value(self, value, description, encoding='utf-8', binarywrap=None):
516 typecode = description[1]
517
518
519 if typecode == self.BINARY:
520 if hasattr(value, 'tostring'):
521 value = value.tostring()
522 maxsize = description[3]
523
524
525
526
527 if maxsize in (16777215, 50331645):
528 if isinstance(value, str):
529 return unicode(value, encoding)
530 return value
531
532
533 if binarywrap is None:
534 return value
535 return binarywrap(value)
536 return DBAPIAdapter.process_value(self, value, description, encoding, binarywrap)
537
539 print '*'*80
540 print 'module type codes'
541 for typename in ('STRING', 'BOOLEAN', 'BINARY', 'DATETIME', 'NUMBER'):
542 print typename, getattr(self, typename)
543 try:
544 cursor.execute("""CREATE TABLE _type_code_test(
545 varchar_field varchar(50),
546 text_field text unicode,
547 mtext_field mediumtext,
548 binary_field tinyblob,
549 blob_field blob,
550 lblob_field longblob
551 )""")
552 cursor.execute("INSERT INTO _type_code_test VALUES ('1','2','3','4', '5', '6')")
553 cursor.execute("SELECT * FROM _type_code_test")
554 descr = cursor.description
555 print 'db fields type codes'
556 for i, name in enumerate(('varchar', 'text', 'mediumtext',
557 'binary', 'blob', 'longblob')):
558 print name, descr[i]
559 finally:
560 cursor.execute("DROP TABLE _type_code_test")
561
562
563
564
565
566
567 PREFERED_DRIVERS = {
568 "postgres" : [ 'psycopg2', 'psycopg', 'pgdb', 'pyPgSQL.PgSQL', ],
569 "mysql" : [ 'MySQLdb', ],
570 "sqlite" : ['pysqlite2.dbapi2', 'sqlite', 'sqlite3',],
571 }
572
573 _ADAPTERS = {
574 'postgres' : { 'pgdb' : _PgdbAdapter,
575 'psycopg' : _PsycopgAdapter,
576 'psycopg2' : _Psycopg2Adapter,
577 'pyPgSQL.PgSQL' : _PgsqlAdapter,
578 },
579 'mysql' : { 'MySQLdb' : _MySqlDBAdapter, },
580 'sqlite' : { 'pysqlite2.dbapi2' : _PySqlite2Adapter,
581 'sqlite' : _SqliteAdapter,
582 'sqlite3' : _PySqlite2Adapter, },
583 }
584
585
586
588 """A simple dict that registers all adapters"""
590 """Registers 'adapter' in directory as adapting 'mod'"""
591 try:
592 driver_dict = self[driver]
593 except KeyError:
594 self[driver] = {}
595
596
597 driver_dict[modname] = adapter
598
599 - def adapt(self, database, prefered_drivers = None, pywrap = False):
600 """Returns an dbapi-compliant object based for database"""
601 prefered_drivers = prefered_drivers or PREFERED_DRIVERS
602 module, modname = _import_driver_module(database, prefered_drivers)
603 try:
604 return self[database][modname](module, pywrap=pywrap)
605 except KeyError:
606 raise NoAdapterFound(obj=module)
607
609 try:
610 return self[database][modname]
611 except KeyError:
612 raise NoAdapterFound(None, modname)
613
614 ADAPTER_DIRECTORY = _AdapterDirectory(_ADAPTERS)
615 del _AdapterDirectory
616
617
618
619
621 """sets the prefered driver module for database
622 database is the name of the db engine (postgresql, mysql...)
623 module is the name of the module providing the connect function
624 syntax is (params_func, post_process_func_or_None)
625 _drivers is a optionnal dictionnary of drivers
626 """
627 try:
628 modules = _drivers[database]
629 except KeyError:
630 raise UnknownDriver('Unknown database %s' % database)
631
632 try:
633 modules.remove(module)
634 except ValueError:
635 raise UnknownDriver('Unknown module %s for %s' % (module, database))
636 modules.insert(0, module)
637
640 """returns a fully dbapi compliant module"""
641 try:
642 mod = ADAPTER_DIRECTORY.adapt(driver, prefered_drivers, pywrap=pywrap)
643 except NoAdapterFound, err:
644 if not quiet:
645 msg = 'No Adapter found for %s, returning native module'
646 print >> sys.stderr, msg % err.objname
647 mod = err.adapted_obj
648 from logilab.common.adbh import get_adv_func_helper
649 mod.adv_func_helper = get_adv_func_helper(driver)
650 return mod
651
652 -def get_connection(driver='postgres', host='', database='', user='',
653 password='', port='', quiet=False, drivers=PREFERED_DRIVERS,
654 pywrap=False):
655 """return a db connexion according to given arguments"""
656 module, modname = _import_driver_module(driver, drivers, ['connect'])
657 try:
658 adapter = ADAPTER_DIRECTORY.get_adapter(driver, modname)
659 except NoAdapterFound, err:
660 if not quiet:
661 msg = 'No Adapter found for %s, using default one' % err.objname
662 print >> sys.stderr, msg
663 adapted_module = DBAPIAdapter(module, pywrap)
664 else:
665 adapted_module = adapter(module, pywrap)
666 if host and not port:
667 try:
668 host, port = host.split(':', 1)
669 except ValueError:
670 pass
671 if port:
672 port = int(port)
673 return adapted_module.connect(host, database, user, password, port=port)
674
675
676 from logilab.common.deprecation import moved
677 get_adv_func_helper = moved('logilab.common.adbh', 'get_adv_func_helper')
678