1.4.6 Executing SQL

The execute() method is used to retrieve information from a database and looks like this:

cursor.execute("SELECT * FROM Test")

cursor.execute("INSERT INTO Test (dateColumn, numberColumn) VALUES ('2004-11-8', 4)")

web.database uses ? style parameter substitution. This means the execute() method can take a list of values to substitute for any unquoted ? symbols in the SQL string.

values = [datetime.date(2004,11,8), 4]
cursor.execute("INSERT INTO Test (dateColumn, numberColumn) VALUES (?, ?)", values)

cursor.execute(
    sql="UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?",
    parameters=[datetime.date(2004,11,8), 4, "where string"]
)

At first sight the parameter substitution doesn't seem to offer much of an advantage but in fact it is extremely useful because web.database will automatically convert the values to SQL for you so that you don't need to convert them yourself.

Note: Parameter substitution can be done for any value which needs conversion. This includes default values in CREATE statements and values in INSERT and UPDATE statements or WHERE clauses. Parameter substitutions are not available for strings which do not need conversions such as table names, column names etc.

The module also supports executemany(). This method does the same as execute() except it executes once for each sequence in the values parameter. For example:

cursor.executemany(
    sql="UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?",
    parameters=[
       [datetime.date(2004,11,8), 4, "string1"],
       [datetime.date(2004,11,8), 5, "string2"],
       [datetime.date(2004,11,8), 6, "string3"],
    ]
)

In web.database this is no more efficient than executing a number of normal cursor.execute() methods.

web.database also provides cursor abstraction methods which provide a functional interface to execute SQL. For example here we insert some values into a table.

cursor.insert(
    table   = 'testTable',
    columns = ['col1','col2'],
    values  = ['val1', 2],
)

Cursor abstraction methods exist for all the SQL commands supported by web.database. These are described later.

The cursor() method takes the following options and will return the appropriate cursor object:

cursor( [execute=True], [format='tuple'], [convert=True], [mode='portable'])

The default values which the cursor abstraction methods will take for the values of execute, format and convert can be set using this method.

format: This can be 'tuple' to return the results as a tuples, 'text' to return as text wrapped to 80 characters for display in a terminal, 'dict' to return the results as dictionaries or 'object' to return the results as result objects to be treated as dictionaries, tuples or via attribute access.
convert: Convert the results to standard formats (should be True for most users)
execute: Used in the cursor SQL methods. If True then rather than returning an SQL string, the methods execute the results
mode: The default mode for the execute() method. Can be 'portable' to use the SQL abstraction methods or 'direct' to send the SQL directly to the underlying cursor.