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... )
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
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
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:
|table, fields [, autoExecute])|
(name, type, required, default, unique). autoExecute is optional and can be
Falseand by default takes the value of the
The fields tuple can take a number of options. Only the options
type are required, the others are optional:
Truemeans the field cannot have
Noneto represent a NULL value)
Trueif the value must be unique
See About this document... for information on suggesting changes.