Tuesday, December 27, 2011

web2py: new appliances web site

Oh, my app I developed for final project for Massimo's class is now available on new appliances site.

Restaurants Listing

Tuesday, December 20, 2011

web2py: social gathering in Chicago

We had a social gathering at Exchequer Restaurant & Pub which is very close to DePaul Unitversity.


226 S. Wabash Avenue
Chicago, IL 60604
Phone: (312) 939-5633

It was fun meeting with the small local group and let's do it again !
I missed the train at 9:40, waited until 10:40 then got home almost 12:00... feel so sleepy today.

Monday, November 21, 2011

web2py: hide register link on page

The following code will hide the link but also disable the function to register user completely even you create the record from appadmin menu.


So here's how I do...

# all we need is login
if request.controller != 'appadmin':
    auth.settings.actions_disabled +=['register']

In this way, it's disabled for users but not admin.

web2py: add additonal field in auth_user

It's actually very easy. Just add the following code in your model before auth.define_tables().

## additonal fields
auth.settings.extra_fields['auth_user']= [

So it will be like this...

from gluon.tools import Auth, Crud, Service, PluginManager, prettydate
auth = Auth(db, hmac_key=Auth.get_or_create_key())
crud, service, plugins = Crud(db), Service(), PluginManager()

## create all tables needed by auth if not custom tables
## additonal fields
auth.settings.extra_fields['auth_user']= [

Wednesday, October 19, 2011

web2py: how to connect existing tables

Tested environment:
mssql 2008

You need to make sure you disable Migration otherwise it might alter your tables. I recommend to specify at connection level (not each table) so that you don't forget.

db = DAL("mssql2://YourID:YourPassword@YourServer/YourDB", migrate=False)

If table has Primary field called id
You're lucky. This meet with the convention of web2py

You can connect with the following.
If table has primary key field called ID
Shoot, it's a capital letter... don't worry the code above will still work !

If table has primary key field called myid
Why didn't I name the field carefully.... no problem. You can still connect.
Since it's not field called id/ID, you need to define your primary field just like others and set primarykey for it.
If table has No primary key field
What's wrong with me ? ... don't worry here's what I found. You know what, the above code still works !

Friday, October 14, 2011

How to set up web2py + ldap with Windows Active Directory

This is a recipe to use web2py + ldap in the real world.

Install python-ldap
1. Download and install  python-ldap (e.g. python-ldap-2.4.3.win32-py2.7)

Edit models/db.py
2. edit auth.define_tables() to allow login with username and not email.


3. Add the following at the bottom of page.
Replace server and base_dn to your setting.

# all we need is login

# you don't have to remember me
auth.settings.remember_me_form = False

# ldap authentication and not save password on web2py
from gluon.contrib.login_methods.ldap_auth import ldap_auth
auth.settings.login_methods = [ldap_auth(mode='ad',

4. Result

Wednesday, October 12, 2011

How to setup web2py + Apache + wsgi (Uniform Server)

The problem for pyodbc in my previous post will be avoided if you use Uniform Server which comes with Apache, mysql, php. Uniform server is very simple and portable (You can even run from USB memory) so I decided to use this until the Apache problem is solved on the next Win32 Binary.

The original instruction was provided by Paolo Caruccio at web2py-users forum.

Finished my previous post

Install Uniform Server
1. Download and install Uniform Server (Orion_7_1_11.exe)

2. Run Orion_7_1_11.exe, and Extract to: "C:\". This will create C:\UniServer folder.

3. Run C:\UniServer\Start.exe. You can access Uniform Server from System Tray.

4. Click Start UniServer (Apache MySQL).

Server certificate for https
5. If you have, place under  the following



From Uniform Server menu, go to Advanced - Server Certificate and key generator and follow the wizard. It will create the files automatically.

6. Download from mod_wsgi-win32-app22py27-3.3.so (This is for Python 2.7.x), renamed and place to C:\UniServer\usr\local\apache2\modules\mod_wsgi.so

Edit httpd.conf
7. Back up and open C:\UniServer\usr\local\apache2\conf\httpd.conf

7.1 Add mod_wsgi after all the other LoadModule lines

LoadModule vhost_alias_module modules/mod_vhost_alias.so
LoadModule wsgi_module modules/mod_wsgi.so

7.2 Add the following at the end of file and save

Include conf/vhost_web2py.conf

7.3 Create C:\UniServer\usr\local\apache2\conf\vhost_web2py.conf and put the following lines (Change ServerName, ServerAdmin to yours) and save.

##########VIRTUAL HOST SETUP##########
<VirtualHost *:80>
ServerName ochiba-183
DocumentRoot C:/web2py/applications
WSGIScriptAlias / "C:/web2py/wsgihandler.py"
ServerAdmin admin@abc.com
<LocationMatch "^(/[\w_]*/static/.*)">
Order Allow,Deny
Allow from all
<Location "/">
Order deny,allow
Allow from all
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogLevel notice
CustomLog C:/UniServer/tmp/web2py.access.log common
ErrorLog C:/UniServer/tmp/web2py.error.log
<VirtualHost *:443>
ServerName ochiba-183
ServerAdmin admin@abc.com
DocumentRoot C:/web2py/applications
WSGIScriptAlias / "C:/web2py/wsgihandler.py"
<LocationMatch "^(/[\w_]*/static/.*)">
Order Allow,Deny
Allow from all
<Location "/">
Order deny,allow
Allow from all
<Directory "C:/web2py">
Order allow,deny
Deny from all
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogLevel notice
CustomLog C:/UniServer/tmp/web2py.access.log common
ErrorLog C:/UniServer/tmp/web2py.error.log
SSLEngine on
SSLProtocol -all +TLSv1 +SSLv3
SSLCertificateFile C:/UniServer/usr/local/apache2/conf/ssl.crt/server.crt
SSLCertificateKeyFile C:/UniServer/usr/local/apache2/conf/ssl.key/server.key
SetEnvIf User-Agent ".*MSIE.*" \
##########END VIRTUAL HOST SETUP##########

7.4 From Uniform Server menu, Stop UniServer  (Apache MySQL) and Start.

7.5 Go to http://(Your Server Name) or https://(Your Server Name)

In my case, http://ochiba-183

7.6  you will see web2py welcome screen !!

How to setup web2py + Apache + wsgi

It's recommended setup to use web2py for production environment. This post is based on 11 Deployment Recipes on official book

Notice that current appache has a issue to load some modules such as pyodbc because it's compiled with older  visual studio complier. (See web2py-users post)

=> Brian M found the apache working with pyodbc ! (2012-05-25)
Yep, got burned by this when setting up a new server last month. The problem is covered in the pyodbc issue tracker here -  http://code.google.com/p/pyodbc/issues/detail?id=126 I ended up grabbing a build from  http://www.apachelounge.com/download/additional/ and then all was good.

Finished my previous post

1. Download and double click to install httpd-2.2.21-win32-x86-openssl-0.9.8r.msi

2. Input the required information if it's not already there. Typical setup is OK.

3. Go to http://localhost/. Apache is successfully running if you see "It works".

4. Download from mod_wsgi-win32-app22py27-3.3.so (This is for Python 2.7.x), renamed and place to C:\Program Files\Apache Software Foundation\Apache2.2\modules\mod_wsgi.so

Server certificate for https
5. If you already have, place to C:\Program Files\Apache Software Foundation\Apache2.2\conf\server.crt  and server.key

5.1 If not, go to http://smithii.com/node/117.  Copy "@echo off"  to  "popd" and create batch file called like ssl.bat and run.

5.2 Command-prompt will pop up, follow the wizard (Input pass phrase, Country name...)

5.3 server.crt, server.csr and server.key will be automatically added to C:\Program Files\Apache Software Foundation\Apache2.2\conf

Edit httpd.conf

6. Back up and open C:\Program Files\Apache Software Foundation\Apache2.2\conf\httpd.conf

6.1. Add Listen 443 after Listen 80

Listen 80
Listen 443

6.2 Remove comment mark (the # character) from mod_ssl.so
#LoadModule ssl_module modules/mod_ssl.so
LoadModule ssl_module modules/mod_ssl.so

6.3 Add mod_wsgi after all the other LoadModule lines

#LoadModule vhost_alias_module modules/mod_vhost_alias.so
LoadModule wsgi_module modules/mod_wsgi.so

6.4 Add the following at the last (Change ServerName to yours) and save

NameVirtualHost *:80
<VirtualHost *:80>
  DocumentRoot "C:/web2py/applications"
  ServerName ochba-183

  <Directory "C:/web2py">
    Order allow,deny
    Deny from all

  <Location "/">
    Order deny,allow
    Allow from all

  <LocationMatch "^(/[\w_]*/static/.*)">
    Order Allow,Deny
    Allow from all

  WSGIScriptAlias / "C:/web2py/wsgihandler.py"


NameVirtualHost *:443
<VirtualHost *:443>
  DocumentRoot "C:/web2py/applications"
  ServerName ochiba-183

  <Directory "C:/web2py">
    Order allow,deny
    Deny from all

  <Location "/">
    Order deny,allow
    Allow from all

  <LocationMatch "^(/[\w_]*/static/.*)">
    Order Allow,Deny
    Allow from all

  WSGIScriptAlias / "C:/web2py/wsgihandler.py"

  SSLEngine On
  SSLCertificateFile conf/server.crt
  SSLCertificateKeyFile conf/server.key


7. Restart Apache and Go to http://localhost/ again , you will see web2py welcome screen !!

How to install web2py from source

You need to install source version if you want to use additional libraries such as pyodbc to connect mssql/db2.

- Python 2.5 or higher (I'm using python 2.7.2)

1. Go to http://web2py.com/examples/default/download

2. Click Source Code on Current(for everybody) which is very left one.

3. Download web2py_src.zip and extract to C:\web2py

4. Double click C:\web2py\web2py.py and input password and click start server

4. If you see Welcome, it's done ! 

5. Copy C:\web2py\parameters_8000.py to parameters_80.py and parameters_443.py to access port 80 and 443 (https). You need to use https to access Administrative interface from other PCs.

Wednesday, September 28, 2011

smartgrid: currency format and text-align

def admin():
    products = SQLFORM.grid(db.Product,deletable=False, paginate=10)
    return dict(products = products)

    Field('List_Price', 'decimal(13,2)'))


{{extend 'layout.html'}}
<h2>Product Table</h2>

By default, all text is aligned to left. You can align it right for decimal fields by adding the following in db.py:

db.Product.List_Price.represent = lambda value, row: XML(DIV(value,_style='text-align: right;')) 

or currency format:

db.Product.List_Price.represent = lambda value, row: '$ %.2f' % (0.0 if value == None else value)

or both:

db.Product.List_Price.represent = lambda value, row: DIV('$ %.2f' % (0.0
if value == None else value), _style='text-align: right;')

Ref: https://groups.google.com/group/web2py/browse_thread/thread/97a3e861957e758b/21bdc438c387abd3#21bdc438c387abd3

Thursday, September 15, 2011

web2py: Input field alignment for decimal and integer

By default, all the fileds are aligned to left.

If you want to align to right such decimal and integer, you can simply add the following to your base.css.

input.decimal, input.integer, input.double { 
    text-align: right; 

Then it will be like this,

You can also use "Tight input widget" developed by Kenji san at S-Cubism.

Tight input widget

Monday, September 12, 2011

A simple password generator with wxPython

This is a simple password generator.

  • Providing First and Last Name, then click Get Password! to generate the password
  • Line3, 4: you can change the combination here
  • Line20 - line25: Use 4 digits of last name to generat the numbers in password
  • Line27 - line28: Get the digits for the given alphabet 
  • Line57: Use lambda to pass multiple parameters

  1. import wx
  3. NBR = "456789012345678901234567897"
  4. KEY = dict(zip(ALP, NBR))
  5. def getpass(event, firstname, lastname):
  6.     password = ''
  8.     # Input both name are required
  9.     if firstname == '' or lastname == '':
  10.         passText.SetValue('Input both first and last name !')
  11.     else:    
  12.         firstname = firstname.upper()
  13.         lastname = lastname.upper()
  15.         length = len(lastname)
  16.         # Fill in blank for the short last name
  17.         if length < 4:
  18.             lasttemp = lastname + '   '
  19.         else:
  20.             lasttemp = lastname
  21.         last4 = lasttemp[0:4]
  23.         for i in last4:
  24.             password += KEY[i]
  25.         passText.SetValue('Password: ' + firstname + password)
  26. if __name__ == '__main__':
  27.     app = wx.App()
  28.     win = wx.Frame(None, title='Password Generator',
  29.             size = (300, 200), style=wx.SYSTEM_MENU|wx.CAPTION|wx.CLOSE_BOX)
  30.     bkg = wx.Panel(win)
  32.     bkg.SetFont(font)
  33.     firstTexts = wx.StaticText(bkg, -1, 'First Name: ', size=(90, 22))
  34.     firstTextc = wx.TextCtrl(bkg, -1, size=(200, 22))
  36.     hboxf = wx.BoxSizer()
  37.     hboxf.Add(firstTexts, proportion=0, flag=wx.ALL, border=0)
  38.     hboxf.Add(firstTextc, proportion=0, flag=wx.ALL, border=0)
  40.     lastTexts = wx.StaticText(bkg, -1, 'Last Name: ', size=(90, 22))
  41.     lastTextc = wx.TextCtrl(bkg, -1, size=(200, 22))
  43.     hboxl = wx.BoxSizer()
  44.     hboxl.Add(lastTexts, proportion=0, flag=wx.ALL, border=0)
  45.     hboxl.Add(lastTextc, proportion=0, flag=wx.ALL, border=0)
  47.     submitButton = wx.Button(bkg, label='Get Password!', size=(290, 22))
  48.     submitButton.Bind(wx.EVT_BUTTON,
  49.             lambda event: getpass(event, firstTextc.GetValue(), lastTextc.GetValue()))
  50.     passText = wx.TextCtrl(bkg, -1, '', style=wx.TE_READONLY|wx.BORDER_NONE, size=(290, 22))
  51.     hboxp = wx.BoxSizer()
  52.     hboxp.Add(passText, proportion=0, flag=wx.ALL, border=0)
  53.     vbox = wx.BoxSizer(wx.VERTICAL)
  54.     vbox.Add(hboxf, proportion=0, flag=wx.EXPAND|wx.ALL, border=5)
  55.     vbox.Add(hboxl, proportion=0, flag=wx.EXPAND|wx.ALL, border=5)
  56.     vbox.Add(submitButton,  proportion=0, flag=wx.ALL, border=5)
  57.     vbox.Add(hboxp, proportion=0, flag=wx.EXPAND|wx.ALL, border=5)
  58.     bkg.SetSizer(vbox)
  59.     win.Show()
  60.     app.MainLoop()

Tuesday, September 6, 2011

How to connect DB2 with Python pyodbc

There are very few infromation about it and this is how I connect to DB2 from python and web2py.

My environment
OS: Windows 7 x86
iSeries Access: V5R3M0 - SI26600
Python: 2.5
pyodbc: pyodbc-2.1.7.win32-py2.5.exe
web2py: 1.98.2 (Source Code)

IBM iSeries Access is installed on your machine so that you have the iSeries Access ODBC Driver.If you use DB2, it's already installed on you machine, isn't it?

1. Install pyodbc
Download and install from http://code.google.com/p/pyodbc/downloads/list.

2. Create ODBC Data Source

2.1 Go to: Control Panel - System and Security - Administrative Tools - Data Sources (ODBC)
2.2 Click Add and select iSeries Access ODBC Driver

2.3 Type Data source name and System (Your AS400 Name)

2.4 On Server tab, type SQL default library

2.5 Click Advanced button at the bottom 
2.6 Change Commit mode: from (*CHG) to (*NONE)

Very important:  If you don't change, you might get the following error.
Error: ('HY000', '[HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - TEST in MYLIB not valid for operation. (-7008)

3. Connect from web2py
Now you are ready to connect.

3.1 Restart your web2py if it's already started
3.2 Create new app like AS400
3.3 Edit DB.PY as follows.
# -*- coding: utf-8 -*-
db = DAL('db2://DSN=MYDSN;UID=YourID;PWD=YourPassword')
3.4 Save and click "database administration"

3.5 Check on AS400

3.6 Now click on "Insert new test" from web2py, input Name and click submit

3.7 Check on AS400 - Oh my got! It works !!!

* You can create separate DSN to connect different libraries

4 Connect from Python Shell
Notice: I'm using "." instead of "/". If you want,  you can change "Naming Conversion" to *SYS on the Server tab in the data source you created.


>>> import pyodbc
>>> conn = pyodbc.connect('DSN=MYDSN;UID=YourID;PWD=YourPassword')
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM MYLIB.TEST")
<pyodbc.Cursor object at 0x01E6A598>
>>> for row in cursor:
print row
(1, 'Omi Chiba')
<pyodbc.Cursor object at 0x01E6A598>

4.3 Check on AS400