turbogears.database

1 """Convenient access to an SQLObject or SQLAlchemy managed database.""" 2 3 __all__ = ['AutoConnectHub', 'bind_metadata', 'create_session', 4 'create_session_mapper', 'commit_all', 'end_all', 5 'DatabaseError', 'DatabaseConfigurationError', 6 'EndTransactions', 'get_engine', 'get_metadata', 'mapper', 7 'metadata', 'PackageHub', 'rollback_all', 'session', 8 'session_mapper', 'set_db_uri', 'so_columns', 'so_joins', 'so_to_dict'] 9 10 import sys 11 import time 12 import logging 13 14 import cherrypy 15 from cherrypy import request 16 17 try: 18 import sqlalchemy 19 import sqlalchemy.orm 20 from sqlalchemy import MetaData 21 try: 22 from sqlalchemy.exc import ArgumentError, OperationalError 23 except ImportError: # SQLAlchemy < 0.5 24 from sqlalchemy.exceptions import ArgumentError, OperationalError 25 except ImportError: 26 sqlalchemy = None 27 28 try: 29 import sqlobject 30 from sqlobject.dbconnection import ConnectionHub, Transaction, TheURIOpener 31 from sqlobject.util.threadinglocal import local as threading_local 32 except ImportError: 33 sqlobject = None 34 35 from peak.rules import abstract, when, NoApplicableMethods 36 37 from turbogears import config 38 from turbogears.util import remove_keys 39 40 log = logging.getLogger('turbogears.database')

41 42 43 -class DatabaseError(Exception):

44 """TurboGears Database Error."""

45

46 47 -class DatabaseConfigurationError(DatabaseError):

48 """TurboGears Database Configuration Error."""

49 50 51 # Provide support for SQLAlchemy 52 if sqlalchemy:

53 54 - def get_engine(pkg=None):

55 """Retrieve the engine based on the current configuration.""" 56 bind_metadata() 57 return get_metadata(pkg).bind

58

59 - def get_metadata(pkg=None):

60 """Retrieve the metadata for the specified package.""" 61 try: 62 return _metadatas[pkg] 63 except KeyError: 64 _metadatas[pkg] = MetaData() 65 return _metadatas[pkg]

66

67 - def bind_metadata():

68 """Connect SQLAlchemy to the configured database(s).""" 69 if metadata.is_bound(): 70 return 71 72 alch_args = dict() 73 for k, v in config.items(): 74 if 'sqlalchemy' in k: 75 alch_args[k.split('.', 1)[-1]] = v 76 77 try: 78 dburi = alch_args.pop('dburi') 79 if not dburi: 80 raise KeyError 81 metadata.bind = sqlalchemy.create_engine(dburi, **alch_args) 82 except KeyError: 83 raise DatabaseConfigurationError( 84 "No sqlalchemy database configuration found!") 85 except ArgumentError, exc: 86 raise DatabaseConfigurationError(exc) 87 88 global _using_sa 89 _using_sa = True 90 91 for k, v in config.items(): 92 if '.dburi' in k and 'sqlalchemy.' not in k: 93 get_metadata(k.split('.', 1)[0] 94 ).bind = sqlalchemy.create_engine(v, **alch_args)

95

96 - def create_session():

97 """Create a session that uses the engine from thread-local metadata. 98 99 The session by default does not begin a transaction, and requires that 100 flush() be called explicitly in order to persist results to the database. 101 102 """ 103 if not metadata.is_bound(): 104 bind_metadata() 105 return sqlalchemy.orm.create_session()

106 107 session = sqlalchemy.orm.scoped_session(create_session) 108 109 if not hasattr(session, 'add'): # SQLAlchemy < 0.5 110 session.add = session.save_or_update

111 112 # Note: TurboGears used to set mapper = Session.mapper, but this has 113 # been deprecated in SQLAlchemy 0.5.5. If it is unavailable, we emulate 114 # the behaviour of the old session-aware mapper following this recipe 115 # from the SQLAlchemy wiki: 116 # 117 # http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SessionAwareMapper 118 # 119 # If you do not want to use the session-aware mapper, import 'mapper' 120 # directly from sqlalchemy.orm. See model.py in the default quickstart 121 # template for an example. 122 - def create_session_mapper(scoped_session=session):

123 def mapper(cls, *args, **kw): 124 set_kwargs_on_init = kw.pop('set_kwargs_on_init', True) 125 validate = kw.pop('validate', False) 126 # we accept 'save_on_init' as an alias for 'autoadd' for backward 127 # compatibility, but 'autoadd' is shorter and more to the point. 128 autoadd = kw.pop('autoadd', kw.pop('save_on_init', True)) 129 130 if set_kwargs_on_init and (getattr(cls, 131 '__init__', object.__init__) is object.__init__ 132 or getattr(cls.__init__, '_session_mapper', False)): 133 def __init__(self, **kwargs): 134 for key, value in kwargs.items(): 135 if validate: 136 if not hasattr(self, key): 137 raise TypeError( 138 "Invalid __init__ argument: '%s'" % key) 139 setattr(self, key, value) 140 if autoadd: 141 session.add(self)

142 __init__._session_mapper = True 143 cls.__init__ = __init__ 144 cls.query = scoped_session.query_property() 145 return sqlalchemy.orm.mapper(cls, *args, **kw) 146 return mapper 147 session_mapper = create_session_mapper() 148 if hasattr(session, 'mapper'): 149 # Old session-aware mapper 150 mapper = session.mapper 151 else: 152 mapper = session_mapper 153 154 _metadatas = {} 155 _metadatas[None] = MetaData() 156 metadata = _metadatas[None] 157 158 try: 159 import elixir 160 elixir.metadata, elixir.session = metadata, session 161 except ImportError: 162 pass 163 164 else:

165 - def get_engine():

166 pass

167 - def get_metadata():

168 pass

169 - def bind_metadata():

170 pass

171 - def create_session():

172 pass

173 session = metadata = mapper = None 174 175 bind_meta_data = bind_metadata # deprecated, for backward compatibility 176 177 hub_registry = set() 178 179 _hubs = dict() # stores the AutoConnectHubs used for each connection URI 180 181 # Provide support for SQLObject 182 if sqlobject:

183 - def _mysql_timestamp_converter(raw):

184 """Timestamp-converter for MySQL. 185 186 Convert a MySQL TIMESTAMP to a floating point number representing 187 the seconds since the Un*x Epoch. It uses custom code the input seems 188 to be the new (MySQL 4.1+) timestamp format, otherwise code from the 189 MySQLdb module is used. 190 191 """ 192 if raw[4] == '-': 193 return time.mktime(time.strptime(raw, '%Y-%m-%d %H:%M:%S')) 194 else: 195 import MySQLdb.converters 196 return MySQLdb.converters.mysql_timestamp_converter(raw)

197

198 199 - class AutoConnectHub(ConnectionHub):

200 """Connect to the database once per thread. 201 202 The AutoConnectHub also provides convenient methods for managing 203 transactions. 204 205 """ 206 uri = None 207 params = {} 208

209 - def __init__(self, uri=None, supports_transactions=True):

210 if not uri: 211 uri = config.get('sqlobject.dburi') 212 self.uri = uri 213 self.supports_transactions = supports_transactions 214 hub_registry.add(self) 215 ConnectionHub.__init__(self)

216

217 - def _is_interesting_version(self):

218 """Return True only if version of MySQLdb <= 1.0.""" 219 import MySQLdb 220 module_version = MySQLdb.version_info[0:2] 221 major = module_version[0] 222 minor = module_version[1] 223 # we can't use Decimal here because it is only available for Python 2.4 224 return major < 1 or (major == 1 and minor < 2)

225

226 - def _enable_timestamp_workaround(self, connection):

227 """Enable timestamp-workaround for MySQL. 228 229 Enable a workaround for an incompatible timestamp format change 230 in MySQL 4.1 when using an old version of MySQLdb. See trac ticket 231 #1235 - http://trac.turbogears.org/ticket/1235 for details. 232 233 """ 234 # precondition: connection is a MySQLConnection 235 import MySQLdb 236 import MySQLdb.converters 237 if self._is_interesting_version(): 238 conversions = MySQLdb.converters.conversions.copy() 239 conversions[MySQLdb.constants.FIELD_TYPE.TIMESTAMP] = \ 240 _mysql_timestamp_converter 241 # There is no method to use custom keywords when using 242 # "connectionForURI" in SQLObject so we have to insert the 243 # conversions afterwards. 244 connection.kw['conv'] = conversions

245

246 - def getConnection(self):

247 try: 248 conn = self.threadingLocal.connection 249 return self.begin(conn) 250 except AttributeError: 251 uri = self.uri 252 if uri: 253 conn = sqlobject.connectionForURI(uri) 254 # the following line effectively turns off the DBAPI connection 255 # cache. We're already holding on to a connection per thread, 256 # and the cache causes problems with SQLite. 257 if uri.startswith('sqlite'): 258 TheURIOpener.cachedURIs = {} 259 elif uri.startswith('mysql') and config.get('turbogears.' 260 'enable_mysql41_timestamp_workaround', False): 261 self._enable_timestamp_workaround(conn) 262 self.threadingLocal.connection = conn 263 return self.begin(conn) 264 raise AttributeError("No connection has been defined" 265 " for this thread or process")

266

267 - def reset(self):

268 """Used for testing purposes. 269 270 This drops all of the connections that are being held. 271 272 """ 273 self.threadingLocal = threading_local()

274

275 - def begin(self, conn=None):

276 """Start a transaction.""" 277 if not self.supports_transactions: 278 return conn 279 if not conn: 280 conn = self.getConnection() 281 if isinstance(conn, Transaction): 282 if conn._obsolete: 283 conn.begin() 284 return conn 285 self.threadingLocal.old_conn = conn 286 trans = conn.transaction() 287 self.threadingLocal.connection = trans 288 return trans

289

290 - def commit(self):

291 """Commit the current transaction.""" 292 if not self.supports_transactions: 293 return 294 try: 295 conn = self.threadingLocal.connection 296 except AttributeError: 297 return 298 if isinstance(conn, Transaction): 299 self.threadingLocal.connection.commit()

300

301 - def rollback(self):

302 """Rollback the current transaction.""" 303 if not self.supports_transactions: 304 return 305 try: 306 conn = self.threadingLocal.connection 307 except AttributeError: 308 return 309 if isinstance(conn, Transaction) and not conn._obsolete: 310 self.threadingLocal.connection.rollback()

311

312 - def end(self):

313 """End the transaction, returning to a standard connection.""" 314 if not self.supports_transactions: 315 return 316 try: 317 conn = self.threadingLocal.connection 318 except AttributeError: 319 return 320 if not isinstance(conn, Transaction): 321 return 322 if not conn._obsolete: 323 conn.rollback() 324 self.threadingLocal.connection = self.threadingLocal.old_conn 325 del self.threadingLocal.old_conn 326 self.threadingLocal.connection.expireAll()

327

328 - class PackageHub(object):

329 """A package specific database hub. 330 331 Transparently proxies to an AutoConnectHub for the URI 332 that is appropriate for this package. A package URI is 333 configured via "packagename.dburi" in the TurboGears config 334 settings. If there is no package DB URI configured, the 335 default (provided by "sqlobject.dburi") is used. 336 337 The hub is not instantiated until an attempt is made to 338 use the database. 339 340 """

341 - def __init__(self, packagename):

342 self.packagename = packagename 343 self.hub = None

344

345 - def __get__(self, obj, type):

346 if self.hub: 347 return self.hub.__get__(obj, type) 348 else: 349 return self

350

351 - def __set__(self, obj, type):

352 if not self.hub: 353 self.set_hub() 354 return self.hub.__set__(obj, type)

355

356 - def __getattr__(self, name):

357 if not self.hub: 358 self.set_hub() 359 try: 360 return getattr(self.hub, name) 361 except AttributeError: 362 return getattr(self.hub.getConnection(), name)

363

364 - def set_hub(self):

365 dburi = config.get('%s.dburi' % self.packagename, None) 366 if not dburi: 367 dburi = config.get('sqlobject.dburi', None) 368 if not dburi: 369 raise DatabaseConfigurationError( 370 "No sqlobject database configuration found!") 371 if dburi.startswith('notrans_'): 372 dburi = dburi[8:] 373 trans = False 374 else: 375 trans = True 376 hub = _hubs.get(dburi, None) 377 if not hub: 378 hub = AutoConnectHub(dburi, supports_transactions=trans) 379 _hubs[dburi] = hub 380 self.hub = hub

381 else:

382 - class AutoConnectHub(object):

383 pass

384

385 - class PackageHub(object):

386 pass

387

388 389 -def set_db_uri(dburi, package=None):

390 """Set the database URI. 391 392 Sets the database URI to use either globally or for a specific package. 393 Note that once the database is accessed, calling it will have no effect. 394 395 @param dburi: database URI to use 396 @param package: package name this applies to, or None to set the default. 397 398 """ 399 if package: 400 config.update({'%s.dburi' % package: dburi}) 401 else: 402 config.update({'sqlobject.dburi': dburi})

403

404 405 -def commit_all():

406 """Commit the transactions in all registered hubs (for this thread).""" 407 for hub in hub_registry: 408 hub.commit()

409

410 411 -def rollback_all():

412 """Rollback the transactions in all registered hubs (for this thread).""" 413 for hub in hub_registry: 414 hub.rollback()

415

416 417 -def end_all():

418 """End the transactions in all registered hubs (for this thread).""" 419 for hub in hub_registry: 420 hub.end()

421

422 423 @abstract() 424 -def run_with_transaction(func, *args, **kw):

425 pass

426

427 428 @abstract() 429 -def restart_transaction(args):

430 pass

431 432 433 _using_sa = False

434 435 -def _use_sa(args=None):

436 return _using_sa

437

438 439 # include "args" to avoid call being pre-cached 440 @when(run_with_transaction, "not _use_sa(args)") 441 -def so_rwt(func, *args, **kw):

442 log.debug("Starting SQLObject transaction") 443 try: 444 try: 445 retval = func(*args, **kw) 446 commit_all() 447 return retval 448 except cherrypy.HTTPRedirect: 449 commit_all() 450 raise 451 except cherrypy.InternalRedirect: 452 commit_all() 453 raise 454 except: 455 # No need to "rollback" the sqlalchemy unit of work, 456 # because nothing has hit the db yet. 457 rollback_all() 458 raise 459 finally: 460 end_all()

461

462 463 # include "args" to avoid call being pre-cached 464 @when(restart_transaction, "not _use_sa(args)") 465 -def so_restart_transaction(args):

466 # log.debug("ReStarting SQLObject transaction") 467 # Disable for now for compatibility 468 pass

469

470 471 -def dispatch_exception(exception, args, kw):

472 # errorhandling import here to avoid circular imports 473 from turbogears.errorhandling import dispatch_error 474 # Keep in mind func is not the real func but _expose 475 real_func, accept, allow_json, controller = args[:4] 476 args = args[4:] 477 exc_type, exc_value, exc_trace = sys.exc_info() 478 remove_keys(kw, ('tg_source', 'tg_errors', 'tg_exceptions')) 479 try: 480 output = dispatch_error( 481 controller, real_func, None, exception, *args, **kw) 482 except NoApplicableMethods: 483 raise exc_type, exc_value, exc_trace 484 else: 485 del exc_trace 486 return output

487

488 489 # include "args" to avoid call being pre-cached 490 @when(run_with_transaction, "_use_sa(args)") 491 -def sa_rwt(func, *args, **kw):

492 log.debug("Starting SA transaction") 493 request.sa_transaction = session.begin() 494 try: 495 try: 496 retval = func(*args, **kw) 497 except (cherrypy.HTTPRedirect, cherrypy.InternalRedirect): 498 # If a redirect happens, commit and proceed with redirect. 499 if sa_transaction_active(): 500 log.debug('Redirect in active transaction - will commit now') 501 session.commit() 502 else: 503 log.debug('Redirect in inactive transaction') 504 raise 505 except: 506 # If any other exception happens, rollback and re-raise error 507 if sa_transaction_active(): 508 log.debug('Error in active transaction - will rollback now') 509 session.rollback() 510 else: 511 log.debug('Error in inactive transaction') 512 raise 513 # If the call was successful, commit and proceed 514 if sa_transaction_active(): 515 log.debug('Transaction is still active - will commit now') 516 session.commit() 517 else: 518 log.debug('Transaction is already inactive') 519 finally: 520 log.debug('Ending SA transaction') 521 session.close() 522 return retval

523

524 525 # include "args" to avoid call being pre-cached 526 @when(restart_transaction, "_use_sa(args)") 527 -def sa_restart_transaction(args):

528 log.debug("Restarting SA transaction") 529 if sa_transaction_active(): 530 log.debug('Transaction is still active - will rollback now') 531 session.rollback() 532 else: 533 log.debug('Transaction is already inactive') 534 session.close() 535 request.sa_transaction = session.begin()

536

537 538 -def sa_transaction_active():

539 """Check whether SA transaction is still active.""" 540 try: 541 return session.is_active 542 except AttributeError: # SA < 0.4.9 543 try: 544 return session().is_active 545 except (TypeError, AttributeError): # SA < 0.4.7 546 try: 547 transaction = request.sa_transaction 548 return transaction and transaction.is_active 549 except AttributeError: 550 return False

551

552 553 -def so_to_dict(sqlobj):

554 """Convert SQLObject to a dictionary based on columns.""" 555 d = {} 556 if sqlobj is None: 557 return d # stops recursion 558 for name in sqlobj.sqlmeta.columns.keys(): 559 d[name] = getattr(sqlobj, name) 560 d['id'] = sqlobj.id # id must be added explicitly 561 if sqlobj._inheritable: 562 d.update(so_to_dict(sqlobj._parent)) 563 d.pop('childName') 564 return d

565

566 567 -def so_columns(sqlclass, columns=None):

568 """Return a dict with all columns from a SQLObject. 569 570 This includes the columns from InheritableSO's bases. 571 572 """ 573 if columns is None: 574 columns = {} 575 columns.update(filter(lambda i: i[0] != 'childName', 576 sqlclass.sqlmeta.columns.items())) 577 if sqlclass._inheritable: 578 so_columns(sqlclass.__base__, columns) 579 return columns

580

581 582 -def so_joins(sqlclass, joins=None):

583 """Return a list with all joins from a SQLObject. 584 585 The list includes the columns from InheritableSO's bases. 586 587 """ 588 if joins is None: 589 joins = [] 590 joins.extend(sqlclass.sqlmeta.joins) 591 if sqlclass._inheritable: 592 so_joins(sqlclass.__base__, joins) 593 return joins

594

595 596 -def EndTransactions():

597 if _use_sa(): 598 try: 599 session.expunge_all() 600 except AttributeError: # SQLAlchemy < 0.5.1 601 session.clear() 602 else: 603 end_all()

604

Source Code for Module turbogears.database