First Steps

That's enough theory; let's create a database and start doing things with it. As a running example, we will use the predictions for regional

Region

Central Africa

Population

330993

Southeastern Africa 743112 Northern Africa 1037463

Southern Asia Asia Pacific

2051941

785468

687630

1362955

593121

223427

661157

387933

100562

Middle East Eastern Asia

South America Eastern Europe North America Western Europe Japan

Figure 15.2: Estimated world population in 2300

populations in the year 2300 shown in Figure 15.2, which is taken from http://www.worldmapper.org. (These values are shown graphically in Figure 15.3, on the next page.)

As promised earlier, we start by telling Python that we want to use sqlite3 by importing the database API:

>>> import sqlite3 as dbapi

Next we must make a connection to our database by calling the database module's connect method. This method takes one string as a parameter, which identifies the database we want to connect to. Since SQLite stores each entire database in a single file on disk, this is just the path to the file. If the database does not exist, it will be created.

Once we have a connection, we need to get a cursor. Like the cursor in your editor, this keeps track of where we are in the database so that if several programs are accessing the database at the same time, the database can keep track of who is trying to do what:

Download db/db_import.cmd

Download db/db_connect.cmd

Download db/db_cursor.cmd

Figure 15.3: World populations in 2300 shown graphically

We can now actually start working with the database. The first step is to create a table to store the population data. To do this, we have to describe the operation we want in SQL, put that SQL in a string, and tell the database to execute that string. The general form of the SQL statement for table creation is as follows:

CREATE TABLE TableName(ColumnName Type, ColumnName Type, ...)

where the names of tables and columns are like the names of variables in a program and the types are chosen from the types the database supports (which we will talk about in a couple of paragraph). To create a two-column table to store region names as strings and projected populations as integers, we use this:

Download db/db_create.cmd

>>> cur.execute( 'CREATE TABLE PopByRegion(Region TEXT, Population INTEGER)') <sqlite3.dbapi2.Cursor object at 0x00AEEC50>

Our table is called PopByRegion; as you can see, executing the command returns a cursor object, which in this case we don't actually need.

The most commonly used data types in SQLite databases are listed in Figure 15.4, on the following page, along with the corresponding Python data types. A few of these deserve some more explanation:

• Python stores integers using a single 32-bit word of memory if the value will fit into it or a more complex multiword structure if it will not. When fetching values from a database, the sqlite3 library decides which to use based on the size of those values.

Type

Python Equivalent

Use

NULL

NoneType

Means "know nothing about it"

INTEGER

int or long

Integers

REAL

float

8-byte floating-point numbers

TEXT

unicode or str

Strings of characters

BLOB

buffer

Binary data

Figure 15.4: SQLite data types

Figure 15.4: SQLite data types

• As we said way back in Section 3.1, Strings, on page 39, Python normally stores strings using ASCII, which represents each character using a single byte and includes only those characters common in English. Python and other (programming) languages use another scheme called Unicode to represent characters from other alphabets like Cyrillic, Arabic, Devanagari, and Thai. By default, sqlite3 represents strings taken from databases as Unicode; we will see in a moment how to get it to use the more familiar str.

• The term BLOB stands for Binary Large OBject, which to a database means a picture, an MP3, or any other lump of bytes that isn't of a more specific type. The Python equivalent is a type we have not seen before called buffer, which also stores a sequence of bytes that have no particular predefined meaning. We will not use BLOBs in our examples, but the exercises will give you a chance to experiment with them.

After we create a table, our next task is to insert data into it. We do this one record at a time using the INSERT command, whose general form is as follows:

INSERT INTO TableName VALUES(Value1, Value2, ...)

As with the parameters to a function call, the values are matched left to right against the columns. For example, we insert data into the Pop-ByRegion table like this:

Download db/db_insert.cmd

>>> cur.execute( 'INSERT INTO PopByRegion VALUES("Central Africa", 330993)') >>> cur.execute( 'INSERT INTO PopByRegion VALUES("Southeastern Africa", 743112)')

>>> cur.execute( 'INSERT INTO PopByRegion VALUES("Japan", 100562)')

Notice that the number and type of values in the INSERT statements matches the number and type of columns in the database table. If we try to insert a value of a different type than the one declared for the column, the library will try to convert it, just as it converts the integer 5 to a floating-point number when we do 1.2 + 5. For example, if we insert the integer 32 into a TEXT column, it will automatically be converted to "32"; similarly, if we insert a string into an INTEGER column, it is parsed to see whether it represents a number. If so, the number is inserted.

if the number of values being inserted does not match the number of columns in the table, the database reports an error, and the data is not inserted. Surprisingly, though, if we try to insert a value that cannot be converted to the correct type, such as the string "string" into an INTEGER field, SQLite will actually do it (though other databases will not).

Was this article helpful?

0 0

Post a comment