cherrypy mvc with mysql issue

Problem setting up the MVC design with Cherrypy/MySQL. Here is the setup: (assume all the imports are correct)

##controller.py
class User(object):

    def __init__(self):
       self.model = model.User()

    @cherrypy.expose
    def index(self):
       return 'some HTML to display user home'


## model.py
class Model(object):
    _db = None

    def __init__(self):
        self._db = cherrypy.thread_data.db


class User(Model):
    def getuser(self, email):
        #get the user with _db and return result


##service.py
class UserService(object):
     def __init__(self):
        self._model = model.User()

     def GET(self, email):
        return self._model.getuser(email)


##starting the server

user = controller.User()
user.service = service.UserService()
cherrypy.tree.mount(user, '/user', self.config)
#app.merge(self.config)

cherrypy.engine.subscribe("start_thread", self._onThreadStart)

self._onThreadStart(-1)

def _onThreadStart(self, threadIndex):
    cherrypy.thread_data.db = mysql.connect(**self.config["database"])

if __name__ == '__main__':
    cherrypy.engine.start()
    cherrypy.engine.block()

the above code has error in model.py at the line: cherrypy.thread_data.db. I got:

AttributeError: '_ThreadData' object has no attribute 'db'

not sure why, could you please point me into the right direction? I can get the connection, and pull info from controller.py at User index, but not in model.py? Please help.. thanks.

Solution

CherryPy doesn't decide for you what tools to use. It is up to you to pick the tools that fit you and your tasks the best. Thus CherryPy doesn't setup any database connection, your cherrypy.thread_data.db, it's your job.

Personally I use the same concept of responsibility separation, kind of MVC, for my CherryPy apps so there follow two possible ways to achieve what you want.

Design note

I would like to note that the simple solution of thread-mapped database connections, at least in case of MySQL, works pretty well in practice. And additional complexity of more old-fashioned connection pools may not be necessary.

There're however points that shouldn't be overlooked. Your database connection may become killed, lost or be in any other state that won't allow you to make queries on it. In this case reconnection must be preformed.

Also pay attention to avoid connection sharing between threads as it will result in hard-to-debug errors and Python crashes. In your example code, it may relate to a service dispatcher and its cache.

Bootstrapping phase

In your code that sets configuration, mounts CherryPy apps, etc.

bootstrap.py

# ...

import MySQLdb as mysql

def _onThreadStart(threadIndex):
  cherrypy.thread_data.db = mysql.connect(**config['database'])

cherrypy.engine.subscribe('start_thread', _onThreadStart)

# useful for tests to have db connection on current thread 
_onThreadStart(-1)

model.py

import cherrypy
import MySQLdb as mysql


class Model(object):
  '''Your abstract model'''

  _db = None


  def __init__(self):
    self._db = cherrypy.thread_data.db

    try:
      # reconnect if needed
      self._db.ping(True)
    except mysql.OperationalError:
      pass

I wrote a complete CherryPy deployment tutorial, cherrypy-webapp-skeleton, a couple of years ago. You can take a look at the code, as the demo application uses exactly this approach.

Model property

To achieve less code coupling and to avoid import cycles it could be a good idea to move all database related code to model module. It may include, initial connection queries like setting operation timezone, making MySQLdb converters timzeone-aware, etc.

model.py

class Model(object):

  def __init__(self):
    try:
      # reconnect if needed
      self._db.ping(True)
    except mysql.OperationalError:
      pass

  @property
  def _db(self):
    '''Thread-mapped connection accessor'''

    if not hasattr(cherrypy.thread_data, 'db'):
      cherrypy.thread_data.db = mysql.connect(**config['database'])

    return cherrypy.thread_data.db