## 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")

or

    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)

or

    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.