06.05.2010

For this I'll be using ipython (Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56) and SQLAlchemy version 0.6 (with mysql-python ).
We assume that we already have created a 'test' database with a table 'users' in it (for the table structure see below)
Because we're dealing with already existing data, we'll be using SqlSoup , which is a convenient way to access existing database tables without having to declare table or mapper classes ahead of time. It is built on top of the SQLAlchemy ORM and provides a super-minimalistic interface to an existing database.

from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup('mysql+mysqldb://root:password@localhost/test?charset=utf8')
# or db = SqlSoup(engine) if you have engine previously defined

users_tbl = db.users
In [73]: type(users_tbl)
Out[73]: <class 'sqlalchemy.ext.sqlsoup.TableClassType'>

First, get all users (we have just two there at this time):

In [17]: [ (user.id,user.name,user.age) for user in users_tbl.all() ]
Out[17]: [(1L, u'John Doe', 34), (2L, u'Tom Bruise', 23)]

Get after primary key (in this case called id)

In [21]: users_tbl.get(2)
Out[21]: MappedUsers(id=2L,name=u'Tom Bruise',age=23)

Filter after some conditions:

- after name only (using filter_by)

In [41]: users_tbl.filter_by(name='Tom Bruise').one()
Out[41]: MappedUsers(id=2L,name=u'Tom Bruise',age=23)

- after name only (using filter and LIKE)

In [57]: users_tbl.filter(users_tbl.name.like("Tom%")).one()
Out[57]: MappedUsers(id=2L,name=u'Tom Bruise',age=23)

- after name and age (using filter, or_, and_)

from sqlalchemy import or_, and_, desc
whereRule = and_(users_tbl.name=='Tom Bruise', users_tbl.age==23)
In [44]: users_tbl.filter(whereRule).all()
Out[44]: [MappedUsers(id=2L,name=u'Tom Bruise',age=23)]

- after name (using IN)

In [62]: users_tbl.filter(users_tbl.name.in_(['Tom Bruise', 'Bruce Lee', 'John Doe'])).all()
Out[62]: 
[MappedUsers(id=1L,name=u'John Doe',age=34),
 MappedUsers(id=2L,name=u'Tom Bruise',age=23)]

eduser = users_tbl.get(2) # grab the user
eduser.age = 12 # modify age
db.commit() # save

In [70]: users_tbl.insert(name="Ronnie James Dio", age=54)
Out[70]: MappedUsers(id=None,name='Ronnie James Dio',age=54)
 
In [71]: db.commit()  # actually commits the data

deluser = users_tbl.get(2) # grab the user using primary key or any other method (filter, filter_by, etc)
db.delete(deluser)
db.commit()

http://www.sqlalchemy.org/docs/ | SQLAlchemy documentation for 0.6
http://www.sqlalchemy.org/docs/ormtutorial.html#ormtutorial-querying | SQLAlchemy querying
http://www.sqlalchemy.org/docs/reference/ext/sqlsoup.html | SqlSoup