Some tricks to use when dealing with databases

May 23, 2010

Warning:

  • web2py’s only knwoledge about table structure is from web2py itself.
    to be more specific, from the files created in the folder databases in side web2py’s main directory.
    If you change the tables outside web2py, web2py gets confused, and you have set migrate=False.
  • If your database connection is not established, even by specifying the right connection string, make sure you’ve specified the right port in the connection string, your database may be running on a different port other than the default one.
  • to use a field of type reference, you’ve to reference another field in a table existing in the same database, you can’t reference a field in a table in another database, use field of type ‘integer’ instead and becareful with your queries.
  • It is actually easy to add a new DB. everything is one file gluon/sql.py
    • find a python module that interface to MaxDB
    • identify the connection strings
    • add translation strings (to of the gluon/sql.py) file
    • run the tests (python gluon/sql.py)
  • Database select syntax:
    db(query).select(*fields,orderby=,groupby=,limitby=)
    

    So you can make something like:

     dbref=db 
     qry=db.address.customer==2 
     fields=['number','city','state'] 
     sel=[db.address[field] for field in fields] 
     rows=dbref(qry).select(*sel) # note the * 
    
  • Note that : db((q1) & (q2)).select() is equivalent to db(q1)(q2)
    so:

     row=db((db.people.name==request.args(0))&  
                    (db.people.company_id==db.company.id)).select().first()
      people_name=row.people.name 
      company_name=row.company.name 
    

    Notice that you can can also define :

         people_and_their_companies=db(db.people.company_id==db.company.id) 
         row=people_and_their_companies(db.people.name==request.args (0)).select().first() 
    

    and wow !!! congratulations you’ve just made an automatic inner join :
    then you can do :

         people_name=row.people.name 
         company_name=row.company.name 
    
  • How to define a table with many number of fields easily?

    Do something like this:

    fields=[Field('addr%02i' %i,'string') for i in range(21)]
    db.define_table('name',*fields)
    
  • If multiple applications access the same database, for every table,
    only one app should have migrate=True so you know who is altering tables. It is not necessary that all apps have the same define_table as long as each define_table defines a subset of existing fields.
    This is not a problem as long as migrate=False.
    If you want one app to change a table definition and all apps to
    reflect that change, yes, you have to change them all.
    This is because changing the table definition may break an app.

  • Difference between requires, required, and notnull

  • requires
    

    enforces validation on the form level.
    Most of us are well experienced with it, so no need for more talking about it.

  • # model
    db.define_table('my_table', Field('soso', 'string', notnull=True))
    #controller
    def index():
        
        form = SQLFORM(db.my_table)
        if form.accepts(request.vars, session, dbio=False):
            db.my_table.insert(soso=None)
            response.flash = T('Accepted')
        elif form.errors:
            response.flash = T('Not sccepted')
        else:
            pass 
        rows = db(db.my.id>0).select()
        return dict(form=form, rows=rows)
    
    

    In this example, I used dbio=False with the accepts() function to make automatic database manipulation stop.
    Now, I’ve full control to do what I want to do.
    Trying to do:

    db.my.insert(soso=None)
    

    while using notnull=True, in table definition in my model, will cause the database to refuse the insertion and you’ll get the error flash message:
    ‘not accepted’ as well as an error message ‘enter a value’.
    This shows you that this validation is enforced on the [database level].

    In fact if you changed the code to :

    ......
    if form.accepts(request.vars, session):
         response.flash = ...
    elif form.errors:
         response.flash = ...
    else:
        pass
    
    

    You’ll get the same error when trying to submit the form without any value in it.
    In the former example using dbio=False even if you’re inserting any value it will fail since am always trying to insert Null into the database field using insert() manually.
    I just wanted to show you that validation is enforced on insert() and on database not form.

  • required=True
    

    Is enforced at the level of db.table.insert, tells web2py that some value (including “” and Null) has to be specified.
    So the database will accept Null values and empty strings.

    #model
    db.define_table('my_', Field('soso',required=True), Field('toto'), Field('lolo'))
    #controller
    def index():
        
        form = SQLFORM(db.my_)
        if form.accepts(request.vars, session, dbio=False):
            db.my_.insert(soso='')  # ok
            db.my_.insert(soso=None, lolo='ss', toto='')      #ok
            db.my_.insert(lolo='ss', toto='')  # Not OK 'required field not specified'
            response.flash = T('Accepted')
        elif form.errors:
            response.flash = T('Not sccepted')
        else:
            pass
        rows =db(db.my_.id>0).select()
        return dict(form=form, s=rows)
    

    In the previous example, you see that a required field has to be specified when ever using insert statement.


  • Using db.executesql()

    Sometimes you just need to execute sql your self, then db.executesql happens to be handy in those situations.

    rawrows=db.executesql(yoursql) -> returns [(col_name, value), ((col2_name, value)), ((col3_name, value))]
    

    Notice that rows are not parsed therefore you cannot say
    rawrows[i].colname but you have to say :
    rawrows[i][j], where j is the col number as returned by the query.
    This may seem confusing in the beginning but in fact if you made something like:

    r=db.executesql('select cola,colb from mytable;')
    

    r[0][0] is cols and r[0][1] is col b. It does not matter in which
    order they were in the table definition[All that amatters is the order with which you made the query].

    ex:

    #model
    db.define_table('my_table', Field('soso'), Field('toto'), Field('lolo'))
    
    # supposing you've entered data[soso='a', toto='b', lolo='c'] in table fields using appadmin interface .
    
    #controller
    def index():    
        r = db.executesql('select soso, lolo from my_table')
        print r  # [(u'a', u'c')]
        print r[0][0], r[0][1] #a c
        return dict()
    

    One thing to note when using executesql, is that you need to escape strings so that you’re not vulnerable to sql injection attacks.
    though, when you’re using DAL functions, you don’t need to do this since it’s automatic.


    You can do things like:

    orderby=db.table.field.upper()
    #or
    orderby=db.table.datefield.month()
    

    Can I have a random orderby directly using web2py DAL?
    yes, use :

    orderby='<random>'
    

    Interesting right ? 😀


    An example showing how can indexes speed things up

    Q:
    I defined a model with two tables, let web2py create them (tried this
    with both sqlite and mysql), and then imported data (about 1,400 rows
    and 140,000 rows, respectively) into the tables using external
    scripts. After doing so I found my app became very slow – it took
    10-15 seconds to respond. Then I tried the csv import function in
    web2py, which worked, and there is no slowness at all. Why does this
    happen?

    A:
    The one thing that can make it slow is the absence of indices. You
    need to create indices on fields you use for searching. If you use
    sqlite you can do, after define_table:

    db.executesql('CREATE INDEX IF NOT EXISTS ON table colname;')
    

    For postgres, mysql, and oracle you are better off creating the index
    outside web2py.


    What else other than not creating indexes when I’ve a big number of records could affect the performance of my database ?

    If you’re making joins using the native SQL syntax ma be using executesql(), then , the db may be performing your joins
    in the order you’ve specified. This is a SQL standard, and in those cases there will not be any optimizations. Therefore, when ever you think about making joins using Traditional SQL, make sure that you do the most discriminating joins first (the ones smallest set of results).

    Using DAL: The query planner in the db may be optimizing the query for you and order of joins isn’t so important.


    What would be the simplest way to create an entry form for a new record, pre-filled with the fields from another record in the same
    table (knowing that record’s id) ?

    Assuming….
    db.define_table(‘mytable’,…)

    and the id of the source record is source_id, you can do:

    # get the record 
    row=db(db.mytable.id==source_id).select().first()
    # set the default for all fields by ID 
    for fieldname in db.mytable.fields: 
          if fieldname!='id': db.mytable[fieldname].default=row[fieldname] 
    form=SQLFORM(db.mytable) 
    

    Can you give me some examples on using (joins) in DAL ?

  • Ex:1
    Given :

    db.define_table('children',   Field('user_name' )) 
    db.define_table('child_profiles', Field('child_id',  db.children), Field('first_name' ), ...) 
    db.define_table('buddies', Field('buddy_id' , db.children),Field('child_id' , db.children), ..) 
    

    You can do :

     
    child_id=....(may be request.args(0)) #or anything else according to the logic you're using in your application.
    rows=db(db.buddies.child_id==child_id) 
                (db.buddies.buddy_id==db.child_profiles.child_id).select(db.buddies.ALL) 
    
    # db(cond1)(cond2) is the same as db((cond1)&(cond2))
    
    rows=db(db.buddies.child_id==child_id) 
                (db.buddies.buddy_id==db.children.id) 
                (db.buddies.buddy_id==db.child_profiles.child_id).select()
    
     for row in rows:
        print row.childer.user_name, 
               row.child_profiles.first_name
     
    
  • Ex:2

    Q:
    I have 2 tables, “owner” and “dog”. The “dog” table has a many to one relation to owners. If I have an an owner id, how can I make a join based on the owner id?
    I did this:

    present_owner = db((db.owner.id==session.user_id) &
    ( db.dog.owner_id==session.user_id)).select()
    

    But when I wanted to retrieve data it traced back, for example:
    present_owner.user_name.
    What is the proper way to retrieve the data from the join?
    Thanks

    Ans:

    * For your query to look more like a join, you may rewrite it as :

     rows = db((db.owner.id== db.dog.owner_id) &
                    ( db.owner.id==session.user_id)
                   ).select()
    

    rows now contain:

    owner = rows[0].owner (rows.first().owner) # an owner
    dog = rows[0].dog  (rows.first().dog)      # a dog that belongs to that owner.
    # then you can get more info using 
    owner.name, dog.name, .......
    
    
  • Ex:3
    Q: How 2 make 2 inner joins using the following tables?

    db.define_table('table1', Field('name'))
    db.define_table('table2', Field('table1_id', 'table3_id'))
    db.define_table('table3', Field('name'))
    

    Ans :

    rows= db((db.table2.table1_id==db.table1.id)&
         (db.table2.table3_id==db.table3.id)).select()
    
    for row in rows:
         print row.table2.id, row.table1.name, row.table3.name
    
  • Q: Does DAL support outer joins?

    Ans:
    Yes, web2py does left outer joins.BUT they require an explicit “left” keyword otherwise you’re trying to make an inner join.
    Example:

     db().select(db.table1.ALL,db.table2.ALL,left=db.table2.on(....==.....))
    

    no need for right joins since the functionality is the same as left joins .
    You can check the manual for discussions and examples about that .

    In fact, you could do something like:

    db.define_table('action_queue', Field('user_id','integer')) 
    db.define_table('unprocessed', Field('action_queue_id','integer')) 
    db (db.action_queue.id==db.unprocessed.action_queue_id).select (left=db.unprocessed) 
    

    BUT : This is an old notation which is supported only for backward comaptibility and works on sqlite. It should be:

    db ().select(db.action.ALL,db.unprocessed.ALL,left=db.unprocessed.on 
     (db.action_queue.id==db.unprocessed.action_queue_id)) 
    

  • Q: How to use sub-queries?
    Ans:

    q = using db()._select()
    print q
    

    you can get the real SQL query used by DAL to perform some sort of selection .
    Using the same concept suppose you’ve :-

    db.define_table('a', Field('f1'))
    db.define_table('b', Field('f1'), Field('f2'))
    
    # a includes the following records:
    a.id   a.f1
    3       1
    4       2
    5       3
    6       4
    
    # b includes the following records:
    b.id	b.f1	b.f2
    3       1        y
    4       4        x
    

    Now suppose you want to perform the following query :

    SELECT b.f2 FROM b WHERE b.f1 IN (SELECT a.f1 FROM a);
    


    So what to do ?

    you’ve 2 options :

    • using :
      db.executesql('SELECT b.f2 FROM b WHERE b.f1 IN (SELECT a.f1 FROM a);')
      
    • Generate the sub-query using db()._select(), then use it in another query using DAL syntax :
      def index():   
         q = db()._select(db.a.f1)
         rows = db(db.b.f1.belongs(q)).select(db.b.f2)
         print db._lastsql
         return dict(rows=rows)
      

    great !!! and db._lastsql will print exactly the same query you’ wanted:

    SELECT b.f2 FROM b WHERE b.f1 IN (SELECT a.f1 FROM a);
    

    and the answer is :-

    b.f2
    hamdy
    x
    
  • belongs can accept a set or a “select…” string and _select() as you may have noticed creates a query string.



    Q:

    I’ve got three tables: “companies”, “region”, and “items”.
    In “companies” there is a “region_id” column. In “items” there are “companies_id” and “price” columns. Is there a simple way to select the top 10 companies which belong to a particular region and whose item prices sum to the highest amount?

    A:

    First, check the “Grouping and counting section in this chapter of the manual”

    Try this:
    
    rows=db(db.companies.regio_id==region_id)
    (db.items.company_id==db.companies.id).select(db.companies.ALL,'sum(items.price)',groupby=db.items.company_id,orderby='sum(items.price)  desc',limitby=(0,10))
    
    for row in rows:
        prin row.companies.name,row._extra['sum(items.price)'] 
    

    A better way to do it a more recent way actually[the previous will work and still work for backward compatibility]:

    you may make:

    summation = db.items.price.sum()
    ..... .select(db.companies.ALL,summation,groupby=.....
    for row in rows:
        prin row.companies.name,row._extra[summation] 
    

    The first method is always there to help if the function you’re trying to use is not implemented(yet) in web2py.

    Note:
    in fact doing something like:

    str(db.table.field.max())  # assuming field is numeric
    

    will print:

    'MAX(table.field)'
    

    Wooooooooooooooow, so they’re the same

    So To understand this, I’ll summarize:

    some backend SQL DBMS support some functions and don’t support others
    if function is supported by all backend DBMS supported by web2py, the function will easily be implemented as:

    db.table.field.function()
    

    if not , you can still use it [assuming you know what to do and the DBMS you’ll always use support it]
    using a query string directly that specify your query

    So in the following example, you’ll get things better :
    Q:

    I’m using web2py with PostgreSQL. I can use ‘like’ and ‘belongs’ to construct simple SQL query, but I didn’t find anything equivalent to regular expression matching operator ~ or ~*. Did I miss something here? Or do I have to use Python to do regex on results returned from SQL?

    A:

    The fact is that postgresql supports it but many backends do not therefore there it no API for it. You have two choices :

    • write the query in SQL:

      query="table.field SIMILAR TO '(a|b)'" 
      rows=db(query).select(db.table.ALL) 
      
      do the pattern macthing in python on the returned values :

          r=re.compile('(a|b)') 
          rows=db().select(db.table.ALL) 
          rows.response=[x for i,x in enumerate(rows.response) if 
      r.match(rows[i].field)] 
      
  • Take another example to make sure you got all this stuff ? OK and you’re welcome

    Q:
    Assuming I’m having:

    # Model:-
    db.define_table('members', Field('name'))
    db.define_table('resources', Field('resource_tier', 'integer' ),
                                  Field('resource_amount', 'integer'))
    db.define_table('deposits', Field('depositer_id', 'reference members',
                                                              requires=IS_IN_DB(db, db.members.id, '%(name)s')),   
                                                    Field('resource_id', 'reference resources',
                                                     requires=IS_IN_DB(db, db.resources.id)))
    
    

    How can I translate this kind of sql statements:

    SELECT members.name, sum(resources.resource_amount) FROM resources, deposits, members WHERE ((members.id=deposits.depositer_id AND deposits.resource_id=resources.id) AND resources.resource_tier=0) GROUP BY deposits.depositer_id;
    

    A:

    summation = db.resources.resource_amount.sum()
        rows = rows=db((db.members.id==db.deposits.depositer_id)& 
                       (db.deposits.resource_id==db.resources.id)& 
                       (db.resources.resource_tier==0)).select(db.members.name,summation
                        ,groupby=db.deposits.depositer_id) 
        for row in rows: print row.members.name,row._extra[summation] 
    
    

    Now time to another question on the same example:

    Q:
    How could I translate this to web2py syntax ?

    SELECT members.name, SUM(resource_amount*(1-ABS(resource_tier))), SUM(resource_amount*(1-ABS(resource_tier-1))), SUM(resource_amount*(1-ABS(resource_tier-2))) FROM members LEFT JOIN deposits,resources WHERE (deposits.depositer_id=members.id AND deposits.resource_id=resources.id) GROUP BY members.name;
    
    

    A:

    member_depositer=(db.deposits.depositer_id==db.members.id)
        deposited_resource=(db.deposits.resource_id==db.resources.id) 
        sqlrows=db(member_depositer&deposited_resource).select(db.members.name, 
                  'SUM(resource_amount*(1-ABS(resource_tier)))', 
                  'SUM(resource_amount*(1-ABS(resource_tier-1)))', 
                  'SUM(resource_amount*(1-ABS(resource_tier-2)))', 
                  groupby=db.members.name,left=(db.deposits,db.resources))
        print sqlrows
    

    Q:
    Having 2 fields of type datetime, How can I order records according to the difference between them?

    A:

    rows = db ((db.ck.user_id==session.userid) & (db.ck.c_time>d1)& (db.ck.c_time<d2)).select (db.ck.ALL, db.c.ALL, left=db.ck.on 
    (db.ck.id==db.c.ck_id),orderby='DATEDIFF(c.c_time, ck.c_time)') 
    
    for row in rows: print (row.c.c_time-row.ck.c_time).days 
    

    DATEDIFF() should be supported by your dbms

    Q:
    assuming:

    db.define_table('flowers', 
       Field('name'), 
       Field('category'), 
       Field('price', 'float'), 
       Field('quantity', 'integer')) 
    

    I want to create a column called TotalPrice that multiplies price and quantity.

    A:

    TotalPrice='price*quantity' 
    rows=db().select(db.flowers.ALL,TotalPrice,orderby=TotalPrice) 
    for row in rows: print row.flowers.name, row._extra[TotalPrice] 
    

    What to do to implement my own function ?
    Ah ….. Looking at sql.py, I found that very easy ….. that’s why I like web2py 😀

    This example should make things clear

    db.define_table('koko', Field('toto', 'integer'))
    db.koko.lolo.insert('toto=5')
    db.koko.insert(toto=10)
    
    from gluon.sql import Expression
    ex = Expression('toto*5', type='integer')
    
    def mulby5():
      return ex
    
    db.koko.toto.mulby5=mulby5
    rows = db(db.koko.id>0).select(db.koko.ALL, db.koko.toto.mulby5())
    for row in rows:print row.koko.id, row.koko.toto, row._extra[db.koko.toto.mulby5()]
    1 5 25
    2 10 50
    

    You can get things done with using ‘Expression’, just make the function return the the string directly.

    1 thing to Note though, because you’ve added to the Rows object resulting from the query an extra payload, then when you want to select a field from it , you should specify the table name explicitly.


    Update record/records
    You can use :

    db(condition).update(my_field=value)
    # db() is a Set object and you'll end up manipulating the field 'my_field' in all records in this set with the specified 'value'
    # set 
    

    you can update only one row [one selected row] using the update_Record function.

    rows =  db(...).select()
    for row in rows:
        row.update_record(my_field=value)
    # or
    db(...).select().first().update_record(my_field=value)
    

    So Remeber :-

    Set.update()
    row.update_record()
    

    Ex:-

    # model
    db.define_table('node', Field('article_id'), Field('rgt', 'integer'))
    db.define_table('comment', Field('article_id', requires=IS_IN_DB(db, db.node.article_id)), Field('lft', 'integer'))
    
    # data inserted in db tables
    
    node.id       node.article_id        node.rgt
    10                   1                   1
    11                   2                   2
    12                   3                   3
    
    comment.id	   comment.article_id      comment.lft
    8                     1                      1
    9                     2                      2
    10                    3                      4
    
    # code in controller:
    for row in db((db.comment.article_id==db.node.article_id)&
                     (db.comment.lft>=db.node.rgt)).select(db.comment.ALL):
        row.update_record(lft=6)
    
    # result
    comment.id	   comment.article_id       comment.lft
    8                       1                     1
    9                       2                     2
    10                      3                     6
    
    

    In fact as some of you may have not noticed ….. db(….=….) makes an automatic join so doing something like:

    rows = db((db.comment.article_id==db.node.article_id)&
                     (db.comment.lft>=db.node.rgt)).select()
    

    you’ll never be able to make something like:

    for row in rows:
       row.update_record(..=...)
    

    why ? because in this case you’ll have an automatic join and you’ll end up with something like:

    <Row {'comment': <Row {'update_record': <function <lambda> at 0x927fc6c>, 'lft': 1, 'article_id': '1', 'id': 8, 'delete_record': <function <lambda> at 0x927f764>}>, 'node': <Row {'update_record': <function <lambda> at 0x927fc34>, 'rgt': 1, 'article_id': '1', 'id': 10, 'delete_record': <function <lambda> at 0x927ff7c>}>}>
    
    

    so in order to use update_record you’ll need to specify which row to use it from explicitly so you’ll end up with some thing like the following code in order to make things work:

    for row in db((db.comment.article_id==db.node.article_id)&
                     (db.comment.lft>=db.node.rgt)).select():
            row.comment.update_record(lft=row.node.rgt)
    

    Of course you don’t need this if you just select records out of one table
    so you can safely do the following :-

     row = db(db.person.id > 2).select().first()
     row.update_record(name='Curt')l
    

    In fact you can use another trick in order to update the row which is :-

    db.executesql('update comment, node set lft=lft+2 where %s;' %str(db.comment.article_id==db.node.article_id))
    

    which mixes SQL with web2py-SQL but is safe from SQL injections.
    Warning :
    Using the previous query with SQLITE, I got that error :

    OperationalError: near ",": syntax error
    

    If some body knows what’s wrong and how it can be fixed, please let me know.
    My guess is that it can be done another way, But I’m not An SQL guru.

    To summarize:
    update_record is very very useful in situations like if you want to use the old value of a field in a row to make some calculations then update it.

    rows=db(db.page.id==1).select() 
    rows.first().update_record(counter = rows.first().counter + 1) 
    

    or you can make it like:

    query=db.table.field == 'whatever' 
    db.executesql('UPDATE ..... WHERE %s' % str(query)) 
    

    Another cool thing that I want to talk about is the flexibility of DAL for dealing with queries.

    in fact :-

    db.table.fiesld == value
    

    produces a Set object …. interesting right ?!!!! 😀
    yes a Set object that can be Anded or ORed
    so you can easily mix many queries together
    ex:

      query = db.table.company_id>5
      query2 = auth.accessible_query('update',  db.company, auth.user.id)
      query3 = auth.accessible_query('owner', db.company, auth.user.id)
      query4 = (query2 | query3)
      result = db(query&query4).select()
    

    auth.accessible_query -> returns records with specific permissions
    As you can see I get either the the records in db.company that the current logged in user has the permission ‘update’ or ‘owner’ on them , by using 2 queries that are gonna be ORed another query which in its turn is Anded with another query.
    This’s beautiful ….. right ?


    you can even do somethings like :

    # model
    db.define_table('node', Field('article_id'), Field('rgt', 'integer'))
    db.define_table('comment', Field('article_id', requires=IS_IN_DB(db, db.node.article_id)), Field('lft', 'integer'), Field('order', 'string'))
    
    # data :
    comment.id comment.article_id comment.lft comment.order
    1               1                     1        None
    2               2                     2        None
    3               3                     4        None
    4               1                     1        a
    5               2                     2        d
    6               3                     2        b
    7               2                     1        Z
    8               3                     1	 -
    9               2                     2        -
    
    # controller
    def index():
        myorder=db.comment.order.upper()|db.comment.id
        print db().select(db.comment.ALL,orderby=myorder)
    
    # result
    comment.id,comment.article_id,comment.lft,comment.order
    1,           1,                  1,           <NULL>
    2,           2,                  2,           <NULL>
    3,           3,                  4,           <NULL>
    8,           3,                  1,
    9,           2,                  2,
    4,           1,                  1,             a
    6,           3,                  2,             b
    5,           2,                  2,             d
    7,           2,                  1,             Z
    

    BTW : groupby can be dealt with in the same manner



    Another neat example on using sub-queries and query sets:-

    Q:
    Suppose you’ve a list of strings representing search items that you want to search database for strings containing tem or ‘LIKE them’.
    The list is variable though, meaning that it can hold another search items in future what can I do then?

    search_terms = ['..', '..', '..', ...]
    for i,st in enumerate(search_terms):
           subquery=db.company.name.lower().like('%'+st.lower()+'%')
           query=query|subquery if i else subquery
    companies = db(db.company.id.belongs(db(query)._select
    (db.company.id))).select() 
    

    see the query=query|subquery if i else subquery 😀 interesting right ?!!
    for python newbies … this has the effect of choosing
    query = subquery in the first iteration since i = 0
    then every time query and subquery are ORed resulting in a query set that will be ORed with the next subquery. Coooool right ?!!


    Some other interesting stuff
    The following code is interesting but I don’t know how to make use of it, ..If some body has an idea, please let me know.

    s=db()
    s.sql_w
    s=s(db.users.id>1)
    s.sql_w
    'users.id>1'
    s=s(db.users.id<=4)
    s.sql_w
    '(users.id>1 AND users.id<=4)'
    

    i

    in fact both of the following queries are equivalent:

    db = DAL('sqlite:memory:')  # interesting right ?!!!
    db.define_table('x', Field('y')
    db.x.insert(y=9)
    db.x.insert(y=10)
    db.commit()
     str(db(db.x.id>0).select()) 
     'x.id,x.y\r\n1,9\r\n2,10\r\n'
    

    oh by the way making db((..) & (..)) is equivalent to db(…)(…) which is happening in the code above s= s() then s = s() => s()()

    str(db().select()) gets a comma separated values of the result interesting right ?!!
    it’s useful by the way in the shell when you’re testing where there’s no tables, just objects 😀

    db(db.table.field … value) is equivalent to db(‘table.value … value’) more interesting right ?

    This’s very interesting in deed, in the sense that enables you to make complicated queries:
    Ex:
    instead of making something like:

    sql_string = 'SELECT * from message where 
    distance_sphere(GeometryFromText(\'POINT(\' || longitude || \' \' || 
    latitude || \')\', 2), GeometryFromText(\'POINT(-122.415686 
    37.799724)\', 2)) > 105;' 
    records = db.executesql(sql_string) 
    return dict(records=records) 
    

    you can do :

    cond="distance_sphere(GeometryFromText(\'POINT(\' || longitude || \'   
    \' ||latitude || \')\', 2), GeometryFromText(\'POINT 
    (-122.41568637.799724)\', 2)) > 105" 
    records=db(cond).select(db.message.ALL) 
    

    Oh, By the way the strings are having their quotes escaped to prevent sql injection attacks, this’s required if you’re going to use executesql() directly
    I found this very cool what about you ?!!! 😀

    Now I think it’s the time to generalize this for more and deep understanding
    In the web2py foder you can get an application shell using:

    python web2py.py -S -M
    

    Now you can do:

    db.define_table('test', Field('x'))
    db.test.insert(x='hey "hamdy", how're ya')
    # in the shell don't forget to commit so that effects can be reflected
    # commits are automatic from within web2py not from within the shell, they've to be explicit
    db.commit() 
    query = db(db.test.x == 'hey "hamdy", how are ya')
    query.sql_w
    # the result :
    'test.x=\'hey "hamdy", how are ya\''
    # quotes are escaped right ?
    

    Now what if the query string is complicated enough and it’s a bare sql query, how can I use it safely without the need for using executesql() which is not safe at most times unless you took care and escaped your strings properly ?

    in my case , I want to select the field’s x value from table test, so I can make my query string like:

    cond = """x = 'hey "hamdy", how are ya'"""
    or
    cond = """test.x = 'hey "hamdy", how are ya'"""
    

    Now when I want to select from database I can play it easily like:

    str(db(cond).select(db.test.ALL))
    # result
    'test.id,test.x\r\n1,"hey ""hamdy"", how are ya"\r\n'
    

    Note that I put the query string inside the db(), then chose the table to select from , in the select()
    and it worked.
    You don’t have to choose [this’s optional in this case] table inside the select() if you provided it in the string .
    This’s not gonna work though:

    cond = """db.test.x = 'hey "hamdy", how are ya'"""
     str(db(cond).select(db.test.ALL))
    

    since in the case of specifying a query string don’t say :

    'db.table.field ....'
    

    we’re not using a pure DAL syntax here
    instead say:

    'table.field. ....'
    or
    'field. ...'
    

    You may instead do:

    db.test.x == 'hey "hamdy", how are ya'
    <gluon.sql.Query object at 0x257bcd0>
    
    str(db(cond).select())
    'test.id,test.x\r\n1,"hey ""hamdy"", how are ya"\r\n'
    
    Confusing ? !!!  😀
    it's so simple, as a role of thumb you can either do:
    db.table.field == value
    db.table.field > value
    or 
    'table.field=value'
    'field=value'
    # string can't have == inside it but can contain any other conditional operators
    

    According to this post which explains how to check whether cache is functioning or not, I used the same trick to explain another interesting thing that I like very much.
    All of us knows that select() statements can be cached using something like:

    db(...).select(..cache=(cache.ram,3600)) #3600 is the expiration time
    

    Now imagine with me this scenario : you have a set of records that are cached in some controller action [say index of your web application]
    those records represent recently added products if your web application is all about an online store.
    Now suppose you’ve another controller function that add new products to your online store.
    What’s the problem here ?
    It’s that you need the cache to be flushed whenever inserting new record .
    Can this be done ?
    Sure, otherwise I wouldn’t have talking about it.
    How?
    You just need something to refresh the cache.Something to enforce reading database and re-caching results again

    ex:

    #model:
    db.define_table('product', Field('x'))
    
    #controller
    def index():
        rows = db().select(db.product.ALL, cache=(cache.ram, 600000))
        if db._lastsql:
            print 'First time or not cached'
        else:
            print 'cached'
        
        return dict(rows=rows)
    
    def insert():
        db.product.insert(x='3')
        rows = db().select(db.product.ALL, cache=(cache.ram, -1))
        return dict(rows=rows)
    
    

    Now before doing any thing just add records to database using appadmin interface.

    product.id    product.x
    1                  1
    2                  1
    3                  3
    

    Now go to the application’s index page
    You’ll have the statement “First time or not cached” printed indicating first time querying database.
    And you’ll get the table:

    product.id    product.x
    1                  1
    2                  1
    3                  3
    

    By keeping refreshing the page you only get the same results and the statement “cached” is printed every time.
    So records are cached for a long period of time
    Now go to the insert page that inserts another record
    the result will be:

    product.id    product.x
    1                  1
    2                  1
    3                  3
    4                  3  # newly inserted
    

    by going to index one last time you’ll see the updated table and you’ll have the statement “cached” being printed even when refreshing the page.

    Explanation

    rows = db().select(db.product, cache=(cache.ram, -1))
    

    checks if the same select() statement was issued before and having some cached records , then it will just refresh the cache [re-read database] then records will continue to be cached for a time equal to the original time used for caching them (600000) in our case.
    If not, they’ll be re-read .

    Now close web2py then re-run it [to flush all caches and starting over]
    try this code:

    def insert():
        db.product.insert(x='3')
        rows = db().select(db.product.ALL, cache=(cache.ram, -1))
        if db._lastsql:
            print 'First time or not cached'
        else:
            print 'cached'
            
        return dict(rows=rows)
    

    Now only go to the insert page and keep refreshing page.
    what you see ?
    Correct , the statement “First time or not cached” keeps printed
    so there’s no real cache. database is re-read and if records were previously cached , cache is renewed.

    both 0, and -1 are correct (cache.ram, 0) or (cache.ram, -1) but -1 is always the safe choice.


    Another thing that is trivial to mention but it’s here anyway for any newbie guy that may not know it:
    any insert() statement like this one:

     db.table.insert(field_name=value)
    

    returns back the id of the newly inserted record so that you may use it
    again.

    Also any update like :

    d(...).update(field_name=vlue, field2_name=value2,...)
    

    statement returns back 0 if there’s no records updated [condition is not True for all the records in the table] and if condition is True the number returned is the number of records that are updated.
    Why would this be helpful ?
    Because sometimes you get situations in which for example you like to update some user records then if update is successful, you’l have to send notification mails for those users.

    delete() has the same behavior as update

    In both update() & delete(), this can be done using the following line of code in gluon/sql.py:

    try:
        counter = self._db._cursor.rowcount
    except:
        counter =  None
    

    truncate

    Imagine the following situation…. You’ve deleted a record in some table which id is referred to by another table and let’s say the referenced field is not a foreign key and in defining the table that includes that record no ‘ondelete=CASCADE’ was specified and thus when deleting the record , all other record tables referring to it will not be deleted automatically.

    Let’s imagine that id of the deleted record was 1000 , and then imagine that you entered a new record in that table ..what id it should take?
    if it took 1000 then you’re in a mess right ? many records that has no relation with it will just refer to it right ?
    To prevent this …. any new record that will be inserted into database will take a new id that was not taken before so our luckily, the new id will take the id 1001 even if you’ve deleted all table records and it includes nothing …
    If you want to reset table to original state , delete all records and reset counters use:

     db.field_name.truncate()
    

    and congratulations !!! you got a brand new table


    Can I make a dummy database for just testing the validation of a form and database query generations?

    Yes sure !!!

    # Model
     db = DAL(None)
    db.define_table('members', Field('name', requires=IS_NOT_EMPTY()))
    
    #Controller
    def index():
        form = SQLFORM(db.members)
        if form.accepts(request.vars, session):
            response.flash = T('hey it worked')
        rows=db().select(db.members.ALL)
        print db._lastsql
       return dict(form=form)
    

    Congratulations !!! without need to introduce extra/new syntax.
    Internally DAL(None) behaves like a sqlite db and you can use it test query generations as well but there is nosqlite file so nothing is stored and no overhead.


    Self referencing tables …!!
    DAL should allow self referencing tables. This is not a DAL issue, this is an SQL issue. There is in fact a logical problem in inserting the first record.
    Actually web2py allows you to create self referencing tables.
    Don’t make an automatic reference, just use instead of a field of type reference a one of the type ‘integer’ then you can, optionally, use the IS_IN_DB validator.
    For a parent record you may use 0, or -1 but [-1 is always the safer choice]
    ex:

    db.define_table('class', Field('name'), Field('parent_class', 'integer'))
    

    then inserting records in parent_class based manually, and insert (-1) if parent_class is ‘Object’ 😀
    A better trick to do this would be:

    db.define_table('class', Field('name'), Field('parent_class', 'integer'))
    db.class.parent_class.type='reference class' 
    Also set db.class.parent_class.requires=IS_NULL_OR(IS_IN_DB 
    (db,'class.id','%(name)s')) 
    

    Now see the IS_NULL_OR(IS_IN_DB()) …. interesting right ? !!!!!! 😀
    Now you can insert object and just don’t insert a parent for it.


    Q:

    Is there any other straight and plain way to get the result? Actually, I have a table with many columns, what I want is select a row which has a max id. As my understood, the db.table.id.max() should return
    directly the max value in column ‘id’. and then I can use ‘db(db.table.id==maxid).select()’ to select the row I want.

    A:

    db().select(db.mytable.ALL,orderby=~db.mytable.id,limitby=(0,1)) 
    

    A hint:
    Please, take care of not using a reserved SQL keywords as a table names or field names, RDBMSes vary and there’re lots of reserved keywords , some of them specific to some RDBMSes
    so take care.


    Abstract tables

    db=DAL() 
    db.define_table('a',Field('name')) 
    db.define_table('b',Field('other')) 
    db.define_table('c',Field('test'),db.a,db.b) 
    print db.c.fields 
    
    ['id','test','name','other'] 
    
    db=DAL() 
    from gluon.sql import SQLTable 
    abstract=SQLTable(None,Field('name')) 
    db.define_table('mytable',abstract,Field('other')) 
    print db.mytable.fields 
    ['id','name','other']
    

    Abstract table is a table that is defined but does not exist in the DB. It can be used to define derived classes that include the same fields. For example:

    db=DAL() 
    from gluon.sql import SQLTable 
    person=SQLTable(None,Field('name'),Field('address')) 
    db.define_table('student',person,Field('degree')) 
    db.define_table('teacher',person,Field('subject')) 
    

    Here table person is not in the DB, but student and teacher are. The
    latter have the fields defined in person plus others.
    Do not abuse this. Often this is better to do:

    db=DAL() 
    db.define_table('person',Field('name'),Field('address')) 
    db.define_table('student',Field('person',db.person),Field('degree')) 
    db.define_table('teacher',Field('person',db.person),Field('subject'))
    
    # Field('person', db.person) 
       is the same as
    # Field('person', 'references person')
    
    

    if a student references a person but it does not have the fields of a
    person.


    Some sweet and light tricks :

    To check the SQL syntax of different dbms :-

    python web2py.py -S welcome 
    db=DAL(None) 
    db.define_table('person',Field('name')) 
    for n in ['sqlite','postgresql','mysql','oracle','mssql','firbird']: 
        db['_dbname']=n 
        print db()._select(db.person.name,limitby=(0,10)) 
    

    Result:

    SELECT person.name FROM person LIMIT 10 OFFSET 0; 
    SELECT person.name FROM person LIMIT 10 OFFSET 0; 
    SELECT person.name FROM person LIMIT 10 OFFSET 0; 
    
    SELECT person.name FROM (SELECT _tmp.*, ROWNUM _row FROM (SELECT person.name FROM person ORDER BY person.id) _tmp WHERE ROWNUM<10 )  WHERE _row>=0; 
    
    SELECT TOP 10 person.name FROM person ORDER BY person.id; 
    SELECT person.name FROM person LIMIT 10 OFFSET 0; 
    

    d2=SQLDB('sqlite://d2.db') 
    d2.define_table('person',SQLField('name')) 
    d2.person._referenced_by 
    [] 
    
    d2.define_table('dog',SQLField('name'),SQLField('owner',d2.person)) 
    d2.person._referenced_by 
    [('dog', 'owner')] 
    
    d2.dog.drop() 
    d2.person._referenced_by 
    []
    

    Q:

    How Do I use IIS with web2py?

    A:

    • Run IIS as a proxy and redirect to the web2py web server. This
      solution should work out of the box
    • Use this or that
    • Use fast cgi

    Validators between classical and professional usage

    April 29, 2010

    Introduction
    Web2py validators work at the form level, not on the database level
    It's recommended though to make both validation at the form level and database level.

  • You can add validators whereever you want but you’ve to pay attention for the logic.
    you can make:

    
    db.define_table('my_table', Field('my_f', requires=[..]))
    # or  inside the same model after the definition of the table :
    db.define_table('my_table', Field('my_f'))
    db.my_table.my_f.requires = [....]
     # you may even do this in your controller
    db.my_table.my_f.requires = [....]  
    form = SQLFORM(db.my_table)
    ......
    

    Things to note though that making adding validators after defining the table or before defining the form in your controller is that in those 2 particular situations, you’re really overriding any other validators on this table field defined in the model, either within the table definition or after it .

    db.define_table('my_table', Field('my_f', requires=[set_no_1]))
    db.my_table.my_f.requires = [set_no_2]  
    

    In the example above, set_no_2 of validators is the one that will actually work.

    To overcome this situation, you may make something like:

    db.define_table('soso', Field('lolo'), Field('moon', requires=[IS_NOT_EMPTY()]))
    db.soso.moon.requires.append(IS_NOT_IN_DB(db, db.soso.moon))
    

    Wow !!! then you can append to the list from anywhere and it will work.

  • Now one can wonders , so what’s the catch ?

  • The catch is that making something like :
    db.my_table.my_f.requires = [IS_IN_DB(...)]
    

    will prevent this field from being represented as a combo box, for this to be achieved you’ve not to make a list

    db.my_table.my_f.requires = IS_IN_DB(...)
    

    in this case you can’t add other validators and any trial to override the validators will ruin the combo box widget.

  • The good news is that there’s a solution 😀 which is ‘using widgets’, look ath this piece of code:
    b.define_table('soso', Field('lolo'), Field('moon', widget=SQLFORM.widgets.options.widget, requires=[IS_IN_DB(db, db.country.name), IS_UPPER()]))
    

    What I did was to force the usage of the options widget, and in this case it will work fine and the other validator will also work, and the value to be entered in database is the the uppercase value of the one you’ve chosen.
    WARNING: IS_IN_DB() MUST be the first in the list, for this to work.


  • IS_IN_DB() and IS_NOT_IN_DB() logical issue

    when using either of the 2 validators, you have to take care of something , which is that you can’t do something like:

    db.define_table('country', Field('name', requires=IS_NOT_IN_DB(db, db.country.name)))
    

    what you want to do is making the country name unique, and not to allow duplication.
    Let’s look more precisely about what you’ve done above.
    requires=IS_NOT_IN_DB(db, db.country.name)
    can you really use db.country.name before the country table is really defined ? You can’t and thus in this case you’ve to move your validation to be after the table definition , something like:

    db.define_table('country', Field('name'))
    db.country.name.requires= IS_NOT_IN_DB(db, db.country.name)
    

    IS_IN_DB() or IS_IN_DB() classical usage
    The same logic goes for both, so I’ll concentrate on one the other will be almost the same, except for the functionality ofcourse.

    db.define_table('category', Field('name'))
    db.define_table('recipe', Field('category', 'reference category'))
     db.recipe.category.requires=IS_IN_DB(db,'category.id','category.name')
       # or
    db.recipe.category.requires=IS_IN_DB(db,db.category.id,'category.name') 
    

    The parameters are:

    • a set of records
    • ‘category.id’ (string) or db.category.id (not a string) describes how the field will be represented in the
      database [this is the value that will be actually sent when accepting a SQLFORM that is using this table]
    • ‘category.name’ describes how the field will be represented to the
      user [Just a representation] and you’ll end up with a combo box displaying the name while accepting the form will send the id to database.
      name is a valid field name in the same table.
    # model
    db.define_table('table_one', Field('name'))
    db.define_table('table_two',Field('a', requires=IS_IN_DB(db, 'table_one.id', '%(name)s')))
    
    # controller
    def index():
        form = SQLFORM(db.table_two)
        if form.accepts(request.vars, session):       
            print form
        return dict(form=form)
    

    supposing table_one has only one record {id=1, name=’hamdy’}
    the form will display a drop down box with one item “hamdy”
    but upon accepting form the id (1) will actually be stored in db

    print form will give you something like:

    <form action="" enctype="multipart/form-data" method="post">
    .........................................................
    <select class="string" id="table_two_a" name="a"><option value=""></option><option value="1">hamdy</option></select></form>
    .........................................................
    

    In fact to be honest what really got to the database in table_two is just an id.
    To make things more professional you’ve to make this id refer to the actual record in tabl_one not just an id.
    To achieve this and test it , do something like:

    # add to your model :
    db.define_table('table_three',Field('a', 'reference table_one', requires=IS_IN_DB(db, 'table_one.id', '%(name)s')))
    
    # controller
    def index():
        form = SQLFORM(db.table_three)
        if form.accepts(request.vars, session):       
            print form
        return dict(form=form)
    

    Now when you go to the appadmin interface to check you database,
    you’ll find that the id in the record is a link to the actual record in the other table and obviously this’s what you wanted from the beginning a foreign key.
    In this case you can add to the field ‘a’ in table_one : ondelete=’CASCADE’ so that when the record in a deleted , all other records in other tables referencing it will be deleted automatically and this is a thing you WILL ALWAYS need, otherwise you’ll suffer a severe headache trying to trace unused records and delete them manually
    in separate database queries.


    IS_IN_DB() professional usage

    • making something like:
      db.define_table('table_two',Field('a', requires=IS_IN_DB(db(db.table_one.name.like('h%')), 'table_one.name')))
      

      you’ll get a combo box with names in your table that starts with ‘h’ .
      choosing one of them and submitting the form will submit its id
      you may even do something like:

      db.define_table('table_two',Field('a', requires=IS_IN_DB(db(db.table_one.name.like('h%')),                       'table_one.id', 'Name: %(name)s, id: %(id)s')))
      

      Note that :

       'Name: %(name)s, id: %(id)s')
      

      is just a string that looks similar to ‘Name:….., id:….’ :

      Name:hamdy, id:1
      Name:aaaa, id:2

      and as I said before, submitting it will submit the id .

      The bottom line here :

      • You just can play with the presentation [how field looks like for user] while the data that will enter the database is different.
      • You can use a query inside IS_IN_DB(query, presentation) and the query is just logically representing a WHERE clause of database query

    Another Professional usage for IS_IN_DB()

  • How to use IS_IN_DB() to make records entered by a user are unique
    but in the same time those records can be entered again but by another user ?
    • Using the web2py example, we have 2 tables, “owners” and “dogs”. The “dogs” table has a many 2 one relation to owners.
      How can I create a constraint/ validator that allows the owner to give
      his dogs unique names? I cannot use IS_NOT_IN_DB because it is is
      database wide, I would like to have duplicate names in the table, but
      not allow duplicate names for a given user. Example, an owner called
      Voltron can have 2 dogs called “skipper” and “lassie” but not
      “skipper” and “skipper”, but another owner called ‘Optimus Prime’ could
      also have a dog called “skipper” or “lassie”
      Was that clear? Any ideas?

      IS_NOT_IN_DB(db(db.dog.owner==owner_id),db.dog.name) 
      

    What I wanted to show you in the previous section is that you can do things using many ways and to ensure you understand the :
    IS_IN_DB() and IS_NOT_IN_DB() and that they can be passed (db) as the first parameter or passed a set of records using db(my_query)
    then the 2nd argument is a database field that either written as :
    'my_table.my_field' as a string or db.my_table.my_field


    Yet another professional usage for IS_IN_DB()

    How to make a data set of some records in a table with appropriate permissions then requires user to choose among them ?

    # Model
    db.define_table('company', ......)
    db.define_table('news',
                    Field('company_id', 'reference company'),                 
                    .....................   
                    migrate=migrate
                    )
    
    db.news.company_id.requires = IS_IN_DB(db(auth.accessible_query(Permissions.OWNER,db.company,auth.user.id)), db.company.name) if auth.is_logged_in() else None
    # db.company.name -> to display company name instead of company id
    

    If you’ve a user who is a company OWNER or several companies OWNER
    then when he ever wants to enter news for one or more companies that he owns, the form should allow him to choose among those companies and make him choose which one to enter news for.

    db(auth.accessible_query(Permissions.OWNER,db.company,auth.user.id
    

    returns records from company db.table that the current logged in user [auth.user.id] has OWNER permission on them
    In fact permissions are set based on groups so what this code really does is to check whether the current user belongs to a group that has OWNER permissions on some records of the table db.company and return those records

    the reason for requires=….. if auth.is_logged_in() else None is that when a user is not logged in the auth.user is not there so it’s None, and that causes the application to fail because auth.user.id is not there [None has no id attribute] so simply I made that simple check knowing that this table is used for forms that requires a user to login.


    Now time for another neat trick that web2py permits
    In fact, you can make validation on a field based on another’s field’s value that is submitted from a form !!! on the fly.
    Consider the following example:

    db.define_table('my_table', Field('category', requires=IS_IN_SET(['url_required', 'url_not_required'
                                ]
                       )
                                 ),
                                 
                           Field('url', requires = IS_EMPTY_OR(IS_URL()) if request.vars.category == 'url_not_required' else IS_URL()
                                 ))
    

    Great .. .right ? 😀

  • In fact IS_IN_SET can be a list of say … records ids and in the same time having labels [aliases] to be human readable
    so making something like:

    db.define_table('my_table', Field('toto', requires = IS_IN_SET([1, 2, 3], labels=['one', 'two', 'three'])))
    

    will yield a dropdown menu with the values[ ‘one’, ‘two’, ‘three’] but in fact what is being sent to database is the integers (ids) [1, 2 or 3]

  • You can even use IS_IN_LIST() with dates, … say for example you’ve a field of type ‘datetime’ then you want users to enter specific dates in it, so you can do something like:
    db.define_table('my_table', Field('date', 'datetime'))
    db.my_table.date.requires=IS_IN_SET([
      datetime.date(1492,10,12),
      datetime.date(1492,11,02),
      datetime.date(1492,11,27)])
    

    Now user will get a drop down menu of the specified dates .


  • Another neat usage for IS_EMPTY_OR() or IS_NULL_OR() [they’re the same] is when you have a table with a field that refers to another table.
    in this particular situation the referencing field must have a value that is equivalent to an existing record id on the referenced table.
    and because it’s difficult to remember the ids or even to know it, you should use IS_IN_DB() validator to have a drop down menu to choose from it . And as you all know, you can format the value in drop down menu to display names instead of ids and what is really going to be sent to server and database is the id.

    db.define_table('cat', Field('category'))
    db.define_table('my_table', Field('category', 'reference cat', requires=IS_IN_DB(db, db.cat.id)))
    # use 'reference cat' or db.cat without single quotes
    

    Now what if you want to have the db.my_table.category set to a category id or if there’s no category you just want Null in this database field or you want just a specific default id to be stored if user let this field empty.
    In this case you use : IS_EMPTY_OR(….., null=) so I can have something like :

    # controller
    def index():
       form = SQLFORM(db.my_table)
       if form.accepts(request.vars, session):
           response.vars = T('yes')
       return dict(form=form)
    
    #model
    db.define_table('cat', Field('category'))
    db.define_table('my_table', Field('category', 'reference cat', requires=IS_EMPTY_OR(IS_IN_DB(db, db.cat.id), null=None)))
    

    In this case you can just let this field empty and not to choose a category and in this case will be stored in this field.
    If you want something other than such as a default category id, you can use :

    requires=IS_EMPTY_OR(IS_IN_DB(db, db.cat.id), null=1))
    # 1 should be valid record in db.cat
    <strong>Warning:</strong> if the value assigned yo null= dosn't exist , you 'll get an error trying to access database table via appadmin interface, it should be a valid record id in the referenced table.
    
    

    oh, don’t forget to check my discussion about the IS_EMPTY_OR() validator in Web2py Zen


  • How could I get rid of some validators that I don’t like in some actions
    [controller functions] ?

    anywhere in your action before defining the SQLFORM, just do:

    db.my_table.my_field.requires=[] 
    

  • How could one validate against a set of translated values?

    you do it the normal way:

    db.define_table('table_two_two',Field('a', 'string', requires=IS_IN_SET([T('v'), T('d')])))
    #requires=[IS_IN_SET(..)] -&gt; No combobox
    #requires=[IS_IN_SET(..)] -&gt; combobox
    

    Date & Time Validators

    Now we've IS_DATE, IS_TIME, IS_DATE_TIME
    
    db.define_table('a', Field('date', 'datetime'))
    db.a.date.requires=IS_DATETIME('%Y-%m-%d %H:%M:%S-%p', error_message=...)
    

    and the date field b will be processed in the desired format, specified by the custom format string that you can edit as you like :

    ('%Y-%m-%d %H:%M:%S-%p'
    

    For Numbers you use

    IS_INT_IN_RANGE(min,max)
    

  • For Telephones:
    you may use something like :

    IS_MATCH(&quot;^1+\d{3}\-?\d{3}\-?\d{4}$&quot;)
    # or for european something like
    IS_MATCH(&quot;^\+?[\d\-]*$&quot;) 
    
  • How do I validate a number that must always consist of 5 numerals including numbers starting with a 0? Here are examples are 12345, 01234, 89765. I used IS_INT_IN_RANGE but the numerals that start with 0 did not validate. ?

    You need to store as ‘string’ with length=5 and require

    IS_MATCH('^\d{5}$') 
    

    Telephone numbers should be stored as strings otherwise numbers starting with 0 will be treated as octal numbers.


  • validation not working on FORM field why?

    Now when talking about FORM validation it’s the same as the SQLFORM validation , though you’ve to notice something :
    Take care and give form field a name in order for validation to work
    ex:

    # validation here won't work, although the form submission seems to work just fine
    form = FORM(INPUT(_type='text', requires=IS_LENGTH(minsize=3, maxsize=8)),
                    INPUT(_type='submit')
                    )
        if form.accepts(request.vars, session):
            response.flash = T('hey')
        return dict(form=form)
    

    In order for the previous example to work, field needs to have a name
    so that requst.vars contains request.vars.field_name and the validation works.
    So you need change the form definition into something like:

    
    form = FORM(INPUT(_name='name', _type='text', requires=IS_LENGTH(minsize=3)),
                    INPUT(_type='submit')
                    )
    

  • You can use IS_LIST_OF() validator to validate a list of values

  • Q: I’ve my own function that does something complex and I want to use it for values submitted from a form to process that data and make some operation on it before actually being saved to database.
    What can I do?

    Ans:

    Suppose you’ve a function like:

    def my_owsome_func(x):
        return return x.replace('-', '_')
    

    Now I’m having in my model:

    db.define_table('my_table', Field('my_url', requires=IS_URL()))
    

    and in my controller:

     form = SQLFORM(db.my_table)
        if form.accepts(request.vars, session):
            response.flash = T('yes')
        return dict(form=form)
    

    Now I want when user inputs a URL, the my_owsome_func() processes the URL before actually saved into database then replace every occurrence of ‘-‘ with ‘_’.

    Think a little … what is the thing that processes data before actually being saved into database ?
    Yes … Validators
    Now if I were able to use my_owsome_func() in a validator, this would be awsome ..!!!!
    Yes you can :
    you can make your custom validator like:

    from gluon.validators import Validator
    class MY_CUSTOM_VALIDATOR(Validator):
        def __init__(self, f):
            self._f = f
        def __call__(self, value):
            return (self._f(value), None)
    

    as you can see a typical validator should (but not required to) extend the Validator class in gluon.validators .
    gluon.validators.Validator includes a formatter() function that you might need in formatting input.
    In fact few validators use formatter() any way, and you’re not going to use it at most conditions too.

    A typical validator includes __init__() and __call__() functions, where __init__() is called once validator class instantiated and is used to initialize the object .
    __call__() is called at the end of the execution and acts upon a value
    so validator needs to be called in this way :

    my_validator(value_to_check_against)(value_to_check)
    

    (value_to_check_against is saved as an attribute using the __init__()
    and value_to_check is called by __call__()

    A typical validator should return (value_to_check, None) if validation succeeded and (value_to_check, error_message) if not
    In our case we just made a validator returns (value_after_processing, None) … where None indicates validation succeeded
    We don’t need to have an error message because we use a validator to process data that should be correct .
    How can I guarantee that data is correct ?
    using another validator that is used before my custom one, so that we end up using one validator for data integrity and the other to process data before actually saving into database.

    In our example, we pass function to the validator and in the __call__() we use it to process data and return result.



  • CRYPT() validator

    Not used By default for password field in auth_user table [It should]
    to use it, You need to do somewhere in your model ,something like:

    auth.define_tables() 
    db.auth_user.password.requires=CRYPT()
    

    auth.define_tables() is responsible for creating the default tables each application should have, one of them ofcourse is auth_user and thus :
    db.auth_user.password.requires=CRYPT() should come after it not before it.

    Warning:
    Doing that when you’re already having passwords saved in database means that they won’t work any more unless you made once and only once :

    for row in db().select(db.auth_user.ALL):
       row.update_record(password=CRYPT()(row.password)[0])
    

    Quickly, getting a table of records

    April 28, 2010
  • Of course you know that something like:
    rows = db(db.jobs.id > 0).slect()
    

    yields a Rows object that can be returned using :

    return dict(rows=rows)
    

    and in your view making something like:

    {{=rows}}
    

    will get you a table , yes an HTML table created for you on the fly

  • The problem with this approach is that when you need more customization like decorating your table using java script or doing any other customization you’ll have to create the table manually in the view doing something like:
    <table class="blahblahblah">
    <th> {{T("Title'')}} </th>
    <th> {{T("Location'')}} </th>
    {{for row in rows:}}
    <tr> 
    <td>{{row.title}} </td>
    <td>{{row.location}} </td>
    </tr>
    {{pass}}
    </table>
    

    boring is not it ?

  • We can make another hackish trick which makes our lives easier:
    since Rows object has an attribute called colnames containing the column names of the rows being returned and since column names consists of table_name.field_name:
    you can do something like :

    x = db(db.jobs.id>0).select()
    [column.split('.')[1] for column in x.colnames]
    ['id', 'title']
    

    Now you can do :

    ---- mytable.html ----
    {{def mytable(records, class_name=''):}}
    <table class="{{=class_name}}">
    {{for col in [column.split('.')[1] for column in x.colnames]:}}
    <th> col</th>
    {{pass}}
    {{for r in records:}}
    <tr>{{for k,v in r.items():}}
    <td>{{=v}}</td>
    {{pass}}</tr>
    {{pass}}
    </table>
    {{return}}
    
  • After that all you need to do when ever trying to make a table is to
    import mytable.html in your view with

     {{include 'mytable.html'}}
    

    and use it

    {{mytable(db(...).select(), 'class_name')}}
    

    then you can personalize your table as you want and reuse it.


  • web2py zen

    April 27, 2010
  • Web2py is a WSGI compliant enterprise web framework .
  • Enterprise” here means mainly one thing: we DO NOT CHANGE THE API because professional users who works in teams and on long term projects must count on stable API add documentation. “Enterprise” do not mean we focus on very large enterprises, we probably focus more on small and medium size ones, nor it means we have every possible “enterprise” feature. We are very straightforward about the web2py features. “enterprise” here sends a message: web2py is not developed by a bunch of kids who try to follow the latest trends and never deliver a stable product. web2py is built by experienced developers who know how to find a balance between usability, stability and features.
  • WSGI server parameters can be passed as command line options
  • Always promises backward compatibility
  • Web2py is not planned to move to python 3 any time soon because
    it will break backward compatibility of all applications and because
    database drivers do not work on 3. Moreover web2py apps would no
    longer work on GAE.
    The main and probably the only issue is with strings becoming unicode
    strings by default. It would not be difficult to change web2py to
    work with 3.0 and it was designed following all the other 3.0
    guidelines but there is no way to make the apps backward compatible.
  • you can make your web2py interact with your cgi scripts :
    You can run web2py on a port (say 8000) and your CGI script on a
    different port (say 8001) using a different web server.
    In this case the web2py program can call the CGI scripts pass parameters and receive response. Example

    import urllib
    data=urllib.urlopen('http://localhost:8001/myscript.cgi?
    parameter=value')
    

    You can read more in the urllib and urllib2 documentation).
    You cannot call the cgi scripts from web2py without going over HTTP.
    This is not a web2py limitation, this is a CGI one. In fact CGI
    assumes that every CGI script is executed in its own process and thus
    has its own folder, environment variables and stdout/stdin. Therefore
    a CGI script is not thread safe and cannot be executed inside a
    thread (threads shares folder, environment and IO streams). web2py
    uses threads.

  • web2py does support IF_MODIFIED_SINCE and PARTIAL REQUESTS out of the box.
    You can add any header you want including ETAGS.
  • There’s a routing system in web2py which unfortunately doesn’t work on application level basis (yet)
    Warning:
    Don’t use the routes module to enforce the use of SSL on a few URLs
    it would not be safe. If you are behind a proxy web2py does
    not know whether the original request was over https or not. The
    method of using X_FORWARDED_FOR has known vulnerabilities. You should use apache to force SSL.
  • Web2py runs on :
    • runs on python >= 2.4 but two things to note:
      • if you save data using CRYPT() validator used with password fields in 2.4 you will not able to read them in 2.5 or 2.6 so be careful python 2.y doesn’t have hashlib used by CRYPT() validator
      • There is not uuid module in Python2.4 , You could make one that will satisfy web2py. You can make it as a module ofcourse
        ## uuid.py 
        import random,time 
        def uuid4(): return '%s%s" % 
        (int(time.time()),random.randint(100000,999999)) 
        ## end file 
        
    • web2py runs on jython.
    • No support for stackless python [yet] and using it actually causes errors
  • Web2py philosophy is not to use separate and un-integrated modules.In fact for some of the web2py components there is a better module out there, the point is that the web2py modules are designed to work together while the alternatives are not.
    You will not appreciate this until you try it. Here is an example: Pygments is a much better general purpose syntax highlighter than the web2py’s one, nevertheless web2py’s one can highlight web2py code, create clickable links from web2py keywords to the web2py online documentation, is faster and fits in 10k. If you need to syntax highlight Java or PHP code you can still easy_install and use Pygments. Although you can, the web2py modules are not designed to be taken out and used separately.
  • Web2py’s philosophy is : if you want scalability, it is best to store as much as possible on disk and use a centralized database when you need transactions and joins.
    This is why web2py by default stores sessions and files on disk.
  • Web2py vs others
  • From Django to web2py
  • From TurboGears to web2py
  • From Rails to web2py
  • Web2py for J2EE programmers
  • from php to web2py
  • web2py can run on a cluster environment like OpenVMS.In fact This is not a problem on web2py as long as all processes see the same
    filesystem. If they do not see the same filesystem you will not be able to use sessions, tickets and upload files (unless you do some
    tweaking)
    Check this section in the manual book
  • web2py suits agile development, with an interactive shell used for testing, debugging and experimenting with your applications.
    you can run interactive shell that will run in ‘ipython’ 😀 by doing something like:

    cd web2py
    python web2py.py -S application_name -M
    # -M means to import also all the models in the application
    

    and congratulations, you can do something like:

    print db.tables
    

    to get list of all database tables used by this application.
    You can ofcourse do more .
    In fact one of the interesting stuff that you can make is:

    python web2py.py -S application_name -M -R file.py
    

    the interesting thing is that file.py can include code that will be executed as if it were a part of a controller.
    If the application specified by -S doesn’t exist, shell offers you to create it. isn’t that cool? 😀
    you can also import your custom modules and use it inside shell:

    exec('from applications.%s.modules.my_custom_mod import *' %(request.application))
    

    Your module will run in its own context, so if you want to do some operations like manipulating request, or some database stuff, you have to pass those objects to your module functions or class explicitly when calling them, this implies of course that you define your functions or classes with that in mind

    class DoThat(object):
        def __init__(self, request, db, cache, T):
             .....
        ......
    

    To execute a model file of another application:
    [soucecode]
    execfile(os.path.join(request.folder,’../other/app/models/db.py’))
    [/sourcecode]

    You can use the previous mechanism to execute other application controllers but there may be issues in importing controllers in other ways since they may contain authorization code outside functions. and it’s not a good idea to import controllers .

    you can even run the shell without ipython if you just don’t want it
    simply pass the -P (uppercase) argument when trying to run the shell.
    Warning:
    In the shell you have to explicitly db.commit() or db.rollback() your transactions.

  • Web2py actions support doctesting, but becareful …. doctets are not thread safe. This is not because of web2py. All
    testing libraries in python are not thread safe because they redirect
    sys.stdout.
  • In fact The web based administrative interface can only test
    doctests in controllers. The shell has a -test option that will run
    any test you like and this does not require web2py running.
  • Web2py admin interface doesn’t allow remote access unless it is secure, other wise connection is denied.
    You can access it locally though without these restrictions, only admin password is required.
    Admin should be used for development and should never be exposed.
    If you want to expose admin:

    • make sure it goes over HTTPS
    • always logout when done
    • Edit admin/controllers/default.py and at the top write:
      response.cookies[response.session_id_name].secure=True
      

      The secure cookies are not enabled by default because admins usually access the admin application through their lcalhost, but it you’re tending to access it remotely you should add secure cookies.

    • Moreover all the */appadmin/* pages should go over https.
    • Another way , that may or may not be easier , is running two
      instances of web2py on different ports. One is exposes by without a
      password and so no admin. One is not exposed, runs only on localhost
      and has a password for admin. Then you connect to the second via a
      ssh tunnel. This is very secure, easy to setup, and you can do
      everything you do now.

    If somebody intercept your communications and steals the session
    cookies for admin, they can become admin.
    These rules apply to web2py as well as to every system that does
    authentication. even gmail has this problem.
    To make things even more secure admin session is default to expire after 10 minutes.

  • Web2py forms including SQLFORM, SQLFORM.factory, all inherit from class FORM and you access their components via form.components:
    ex:

    form.components[0].components[0].components[1]
    

    or you can deal with forms as list of lists and edit its components according to your needs [just print a form and youi’ll know how to access it using this method.

    form[0][..][..]
    

    Warning:

    It is never a good idea to access form components since the internal
    implementation may change in the future (Although there ‘re no plans to do so).
    you can access them that way if there’s no other way to do so.

  • Web2py allows you to byte code compile applications and distribute them .
    you can even distribute your applications in a compiled binary files.
    This way, one of the advantages is that templates are only parsed
    once when the application is compiled and this makes web2py apps fast
    Warning:
    If you are using the binary distribution of web2py. It ignores
    your python installation because it uses its own.
    This may cause some errors when trying to import some modules.
  • Web2py is an MVC frame work , while all models are executed for every request.
    Technically a model file is not a module because they access symbols
    that are only defined in the context they are intended to be executed
    (file request and response). So importing a model explicitly can
    create problems.
    Put stuff in models if:

      Have something to do with accessing db data or helpers for visualizing db data
      They consist of a one single file with no dependences
      The code only makes sense inside a web2py app and I would not be reusing it outside web2py

    To create your tables, you may either do it manually or use a web based tool for this purpose.
    It’s able to generate web2py’s DAL compatible code.
    This great tool can’t be a part of web2py framework itself because its license conflicts with web2py’s license that have an exception to ship the binary web2py code with your web applications without the source code as long as you didn’t touch the source code .
    You can’t use controller functions in another controller because technically controller is not a module.
    you need to gather all your common functions,and put them inside a model or module, or even in gluon.contrib so that they can be accessible to all your applications without no problem

    When a request comes to a controller the requested controller file is loaded this is not achieved using reload() but exec() which executes the controller file and only takes about 0.03 seconds only.
    Using exec() also has the advantage that the framework does not
    need to monitor modules for changes before reloading (like Pylons)
    which may cause a slow down for apps with multiple files. web2py only
    execs those controllers that are requested.

    For framework like django, application is just the reuse unit, but not the execution unit. And in web2py app is execution unit, but
    not reuse unit. For example, I have user management app, and I want to reuse it, in Django, I can import the user model, user controller
    functions, and use template also. But in web2py I can’t directly
    import and reuse them. Only I can think out is copy the file which I
    want to use to target app. There are many ways to combine different apps together, but the usage of exec make it difficult. And how to orginaze the development unit and execution unit is a design issue. If we only care about the execution unit, so how to use others app functionalities in our app? Only through xml-rpc? Or copy the files into the application.

    In the contrary:

    There are also other problems with modules related to the search path
    and different applications may end up with conflicting modules.
    There may also be conflicting classes because all modules would be
    imported in the same context and if two modules have classes with the
    same name they would overwrite each other. This is not an issue if one just made an import like:

    from app1 import Class1
    from app2 import Class1
    

    Now you can use app1.Class1 or app2.Class1 wihtout any problems.

    In web2py exec() behavior is similar to:
    For example:

    a=""" 
    class A: 
        name='a' 
    print A.name 
    """ 
    b=""" 
    class A: 
        name='b' 
    print A.name 
    """ 
    c={} 
    exec(a,{},c) 
    exec(b,{},{}) 
    exec('print A.name',{},c) 
    prints 
    a 
    b 
    a 
    

    which is nice: the two classes called A ignore each other. If you use
    modules you end up with a lot of potential conflicts between multiple
    apps. In Django for example you would have to careful in designing
    apps that do not conflict. In web2py you do not need to think about
    it. You have to do a little of extra work to share stuff but at least
    you know for sure that no unwanted sharing takes place.

    the ‘exec’ have a good thing , that is the module can be GC by python afte request is over.

    sometimes you need to call some controllers functions and pass to them some variables[request variables] to make some functionality based on the values of these variables.In this case you may face one or more situations like those:

    • If you are doing a redirect to a controller function that is
      supposed to receive user input, you use :

      URL(r=request, c='controller_name', args=[...], vars=dict(..=.., ..=..))
      

      args are list why ? It allows you to do ‘/’.join(request.args) and rebuild the original string easily.

      vars is a dictionary why ? because this is the best fit for it key/value pairs .

    • If you want to call a controller function that is not intended to
      receive user input and is in the same controller, save the variables
      in session and retrieve them later.
    • If you want to call a controller function that exposes some
      functionality but has no state (so no use of sessions), expose it via
      xmlrpc .
      You can use urllib2.urlopen to call other functions, so you can do something similar to the following
    • example:
      Warning: This’s just an example made for test purposes to clarify things.
      Assume application name is ‘t2’

      def index():
          return dict(url=A('click here', _href=URL(r=request, f='another', args=['a'], vars=dict(b='b'))))
      
      def another():
          print response._caller(another)
          import urllib2, urllib
          if request.args(0) and request.vars.has_key('b'):        
              test_values = {'hidden_var' : 1}
              data = urllib.urlencode(test_values)        
              try:
                   req = urllib2.urlopen('http://127.0.0.1/t2/default/third?%s'%data).read()             
                   return dict(x=XML(req))        
              except Exception, e:
                   print 'Error:', e
      def third():
          form = SQLFORM(db.my_table, request.vars.hidden_var)
          if form.accepts(request.vars, session):
              pass
          return form
      
  • Web2py includes a separate folder for your custom modules that you want to use, in this way you can use your own python libraries in your web application.
    things to note when using modules:
    Put stuff in modules if:

      The do not require access to request, response, cache, session and thus can be used with ot without web2py .
      They consist of multiple files
      I need them only in some (but not all) controller functions.
  • whenever you want to import something from a module don’t do something like:

    • from applications.yourapp.modules.Custom_Validators import IS_AAA
      

      making your import dependent on the name of the app is not a good idea, instead use something like:

      exec('from applications.%s.modules.validators import IS_AAA' %  
      request.application)
      
  • modules can’t see session, request, response objects of web2py, if you want to make operations using one or more web2py objects, you’ve to make a module function that takes those objects as its parameters.
  • The first time you need to run web2py with all its files. After that
    you can remove any installed application you want including admin,
    examples and welcome.

    Nothing breaks except that you can no longer visit pages that you do
    not have. You only get an internal error if you try to do that.

    welcome.tar is the scaffold application. That’s the one file you
    should not remove.

    Notice that if you remove admin you can no longer login as
    administrator and the database administration (appadmin) will be
    disabled for all you apps, unless you adit the appadmin.py files and
    change the authentication mechanism.
    You may need to rename your applicationa ‘init’, so that it can be the default application (the one that starts, once you start web2py) .

  • In web2py (and many web frameworks) each request is served by a different thread. This if done for speed. The different threads share
    the same memory space and the same environment variables. By default, you cannot execute in a thread any function or module that attempts to change environment variables or gain exclusive access to memory or other OS resource without mutex locking.
    For example you cannot do os.chdir(‘somedir’) else all threads would
    change folder and web2py would behave weird.
    There are ways many around this. Run processes, not thread; perform a
    mutex lock; create a background process that access exclusively the
    thread-unsafe module.
    So be careful :
    When ever making a web application using web2py, DON’T CHANGE THE CURRENT WORKING DIRECTORY , DON’T USE os.chdir() in your web applications.
    Changing working directory may cause your application to fail and moreover it’s not thread safe.
    To know more about this issue and more, check this interesting topic in the web2py user group:
    Very, Very interesting discussion

  • web2py can serve xmlrpc requests (even to itself, without deadlocks).
  • when uploading files using web2py, they’re renamed by adding to the name a random string .
    This is important for situations like :
    If the real name conflicts with another file or
    contains characters not supported by the file system
  • Web2py provides an easy way to access cookies:
    The value can be a string or a Morsel Object .
    Check that for more info
    Do Not Use cookies your self, and store everything always server side.
    Let web2py manage session cookies for you, it’s done automatically and you need not to worry about any thing.
  • web2py provides a good mechanism for internationalization through the T object that supports the lazy translation mode and immediate mode
    More information about T
    T returns an object that can be serialized as a string
    web2py does not allow you to do :

    T("bla")+T("bla")
    

    but it does allow :

    T("bla %(name)s",dict(name='Tim')) 
    

    why?

    • You need to be careful concatenating strings. String concatenation is one of those “no-no”s of internationalization.
      The reason for this is that different languages have different sentence
      structures.For instance, some languages negation is before a word and other’s negationis in other parts of the word.Many slavic languages, like Czech, have the interesting feature that word order isn’t particularly important, so for Pete’s sake don’t start with one of those as your base language that everything’s translated too.The worst example is something like German where a negation can happen in two different parts of the sentence, and you just get screwed concatenating that string.If you’re gluing together strings, or you need to put dynamic content in an internationalized string, it’s really best if you put it in a string that’s parameterized: “hello %(name)s”, dict(“name”: “Tim”)
  • Web2py makes a good use of decorators to make your life easier
    for example:

    @auth.requires_login()
    @auth.requires_memebrship('admin')
    def do_it():
        # This won't be executed unless user is logged in and belongs to the 'admin' group
    

    Web2py philosophy is not to use decorators for validation since decorators are associated to a function (i.e. a page) while validation is associated to a form (and a page can have multiple forms).
    A form should be an object that knows how to validate itself.

  • Web2py mixes between the database tables and forms that should enter data into those tables; by using SQLFORM you can deal with a database table and enter data into it directly.
    ex:

    #in a model:
    db.define_table('my_table', Field('my_field', 'string' ,requires=[....]))
    #in a controller
    form = SQLFORM(db.my_Table)
    .........................................
    
    • “requires” is assigned one or more validators [they work on form level not database level], i.e IS_NOT_EMPTY()
      To make a mix between validators you just add them to the list assigned to “requires”
    • A validator is a two way filter.
      user_input -> validator -> data_for_db OR error
      user_output <- validator <- data_from_db
      
    • using multiple validators at the same time makes sense and it’s just as if they’re ANDed together.
    • AND makes sense because it is like piping validators. When data
      goes in it goes through one validator after another and has to pass
      them all. If it misses one that one returns the error message. When
      data comes out it goes through the same validators in reversed order.
    • OR is problematic because if user input does not pass any validator,
      which error should be generated? The error is different that the
      errors associated to the individual validators.
      Moreoever when data comes out which validator should do the
      formatting? Consider this example:

      IS_DATE('%Y-%m-%d') OR IS_DATE('%d-%m%Y')
      

      This cannot be.
      To understand this well, consider this example:
      consider IS_DATE
      it has a constructor a __call__() method and a formatter method,
      when data is validated in a form it is filtered by IS_DATE.__call__
      and a string, say ’01/01/2007′ is converted to a datetime object.
      when data is presented into an edit SQLFORM or a SQLTABLE, formatter , it is called and the datetime object is converted back to ’01/01/2007′.
      The constructor takes an argument that specifies how the formatter
      can be done.
      If you have a list of validators and they all pass the the __call__
      method, they are called in the order of the list and the formatter methods are called in reversed order.
      Not so many validators use formatter method, but the problem is that : wil this continue for ever ?
      People can write validators that take formatter method and thus using OR will be problematic.

    • The bottom line is that in order to implement an OR
      some combination should be allowed and others should be forbidden. This will make individual validators more complex objects that they are now and their behavior less intuitive.
    • The only Validator to use OR is : IS_EMPTY_OR()
      ex:

      IS_EMPTY_OR(IS_URL())
      
  • To build a great and rapid development environment, dictionaries returned by web2py’s actions [controller functions] are automatically rendered by a generic view , without the need to make a view file for this action .
    This’s done as you may know using a generic view files that use the
    response._vars which holds the returning values of the action [function]
    and is rendered using

    {{=BEAUTIFY(response._Vars)}}
    
  • Web2py can supports both POST and GET variables together sent with each other and you can extract each set.

     request.post_vars, request.get_vars
    

    Web2py takes care of the situations like when MS-Windows user input data which will be sent with ‘\r’ in the end of your input [‘\n\r’ is the line terminator in windows but not in linux .. In linux it’s just ‘\n’] .any way user input will have to be filtered using something like:

    string.replace('\r', '')
    
  • One another greatest thing is that you may not create a view file for every action you want to expose:
    • you may edit the generic view file [generic.html] which includes the following lines of code :

      {{extend 'layout.html'}}
      {{"""
      
      You should not modify this file. 
      It is used as default when a view is not provided for your controllers
      
      """}}
      
      {{=BEAUTIFY(response._vars)}}
      
      <button onclick="document.location='{{=URL("admin","default","design",
      args=request.application)}}'">admin</button>
      <button onclick="jQuery('#request').slideToggle()">request</button>
      <div class="hidden" id="request"><h2>request</h2>{{=BEAUTIFY(request)}}</div>
      <button onclick="jQuery('#session').slideToggle()">session</button>
      <div class="hidden" id="session"><h2>session</h2>{{=BEAUTIFY(session)}}</div>
      <button onclick="jQuery('#response').slideToggle()">response</button>
      <div class="hidden" id="response"><h2>response</h2>{{=BEAUTIFY(response)}}</div>
      <script>jQuery('.hidden').hide();</script>
      
      

      this stuff is useful in development time since it gives you buttons to check session variables, request and response but in production you may just make it look like:

      {{extend 'layout.html'}}
      {{=BEAUTIFY(response._vars.values())}}
      

      and bingoo !!!!!! you get every thing almost automated and you don’t need to add one view file per action .
      Ofcourse this is not practical all the time , and in real-life you always have some thing to add to the view but sometimes you don’t have 🙂
      and you can rely on the generic.html then to render your view.

    • Another situation is that you can make use of one view file in different actions in different controllers … wooooow !!!!
      look at this example :

      def test():
           response.flash=T('Welcome to web2py')
           response.view='default/view.html'  ### this indicates the view file
           return dict(message=T('Hello World'))
      
      #Here's the same version of the function but more suitable for caching the view .
      
      @cache(request.env.path_info, time_expire=5, cache_model=cache.ram)
        def test():
           response.flash=T('Welcome to web2py')
           response.view='default/view.html'
           return response.render(dict(message=T('Hello World')))
      
      
    • Web2py views are different than django’s and Macko templates, in web2py : If a variable is accessed in the view, if has to be defined.
      in django, it just ignore it
      so in your view in web2py, if you’re returning a form that may sometimes have no value, you’ve to make additional check in the view:

      {{if reponse._vars.has_key('form'):}}
      # or
      {{if form:}}
      
    • One another interesting feature that suits the rapid development environment is that you can have multiple views per action(controller function), that is it , you can render output as html, rss, xml.
      You can do this by either depending on the generic views files “generic.xml, generic.xxx, …” that will render different actions based on the extension you provide in the url or you can make your own views, ending with different views for one action like [index.html, index.xml, ….]
      You can just open up one generic view file and follow the same behavior in your custom view files for other extensions other than html

      def index():
         form = FORM(INPUT(_name='Name'), INPUT(_type='submit'))
         if form.accepts(request.vars, session):
            pass
         return dict(form=form)
      

      Now trying to access page as index.html will return the form as expected, but trying to access index.xml will return something like:

      <document>
      −
      <form>
      <form action="" enctype="multipart/form-data" method="post"><input name="Name" type="text" /><input type="submit" /><div class="hidden"><input name="_formkey" type="hidden" value="43ab2943-2f1c-4ae6-bdaf-73e3224967ee" /><input name="_formname" type="hidden" value="default" /></div></form>
      </form>
      </document>
      

      Can you see the beauty of this? You can use similar behaviors for debugging without trying to print the form itself in your code.
      Faster right ? easier right ? 😀

  • No daemons [except for cron]
  • Daemons tend to need starting/stopping and affect application portability.
  • If you’ve long running processes run by a thread , server would kill it any way. you need your daemon to run it
  • It is not a good idea to store the entire file in the db. better to store name only. That’s why web2py saves uploaded images to the directory called ‘uploads’ and the name in the db.
    In fact looking into gluon.sql.py, you’ll find something like:

    if fieldtype=='blob': 
             obj=base64.b64encode(str(obj)) 
    

    web2py is not currently using blobs by the book. The reason is that
    blob values need to be escaped differently by different database
    backends and in some cases this is not well documented. web2py avoids
    the problem by storing the base64 encoded data, thus using the blob
    as a text field.
    The current behavior has both advantages (simpler code, faster encoding, humanly readable SQL string all the time, works with databases that may not have a blob type) and disadvantages (the storage is increased by 30%, you cannot search with LIKE,, in a blob).

  • Field('blobf','blob')  # Not Recommended
    Field('uploadf','upload') # Reommended
    
  • There’re 3 reasons for using error tickets for displaying errors:
    • Not to separate between production and debugging modes.
    • It is conceptually possible to cause bad errors that Web2py
      may not be able to read the file that contains the error and even
      requires web2py restart.
    • No configuration files for the framework itself, but you can make your own configuration file for your web application.
  • Error messages are by default saved on file system not in database why?
    Simply because in development time most errors come from database itself, and thus errors won’t come if they’re stored there.
    And because web2py’s philosophy is than no separation between development and production stages, it’s the best choice to have errors stored on file system.
  • No more template languages, only python code.
    This doesn’t mean that you can’t use other template languages.
    You can use easily use your favorite template language.
    Django’s philosophy is to let view to designer and use template languages because in most cases designers are not programmers, but this’s not web2py’s philosophy.
    In fact, using web2py you can return to the view all elements that you want and just let designer control how things look like and when a designer needs to do some customization, he can still use usual HTML tags.

    • In your view you can use {{}} to write your python code into it
    • Multi lines are allowed between single {{}}.
    • no indentation is required, when using python in views
    • if statements, for, and while need to be closed using {{pass}} because in view , there’s no indentation
    • {{if x > 6:}}
      {{do_some_thing}}
      {{else:}}
      {{pass}}
      
    • web2py can have any implementation of any template language, its design allows so but why would one trade python for a template language ? !!!
  • unpickeled objects stored in session causes problems.ex: trying to store in session a database table object causes aplication to crash
  • session.table = db.my_table   # Don't do this
    

    SQLDB objects are not pickleble because of the open connection.
    In fact you cannot store objects that belong to a class starting with SQL*, i.e. connections, record sets, queries, individual records, etc. You also cannot store classes (only objects), functions and lambdas.
    [classes starting with SQL like SQLFIELD are having short names now like Field, ….. both names are working in fact ]

    user = db(db.users.username==username).select().first()
    You cannot store a user object in the session.The reason is that user has a method user.update_record which requires a db connection and it is not serializable.

    If cPickle.dumps(obj) does not raise an exception than you can do
    session.obj=obj

  • web2py provides an ajax app that provides a python console.
    It uses pickle to store state therefore it is somewhat limited in what it can do and it has security issues but it is a fun toy to play with and show off python.
    Too bad in its present for it cannot be used to interact with SQLDB
    which is not pickleble because of the open connection.
  • It is standard in MVC frameworks including web2py that each page/form is a self submitting and it is uniquely associated to a controller function (Rails calls them actions). The reason is that a form knows how to validate its own input variables. Upon validation the page redirects to a different page(action). In web2py URL builds URLs within web2py.
    So generally it’s a bad behavior to have a form to validate inputs of another form

    For more Information click here

  • web2py prevents double submission of forms using a unique key passed to the form accepts() function via session.
    It makes sure if you click on a form twice, it is processed only once. This prevents user error and some forms of reply attacks. If you do not pass the session variable this mechanism is disabled.

    if form.accepts(request.vars, session)
    

    This mechanism fail though if you’ve multiple forms in the same page
    and in this case we should pass to the accepts function of every form explicit and different form names to distinguish between forms when they’re being accepted.

    if form1.accepts(request.vars, formname='custom_name'):
        #do_something
    if form2.accepts(request.vars, formname='custom_name2'):
        #do_another thing
    
  • Web2py had its own Database Abstraction Layer, the thing that bothers some people and push them to argue about the benefits of this.
    and why not using say for example SQLAlchemy the famous ORM.
    SQLAlchemy is better than the web2py ORM in dealing with legacy databases. web2py has restrictions in this case. But the web2py ORM is much better integrated with the rest of the framework than SQLAlchemy is integrated with, for example, Pylons or TurboGears. If you do not use the entire web2py framework you are better off with SQLAlchemy. If you do use web2py, you are better off with its own ORM.

    Any way the new Google appengine Bigtable datastore makes any SQL-based ORMs moot now anyway, so all we really need to worry about are the business logic and presentation, both of which web2py excels at.

  • web2py’s Database Abstraction Layer (DAL) supports many kinds of RDBMS like sqlite, mysql, oracle, …..
    In fact any new application created by web2py has a support of sqlite by default and this’s on purpose because this provides a quick solution for making an application that’s up and running on the fly without many configurations required or something.

    To change this [may be in a real production mode], you’ve to change one line in models/db.py from

    db = DAL('sqlite://storage.sqlite')
    

    into the appropriate dbms url in your system, for example to support mysql you simply have to do :

    'mysql://username:password@localhost/test'
    
  • More interestingly, you can use DAL without database using :

    db = DAL(None)
    

    then you can define tables, and create SQLFORMs from those tables and test the validation mechanism
    and generate the real sql queries generated by DAL and that are hidden by the DAL syntax

    # Model
     db = DAL(None)
    db.define_table('members', Field('name', requires=IS_NOT_EMPTY()))
    
    #Controller
    def index():
        form = SQLFORM(db.members)
        if form.accepts(request.vars, session):
            response.flash = T('hey it worked')
        rows=db().select(db.members.ALL)
        print db._lastsql
       return dict(form=form)
    

    Congratulations !!! without need to introduce extra/new syntax.
    Internally DAL(None) behaves like a sqlite db and you can use it test query generations as well but there is no sqlite file so nothing is stored and no overhead.

  • Things to take care of when using sqlite though are :

    • SQLite locks the database. Only one thread can safely access it.
    • Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

      If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

      For example, suppose you have a table named “t1” with columns names “a”, “b”, and “c” and that you want to delete column “c” from this table. The following steps illustrate how this could be done:

          BEGIN TRANSACTION;
          CREATE TEMPORARY TABLE t1_backup(a,b);
          INSERT INTO t1_backup SELECT a,b FROM t1;
          DROP TABLE t1;
          CREATE TABLE t1(a,b);
          INSERT INTO t1 SELECT a,b FROM t1_backup;
          DROP TABLE t1_backup;
          COMMIT;
      
    • To learn more about sqlite you can take a look at :
      Features not supported by sqlite
      Sqlite faq

  • Previous limitations belong to Sqlite only, other dbms runs smoothly
    In fact to ensure that web2py is helpful in an agile environment,
    web2py’s Database Abstraction Layer (DAL) supports migration,
    so you can easily make something like:

    db.define_table('my_table', Field('my_field', .....), migrate=True)
    # migrate=True
    

    setting migrate to True comes in rescue in the development time, you can just change the structure of your table [add more fields, remove ones] and your changes will take effect on the fly, upon the next request [all models are executed at every request and thus those changes will take effect then if migrate = True].
    This fact can lead to an interesting stuff, such as that you can make a const that holds the value of the migrate variable and set it to True while development and return it to False when you’re ready to publish your work.

  • web2py DAL does not perform any hidden Database IO. You have to call count, insert, delete, update or select to do any IO. This is a feature, not a limitation.
    At the current time no need for an ORM on top of it because the current DAL is more usable then existing ORMs.
  • Web2py DAL makes a good work for preventing uncompleted commitments to database to ruin your data
    All calls are wrapped into a transaction, and any uncaught exception
    causes the transaction to roll back. If the request succeeds, the trans-
    action is committed.
  • DAL supports distributed transactions
  • Web2py DAL provides us with some interesting stuff that makes your life easier :
    • db().select(db.table_name.field, orderby='<random>')
      
      You can get a random order every time the query is initiated.</li>
      
      
      <li>
      db._lastsql -> returns the last sql code generated by DAL
      
    • most web2py expressions can easily be serialized in SQL. For example:
      myquery=(db.table.field==value)|(db.table.id>10)
      

      Warning Warning Warning :
      Always use when ever doing queries the operators ‘&’, ‘|’ , Don’t use ‘and’, ‘or’ .

    • you can print str(myquery) and see the SQL that corresponds to the
      query.
    • methods :
       .delete(), .update(), .insert() and .select() 
      

      they all have a :

      ._delete(), ._update(), ._indert(), ._select()
      

      that instead of performing the operation return the SQL that does.

    • You can list all tables in database using : db.tables() which leads to some interesting stuff like:
      db.define_table('rating', Field('table_name'), Field('record_id', 'integer'), Field('rate', 'integer'))
      
      db.rating.table_name.requires = IS_IN_SET(db.tables())
      

      and whenever trying to insert some record you can do something like:

      db.rating.insert(table_name = str(db.game), record_id=..., rate=...)
      
    • DAL supports ‘NOT’ using the ‘~’, so you can do something like:

      db( ~db.table.field.belongs((1,2,3))).select() 
      
    • Web2py’s DAL supports limitby for pagination.Supporting limitby in oracle was a problem since oracle doesn’t support it.
      Both web2py and Django support this feature differently, While the code generated by Django is more readable, it is slower
      because it asks Oracle to first select all rows, then select the
      limited subset. The web2py way does it with three nested select that,
      as discussed in This article , allows Oracle to perform additional optimizations (and it works with joins too).
  • web2py’s Database Abstraction Layer (DAL) supports unicode by default
    you can check for that in gluon/sql.py

            charset = m.group('charset') or 'utf8'
             self._pool_connection(lambda : MySQLdb.Connection(
                        db=db,
                        user=user,
                        passwd=passwd,
                        host=host,
                        port=int(port),
                        charset=charset,
                        ))
            .........................................
            .........................................
            # table should use this utf-8 when creating it
            if self._db._dbname == 'mysql':
                fields.append('PRIMARY KEY(%s)' % self.fields[0])
                other = ' ENGINE=InnoDB CHARACTER SET utf8;'
    
  • To deal with oracle database, web2py needs cx_Oracle
  • single quotes are escaped in Oracle SQL using repeated single quotes and web2py takes care of this
  • DAL makes a great job in simplifying selecting from database, so you can do :
    db(db.my_table.id>0).select()
    or
    db((db.my_table.id>0) & (db.my_table.second_field == 'value') ).select()
    or even easier :
    db(db.my_table.id>0)(db.my_table.second_field == 'value').select()
    
    so:
    db(a)(b)(c) is the same as db((a)&(b)&(c))
    in fact the former is interpreted as taking the (c) subset of the (b)  
    subset of the (a) subset of db. 
    
  • dropping a database table manually causes web2py to fail and not working, with an error message that table doesn’t exist? while this doesn’t happen in case of dropping the table through web2py and the reason for this is :
    • when tables are created the first time web2py makes a .table file under applications/yourapp/databases
      that is how keeps track of table structure.
    • If you drop the tables manually, you need to delete those files as well or they will not be re-created.
  • You can make 2 web2py’s applications share the same database, you can copy the model files within database tables are defined to another application and start to use it.
    One proble though is that there should be only one application that is allowed to create tables and the other should have ‘migrate=False’ s the last argument in db.defin_table()

    Web2py keeps track of the migration files in a folder called database, web2py generates a random name for it and you should [if you tend set migration to True to give it a name]

    migrate='table_name.table'   => '%s.table'%table_name
    

    if the database is re-factored with a name change, we wouldn’t want to lose the data from the column, just have the column name changed
    So what to do?

    Find the .table file for that table and set migrate= to that filename.
    One should actually use migrate=”mytable.table” to give a name to the table files from the beginning create instead of sing the cryptic
    names created vy web2py.

  • You can’t reference other tables using other fields than their id, which is a good practice and in the same time efficient (strings need to be hashed while integers don’t. )

  • Dal has some limitations though:
    • Doesn’t support inheritance, and although you can do something like:
      db.define_Table('my_table', ....)
      db.new_table = db.my_table
      

      There’s no sync between them, changes in one of them are not reflected in the other.

    • Doesn’t support multiple insertion in the same query.
    • No support for special field types(Enum, Array e.t.c) and XML fields in POSTGRESQL.
    • You can’t rename a column, if you renamed it in your model, this will drop the old and create a new empty one [in case migration=True].
      To rename a column, just :
      1)create the new column.
      2)drop the old one.
      3)copy data
    • No support for self referencing foreign key [No parent/child relationship support], instead you can make an integer field and us it to store a self referencing key, with the parent field has the value of this field set to NUll or -1.
    • many-to-many relationship in web2py has to be explicit (as in rails, not as in Django). This means you have to create the intermediate link table.
      The intermediate table in a many-2-many relation, in the general case, can hold more than two keys.
      You can use sqldesigner, but you need to create the intermediate table to link the many- to the -many.
    • DAL doesn’t support something like :
      table A depends on table B
      table B depends on table C
      table C depends on table A
      
  • In web2py, menus can be created automatically :
    • check menu.py in your models in your web application folder
    • you can also check for the current used action in a menu [To make an active item]
      response.menu=[
      ['item name',(request.function=='action'), URL(r=request,f='action',vars={}, args=[])],
      ]
      

      as you can see you check with request.function==’action’ where ‘action’ is the name of the function called [change it to the name of the controller function that is referred to by this menu item]

  • Making a private function is easy in web2py :
    • Functions defined in a model are private.
    • Functions that are defined in controllers and that takes arguments are private.
    • Functions defined in controllers and start with ‘__’ [double underscores] are private.
      But functions starting with single ‘_’ are not private and they should not be because some people use them for an ajax callback.
    • Functions defined in controllers and having a space after the () and before the ‘:’ are private.
      ex:

      def pri_action() :
      # do some thing
      
  • web2py provide us with many many HTML helpers that makes your life easier and instead of writing an HTML code all the time, you can use them as [python code] either in the view or in your controller functions (actions) .
    They do a grat job and makes you use python instead of HTML code in your actions.

    A('Download', _href=URL(....)) instead of <a href=''>'Download'</a>
    

    You can also have a server side highlighting using CODE helper that will display code in HTML with highlighting.

     {{=CODE(\"print 'hello world'\", language='python', link=None,
                counter=1, styles={})}}
    

    another example:

    BR -> <br/>
    
    P(..., cr2br=True)  Will replace ``\\n`` by ``<br />`` if the `cr2br` attribute is provided.
    
    P() -> <p>
    
  • You need to know that the philosophy of web2py HELPERS is:
    • Not to deliver all possible HTML helpers since it’s very easy to do a one , just look at gluon.html.py and you’ll see how easy it is
      you just make a class that extends the DIV class and into it you type
      tag = ‘whatever tag you want to implement’ .
      ex:

      class FIELDSET(DIV):
          tag = 'fieldset'
      

      The fieldset tag should just wrap whatever is in it, since most designers never use tables to format forms.
      The fieldset tag helps create visible rubrics with very large forms

  • In web2py, you can return result to a user while making some computation that takes longer time.
    • For this don’t use forking because it doesn’t work in windows.
    • If the computation can be interrupted you can have an ajax call from
      the client initiate the computation in a separate controller function.
    • If the computation can not be interrupted I would run it a separate
      thread (not process). Mind that wsgiserver will kill threads that run
      too long.