### 1.4.2.1 Understanding Field Types

The information you send to the database and the information retrieved from the database will be automatically converted to the correct formats so that you can treat the values as normal Python objects.

Traditional SQL databases usually have support for a number of different fields. Date fields behave differently to integer fields for example. All of the fields are set using an SQL representation of the data in the form of a string and all of the queries from the database return strings.

The `web.database` module provides ten field types and rather than passing information to and from the database as specially SQL encoded strings, you can also pass it as a python data structure. For example to set an `Integer` field you could give the cursor an integer. To set a `Date` field you would give the cursor a `datetime.date` object. The `web.database` cursor would do all the conversion for you.

Furthermore when you retrieve information from the database the `cursor` will convert the strings recieved back into Python objects so that you never need to worry about the encodings.

This doesn't sound like too much of a big deal but because different databases handle different datatypes in slightly different ways your SQL could have different results on different databases. Programming with a `web.database` `cursor` removes these inconsistencies.

Here are the supported datatypes:

Type | Description
--- | ---
``Bool`` | True or False
``Integer`` | Any Python integer (not Python Long or Decimal)
``Long`` | Any Python long integer between -9223372036854775808 and 9223372036854775807
``Float`` | Any Python floating point number
``String`` | A string of 255 characters or less (Not unicode?) [a]
``Text`` | A 24-bit string [b]
``Binary`` | A 24-bit binary string [b]]
``Date`` | Any valid Python `datetime.date` object. Takes values in the form of python `datetime` objects. Only stores days, months and years, any other information is truncated. Dates from `0001-01-01` to `9999-12-31`. [c]
``Time`` | Any valid Python `datetime.time` object. Takes values in the form of python `datetime` objects. Only stores hours, minutes and seconds, any other information is truncated. [c]
``Datetime`` | Any valid Python `datetime.datetime` object. Takes values in the form of python `datetime` objects. Only stores days, months, years, hours, minutes and seconds, any other information is truncated. [c]

[a] Some databases make a distinction between short strings (often named VARCHAR) and long strings (often TEXT). Short string fields are normally faster and so a distinction is also made in this specification.

[b] Although Python supports strings of greater than 24 bit, a lot of databases do not and so in order to be compatible with those databases Binary and String objects should be no longer than 24 bit.

[c] Although Python < 2.3 does not support datetime objects, pure Python compatible libraries exist for Python < 2.3 and these can be used instead so it makes sense to use the standard Python types where possible.

The values you pass to the `cursor.execute()` method should be of the correct type for the field they are representing. The values returned by the `cursor.fetchall()` method will automatically be returned as the appropriate Python type.

For example, `Bool` fields should have the Python values `True` or `False`, `Long` fields should be a valid Python `long` etc.

There are some exceptions:

`String` fields should contain Python strings of 255 characters of less. `Text` fields should contain 24 bit strings less. For strings longer than this length you should consider saving the string in a file and saving the filename in the database instead.

`Date`, `Datetime` and `Time` fields take Python `datetime.date`, `datetime.datetime` and `datetime.time` objects respectively.

Unfortunately Python 2.2 and below do not support the `datetime` module. However `web.database` uses a compatibility module that behaves closely enough for most purposes. Simply import `web.database` and then you can import the datetime module automatically. This is what it looks like at the Python prompt:

    Python 2.2.3 (#42, May 30 2003, 18:12:08) [MSC 32 bit (Intel)] on win32
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import \module{web.database}
    >>> import datetime
    >>> print datetime.date(2004,11,24)
    2004-11-24
    >>>