to the database and provides the <code>run_sql()</code> function to
perform SQL queries and <code>run_sql_cached()</code> for rarely
changing SELECT queries. It also exports DB exceptions for the client
code to use (see below).
</p>
<h3><code>run_sql()</code> API</h3>
<p><code>run_sql()</code> signature:
<blockquote>
<pre>
def run_sql(sql, param=None, n=0, with_desc=0):
"""Run SQL on the server with PARAM and return result.
@param param: tuple of string params to insert in the query
(see notes below)
@param n: number of tuples in result (0 for unbounded)
@param with_desc: if true, will return a
DB API 7-tuple describing columns in query
@return: if SELECT, SHOW, DESCRIBE statements: tuples of data, followed
by description if parameter
provided
if INSERT: last row id.
else: SQL result as provided by database
When the site is closed for maintenance (as governed by the
config variable CFG_ACCESS_CONTROL_LEVEL_SITE), do not attempt
to run any SQL queries but return empty list immediately.
Useful to be able to have the website up while MySQL database
is down for maintenance, hot copies, table repairs, etc.
In case of problems, exceptions are returned according to the
Python DB API 2.0. The client code can import them from this
file and catch them.
"""
</pre>
</blockquote>
</p>
<p><code>run_sql()</code> normally escapes its parameters if you
pass them in a tuple. Usually the params must use the string format (<code>%s<code>):
<blockquote>
<pre>
from invenio.dbquery import run_sql
[...]
res = run_sql("SELECT id FROM collection WHERE name=%s", (c,))
if res:
colID = res[0][0]
</pre>
</blockquote>
If you want to escape the parameters yourself in the client code, you
could in principle import and make use of the
function <code>escape_string()</code>:
<blockquote>
<pre>
from invenio.dbquery import run_sql, escape_string
[...]
res = run_sql("SELECT id FROM collection WHERE name='%s'" % escape_string(c), None)
if res:
colID = res[0][0]
</pre>
</blockquote>
but beware, this function is not UTF-8 safe! So better do not use it.
</p>
<p>The <code>run_sql()</code> raises Python DB API 2.0 exceptions that
the client code should catch and handle. An example:
<blockquote>
<pre>
from invenio.dbquery import run_sql, OperationalError
[...]
query = "select citation_data from rnkCITATIONDATA"
try:
compressed_citation_dic = run_sql(query)
except OperationalError:
compressed_citation_dic = []
</pre>
</blockquote>
</p>
<p>For the list of all exceptions and the conditions when they are
raised, see <a href="http://www.python.org/dev/peps/pep-0249/">PEP 249</a>.
<h3>Note for handling date types</h3>
<p>There is an incompatibility in handling date types between MySQLdb 0.9 and MySQLdb 1.2 (while using Python 2.2 or 2.3). If a date field is in the received tuple, its format will be:</p>
<ul>
<li>string with MySQLdb 0.9</li>
<li>datetime with MySQLdb 1.2</li>
</ul>
<p>As Python 2.2 doesn't provide <code>datetime</code> class, handling of this
problem should be done for backwards compatibility reasons. The
solution is to force MySQL to convert date to a textual format:</p>
<pre>
SELECT DATE_FORMAT(date_field,'%%Y-%%m-%%d %%H:%%i:%%s') FROM table
</pre>
<p>This conversion will return a datetext format as described in <a href="miscutil-dateutils">dateutils library</a><code>(YEAR-MONTH-DAY HOUR:MINUTE:SECOND)</code>.</p>
<h3><code>run_sql_cached()</code> API</h3>
<p> If you execute a certain SELECT query often, you can
use <code>run_sql_cached()</code> that will cache its result in memory
and return it faster next time. The function signature and usage is
similar to the one known from <code>run_sql()</code>: