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