1.4.4.6 Creating Tables

To create a table in SQL you would use the following command:

CREATE TABLE table_name
(
    column_name1 data_type,
    column_name2 data_type,
    etc...
)

For example:

CREATE TABLE Person 
(
    LastName varchar,
    FirstName varchar,
    Address varchar,
    Age int
)

To create the table above using a web.database cursor we would use the following code:

cursor.create(
    'Person',
    (
        ('LastName', 'String'),
        ('FirstName', 'String'),
        ('Address', 'String'),
        ('Age', 'Integer'),
    ) 
)

The create() method takes the table name as the first argument and then a tuple of field tuples as the second argument. Each one of these field tuples can have the entries (name, type, required, default, unique).

The first entry is the name of the column and the second entry is the column field code. Column field codes are different for different databases but you can always get the correct one of the cursor variables. There are eight field types available and these are listed in the previous section.

The next three entries of the field tuples are optional. required specifies whether the field is required. If it set to True then the field cannot take the value NULL. default is the value to be used if no value is specifed for the field and if unique is set to True all values in the column have to be different. Specifying these values as None is the same as leaving them out.

Here is a more complicated example:

cursor.create(
    'Person',
    (
        ('LastName', 'String', True, None, True),
        ('FirstName', 'String', False, cursor.StringConverter().sql('Not Specified'), False),
        ('Address', 'String'),
        ('DateOfBirth', 'Date'),
    ) 
)

In this example we specified that the LastName must always be entered, does not have a default value and must be unique so that no two people in the database can have the same LastName. We have also specified that FirstName is not required and is not unique. If no value is entered for FirstName the field should be set to the string Not Specified.

Note: Note the use of the cursor.StringConverter() object to encode the string 'Not Specified' to sql to be used in the create statement. All of the field types have a corresponding encode() and decode() method.

In mysql This would create the following table:

mysql> describe Person;
+-------------+--------------+------+-----+---------------+-------+
| Field       | Type         | Null | Key | Default       | Extra |
+-------------+--------------+------+-----+---------------+-------+
| LastName    | varchar(255) |      | PRI |               |       |
| FirstName   | varchar(255) | YES  |     | Not Specified |       |
| Address     | varchar(255) | YES  |     | NULL          |       |
| DateOfBirth | date         | YES  |     | NULL          |       |
+-------------+--------------+------+-----+---------------+-------+
4 rows in set (0.00 sec)

The create() method of a web.database cursor looks like this:

create( table, fields [, autoExecute])

table should be the table name as a string. fields should be a tuple of field tuples in the form (name, type, required, default, unique). autoExecute is optional and can be True or False and by default takes the value of the cursor object.

The fields tuple can take a number of options. Only the options field and type are required, the others are optional:

name: The name of the field as a string.
type: The fieldCode for the field. This can take one of the values: 'Char', 'String', 'Text', 'Integer', 'Float', 'Date', 'Time', 'DateTime'
required: Whether or not the field is required. Setting to True means the field cannot have NULL values.
default: The default value of the field (can be None to represent a NULL value)
unique: Set to True if the value must be unique

See About this document... for information on suggesting changes.