Let's begin - simple operations

First you initialize the module - connect to the database and create sqlabstr.Database instance. The following example assumes use of pyPgSQL module to connect to postgres database.

from sqlabstr import Database
conn = PgSQL.connect('::mydb')
db = Database(db)

The db object now contains the database instance - all tables of the current schema in database. Our test database contains tables foo, bar and test. In the beginning we will try to print all rows from the table 'foo'.

for row in db.foo():
     print row['nick'],row['name']

This is how you create a select instance. Just call the table:

db.foo()

The select instance represents an SQL SELECT query. You can add new conditions to it, sort it, join to it other tables. To read it, use it as an iterator - it executes the query and returns all relevant lines. We will get to the select instance later.

If the result of the query is only one line, it can be accessed by a Data attribute. The module checks, that really only one line is returned. If no line is returned, the Data attribute is None, and the result is False. Alternatively, one-line result can be accessed by [] operator:

query = db.foo(userid=andrewid)
if query:
    details = query.Data
    print details.nick, details.name, details.email
# Or alternatively
if query:
    print query['nick'], query['name'], query['email']
# NOT NOT NOT
if query:
    print query.nick ### XXX THIS DOES NOT WORK

Insert statement

The only simple operation in SQL world is an INSERT statement.

db.bar.insert(nick='andy',name='Andrew',email='nospam-please')

This statment inserts a new line to the table 'bar'. If the table has OIDs, the insert call returns an object, that - when accessed - turns to be the row, that you have just inserted into the database. This feature comes handy if you have columns with default values and serial numbers.

andyid = db.bar.insert(nick='andy',name='Andrew',www='nospam-please').userid

Commit/rollback

After making changes to database you are likely to commit/rollback. The database instance provides methods - commit & rollback.

try:
    db.foo.insert(name='andy')
    db.foo.insert(name='brown')
    do_something_that_may_fail
except:
    db.rollback()
    raise
else:
    db.commit()

Calling server-side fuctions

Sometimes it is better to write a code, that would run directly on the server. Sqlabstr finds all functions in the current schema and imports the functions as attributes of the database instance. Suppose that we have a function insert_new_user(text,text).

db.insert_new_user('andy','Andrew')

---