Homec4science
Diffusion invenio-infoscience (maint-1.0) Browse

R3600/modules/miscutil/lib/dbquery.py.wml
18b100fe8575maint-1.0

Last Change
Raw File

dbquery.py.wml
View Options

## $Id$
## CDS Invenio utility to run SQL queries. The core is taken from
## modpython FAQ and modified to suit our needs. The insert_id() is
## inspired by Erik Forsberg's mod_python slides.
## This file is part of CDS Invenio.
## Copyright (C) 2002, 2003, 2004, 2005, 2006 CERN.
##
## CDS Invenio is free software; you can redistribute it and/or
## modify it under the terms of the GNU General Public License as
## published by the Free Software Foundation; either version 2 of the
## License, or (at your option) any later version.
##
## CDS Invenio is distributed in the hope that it will be useful, but
## WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
## General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with CDS Invenio; if not, write to the Free Software Foundation, Inc.,
## 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
#include "configbis.wml"
"""CDS Invenio utility to run SQL queries."""
import MySQLdb
import string
## FIXME: these are read here and not in config.py in order to prevent
## them to show up in case some mod_python web script uses ``from
## config import *''. If we waterproof all mod_python web scripts,
## then it may possibly be moved there. But it's safer to read them
## only here.
dbhost = "<DBHOST>"
dbname = "<DBNAME>"
dbuser = "<DBUSER>"
dbpass = "<DBPASS>"
__version__ = "$Id$"
def _db_login(relogin = 0):
"""Login to the database."""
global DB_CONN
if relogin:
DB_CONN = MySQLdb.connect(host=dbhost, db=dbname, user=dbuser, passwd=dbpass)
return DB_CONN
else:
try:
d = DB_CONN
return d
except NameError:
DB_CONN = MySQLdb.connect(host=dbhost, db=dbname, user=dbuser, passwd=dbpass)
return DB_CONN
def run_sql(sql, param=None, n=0, with_desc=0):
"""Run SQL on the server and returns result."""
### log_sql_query(sql, param) ### UNCOMMENT ONLY IF you REALLY want to log all queries
db = _db_login()
if param:
param = tuple(param)
try:
cur = db.cursor()
rc = cur.execute(sql, param)
except MySQLdb.OperationalError, e: # unexpected disconnect, bad malloc error, etc
# FIXME: now reconnect is always forced, we may perhaps want to ping() first?
db = _db_login(relogin = 1)
cur = db.cursor()
rc = cur.execute(sql, param)
if string.upper(string.split(sql)[0]) in ("SELECT", "SHOW", "DESC", "DESCRIBE"):
if n:
recset = cur.fetchmany(n)
else:
recset = cur.fetchall()
if with_desc:
return recset, cur.description
else:
return recset
else:
if string.upper(string.split(sql)[0]) == "INSERT":
rc = cur.lastrowid
return rc
def blob_to_string(ablob):
"""Return string representation of ABLOB. Useful to treat MySQL
BLOBs in the same way for both recent and old MySQLdb versions.
"""
if type(ablob) is str:
# BLOB is already a string in MySQLdb 0.9.2
return ablob
else:
# BLOB is array.array in MySQLdb 1.0.0 and later
return ablob.tostring()
def log_sql_query(sql, param=None):
"""Log SQL query into prefix/var/dbquery.log log file. In order
to enable logging of all SQL queries, please uncomment one line
in run_sql() above. Useful for fine-level debugging only!
"""
from invenio.config import logdir
from invenio.dateutils import convert_datestruct_to_datetext
import time
from invenio.textutils import indent_text
log_path = logdir + '/dbquery.log'
date_of_log = convert_datestruct_to_datetext(time.localtime())
message = date_of_log + '-->\n'
message += indent_text('Query:\n' + indent_text(str(sql),2),2)
message += indent_text('Params:\n' + indent_text(str(param),2),2)
message += '-----------------------------\n\n'
try:
log_file = open(log_path, 'a+')
log_file.writelines(message)
log_file.close()
except:
pass
c4science · Help