Hello everyone!
The topic of a MySQL session implementation has come up here several
times in the past. We have recently written one in-house and have
been running it on a production infrastructure for some time without
incident. As it seems a fairly common component which people can
benefit from, it's being provided below for consideration.
Currently this was built directly into Session.py and takes
configuration options through httpd.conf. Clearly this could be
something which may need to be completely segmented from mod_python
given the reliance on MySQL being installed. Hopefully this can get
the conversation going.
Please note that no connection pooling has been implemented in this
version, and the benefits of doing this may or may not prove valuable
depending on the scope of its use. For our clustered session
environment it was unnecessary.
httpd.conf example:
PythonOption session MySQLSession
PythonOption session.db sessions
PythonOption session.db_host db.whatever.com
PythonOption session.db_user user
PythonOption session.db_password pass
PythonOption session.timeout 3600
Mahalo,
earle.
33d32
< import MySQLdb
704,829d702
<
<
########################################################################
###
< ## MySQLSession
<
< class MySQLSession(BaseSession):
<
< def __init__(self, req, sid=0, secret=None, timeout=0, lock=1):
<
< self.db = None
< self.db_host = None
< self.db_user = None
< self.db_password = None
< self.timeout = timeout
< self.req = req
<
< opts = req.get_options()
<
< # parse httpd.conf for DB information
< for opt in [ 'db', 'db_host', 'db_user', 'db_password' ]:
< if opts.has_key('.'.join([ 'session', opt ])):
< setattr(self, opt, opts['.'.join([ 'session', opt ])])
< else:
< self.req.log_error("MySQLSession: missing
PythonOption: session.%s" % (opt, ),
< apache.APLOG_NOTICE)
<
< raise apache.SERVER_RETURN,
apache.HTTP_INTERNAL_SERVER_ERROR
<
< if opts.has_key('session.timeout'):
< self.timeout = opts['session.timeout']
<
< BaseSession.__init__(self, req, sid=sid, secret=secret,
< timeout=timeout, lock=lock)
<
< def do_cleanup(self):
<
< data = [self._req.server, ]
<
< self._req.register_cleanup(mysqldb_cleanup, data)
< self._req.log_error("MySQLSession: registered database
cleanup.",
< apache.APLOG_NOTICE)
<
< def do_load(self):
< self.conn = MySQLdb.connect(host = self.db_host, user =
self.db_user,
< passwd = self.db_password, db =
self.db, use_unicode=1,
< init_command="set names utf8")
<
< self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
<
< try:
< self.cursor.execute("SELECT * FROM session WHERE id=%
s" , self._sid)
< res = self.cursor.fetchone()
<
< if res is not None and res.has_key('data'):
< retVal = cPickle.loads(res['data'].tostring())
< else:
< retVal = None
< except:
< retVal = None
<
< self.cursor.close()
< self.conn.close()
<
< return retVal
<
< def do_save(self, dict):
< self.conn = MySQLdb.connect(host = self.db_host, user =
self.db_user,
< passwd = self.db_password, db =
self.db, use_unicode=1,
< init_command="set names utf8")
<
< self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
< self.cursor.execute("SELECT * from session where id=%s" ,
self._sid)
<
< res = self.cursor.fetchone()
<
< if res is None:
< self.cursor.execute("INSERT INTO session (id, data,
created, accessed, timeout) VALUES (%s, %s, NOW(), NOW(), %s)",
(self._sid, cPickle.dumps(dict), self._timeout, ))
< else:
< self.cursor.execute("UPDATE session SET data=%s,
accessed=NOW() WHERE id=%s" , (cPickle.dumps(dict), self._sid, ))
<
<
< self.cursor.close()
< self.conn.close()
<
<
< def do_delete(self):
< self.conn = MySQLdb.connect(host = self.db_host, user =
self.db_user,
< passwd = self.db_password, db =
self.db, use_unicode=1,
< init_command="set names utf8")
<
< self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
<
< self.cursor.execute("DELETE FROM session WHERE id=%s",
(self._sid,))
<
< self.cursor.close()
< self.conn.close()
<
<
< def mysqldb_cleanup(data):
< try:
< conn = MySQLdb.connect(host = self.db_host, user =
self.db_user,
< passwd = self.db_password, db = self.db,
use_unicode=1,
< init_command="set names utf8")
<
< cursor = conn.cursor(MySQLdb.cursors.DictCursor)
< server = data
<
< expired = []
<
< sessions = cursor.execute("SELECT *, UNIXTIME(accessed) AS
uaccessed, UNIXTIME(created) AS ucreated FROM session")
<
< for session in sessions:
< if ((time.time() - session['accessed']) > session
['timeout']):
< expired.append(session)
<
< for session in expired:
< cursor.execute("DELETE FROM session WHERE id=%s",
(session['id'],))
<
< except:
< pass
<
< try:
< cursor.close()
< conn.cose()
< except:
< pass
<
<
856,857d728
< elif sess_type == 'MySQLSession':
< sess = MySQLSession
