CERN Accelerating science

Pàgina inicial > Hacking Invenio > MiscUtil Internals > Database access API

Database access API

dbquery module handles automatically connection (and reconnection) to the database and provides the run_sql() function to perform SQL queries. It also exports DB exceptions for the client code to use (see below).

run_sql() API

run_sql() signature: 

def run_sql(sql, param=None, n=0, with_desc=False, with_dict=False):
    """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
        @param with_dict: if True, will return a list of dictionaries
                          composed of column-value pairs
        @return: if SELECT, SHOW, DESCRIBE statements: tuples of data, followed
                                                       by description if parameter
                                                       provided
                 If SELECT and with_dict=True, return a list of dictionaries
                 composed of column-value pairs, followed by description
                 if parameter with_desc is 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.
    """

run_sql() normally escapes its parameters if you pass them in a tuple. Usually the params must use the string format (%s): 

from invenio.dbquery import run_sql
[...]
res = run_sql("SELECT id FROM collection WHERE name=%s", (c,))
if res:
    colID = res[0][0]
If you want to escape the parameters yourself in the client code, you could in principle import and make use of the function real_escape_string(): 
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]
but it is better to use the former automatic technique.

The run_sql() raises Python DB API 2.0 exceptions that the client code should catch and handle. An example: 

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 = []

For the list of all exceptions and the conditions when they are raised, see PEP 249.

Note for handling date types

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:

  • string with MySQLdb 0.9
  • datetime with MySQLdb 1.2

As Python 2.2 doesn't provide datetime 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:

    SELECT DATE_FORMAT(date_field,'%%Y-%%m-%%d %%H:%%i:%%s') FROM table

This conversion will return a datetext format as described in dateutils library(YEAR-MONTH-DAY HOUR:MINUTE:SECOND).

Logging SQL Queries

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