turbogears.database

1 """Convenient access to an SQLObject or SQLAlchemy managed database.""" 2 3 import sys 4 import time 5 import logging 6 7 import cherrypy 8 from cherrypy import request 9 from cherrypy.filters.basefilter import BaseFilter 10 11 try: 12 import sqlalchemy, sqlalchemy.orm 13 from sqlalchemy import MetaData 14 try: 15 from sqlalchemy.exc import ArgumentError, OperationalError 16 except ImportError: # SQLAlchemy < 0.5 17 from sqlalchemy.exceptions import ArgumentError, OperationalError 18 except ImportError: 19 sqlalchemy = None 20 21 try: 22 import sqlobject 23 from sqlobject.dbconnection import ConnectionHub, Transaction, TheURIOpener 24 from sqlobject.util.threadinglocal import local as threading_local 25 except ImportError: 26 sqlobject = None 27 28 from peak.rules import abstract, when, NoApplicableMethods 29 30 from turbogears import config 31 from turbogears.util import remove_keys 32 33 log = logging.getLogger("turbogears.database") 34 35 _using_sa = False

36 37 38 -class DatabaseError(Exception):

39 pass

40 -class DatabaseConfigurationError(DatabaseError):

41 pass

42 43 # Provide support for SQLAlchemy 44 if sqlalchemy:

45 46 - def get_engine(pkg=None):

47 """Retrieve the engine based on the current configuration.""" 48 bind_metadata() 49 return get_metadata(pkg).bind

50

51 - def get_metadata(pkg=None):

52 """Retrieve the metadata for the specified package.""" 53 try: 54 return _metadatas[pkg] 55 except KeyError: 56 _metadatas[pkg] = MetaData() 57 return _metadatas[pkg]

58

59 - def bind_metadata():

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

86

87 - def create_session():

88 """Create a session that uses the engine from thread-local metadata. 89 90 The session by default does not begin a transaction, and requires that 91 flush() be called explicitly in order to persist results to the database. 92 93 """ 94 if not metadata.is_bound(): 95 bind_metadata() 96 return sqlalchemy.orm.create_session()

97 98 session = sqlalchemy.orm.scoped_session(create_session) 99 100 if not hasattr(session, 'add'): # SQLAlchemy < 0.5 101 session.add = session.save_or_update

102 103 # Note: TurboGears used to set mapper = Session.mapper, but this has 104 # been deprecated in SQLAlchemy 0.5.5. If it is unavailable, we emulate 105 # the behaviour of the old session-aware mapper following this recipe 106 # from the SQLAlchemy wiki: 107 # 108 # http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SessionAwareMapper 109 # 110 # If you do not want to use the session-aware mapper, import 'mapper' 111 # directly from sqlalchemy.orm. See model.py in the default quickstart 112 # template for an example. 113 - def create_session_mapper(scoped_session=session):

114 def mapper(cls, *args, **kw): 115 set_kwargs_on_init = kw.pop('set_kwargs_on_init', True) 116 validate = kw.pop('validate', False) 117 # we accept 'save_on_init' as an alias for 'autoadd' for backward 118 # compatibility, but 'autoadd' is shorter and more to the point. 119 autoadd = kw.pop('autoadd', kw.pop('save_on_init', True)) 120 121 if set_kwargs_on_init and (getattr(cls, 122 '__init__', object.__init__) is object.__init__ 123 or getattr(cls.__init__, '_session_mapper', False)): 124 def __init__(self, **kwargs): 125 for key, value in kwargs.items(): 126 if validate: 127 if not hasattr(self, key): 128 raise TypeError( 129 "Invalid __init__ argument: '%s'" % key) 130 setattr(self, key, value) 131 if autoadd: 132 session.add(self)

133 __init__._session_mapper = True 134 cls.__init__ = __init__ 135 cls.query = scoped_session.query_property() 136 return sqlalchemy.orm.mapper(cls, *args, **kw) 137 return mapper 138 session_mapper = create_session_mapper() 139 if hasattr(session, 'mapper'): 140 # Old session-aware mapper 141 mapper = session.mapper 142 else: 143 mapper = session_mapper 144 145 _metadatas = {} 146 _metadatas[None] = MetaData() 147 metadata = _metadatas[None] 148 149 try: 150 import elixir 151 elixir.metadata, elixir.session = metadata, session 152 except ImportError: 153 pass 154 155 else:

156 - def get_engine():

157 pass

158 - def get_metadata():

159 pass

160 - def bind_metadata():

161 pass

162 - def create_session():

163 pass

164 session = metadata = mapper = None 165 166 bind_meta_data = bind_metadata # deprecated, for backward compatibility 167 168 hub_registry = set() 169 170 _hubs = dict() # stores the AutoConnectHubs used for each connection URI 171 172 # Provide support for SQLObject 173 if sqlobject:

174 - def _mysql_timestamp_converter(raw):

175 """Convert a MySQL TIMESTAMP to a floating point number representing 176 the seconds since the Un*x Epoch. It uses custom code the input seems 177 to be the new (MySQL 4.1+) timestamp format, otherwise code from the 178 MySQLdb module is used.""" 179 if raw[4] == '-': 180 return time.mktime(time.strptime(raw, '%Y-%m-%d %H:%M:%S')) 181 else: 182 import MySQLdb.converters 183 return MySQLdb.converters.mysql_timestamp_converter(raw)

184

185 186 - class AutoConnectHub(ConnectionHub):

187 """Connects to the database once per thread. The AutoConnectHub also 188 provides convenient methods for managing transactions.""" 189 uri = None 190 params = {} 191

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

193 if not uri: 194 uri = config.get("sqlobject.dburi") 195 self.uri = uri 196 self.supports_transactions = supports_transactions 197 hub_registry.add(self) 198 ConnectionHub.__init__(self)

199

200 - def _is_interesting_version(self):

201 """Return True only if version of MySQLdb <= 1.0.""" 202 import MySQLdb 203 module_version = MySQLdb.version_info[0:2] 204 major = module_version[0] 205 minor = module_version[1] 206 # we can't use Decimal here because it is only available for Python 2.4 207 return (major < 1 or (major == 1 and minor < 2))

208

209 - def _enable_timestamp_workaround(self, connection):

210 """Enable a workaround for an incompatible timestamp format change 211 in MySQL 4.1 when using an old version of MySQLdb. See trac ticket 212 #1235 - http://trac.turbogears.org/ticket/1235 for details.""" 213 # precondition: connection is a MySQLConnection 214 import MySQLdb 215 import MySQLdb.converters 216 if self._is_interesting_version(): 217 conversions = MySQLdb.converters.conversions.copy() 218 conversions[MySQLdb.constants.FIELD_TYPE.TIMESTAMP] = \ 219 _mysql_timestamp_converter 220 # There is no method to use custom keywords when using 221 # "connectionForURI" in sqlobject so we have to insert the 222 # conversions afterwards. 223 connection.kw["conv"] = conversions

224

225 - def getConnection(self):

226 try: 227 conn = self.threadingLocal.connection 228 return self.begin(conn) 229 except AttributeError: 230 if self.uri: 231 conn = sqlobject.connectionForURI(self.uri) 232 # the following line effectively turns off the DBAPI connection 233 # cache. We're already holding on to a connection per thread, 234 # and the cache causes problems with sqlite. 235 if self.uri.startswith("sqlite"): 236 TheURIOpener.cachedURIs = {} 237 elif self.uri.startswith("mysql") and \ 238 config.get("turbogears.enable_mysql41_timestamp_workaround", False): 239 self._enable_timestamp_workaround(conn) 240 self.threadingLocal.connection = conn 241 return self.begin(conn) 242 raise AttributeError( 243 "No connection has been defined for this thread " 244 "or process")

245

246 - def reset(self):

247 """Used for testing purposes. This drops all of the connections 248 that are being held.""" 249 self.threadingLocal = threading_local()

250

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

252 """Start a transaction.""" 253 if not self.supports_transactions: 254 return conn 255 if not conn: 256 conn = self.getConnection() 257 if isinstance(conn, Transaction): 258 if conn._obsolete: 259 conn.begin() 260 return conn 261 self.threadingLocal.old_conn = conn 262 trans = conn.transaction() 263 self.threadingLocal.connection = trans 264 return trans

265

266 - def commit(self):

267 """Commit the current transaction.""" 268 if not self.supports_transactions: 269 return 270 try: 271 conn = self.threadingLocal.connection 272 except AttributeError: 273 return 274 if isinstance(conn, Transaction): 275 self.threadingLocal.connection.commit()

276

277 - def rollback(self):

278 """Rollback the current transaction.""" 279 if not self.supports_transactions: 280 return 281 try: 282 conn = self.threadingLocal.connection 283 except AttributeError: 284 return 285 if isinstance(conn, Transaction) and not conn._obsolete: 286 self.threadingLocal.connection.rollback()

287

288 - def end(self):

289 """End the transaction, returning to a standard connection.""" 290 if not self.supports_transactions: 291 return 292 try: 293 conn = self.threadingLocal.connection 294 except AttributeError: 295 return 296 if not isinstance(conn, Transaction): 297 return 298 if not conn._obsolete: 299 conn.rollback() 300 self.threadingLocal.connection = self.threadingLocal.old_conn 301 del self.threadingLocal.old_conn 302 self.threadingLocal.connection.expireAll()

303

304 - class PackageHub(object):

305 """Transparently proxies to an AutoConnectHub for the URI 306 that is appropriate for this package. A package URI is 307 configured via "packagename.dburi" in the TurboGears config 308 settings. If there is no package DB URI configured, the 309 default (provided by "sqlobject.dburi") is used. 310 311 The hub is not instantiated until an attempt is made to 312 use the database. 313 """

314 - def __init__(self, packagename):

315 self.packagename = packagename 316 self.hub = None

317

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

319 if self.hub: 320 return self.hub.__get__(obj, type) 321 else: 322 return self

323

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

325 if not self.hub: 326 self.set_hub() 327 return self.hub.__set__(obj, type)

328

329 - def __getattr__(self, name):

330 if not self.hub: 331 self.set_hub() 332 try: 333 return getattr(self.hub, name) 334 except AttributeError: 335 return getattr(self.hub.getConnection(), name)

336

337 - def set_hub(self):

338 dburi = config.get("%s.dburi" % self.packagename, None) 339 if not dburi: 340 dburi = config.get("sqlobject.dburi", None) 341 if not dburi: 342 raise DatabaseConfigurationError( 343 "No sqlobject database configuration found!") 344 if dburi.startswith("notrans_"): 345 dburi = dburi[8:] 346 trans = False 347 else: 348 trans = True 349 hub = _hubs.get(dburi, None) 350 if not hub: 351 hub = AutoConnectHub(dburi, supports_transactions=trans) 352 _hubs[dburi] = hub 353 self.hub = hub

354 else:

355 - class AutoConnectHub(object):

356 pass

357

358 - class PackageHub(object):

359 pass

360

361 -def set_db_uri(dburi, package=None):

362 """Sets the database URI to use either globally or for a specific 363 package. Note that once the database is accessed, calling 364 setDBUri will have no effect. 365 366 @param dburi: database URI to use 367 @param package: package name this applies to, or None to set the default. 368 """ 369 if package: 370 config.update({'global': 371 {"%s.dburi" % package : dburi} 372 }) 373 else: 374 config.update({'global': 375 {"sqlobject.dburi" : dburi} 376 })

377

378 -def commit_all():

379 """Commit the transactions in all registered hubs (for this thread).""" 380 for hub in hub_registry: 381 hub.commit()

382

383 -def rollback_all():

384 """Rollback the transactions in all registered hubs (for this thread).""" 385 for hub in hub_registry: 386 hub.rollback()

387

388 -def end_all():

389 """End the transactions in all registered hubs (for this thread).""" 390 for hub in hub_registry: 391 hub.end()

392

393 @abstract() 394 -def run_with_transaction(func, *args, **kw):

395 pass

396

397 @abstract() 398 -def restart_transaction(args):

399 pass

400

401 -def _use_sa(args=None):

402 return _using_sa

403

404 # include "args" to avoid call being pre-cached 405 @when(run_with_transaction, "not _use_sa(args)") 406 -def so_rwt(func, *args, **kw):

407 log.debug("Starting SQLObject transaction") 408 try: 409 try: 410 retval = func(*args, **kw) 411 commit_all() 412 return retval 413 except cherrypy.HTTPRedirect: 414 commit_all() 415 raise 416 except cherrypy.InternalRedirect: 417 commit_all() 418 raise 419 except: 420 # No need to "rollback" the sqlalchemy unit of work, 421 # because nothing has hit the db yet. 422 rollback_all() 423 raise 424 finally: 425 end_all()

426

427 # include "args" to avoid call being pre-cached 428 @when(restart_transaction, "not _use_sa(args)") 429 -def so_restart_transaction(args):

430 #log.debug("ReStarting SQLObject transaction") 431 # Disable for now for compatibility 432 pass

433

434 -def dispatch_exception(exception, args, kw):

435 # errorhandling import here to avoid circular imports 436 from turbogears.errorhandling import dispatch_error 437 # Keep in mind func is not the real func but _expose 438 real_func, accept, allow_json, controller = args[:4] 439 args = args[4:] 440 exc_type, exc_value, exc_trace = sys.exc_info() 441 remove_keys(kw, ("tg_source", "tg_errors", "tg_exceptions")) 442 try: 443 output = dispatch_error( 444 controller, real_func, None, exception, *args, **kw) 445 except NoApplicableMethods: 446 raise exc_type, exc_value, exc_trace 447 else: 448 del exc_trace 449 return output

450

451 # include "args" to avoid call being pre-cached 452 @when(run_with_transaction, "_use_sa(args)") 453 -def sa_rwt(func, *args, **kw):

454 log.debug("Starting SA transaction") 455 request.sa_transaction = session.begin() 456 try: 457 try: 458 retval = func(*args, **kw) 459 except (cherrypy.HTTPRedirect, cherrypy.InternalRedirect): 460 # If a redirect happens, commit and proceed with redirect. 461 if sa_transaction_active(): 462 log.debug('Redirect in active transaction - will commit now') 463 session.commit() 464 else: 465 log.debug('Redirect in inactive transaction') 466 raise 467 except: 468 # If any other exception happens, rollback and re-raise error 469 if sa_transaction_active(): 470 log.debug('Error in active transaction - will rollback now') 471 session.rollback() 472 else: 473 log.debug('Error in inactive transaction') 474 raise 475 # If the call was successful, commit and proceed 476 if sa_transaction_active(): 477 log.debug('Transaction is still active - will commit now') 478 session.commit() 479 else: 480 log.debug('Transaction is already inactive') 481 finally: 482 log.debug('Ending SA transaction') 483 session.close() 484 return retval

485

486 # include "args" to avoid call being pre-cached 487 @when(restart_transaction, "_use_sa(args)") 488 -def sa_restart_transaction(args):

489 log.debug("Restarting SA transaction") 490 if sa_transaction_active(): 491 log.debug('Transaction is still active - will rollback now') 492 session.rollback() 493 else: 494 log.debug('Transaction is already inactive') 495 session.close() 496 request.sa_transaction = session.begin()

497

498 -def sa_transaction_active():

499 """Check whether SA transaction is still active.""" 500 try: 501 return session.is_active 502 except AttributeError: # SA < 0.4.9 503 try: 504 return session().is_active 505 except (TypeError, AttributeError): # SA < 0.4.7 506 try: 507 transaction = request.sa_transaction 508 return transaction and transaction.is_active 509 except AttributeError: 510 return False

511

512 -def so_to_dict(sqlobj):

513 """Convert SQLObject to a dictionary based on columns.""" 514 d = {} 515 if sqlobj is None: 516 return d # stops recursion 517 for name in sqlobj.sqlmeta.columns.keys(): 518 d[name] = getattr(sqlobj, name) 519 d['id'] = sqlobj.id # id must be added explicitly 520 if sqlobj._inheritable: 521 d.update(so_to_dict(sqlobj._parent)) 522 d.pop('childName') 523 return d

524

525 -def so_columns(sqlclass, columns=None):

526 """Return a dict with all columns from a SQLObject. 527 528 This includes the columns from InheritableSO's bases. 529 530 """ 531 if columns is None: 532 columns = {} 533 columns.update(filter(lambda i: i[0] != 'childName', 534 sqlclass.sqlmeta.columns.items())) 535 if sqlclass._inheritable: 536 so_columns(sqlclass.__base__, columns) 537 return columns

538

539 -def so_joins(sqlclass, joins=None):

540 """Return a list with all joins from a SQLObject. 541 542 The list includes the columns from InheritableSO's bases. 543 544 """ 545 if joins is None: 546 joins = [] 547 joins.extend(sqlclass.sqlmeta.joins) 548 if sqlclass._inheritable: 549 so_joins(sqlclass.__base__, joins) 550 return joins

551

552 -class EndTransactionsFilter(BaseFilter):

553 - def on_end_resource(self):

554 if _use_sa(): 555 try: 556 session.expunge_all() 557 except AttributeError: # SQLAlchemy < 0.5.1 558 session.clear() 559 else: 560 end_all()

561 562 __all__ = ["AutoConnectHub", "bind_metadata", "create_session", 563 "create_session_mapper", "commit_all", "end_all", 564 "DatabaseError", "DatabaseConfigurationError", 565 "EndTransactionsFilter", "get_engine", "get_metadata", "mapper", 566 "metadata", "PackageHub", "rollback_all", "session", 567 "session_mapper", "set_db_uri", "so_columns", "so_joins", 568 "so_to_dict"] 569

Source Code for Module turbogears.database