wiki:Tutorial
Last modified 6 years ago Last modified on 10/19/08 10:33:45

Preparation

Create a Database

...if you do not have it already.

CREATE TABLE cities (name character varying(80), location point);

Create configuration XML file

This example is a cut-back version of weather.rdb

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE rdb SYSTEM "sqlface.dtd">
<rdb connect="dbname=city_weather" title="Weather Data per City">
  <query name="cities" expression="SELECT name,location FROM cities">
    <out>name</out>
    <out>location</out>
  </query>
  <statement name="add_city" expression="INSERT INTO cities (name,location) VALUES (%s, %s)">
    <in>name</in>
    <in>location</in>
  </statement>
</rdb>

As you can see the root XML tag is rdb having two attributes: connect and title for describing the DBAPI2 connect string and a title for the documentation respectively. There can be several query and statement tags. SQLFace will create a Query command object for the namespace cities that returns data after executing the expression, and a Statement command object for the namespace add_city to just execute the expression. The in and out tags are only present for generating documentation.

Coding

Initialize SQLFace

In order to use SQLFace you need to import a DBAPI2 compliant driver. In the examples psycopg2 is used as follows:

import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)

This UNICODE stuff is here to make sure you will get unicode type variables instead of plain string. No mess with encodings.

Construct an SQLFace object using the DB driver and the config file:

from sqlface import SQLFace

rdb = SQLFace(psycopg2, "weather.rdb")

Use the special SQL interface

Fetch the cities from database, and print them:

print 'cities:' + str(rdb.cities())

You have millions of cities, and got out of memory error? Fetch only a few records at a time, and print them in an iteration. In this case, we fetch 2 records at a time:

print '\nweather fetching using iteration:'
for i in rdb.cities.iteritems(2):
  print "item:",i

Add a new city to the database:

rdb.add_city(u'Budapest', '(1,1)')

By the time you reached this point, you know everything you need to handle relational databases.