import cherrypy
from cherrypy.process import wspbus, plugins
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String
import os


# BELOW CLASSES COPIED FROM
# https://bitbucket.org/Lawouach/cherrypy-recipes/src/d140e6da973aa271e6b68a8bc187e53615674c5e/web/database/?at=default
class SATool(cherrypy.Tool):
    def __init__(self):
        """
        The SA tool is responsible for associating a SA session
        to the SA engine and attaching it to the current request.
        Since we are running in a multithreaded application,
        we use the scoped_session that will create a session
        on a per thread basis so that you don't worry about
        concurrency on the session object itself.

        This tools binds a session to the engine each time
        a requests starts and commits/rollbacks whenever
        the request terminates.
        """
        cherrypy.Tool.__init__(self, 'on_start_resource',
                               self.bind_session,
                               priority=20)

    def _setup(self):
        cherrypy.Tool._setup(self)
        cherrypy.request.hooks.attach('on_end_resource',
                                      self.commit_transaction,
                                      priority=80)

    def bind_session(self):
        """
        Attaches a session to the request's scope by requesting
        the SA plugin to bind a session to the SA engine.
        """
        session = cherrypy.engine.publish('bind-session').pop()
        cherrypy.request.db = session

    def commit_transaction(self):
        """
        Commits the current transaction or rolls back
        if an error occurs. Removes the session handle
        from the request's scope.
        """
        if not hasattr(cherrypy.request, 'db'):
            return
        cherrypy.request.db = None
        cherrypy.engine.publish('commit-session')


class SAEnginePlugin(plugins.SimplePlugin):
    def __init__(self, bus):
        """
        The plugin is registered to the CherryPy engine and therefore
        is part of the bus (the engine *is* a bus) registery.

        We use this plugin to create the SA engine. At the same time,
        when the plugin starts we create the tables into the database
        using the mapped class of the global metadata.
        """
        plugins.SimplePlugin.__init__(self, bus)
        self.sa_engine = None
        self.session = scoped_session(sessionmaker(autoflush=True,
                                                   autocommit=False))

    def start(self):
        self.bus.log('Starting up DB access')

        self.sa_engine = create_engine('oracle+cx_oracle://%s:%s@%s' %
            (os.getenv('DB_USERNAME'), os.getenv('DB_PASSWORD'), os.getenv('DB_SERVER')),
            echo=True)

        self.bus.subscribe("bind-session", self.bind)
        self.bus.subscribe("commit-session", self.commit)

    def stop(self):
        self.bus.log('Stopping down DB access')
        self.bus.unsubscribe("bind-session", self.bind)
        self.bus.unsubscribe("commit-session", self.commit)
        if self.sa_engine:
            self.sa_engine.dispose()
            self.sa_engine = None

    def bind(self):
        """
        Whenever this plugin receives the 'bind-session' command, it applies
        this method and to bind the current session to the engine.

        It then returns the session to the caller.
        """
        self.session.configure(bind=self.sa_engine)
        return self.session

    def commit(self):
        """
        Commits the current transaction or rollbacks if an error occurs.

        In all cases, the current session is unbound and therefore
        not usable any longer.
        """
        try:
            self.session.commit()
        except:
            self.session.rollback()
            raise
        finally:
            self.session.remove()

# SQL Alchemy model
Base = declarative_base()
class Registration(Base):
    __tablename__ = 'beta_registration'
    email = Column('email', String, primary_key=True)


class Root():
    @cherrypy.expose
    def index(self):
        registration = Registration()
        registration.email = "test@test.com"

        db = cherrypy.request.db
        try:
            db.add(registration)
        except Exception as e:
            # **** never gets here *****
            # should be IntegrityError on second call from sqlalchemy.exc
            raise cherrypy.HTTPError("409 Conflict", "The email address has already been registered")

SAEnginePlugin(cherrypy.engine).subscribe()
cherrypy.tools.db = SATool()

cherrypy.config.update({
'tools.sessions.on' : True,
'tools.sessions.storage_type' : 'File',
'tools.sessions.storage_path' : 'adf'
})


cherrypy.quickstart(Root(), '/')

[09/Apr/2014:16:43:54] ENGINE Error in 'commit-session' listener <bound method SAEnginePlugin.commit of <plugins.saplugin.SAEnginePlugin object at 0x105a0cd10>>
Traceback (most recent call last):
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/process/wspbus.py", line 197, in publish
    output.append(listener(*args, **kwargs))
  File "/Users/xatter/Sites/connectfor/plugins/saplugin.py", line 57, in commit
    self.session.commit()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 765, in commit
    self.transaction.commit()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 370, in commit
    self._prepare_impl()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 350, in _prepare_impl
    self.session.flush()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1879, in flush
    self._flush(objects)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1997, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1961, in _flush
    flush_context.execute()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 370, in execute
    rec.execute(self)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 523, in execute
    uow
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    mapper, table, insert)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 562, in _emit_insert_statements
    execute(statement, multiparams)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 814, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "beta_registration_pk"
DETAIL:  Key (email)=(test@test.com) already exists.
 'INSERT INTO beta_registration (email) VALUES (%(email)s)' {'email': u'test@test.com'}

[09/Apr/2014:16:43:54]  Traceback (most recent call last):
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/_cprequest.py", line 102, in run
    hook()
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/_cprequest.py", line 62, in __call__
    return self.callback(**self.kwargs)
  File "/Users/xatter/Sites/connectfor/plugins/satool.py", line 47, in commit_transaction
    cherrypy.engine.publish('commit-session')
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/process/wspbus.py", line 215, in publish
    raise exc
ChannelFailures: IntegrityError('(IntegrityError) duplicate key value violates unique constraint "beta_registration_pk"nDETAIL:  Key (email)=(test@test.com) already exists.n',)

[09/Apr/2014:16:43:54] HTTP
Request Headers:
  Content-Length: 25
  COOKIE: remember_token=_tm4c-HNpJWTHB1PXj8Wbg; session_id=25757ddac3a84730ce3b87f32b80a4288f5421b4
  HOST: localhost:5000
  ORIGIN: http://localhost:5000
  CONNECTION: keep-alive
  Remote-Addr: 127.0.0.1
  ACCEPT: application/json, text/plain, */*
  USER-AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_2) AppleWebKit/537.75.14 (KHTML, like Gecko) Version/7.0.3 Safari/537.75.14
  REFERER: http://localhost:5000/
  ACCEPT-LANGUAGE: en-us
  Content-Type: application/json;charset=UTF-8
  ACCEPT-ENCODING: gzip, deflate
[09/Apr/2014:16:43:54] HTTP Traceback (most recent call last):
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/_cprequest.py", line 670, in respond
    self.hooks.run('on_end_resource')
  File "/Users/xatter/Sites/connectfor/venv/lib/python2.7/site-packages/cherrypy/_cprequest.py", line 112, in run
    raise exc
ChannelFailures: IntegrityError('(IntegrityError) duplicate key value violates unique constraint "beta_registration_pk"nDETAIL:  Key (email)=(test@test.com) already exists.n',)

127.0.0.1 - - [09/Apr/2014:16:43:54] "POST /beta_registration HTTP/1.1" 500 1303 "http://localhost:5000/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_2) AppleWebKit/537.75.14 (KHTML, like Gecko) Version/7.0.3 Safari/537.75.14"

import cherrypy

cherrypy.config.update({
'tools.sessions.on' : True,
'tools.sessions.storage_type' : 'File',
'tools.sessions.storage_path' : 'adf'
})

class Root(object):
    @cherrypy.expose
    def index(self):
        try:
            asdf = cherrypy.session['asdf']
        except Exception as e:
            raise cherrypy.HTTPError("409 Conflict", "The email address has already been registered")
            #return str(e)
        return 1

    @cherrypy.expose
    @cherrypy.tools.json_out()
    def main(self):
        return 'hi'

cherrypy.quickstart(Root())