In this section we will work with a very simple database, which we will call PersonDb,
which will have just one table, called Person. The code in the following section is in the
SQL language not Python, though later we go on to show how Python can be used to
interact via SQL. To create a table, we use the SQL CREATE command. The simplest
version is where just the column names and types are specified. For example, suppose that
in the Person table there are three columns, firstName, lastName (both strings) and
birthYear (an integer). We could create the table via:
The SQL keywords are case-insensitive although they are often written in all capital
exact SQL implementation being used, but it is probably safest to assume that they are
also case-insensitive. The SQL type for text strings is tricky. In most SQL
implementations there are several alternatives. TEXT is usually one of the alternatives,
and it means that the strings are of unlimited length. If we had put TEXT(30) (for
example) it would mean that the strings are of length no more than 30. And an alternative
string type to TEXT would be VARCHAR, which is somewhat less understandable, but
has been around longer in SQL implementations.
If a given column is mandatory, whereby every record has to have a value for that
column, then the NOT NULL descriptor is used. For example, if the firstName and
lastName were mandatory in the Person table then we would have:
CREATE TABLE Person (
firstName TEXT(30) NOT NULL,
lastName TEXT(30) NOT NULL,
birthYear INT
);
Here we have also changed the strings to have a maximum length of 30.
A
primary key for a table is a list of one or more columns for which the corresponding
values uniquely identify a record, and we can specify which column or columns these are.
For example, perhaps we think that the firstName and lastName uniquely identify a person
in our database (although that is probably not a very good assumption). Then we would
have:
CREATE TABLE Person (
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL,
birthYear INT,
PRIMARY KEY (firstName, lastName)
);
It is this final version that we will use here. We have changed the string type from
TEXT to VARCHAR because MySQL does not allow the former for any column that is
part of the primary key. With SQLite we could have stayed with TEXT(30) or indeed just
TEXT. It is these little annoyances that make life difficult if one tries to support more than
one SQL implementation.
Do'stlaringiz bilan baham: