1.5.2 Introductory Example

Below is about the simplest possible example where a database object named MyDatabase is created. The database object is connected to an SQLite database named object-simple.db but could equally well be a MySQL database or ODBC supported database like MS Access.

import web.database, web.database.object                                    
                                                                            
connection = web.database.connect(type="sqlite",database="object-simple.db")
cursor = connection.cursor()                                                
                                                                            
person = web.database.object.Table("Person")                                
person.addColumn(web.database.object.String(name="firstName"))              
person.addColumn(web.database.object.String(name="surname"))                

database = web.database.object.Database()                                   
database.addTable(person)                                                   
database.init(cursor)

This first lines import the modules we need and make the web.database connection. We could have made any database connection supported by the web.database module. Below are some other examples for the 3rd line.

connection = web.database.connect(type="odbc", database="AccessDatabase")
connection = web.database.connect(type="mysql", host="pythonweb.org", user="james", password="hello")

The database will contain one table named Person. The Person table has two columns, both of which are String columns. One is named firstName and the other surname. All web.database.object column objects must take a name parameter and this is used as the column name.

Once we have finished defining our table we create a web.database.object.Database() which will be the object we use to manipulate the database. We add out table definition to the database definition using database.addTable(person) and then initilise the database to associate it with the live databaseusing database.init(cursor).

Warning: Once a database object is initialised you cannot add any more tables or modify the database's structure in any way.

Now we have defined and initialised out database we can start using it. If the table does not already exist in the live database we need to create it as follows:

if not database.tablesExist():
    database.createTables()
    print "Created Table"

This command creates every table the database needs (in our case just the one). If you decide to change the structure of the tables at a later date after you have created the tables in the live database you will need to remove them all using database.dropTables() and recreate them from scratch. This means you would loose all the information so it is important to decide on the correct structure before creating the tables.

All information in the database can be accessed through a dictionary-like interface. For example the database object acts like a dictionary of tables and each table acts like a dictionary of rows. Each row acts like a dictionary of field values.

Now we have created the table we are free to add, edit and remove data. Following on from the previous example.

>>> john = database['Person'].insert(firstName="John", surname="Smith")
>>> print john['firstName']
John
>>> print john['surname']
Smith
>>> john['surname'] = 'Doe'
>>> print john['surname']
Doe
>>> print john['rowid']
1

In this way you can create and modify the table information. Take note of the line »> john['rowid']. Each new object (which is equivalent to a row in the table) is given a unique integer number named the rowid by which it can be identified.

We can use this rowid to retrieve John Smith's information from the database at a later time. There are two ways to retrieve rows from the table using the rowid:

>>> row1 = database['Person'][1]
>>> row2 = database['Person'].row(1)
>>> print row1 == row2 == john
1

Once you have made changes to the database you will need to commit your changes using connection.commit() otherwise your changes may be lost. By the end of this session our database table looks like this:

# Tables in the 'test' database
+----------------+
| Tables_in_test |
+----------------+
| Person         |
+----------------+

# The Person table
+-------+-----------+---------+
| rowid | firstName | surname |
+-------+-----------+---------+
| 1     | John      | Doe     |
+-------+-----------+---------+