The examples below are from the official SQLAlchemy page. If anything is wrong, incorrect, or simply can be done in less code SQLAlchemy please let me know and I will fix.

Supported backends

SQLAlchemy

SQLite, MySQL, PostgreSQL, Oracle, MSSQL(*), FireBird, Informix, DB2

web2py

SQLite, MySQL, PostgreSQL, Oracle, MSSQL, FireBird, DB2, Informix, Ingres, Google App Engine(2)

(1) = no OR, LIKE, JOIN, nested selects

Connecting

SQLAlchemy

>>> from sqlalchemy import *
>>> from sqlalchemy.sql import select
>>> engine = create_engine('sqlite://file.db')

web2py

>>> from gluon.sql import *
>>> db=DAL('sqlite://file.db')

Tables

SQLAlchemy

>>> metadata = MetaData()
>>> users = Table('users', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
... )
>>> addresses = Table('addresses', metadata, 
...   Column('id', Integer, primary_key=True),
...   Column('user_id', None, ForeignKey('users.id')),
...   Column('email_address', String, nullable=False)
...  )
>>> metadata.create_all(engine)

web2py

>>> db.define_table('users',Field('name'),Field('fullname'))
>>> db.define_table('addresses',Field('user_id',db.user),Field('email_address',notnull=True))

Notes:

• in SQLAlchemy you can access any legacy database
• in web2py you can access legacy databases if tables have an existing unique auto-increment field id and if this field is used for references
• web2py creates the tables if the do not exist and alters table when the definition changes.
• in web2py all tables, fields, etc live in inside a connection object (db in the example) because web2py allows concurrent connections to multiple databases.
• the both support rollback and commit
• web2py can do distributed transactions with PostgreSQL

One can define

users=db.users
addresses=db.addresses

Insert

SQLAlchemy

>>> ins = users.insert(values={'name':'jack', 'fullname':'Jack Jones'})
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'
>>> conn = engine.connect()
>>> result = conn.execute(ins)
>>> result.last_inserted_ids()
1

web2py

>>> id=db.users.insert(name='jack', fullname='Jack Jones')
>>> repr(db._lastsql)
"INSERT INTO users (name, fullname) VALUES ('jack','Jack Jones')"
>>> id
1

select

SQLAlchemy

>>> s = select([users])
>>> result = conn.execute(s)
>>> for row in result:
...     print row
(1, u'jack', u'Jack Jones')
(2, u'wendy', u'Wendy Williams')
(3, u'fred', u'Fred Flintstone')

web2py

>>> s = db().select(db.users.ALL)
>>> for row in result:
...     print row.id, row.name, row.fullname
1 jack Jack Jones
2 wendy 'Wendy Williams
3 fred Fred Flintstone

SQLAlchemy

>>> row = result.fetchone()
>>> print "name:", row['name'], "; fullname:", row['fullname']
name: jack ; fullname: Jack Jones

web2py

>>> row = result[0]
>>> print "name:", row['name'], "; fullname:", row['fullname']
name: jack ; fullname: Jack Jones

SQLAlchemy

>>> s = select([users.c.name, users.c.fullname])
>>> result = conn.execute(s)
>>> for row in result:  
...     print row
(u'jack', u'Jack Jones')
(u'wendy', u'Wendy Williams')
(u'fred', u'Fred Flintstone')
(u'mary', u'Mary Contrary')

web2py

>>> db().select(db.users.name,db.users.fullname)
>>> for row in rows: print row.name, row.fullname
jack Jack Jones
wendy 'Wendy Williams
fred Fred Flintstone

Notes:

• in web2py you can print a row, we just like it better to print field values.

Implicit JOIN

SQLAlchemy

>>> s = select([users, addresses], users.c.id==addresses.c.user_id)
sql>>> for row in conn.execute(s):
...     print row
(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')
(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')
(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

web2py

>>> for row in db(db.users.id==db.addresses.user_id).select():
...     print row.users.name, row.addresses.email_address
jack jack@yahoo.com
jack jack@msn.com
wendy www@www.org
wendy wendy@aol.com

Queries

SQLAlchemy

>>> print users.c.name.like('j%') & (users.c.id==addresses.c.user_id) &  \
...     ((addresses.c.email_address=='wendy@aol.com') | \
... (addresses.c.email_address=='jack@yahoo.com')) \
...     & ~(users.c.id>5) 
users.name LIKE :name_1 AND users.id = addresses.user_id AND 
(addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2) 
AND users.id <= :id_1

web2py

>>> print db.users.name.like('j%') & (db.users.id==db.addresses.user_id) &  \
...     ((db.addresses.email_address=='wendy@aol.com') | \
...     (db.addresses.email_address=='jack@yahoo.com')) \
...     & ~(db.users.id>5) 
users.name LIKE 'j%; AND users.id = addresses.user_id AND 
(addresses.email_address = 'wendy@aol.com' OR addresses.email_address = 'jack@yahoo.com' AND users.id <= :id_1

LEFT JOIN

SQLAlchemy

>>> s = select([users.c.fullname,addresses.email_address],\
...            from_obj=[users.outerjoin(addresses, addresses.c.user_id==users.c.id)])
>>> for row in conn.execute(s): print row
(u'Jack Jones', u'jack@yahoo.com')
(u'Jack Jones', u'jack@msn.com')
(u'Wendy Williams', u'www@www.org')
(u'Wendy Williams', u'wendy@aol.com')
(u'Fred Flintstone', None)
(u'Mary Contrary', None)

web2py

>>> rows=db().select(db.users.fullname, db.addresses.email_address, \
...                  left=db.addresses.on(db.addresses.user_id==db.users.id))
>>> for row in rows: print row.users.fullname, row.addresses.email_address
Jack Jones jack@yahoo.com
Jack Jones jack@msn.com
Wendy Williams www@www.org
Wendy Williams wendy@aol.com
Fred Flintstone None
Mary Contrary None

ordery, groupy and limitby

...

date, datetime manipulation

...

in and belongs

...

upper, lower, like

...

I am tired ... I will finish this some other time....