Know SQLAlchemy and want to learn web2py?

We translated all the examples from the SQLAlchmey tutorial. Sorry for spelling errors and cut/paste errors, I am sure there are some. Please report any inaccuracy. This is not a feature comparison but more of a legend for new web2py users with SQLAlchemy experience, it can serve us as a guide to improve the syntax where lacking. We thank the SQLAlchemy team for such a great product and documentation. We have omitted web2py specific syntax from these examples.

SQLAlchemy

from sqlalchemy import create_engine
db = create_engine('sqlite:///:memory:', echo=True)

web2py

from gluon.sql import DAL, Field
db = DAL('sqlite:memory:')

As with SQLALchemy, you can import the DAL gluon.sql from any Python app, even if you do not run web2py. Web2py supports SQLite, MySQL, PostgreSQL, Oracle, DB2, Informix, MSSQL FireBird, Ingres and Google Big Table (not all the queries below work with GAE).

SQLAlchemy

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users_table = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
    Column('password', String)
)

web2py

db.define_table('users',
    Field('name'),
    Field('fullname'),
    Field('password',type='password'))

The 'id' field is implicit although it can be redefined and renamed. web2py has a special 'password' type. It is used when forms are generated from models. web2py also a type='upload' that works like in Django.

SQLAlchemy

metadata.create_all(db)

web2py

(no equivalent)

SQLAlchemy

class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
       return "" % (self.name, self.fullname, self.password)

web2py

# not quite the same but close
User = db.define_table('users',
    Field('name'),
    Field('fullname'),
    Field('password',type='password'))

SQLAlchemy

from sqlalchemy.orm import mapper
mapper(User, users_table)

web2py

(no equivalent)

SQLAlchemy

ed_user = User('ed', 'Ed Jones', 'edspassword')
ed_user.name

web2py

ed_user = User.insert(name='ed',fullname='Ed Jone',password='edspassword')
ed_user.name

SQLAlchemy

from sqlalchemy.ext.declarative import declarative_base

web2py

(no equivalent)

SQLAlchemy

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
       return "" % (self.name, self.fullname, self.password)

web2py

# not quite the same but close
User = db.define_table('users',
    Field('name'),
    Field('fullname'),
    Field('password',type='password'))

SQLAlchemy

users_table = User.__table__

web2py

(no equivalent)

SQLAlchemy

metadata = Base.metadata

web2py

(no equivalent)

SQLAlchemy

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=db)

web2py

(no equivalent)

SQLAlchemy

Session = sessionmaker()

web2py

(no equivalent)

SQLAlchemy

Session.configure(bind=db)  # once db is available

web2py

(no equivalent)

SQLAlchemy

session = Session()

web2py

(no equivalent)

SQLAlchemy

ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)

web2py

ed_user = User.insert(name='ed',fullname='Ed Jone',password='edspassword')

SQLAlchemy

our_user = session.query(User).filter_by(name='ed').first()

web2py

our_user = db(User.name=='ed').select().first()

SQLAlchemy

session.add_all([
    User('wendy', 'Wendy Williams', 'foobar'),
    User('mary', 'Mary Contrary', 'xxg527'),
    User('fred', 'Fred Flinstone', 'blah')])

web2py

User.insert(name='wendy',fullname='Wendy Williams',password='foobar')
User.insert(name='mary',fullname='Mary Contrary', password='xxg527')
User.insert(name='fred',fullname='Fred Flinstone', password='blah')

SQLAlchemy

ed_user.password = 'f8s7ccs'

web2py

ed_user.update_record(password='f8s7ccs')

SQLAlchemy

session.dirty

web2py

(no equivalent)

SQLAlchemy

session.new

web2py

(no equivalent)

SQLAlchemy

session.commit()

web2py

db.commit()

SQLAlchemy

ed_user.name = 'Edwardo'

web2py

ed_user.update_record(name = 'Edwardo')

SQLAlchemy

session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

web2py

db(User.name.belongs(['Edwardo', 'fakeuser'])).select()

SQLAlchemy

session.rollback()

web2py

db.rollback()

SQLAlchemy

fake_user in session

web2py

(no equivalent)

SQLAlchemy

for row in session.query(User).order_by(User.id): 
    print row.name, row.fullname

web2py

for row in db().select(User.ALL,orderby=User.id):
    print row.name, row.fullname

SQLAlchemy

for name, fullname in session.query(User.name, User.fullname): 
    print name, fullname

web2py

for row in db().select(User.name, User.fullname):
    print row.name, row.fullname

SQLAlchemy

for row in session.query(User, User.name).all(): 
   print row.users, row.name

web2py

for row in db().select(User.ALL):
    print row, row.name

SQLAlchemy

from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')
for row in session.query(user_alias, user_alias.name.label('name_label')).all(): 
   print row.user_alias, row.name_label

web2py

(no equivalent)

SQLAlchemy

for row in session.query(User).order_by(User.id)[1:3]: 
   print row

web2py

for row in db().select(User.ALL,orderby=db.user.id)[1:3]: 
   print row

SQLAlchemy

for name in session.query(User.name).filter_by(fullname='Ed Jones'): 
   print name

web2py

for row in db(User.fullname=='Ed Jones').select(User.name): 
   print row.name

SQLAlchemy

for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'): 
   print user

web2py

for name in db(User.name=='ed')(User.fullname=='Ed Jones').select(): 
   print name

SQLAlchemy

query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
query.all()

web2py

query = db(User.name.like('%ed'))
query.select(orderby=User.id)

SQLAlchemy

from sqlalchemy.orm.exc import MultipleResultsFound
try: 
    user = query.one()
except MultipleResultsFound, e:
    print 'error'

web2py

users = db(query).select(limitby=(0,2))
user = users.first()
if users>2:
    print 'error'

SQLAlchemy

for user in session.query(User).filter("id<224").order_by("id").all(): 
    print user.name

web2py

for user in db(User.id<224).select(orderby=User.id):
    print user.name

SQLAlchemy

session.query(User).filter("id<:value and name=:name").\
    params(value=224, name='fred').order_by(User.id).one()

web2py

(value, name) = (224, 'fred')
db(User.id<value)(User.name==name)(orderby=User.id,limitby=(0,1)).first()

SQLAlchemy

session.query(User).from_statement("SELECT * FROM users where name=:name").params(name='ed').all()

web2py

(no equivalent but DAL and SQL syntax can be mixed in web2py)

SQLAlchemy

session.query(User).filter(User.name.like('%ed')).count()

web2py

db(User.name.like('%fred')).count()

SQLAlchemy

session.query(User.id, User.name).filter(User.name.like('%ed')).count()

web2py

db(User.name.like('%ed')).count()

SQLAlchemy

session.query(User.name).group_by(User.name).count()

web2py

db().select(User.id.count(),groupby=User.name)

SQLAlchemy

from sqlalchemy import func
session.query(func.count(User.name), User.name).group_by(User.name).all()

web2py

db().select(User.name,User.name.count(),groupby=User.name)

SQLAlchemy

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation, backref
class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relation(User, backref=backref('addresses', order_by=id))

    def __init__(self, email_address):
        self.email_address = email_address

    def __repr__(self):
        return "" % self.email_address

web2py

Address = db.define_table('addresses',
    Field('email_address',notnull=True),
    Field('user',User))

SQLAlchemy

metadata.create_all(db)

web2py

(no equivalent)

SQLAlchemy

jack = User('jack', 'Jack Bean', 'gjffdd')
jack.addresses

web2py

jack = User.insert(name='jack',fullname='Jack Nean', password='gjffdd')
jack.addresses.select()

SQLAlchemy

jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]

web2py

Address.insert(user=jack,email_address='jack@google.com')
Address.insert(user=jack,email_address='j25@yahoo.com')

SQLAlchemy

jack.addresses[1]

web2py

jack.addresses.select()[1]

SQLAlchemy

jack.addresses[1].user

web2py

jack.addresses.select()[1].user

SQLAlchemy

session.add(jack)
session.commit()

web2py

(no equivalent)

SQLAlchemy

jack = session.query(User).filter_by(name='jack').one()

web2py

jack = db(User.name=='jack').select().first()

SQLAlchemy

from sqlalchemy.orm import eagerload

web2py

SQLAlchemy

jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one()

web2py

jack = db(User.name=='jack')(User.id==db.addresses.user).select()

SQLAlchemy

for u, a in session.query(User, Address).filter(User.id==Address.user_id).\
        filter(Address.email_address=='jack@google.com').all():   
    print u, a

web2py

for row in db(User.id==Address.user)\
        (Address.email_address=='jack@google.com').select()   
    print row.users, row.addresses

SQLAlchemy

from sqlalchemy.orm import join
session.query(User).select_from(join(User, Address)).\
        filter(Address.email_address=='jack@google.com').all()

web2py

for row in db(User.id==Address.user)\
        (Address.email_address=='jack@google.com').select()   
    print row.users, row.addresses

SQLAlchemy

from sqlalchemy.orm import aliased
adalias1 = aliased(Address)
adalias2 = aliased(Address)
for username, email1, email2 in \
    session.query(User.name, adalias1.email_address, adalias2.email_address).\
    join((adalias1, User.addresses), (adalias2, User.addresses)).\
    filter(adalias1.email_address=='jack@google.com').\
    filter(adalias2.email_address=='j25@yahoo.com'):
    print username, email1, email2

web2py

adalias1 = Address.with_alias('adalias1')
adalias2 = Address.with_alias('adalias2')
for row in db().select(User.name, adalias1.email_address, adalias2.email_address,
                       left = [adalias1.on((adalias1.user==User.id)&\
                               (adalias1.email_address=='jack@google.com')),
                               adalias2.on((adalias2.user==User.id)&\
                               (adalias2.email_address=='j25@google.com'))]):
    print row.users.name, row.adalia1.email_address, row.adalia2.email_address

SQLAlchemy

from sqlalchemy.sql import func
stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()
for u, count in session.query(User, stmt.c.address_count).\
    outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): 
    print u, count

web2py

for row in db(User.id==Address.id).select(User.ALL,Address.id.count(),groupby=Address.user):
    print row.users.name, row._extra[Address.id.count()]

SQLAlchemy

stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
adalias = aliased(Address, stmt)
for user, address in session.query(User, adalias).join((adalias, User.addresses)): 
    print user, address

web2py

for row in db(Address.email_address != 'j25@yahoo.com')(Address.user==User.id).select():
    print row.users.name, row.addresses.email_address

SQLAlchemy

from sqlalchemy.sql import exists
stmt = exists().where(Address.user_id==User.id)
for name, in session.query(User.name).filter(stmt):   
    print name

web2py

(not sure what it does)

SQLAlchemy

for name, in session.query(User.name).filter(User.addresses.any()):   
    print name

web2py

(no sure what it does)

SQLAlchemy

for name, in session.query(User.name).\
    filter(User.addresses.any(Address.email_address.like('%google%'))):   
    print name

web2py

for row in db(User.id.belongs(db(Address.email_address.like('%google%'))\
                                  ._select(Address.user))).select(User.ALL):
    print user.name

SQLAlchemy

session.query(Address).filter(~Address.user.has(User.name=='jack')).all()

web2py

db(Address.user==User.id)(User.name!='jack').select(Address.ALL)

SQLAlchemy

session.delete(jack)
session.query(User).filter_by(name='jack').count()

web2py

del User[jack]
db(User.name=='jack').count()

SQLAlchemy

session.query(Address).filter(
    Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
 ).count()

web2py

db(Address.email_address.belongs(['jack@google.com', 'j25@yahoo.com']).count()

SQLAlchemy

session.close()  # roll back and close the transaction
from sqlalchemy.orm import clear_mappers
clear_mappers() # clear mappers

web2py

db.rollback()
db.close()

SQLAlchemy

mapper(User, users_table, properties={    
    'addresses':relation(Address, backref='user', cascade="all, delete, delete-orphan")
})

web2py

# at definition of table, cannot be changed
Address = db.define_table('addresses',
                          Field('email_address'),
                          Field('user',User,ondelete="cascade"))

SQLAlchemy

jack = session.query(User).get(5)

web2py

jack = User[5]

SQLAlchemy

del jack.addresses[1]

web2py

del Address[jack.addresses.select()[1].id]

SQLAlchemy

session.delete(jack)

web2py

del User[jack.id]

SQLAlchemy

# association table
post_keywords = Table('post_keywords', metadata,
    Column('post_id', Integer, ForeignKey('posts.id')),
    Column('keyword_id', Integer, ForeignKey('keywords.id'))
)

class BlogPost(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    # many to many BlogPost<->Keyword
    keywords = relation('Keyword', secondary=post_keywords, backref='posts')

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body

    def __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)

class Keyword(Base):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)

    def __init__(self, keyword):
        self.keyword = keyword

web2py

BlogPost = db.define_table('blog_post',
    Field('user', User),
    Field('headline',notnull=True),
    Field('body','text'))

Keyword = db.define_table('keywords',
    Field('keyword', length=50, notnull=true, unique=True))

PostKeyword = db.define_table('post_keywords',
    Field('post',Post),
    Field('keyword',Keyword))

SQLAlchemy

from sqlalchemy.orm import backref
# "dynamic" loading relation to User
BlogPost.author = relation(User, backref=backref('posts', lazy='dynamic'))

web2py

(no equivalent because in web2py every expression always maps in a single SQL query, on RDBS)

SQLAlchemy

metadata.create_all(db)

web2py

(no equivalent)

SQLAlchemy

wendy = session.query(User).filter_by(name='wendy').one()

web2py

wendy = db(User.name=='wendy').select().first()

SQLAlchemy

post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
session.add(post)

web2py

post = BlogPost.insert(headline="Wendy's Blog Post",body="This is a test",user=wendy)

SQLAlchemy

post.keywords.append(Keyword('wendy'))
post.keywords.append(Keyword('firstpost'))

web2py

PostKeywords.insert(keyword=Keyword.insert(keyword='wendy'),post=post)
PostKeywords.insert(keyword=Keyword.insert(keyword='firstpost'),post=post)

Again in web2py one expression never maps into more than one SQL operation. In this case one have to be explicit about performing two inserts per line.

SQLAlchemy

session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all()

web2py

db(BlogPost.id.belongs(db(PostKeyword.keyword==Keyword.id)\
                         (Keyword.keyword=='firstpost') \
                       ._select(PostKeyword.post))).select()

SQLAlchemy

session.query(BlogPost).filter(BlogPost.author==wendy).\
filter(BlogPost.keywords.any(keyword='firstpost')).all()

web2py

db(BlogPost.id.belongs(db(PostKeyword.keyword==Keyword.id)\
                         (Keyword.keyword=='firstpost') \
     ._select(PostKeyword.post)))(BlogPost.user=wendy).select()

SQLAlchemy

wendy.posts.filter(BlogPost.keywords.any(keyword='firstpost')).all()

web2py

db(BlogPost.id.belongs(db(PostKeyword.keyword==Keyword.id)\
                         (Keyword.keyword=='firstpost') \
     ._select(PostKeyword.post)))(BlogPost.user=wendy).select(BlogPost.ALL)