web2py: how to connect existing tables

Tested environment:
mssql 2008

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

db.define_table('table1',
    Field('name'))

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.

db.define_table('table1',
    Field('myid'),
    Field('name'),
    primarykey=['myid'])

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 !

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.


auth.define_tables(username=True)

3. Add the following at the bottom of page.

Replace server and base_dn to your setting.

# all we need is login

auth.settings.actions_disabled=['register','change_password','request_reset_password','retrieve_username','profile']

# 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',

   server='OchibaServer',

   base_dn='dc=ochiba,dc=com')]

4. Result

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.


Pre-Requirement:
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

C:\UniServer\usr\local\apache2\conf\ssl.crt\server.crt
C:\UniServer\usr\local\apache2\conf\ssl.key\server.key

Or

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

mod_wsgi
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##########
# WEB2PY.LOCALHOST
<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
</LocationMatch>
<Location "/">
Order deny,allow
Allow from all
</Location>
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>
#------------------------------------------------------------
<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
</LocationMatch>
<Location "/">
Order deny,allow
Allow from all
</Location>
<Directory "C:/web2py">
Order allow,deny
Deny from all
</Directory>
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
SSLCipherSuite HIGH:MEDIUM:!aNULL:+SHA1:+MD5:+HIGH:+MEDIUM
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.*" \
</VirtualHost>
##########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.

Pre-Requirement:
Finished my previous post

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

mod_wsgi
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
  </Directory>

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

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

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

</VirtualHost>

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

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

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

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

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

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

</VirtualHost>

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.

Pre-Requirements:
- 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.