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.
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:
By the time you reached this point, you know everything you need to handle relational databases.