NodeBox | Database

Database

Description

The NodeBox Database library offers a very easy way to store data and browse through it. The commands are simple and straightforward (no knowledge of SQL is needed) but the database-capabilities limited. The library builds upon PySQLite, and the data itself is stored in a portable SQLite database. This means the database is just another file in your project folder that you can copy and redistribute along with your NodeBox-scripts.

If you need a viewer application for the SQLite database you can download the free SQLite Database Browser and bundle it with your project. There's also a Firefox extension.

Download

PowerPC | Intel (140KB)
Last updated for NodeBox 1.9.2
Author: Tom De Smedt

Documentation

How to get the library up and running

Put the downloaded database folder in ~/Library/Application Support/NodeBox/ or in the same folder as your script so NodeBox can find the library. Then import the library in your script:

database = ximport("database")

Creating a database

db = database.create(name, overwrite=True)

db = database.connect(name)

The create() command starts a new database file with the given name in the current folder (a .db file extension is added automatically). The connect() command opens an existing database file. Both commands return a Database object which you can use in your script to create tables or retrieve data.

Creating tables and indexes

A database is organised in tables that hold different records of data. For example, a persons table could store name, address, age and email information for each person in the table.

db.create_table(name, fields=[], key="id")

db.create_index(table, field, unique=False, ascending=True)

Once you have a Database object returned from create() or open() you can use its create_table() method to start new tables. You need to supply a name for the table and list of fields in the table. The optional key parameter defines the name of an id-field. An id stores a unique number for each record of data.

Aside from the id-field you can create your own indexes on a table with the create_index() method. Indexes in a database makes things go faster: if we are going to perform a lot of search queries on the age field in the persons table it's a good idea to index that field. When you define the unique parameter as True, the indexed field must be a unique value for each record in the table.

book = database.create("book")
book.create_table("pages", ["title", "text", "image", "pagenumber"])
book.create_index("pages", "title")

Find out what tables are in the database with a for-loop:

for table in book:
    print table.name
>>> pages

Retrieving a table from the database

Once the database has one or more tables you can retrieve these as Table objects. Table objects have various methods to add, edit and retrieve data.

table = db.table_name

table =  db[table_name]

You can retrieve a Table object by accessing the database as a list or by directly typing the name of the table after the dot:

book = database.create("book")
book.create_table("pages", ["title", "text", "image", "pagenumber"])
pages = book["pages"]
pages = book.pages # this is even easier

Adding and editing records of data in a table

table.append(fields)

table.edit(id, fields)

table.remove(id, operator="=", key=None)

The append() method adds a new record with the given fields of data to the table. The fields can be a dictionary or a number of parameters specifying the names of the fields and the data in each field. You don't need to supply a value for the id-field - this is a number that is automatically incremented.

book = database.create("book")
book.create_table("pages", ["title", "text", "image", "pagenumber"])
book.pages.append({
    "title": "Chapter One", 
    "text": "A short intro on databases in NodeBox", 
    "pagenumber": 2
})

Here we used a dictionary to supply the field data, but named parameters are even easier:

book.pages.append(title="Chapter Two", text="", pagenumber=3)

Editing works in a similar way. You can supply fields to update as a dictionary or as named parameters:

book.pages.edit(1, pagenumber=1)

The remove() method deletes the record with the given id from the table. The operator parameter allows you to specify a different comparison than =, >= or < for example. The key parameter allows you to specify a different field to search in.

# Delete the record with id 2: 
book.pages.remove(2)
# Delete all pages beyond the first:
book.pages.delete(2, comparison=">=", key="pagenumber")

Find out what fields are in the table with the fields() method:

print book.pages.fields()
>>> ["id", "title", "text", "image", "pagenumber"]

Retrieving data from a table

table.find(q, operator="=", fields="*", key=None)

table.all()

The find() method returns records from the table that match a given query. By default, it returns the record whose id is equal to q. The operator parameter allows you to specify a different comparison. The key parameter allows you to specify a different field to search in.

By default, all fields from the record are returned, or you can define a list of field names in the fields parameter.

The q parameter can contain an asterisk (*) at the start or at the end as a wildcard character.

book = db.connect("book")
r = book.pages.find("Chapter *" key="title")
for record in r:
  print record
>>> (1, u'Chapter One', u'A short intro on databases in NodeBox', None, 2)
>>> (2, u'Chapter Two', u'', None, 3)
 
# As you see the returned value is a list of tuple-values, 
# which is easy to unpack:
for id, title, text, image, pagenumber  in r:
  print title
>>> Chapter One
>>> Chapter Two

You can also query fields directly. For example, this does exactly the same as above:

r = book.pages.title("Chapter *")

To get all the records in the table you can use the all() method.

Batch transactions

It's faster to write records to disk in batches of, say, 10 000, than writing each record to the database file individually. Automated scripts that process a lot of records can make use of the commit() method for a significant speed increase:

db.commit(each=0)

When called without parameters, commits any pending transactions. A transaction is a call to a table's append(), edit() or remove() method, or the database's sql() method.

When commit() is called with the each parameter, sets the batch size. By default, transactions are committed instantly, but if you plan to add a lot of records at the same time it's better to commit them in batch.

Do not forget to close the database when you are done with it. This commits any pending transaction that have not yet been written to disk.

db.close()

Exporting the database to XML

db.dump()

The dump() method generates an XML-file with all the database's contents. XML is a standardized format to hold data. It's pretty human readable and supported by many applications.

book = database.connect("book")
book.dump()
xml = open("book.xml").read()
print xml

Executing raw SQL queries

db.sql(query)

Executes an SQL-statement on the database. For example, a SELECT-query returns the list of records found.

book = db.connect("book")
book.sql("select * from pages")