1.4.13.5 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 cursor.column() helper method:

column( name, type[, required=0][, unique=0][, primaryKey=0][, foreignKey=None][, default=None])

Return a column tuple suitable for use in the columns tuple used in the create() method.

Bool, Float, Binary and Text columns cannot be used as primary keys or unique columns. Binary and Text columns cannot have default values.

name: The name of the field as a string.
type: The field type. This can take one of the values: 'Bool', 'String', 'Text', 'Binary', 'Long', 'Integer', 'Float', 'Date', 'Time', 'Datetime'
required: Whether or not the field is required. Setting to True means the field cannot have NULL values.
unique: Set to True if the value must be unique. Two fields in the column cannot have the same value unless that value is NULL
primaryKey: The field is to be used as a primary key, the field has the same behaviour as being unique and required but no default value can be set
foreignKey: The field is to be used as a foreign key, the value should be the name of the table for which this is a child table. Note: There is no need to specify the column name as tables can only have one primary key.
default: The default value for the field to be set to. If not specified the default is NULL

For example:

cursor.create(
    table = 'Person',
    columns = [
        cursor.column(name='LastName',  type='String' ),
        cursor.column(name='FirstName', type='String' ),
        cursor.column(name='Address',   type='String' ),
        cursor.column(name='Age',       type='Integer'),
    ],
)

The create() method takes the table name as the first argument and then a sequence column dictionaries returned from the cursor.column() method as the second argument.

Here is a more complicated example:

cursor.create(
    table = 'Person',
    columns = [
        cursor.column(name='LastName',  type='String', required=True, unique=True),
        cursor.column(name='FirstName', type='String', default='Not Specified'),
        cursor.column(name='Address',   type='String' ),
        cursor.column(name='Age',       type='Integer'),
    ],
)

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.

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, columns [, values=[]] [, execute] )

Create table with fields specified by fields. fields is a tuple of field tuples which can be obtained as follows:

columns = [
    cursor.column( field options... ),
    cursor.column( field options... ),
    cursor.column( field options... ),
    cursor.column( field options... ),
]

table: The table name as a string.
columns: A sequence of field tuples returned by cursor.column()
values: A sequence of values to substitute for default values in the columns
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True