Diffusion invenio-infoscience (master) Browse

miscutil-dbquery.webdoc
d30e56fa001amaster

miscutil-dbquery.webdoc
View Options

			## -- mode: html; coding: utf-8; --
			## This file is part of Invenio.
			## Copyright (C) 2002, 2003, 2004, 2005, 2006, 2007, 2008 CERN.
			##
			## 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.
			##
			## 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 Invenio; if not, write to the Free Software Foundation, Inc.,
			## 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.


			<!-- WebDoc-Page-Title: Database access API -->
			<!-- WebDoc-Page-Navtrail: <a class="navtrail" href="<CFG_SITE_URL>/help/hacking">Hacking Invenio</a> > <a class="navtrail" href="miscutil-internals">MiscUtil Internals</a> -->
			<!-- WebDoc-Page-Navbar-Select: hacking-miscutil-dbquery -->

			<p>dbquery module handles automatically connection (and reconnection)
			to the database and provides the <code>run_sql()</code> function to
			perform SQL 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>real_escape_string()</code>:

			<blockquote>
			<pre>
			from invenio.dbquery import run_sql, real_escape_string
			[...]
			res = run_sql("SELECT id FROM collection WHERE name='%s'" % real_escape_string(c), None)
			if res:
			colID = res[0][0]
			</pre>
			</blockquote>

			but it is better to use the former automatic technique.
			</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>Logging SQL Queries</h3>

			<p>If you want to investigate some DB related problems, note that you
			can uncomment some lines in <code>dbquery.py</code> to obtain detailed
			log of every SQL query and its parameters. Look for
			string <code>log_sql_query</code> to know more.
			</p>

R3600/modules/miscutil/doc/hacking/miscutil-dbquery.webdocd30e56fa001amaster

miscutil-dbquery.webdocView Options

R3600/modules/miscutil/doc/hacking/miscutil-dbquery.webdoc
d30e56fa001amaster

miscutil-dbquery.webdoc
View Options