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