Some of the information here may be outdated, please check the book instead

Edit page

Title:

Security Code:

Body: (use this wiki markup)

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....

AlterEgo

old web2py blog

Useful Links

Edit page