与Web2Py共舞

  
     
     
           
   
Rolling with web2py
与Web2Py共舞
(formerly known as Gluon) created by Massimo Di Pierro
(前身为Gluon) 由Massimo Di Pierro 创建 由limodou翻译
Perhaps you have heard of web2py, the new kid on
the block of Web Frameworks. web2py is written in Python so it is more
solid and much faster than Ruby on Rails. web2py is also a web
application itself so you can do all development, deployment and
maintenance of your applications through your web browser and that
makes it easier to use than any other framework. Moreover web2py ships
in one complete package (for Windows, Mac or Unix/Linux) including
everything you need to start development (including Python, SQLite3,
and multi-threaded web server).
也许你已经听过说web2py，它是Web开发框架中的新成员。web2py使用Python进行编写，所
以它很可靠并且比Ruby on Rails快。web2py本身也是一个web应用，所以你可以通过浏览
器对你的应用程序进行所有的开发、部署和维护，而这种方式使得它比其它任何框架都易
于使用。除此之外，web2py被打成一个完整的包(可用于Windows, Mac或Unix/Linux)，同
时包含了开发所需要的一切(包括Python, SQLite3, 和多线程web服务器). [译注: 现在
是cherrypy]
You can get web2py here: http://www.web2py.com
This document is intentionally designed to mimic
http://onlamp.com/pub/a/onlamp/2005/01/20/rails.html
so that you can compare web2py with Rails.
你可以从这里得到web2py: http://www.web2py.com 。
这篇文档在设计时有意模仿了
http://onlamp.com/pub/a/onlamp/2005/01/20/rails.html
。
这样你就可以同Rails进行比较了。
What is Python?
什么是Python？
Python is an object oriented programming
language designed to be super easy to teach without any compromise on
functionality. Most Java algorithms can be rewritten in Python in one
tenth of their original length. Python comes with an extensive set of
portable standard libraries including support for many standard
internet protocols (http, xml, smtp, pop, and imap, just to mention a
few) and APIs to the Operating System.
Python是一种面向对象的编程语言，被设计得超级容易教学，并且在功能上没有任何打折。
绝大部分Java算法都可以用Python来重写，而长度仅为原来的二十分之一。Python自带了
一整套可移植的库，包括对许多标准互联网协议(http, xml, smtp, pop, 和imap，
只提到了几个)的支持和对操作系统API的支持。
What is web2py?
什么是web2py？
web2py is an open source web framework written
in Python and programmable in Python for fast development of
database-driven web applications. There are many web frameworks today
including Ruby on Rails, Django, Pylons and Turbo Gears, so why another
one?
I developed web2py with the following goals in mind:
web2py是使用Python编写的一个开源web框架，并可以使用Python进行数据库驱动的web
应用方面的快速编程。如今有许多的web框架，包括Ruby on Rails, Django, Pylons和
Turbo Gears，所以为什么又开发一个呢？我是在心中带着下面的目标进行web2py的开发的:
As similar as possible to Rails but in Python, so that it is more solid and much faster.
All-in-one package with no installation, no configuration and no shell scripting required.
Be super easy to teach (my job is to teach). So I made web2py itself as a web application.
Top-down design so that the web2py APIs would be stable from day one.
尽可能象Rails, 但是用Python来开发，这样可以更稳定和更高效。
一体化的包，不需要安装、无配置和不需要shell脚本。
超级容易教学(我的工作是教学)。所以我把web2py本身也做成了一个web应用程序。
从上到下的设计，这样web2py的API从头一天开始就是稳定的。
Seeing is Believing
眼见为实
Programming web2py is as easy as programming
Rails but, if you do not know Python nor Ruby, web2py is easier to
learn than Rails.
web2py编程象Rails编程一样容易，但如果你既不会Python也不会Ruby，web2py学起来要比Rails容易多了。
What is most important is that web2py requires
much less code than J2EE equivalent or PHP equivalent, while enforcing
a vary good and safe programming style.
最重要的是，与同等功能的J2EE或PHP相比，web2py所需的代码量要少，同时它强迫你使用一种非常好并且安全的编程习惯。
web2py prevents directory traversal, SQL injections, cross site scripting, and reply attack vulnerabilities.
web2py阻止目录遍历，SQL注入攻击(SQL injection)，跨站脚本执行(cross site scripting)，和回复攻击弱点(reply attack vulnerability)。
web2py manages session, cookies and application
errors for you. All application errors result in a ticket issued to the
user and a log entry for the administrator.
web2py替你对session，cookie和应用错误进行管理。所有应用错误都会生成一个ticket发送给用户，并且会为管理员生成一条日志项。
web2py writes all the SQL for you. It even creates the tables and decides when to do a migration of the database.
web2py会为你编写所有的SQL。它甚至可以创建表并决定何时执行一个数据库迁移的动作。
Give it a try.
试一下吧。
Installing the Software
软件安装
Go to
http://mdp.cti.depaul.edu/examples
  and download the Windows, Mac or Unix files.
访问
http://mdp.cti.depaul.edu/examples
  并下载Windows, Mac或Unix文件。
If you choose to use the Windows or Mac version
you do not need anything else: unzip the file and click on web2py.exe
or web2py.app respectively.
如果你选择使用Windows或Mac版本，你只需要执行：unzip文件，然后分别点击web2py.exe或wweb2py.app。
If you choose to use the Unix version you need
the Python interpreter (version 2.4 or later) and the SQLite3 database.
After you have those, unzip web2py and run
python web2py.py
如果你选择使用Unix版本，你需要安装Python解释器(版本2.4或更高)和SQLite3数据库。 [译注：2.4与2.5有些区别。在2.5中使用sha512的摘要算法，而2.4只使用sha。同时2.5内置了sqlite，因此不需要安装。如果有加密数据，则2.4与2.5下的版本处理可能有不同，需要注意。] 有了这些之后，unzip web2py然后运行
python web2py.py
In a production setting you should use
PostgreSQL or MySQL and not SQLite3. From the web2py prospective that
is as easy as changing one line in the program but that is not
discussed here since you do not need it for development.
在生产配置中，你应该使用PostgreSQL或MySQL而不是SQLite3。从web2py的角度来看，修改配置象修改程序中的一行代码一样简单，不过在这里不讨论它，因为开发中你不需要关心它。
Running web2py
运行web2py
At startup web2py asks one question: “choose
the administrator password” Choose one. After that web2py will open a
web browser for you (remember no commands to type ever!) showing this
welcome page
在启动时，web2py会问一个问题: “choose the administrator password”，选择一个。
在那之后，web2py会替你打开一个web浏览器(记住未曾输入过命令！)，同时显示这个
欢迎页面

Click on “administrative interface”
点击 “administrative interface”

and type the password that you choose at startup. You will be redirected to the “site” page of the administrative interface:
然后输入在启动时选择的口令。你应该被重定向到管理界面的”site”页面:

Here you can:
这里你可以:
install and uninstall applications
create and design (edit) your applications
cleanup error logs and sessions
byte-code compile applications for distribution and faster execution

安装和反安装应用程序
创建和设计(编程)你的应用程序
清理错误日志和session
以字节码方式编译应用程序用来分发和快速执行

web2py comes with three applications: admin (the administrative interface itself), examples (interactive documentation), and welcome (a basic template for any other application).
web2py自带了三个应用程序: admin (管理界面本身), examples (交互文档),
和 welcome (可用来创建其它应用程序的基础模板)。
Let’s Write Code
开始写代码
We’ll create an online collaborative cookbook for holding and sharing recipes. We want our cookbook to:
我们将创建一个在线的协作方式的 cookbook ，用来保存和分享菜谱。我们想让我们的
食谱书有以下功能:
Display a list of all recipes.
Create new recipes and edit existing recipes.
Assign a recipe to a category (like “dessert” or “soup”).

显示所有菜谱的清单。
创建新菜谱和编辑存在的菜谱。
给菜谱设定 category (象”dessert”(餐后甜点)或”soup”(汤))。

If you like, you can download the complete web2py Cookbook example and follow along.
如果需要，你可以下载完整的web2py Cookbook例子并且跟着做。
Creating an Empty web2py Application
创建一个空的web2py应用程序
To start a new application type a name in the appropriate field, in our case cookbook, and press the button submit:
为创建一个新的应用，在合适的字段 [译注：create new application] 上输入一个名字
(在本例中为cookbook)，然后点击Submit按钮:

A new web2py application is not empty but it is
a clone of the welcome applicaiton. It contains a single controller, a
single view, a base layout, a generic view and its own database
administrative interface called appadmin (not to be confused with
admin, the site-wide administrative interface).
一个新的web2py应用程序不是空的，它是welcome应用程序的克隆。它包含单个controller(控制器)，
单个view(视图)，基本layout(布局)，通用view和称为appadmin(不要与admin混了，admin
是整个站点的管理界面)的数据库管理界面。
Testing the Empty Web Application
测试空的Web应用程序
You are already running web2py web server so there is nothing to test really. Anyway, click on cookbook/design and you will see
你已经运行了web2py web服务器，所以其实没有什么可以测试的。不管怎么样，在 cookbook/design
上点击，你会看到

Here is where you can view/create/edit the
components of your application. Under Controllers there is a file
called default.py which “exposes index” If you click on index your
newly created application will “welcome you”
在这里你可以查看/创建/编辑你的应用程序组件。在 Controllers 下，有一个叫做
default.py 的文件，带有 “exposes index”。如果你在 index 上点击，你创建
的新应用程序会 “welcome you”
web2py Model View Controller Design
web2py Model(模型) View(视图) Controller(控制器) Design(设计)
Any web2py application is comprised of:
任何web2py应用程序由以下内容组成:
Models: files
that contain a description of the data stored by your application. For
example the fields in the tables of your databases, their relations,
and requirements. web2py tells you which tables are defined in each
model file.
Controllers:
files that contain the logic of your application. Each URL is uniquely
mapped into a function in a controller file. That function can generate
a page, delegate a view to render a page, redirect to another URL or
raise an exception (depending on the exception that may result in a
ticket being issued or in a HTTP error page). web2py tells you which
functions are exposed by each controller file.
Views: files
that contain HTML and special {{ }} tags which render in HTML variables
returned by the controller. This is the presentation layer of your
application. web2py tells you when a view extends or imports other
views.
Languages: files
that contain translation tables for all strings (those that you
explicitly mark as language dependent) for any of the languages you
want to support.
Static files: all other files, including images, CSS, JavaScript, etc.

Models: 用来描述你的应用程序的数据存储的文件。例如，你的数据库表中的字段、
它们的关系和要求。web2py会告诉你在每个model文件已经定义了哪些表。 [译注:
model.tables?]
Controllers: 包含你的应用程序处理逻辑的文件。每个URL唯一映射到一个controller
文件的一个函数上。这个函数可以生成页面、委派一个view来渲染一个页面、重定向
到另一个URL或引发一个异常(根据异常的不同，有可能会产生一个ticket或出现在一个
HTTP错误页面中)。web2py会告诉你每个controller文件所暴露出来的函数。 [译注：
如果在controller中的函数使用``__``开始，如``def __init()``，它将是一个私有
函数，不会被暴露出来，符合Python定义的习惯。]
Views: 包含HTML和特殊的 {{ }} 标签的文件。这些标签可以在由controller中返回的
变量中进行渲染。这是你的应用程序的展现层。web2py会告诉你何时一个view从其它的
view中进行扩展(extend)或导入。
Languages: 包含所有你想要支持为其它任一语言的字符串的翻译列表的文件。这些字
符串需要你明确标识为语言依赖。 [译注：在需要翻译的字符串使用T()函数进行封装。
不过目前好象只能用在controller, view, model中。对于其它的模块好象还不支持。]
Static files: 其它的所有文件，包括图片、CSS、JavaScript，等等。

Notice that you do not need an editor nor you
need to know the web2py directory structure since you can create and
edit files from the design page.
注意，你即不需要一个编辑器，也不需要知道web2py的目录结构，因为你可以通过design
页面来创建和编辑。 [译注：web2py自带的web编辑器可以很好的支持语法高亮，包括
Python。不过对于某些静态文件，如JavaScript不能进行修改，希望在以后可以改进。]
Also notice that while it is good policy to
give a view to every controller function (called action in rails), you
do not have to since web2py always provides a generic.html view that
will render any page that is missing a template.
还要注意，为每个controller函数(在Rails中叫action)给定一个view是一种好的习惯，不
过不一定非要如此，因为当没有指定view( [译注：可以简单理解为模板] )时，web2py
会总是使用 generic.html view来渲染任何页面。
URLs and Controllers
URL和Controller
This image represents the general structure of web2py’s core functiy
这张图展示了web2py核心功能的通用结构

A URL like
http://hostname/cookbook/default/index/bla/bla/bla?variable=value
will result in a call to function index() in controller default.py in applicaiton cookbook.
一个象
http://hostname/cookbook/default/index/bla/bla/bla?variable=value
的链接会产生一个对cookbook应用程序的 default.py controller中的 index() 函数的一个调用。
“bla”, “bla”, and “bla” will be passed as request.args[0:3] while “value” will be stored in request.vars.variable.
“bla”, “bla”, 和 “bla” 将被传递为 request.args[0:3] ，而”value”将被保存在 request.vars.variable 中。
Controller functions should return a dictionary like in
return dict(name=value, othername=othervalue)
controller函数应该象下面一样返回一个dict(字典)
return dict(name=value, othername=othervalue)
and the variables name and othername will be passed to the associated view.
这样变量 name 和 othername 将被传到相应的view中。
Try now, from cookbook/design, to create a
test.py controller (just type the name and click submit), edit test.py
and create your own index function
现在试一试，从 cookbook/design 来创建一个 test.py controller(只需要输入名字并点击Submit)，编辑 test.py 然后创建你自已的 index 函数。 [译注：如果文件名不带.py扩展名则web2py会自动添加，其它的也类似。]

go back to cookbook/design and click on the index function exposed by test.py.
回到 cookbook/design 并在 test.py 所暴露出来的 index 函数上点击。

web2py is using the generic.html view, which extends the basic layout.html, to render the variable text returned by your index() function.
web2py使用了 generic.html view(它是从基础的 layout.html 扩展来的)来对你的 index() 函数返回的变量进行渲染。
The excitement Begins...
激动的时刻开始了...
Creating the Model
模型创建
Go to cookbook/design and create a new model
called db.py (just type db in the apposite field and click submit). The
definition of a model here is slightly different than in Rails. In
web2py a model is a single file that contains a definition of all
tables in each database.
回到 cookbook/design ，然后创建一个叫 db.py 的新model(只需要在适当的字段上输入 db 然后点击Submit)。model定义在这里与Rails有些不同。在web2py中，一个model是一个包含了在每个数据库中 所有表 的定义文件。

Edit the just created db.py model and write the following:
编辑则才创建的 db.py model，然后输入下面的代码:

This model defined two tables category and recipe. recipe has a field category that is a reference to db.category and field date that default to today. Each field has some requirements (this is optional), category.name requires that a new value IS_NOT_IN_DB (the field must be unique), recipe.category requires that the field IS_IN_DB (the reference is valid), recipe.date requires that it contains a valid date.
这个model定义了两个表 category 和 recipe 。**recipe** 有一个 category 字段，它是一个对 db.category 的引用，并且 date 字段缺省为今天。每个字段有一些要求(这是可选的)， categore.name 要求一个新值要满足 IS_NOT_IN_DB(字段必须唯一)， recipe.category 要求这个字段满足 IS_IN_DB(引用是有效的)， recipe.date 要求它要包含一个有效的日期。 [译注：如果你同时希望它可以为空，或不为空时需要为有效的日期格式，可以使用 IS_NULL_OR(IS_DATETIME())]
These requirements will be enforced in any entry form, whether part of the administrative interface or user generated.
这些要求将被强制使用在任一入口的表单中，无论它是管理界面部分或用户生成的部分。 [译注：web2py会提供象SQLFORM这样的东西进行记录的录入，用户也可以使用它。而SQLFORM会对字段的约束项进行检查]
The Database Administrative Interface (appadmin)
数据库管理界面(appadmin)
Go to cookbook/design and, under model, you
will see two new links database administration and sql.log. Click on
the former and if you do not have typos you will see:
回到 cookbook/design ，在model下，你会看到两个新的链接，分别为 database administration 和 sql.log 。在前者上点击，如果不存在拼写错误时你会看到:

This is your application administrative interface. Try to insert a new category record:
这是你的数据库管理界面。试着插入一个新的category记录:

and some new recipes:
和一些新的菜谱:

Wasn’t this easier than Rails? Let’s not even compare with PHP, JSP, ASP, J2EE, etc.
这难道不比Rails简单吗？甚至都不需要和PHP、JSP、ASP、J2EE等相比。
Who created the tables? web2py did! web2py
looked for a database called db.db, could not find one so it created
the database and the tables you just defined. If you modify a table
definition, web2py will alter the table for you (SQLite3 only supports
adding fields, Postgresql also supports dropping fields). If you define
another table it will be created. You can look at the SQL generated by
web2py for this migration by clicking on sql.log.
是谁创建的这些表呢？web2py干的！web2py会查找一个叫做db.db的数据库( [译注：在SQLDB中定义的文件名，因为使用的是SQLite3数据库。同时使用SQLite3你还可以使用绝对路径。否则它会在你的应用程序目录下的databases子目录下创建这个数据库文件。])，如果找不到，那么它会创建这个数据库和你刚才定义的表。如果你修改了一个表的定义，web2py会替你修改表结构。如果你定义了另一张表，它会被创建。你可以看一下由web2py为这种迁移所生成的SQL，通过点击 sql.log 。
Feel free to explore the administrative interface, insert a few records and try to list them.
随便探索管理界面，插入几条记录，并试着列出它们。

The table is sortable by clicking on the header
and will paginate if you have more than 100 items. Try a JOIN by typing
“recipe.category=category.id” in the SQL FILTER field.
这张表可以通过点击表头进行排序，并且当记录超过100条时会进行分页。试一下JOIN(表的联接操作)，在SQL FILTER字段上输入 recipe.category=category.id 。

Where did field id come form? In web2py every table has a unique integer key called id. If you click on the id value in the table you will be able to edit the individual record.
字段 id 是从哪来的？在web2py中的每张表都有一个唯一的整数键叫做 id 。如果你在表的 id 值上点击，你就可以单独修改这条记录。
Notice that appadmin.py
is part of your cookbook application so you can read it and modify it.
In this tutorial we choose not to do it and we prefer to take the
longer route and write a new controller from scratch. We believe this
better serves our didactic purpose.
注意 appadmin.py 是你的cookbook应用程序的一部分，所以你可以对它进行阅读和修改。在这个教程中，我们不这样做，而宁愿从头编写一个新的controller。这样会起到更好的宣传作用。
Creating Functions (Actions)
创建函数(Action)
While in cookbook/design, edit the test.py controller and add the following:
def recipes():
    records=db().select(db.recipe.ALL,orderby=db.recipe.title)
    return dict(records=SQLTABLE(records))
在 cookbook/design 中，编辑 test.py controller 并加入如下代码:
def recipes():
    records=db().select(db.recipe.ALL,orderby=db.recipe.title)
    return dict(records=SQLTABLE(records))
Now back in design, click on “recipes” and you should see
现在回到design，在 recipes 上点击，你会看到

Notice that the variable records passed to the 11049004876800view is a SQLTABLE that knows how to  render itself in CSS friendly HTML. The variable records is rendered by the generic.html view.
注意，传给view的变量 records 是一个 SQLTABLE 对象，它知道如何把自身渲染为CSS友好的HTML。变量 records 是由 generic.html view来渲染的。
Let’s customize this more. Change the controller into:
让我们再改一下。修改controller为：

Notice how:
注意：
recipes now returns a list of records, not an SQLTABLE, moreover it generates a selection form from the category field of the table.
show takes the request.vars.id and performs select, on failure it redirects to recipes
new_recipe returns a SQLFORM object which builds an HTML form from the definition of a table (db.recipe). form.accepts()
performs validation of the form (according to the requirements in the
model), updates the form with error messages and, on successful
validation, it inserts the new record in the database.
URL(r=request,f='function' generates the url for “function” in the current application and controller as determined by the HTTP request.

recipes 现在返回记录列表，而不是一个SQLTABLE，而且它会从表的 category 字段生成一个选择 表单 .
show 接受 rquest.vars.id ，并且执行选择，一旦失败，它会重定向到 recipes 。
new_recipe 返回一个SQLFORM对象，它可以根据一个表(db.recipe)的定义创建一个HTML表单。 form.accepts() 执行对表单的校验(根据模型中的需求)，用错误信息来更新表单，如果检验成功，它插入新记录到数据库中。
URL(r=request,f='function') 可以在当前的应用程序和controller中生成 “function” 的url，根据HTTP请求。

This code is already fully working using the
generic view but we will perform additional customization at the layout
layer below.
这块代码使用通用view已经可以完全运行，但是我们将在下面的布局层执行额外的客户化处理。
Notice that some validators, like IS_DATETIME() for a ‘datetime’ field, are automatically set by default.
注意，一些检验器(validator)，象用于’datetime’字段上的 IS_DATETIME() ，缺省是自动设置的。 [译注：如果你不想使用缺省的validator可以设置字段的 ``field.requires=[]`` ]
Creating Views
创建View
Now create a view for recipes. This view is called test/recipes.html (type the name with path in the opposite field and click submit).
现在为 recipes 创建一个view。这个view叫做 test/recipes.html (在适当的字段上输入带路径的名字然后点击Submit)。

Edit the newly created file
编辑新创建的文件

Now try the calling recipes again
现在再试着调用 recipes

Notice that the code inside {{ }} tags is Python code with some caveats:
注意在 {{ }} 标签中的代码是Python代码，需要注意:
There is no
indentation requirement, a block of code starts with a line ending in
colon and ends with a line starting with pass (exemptions are
def:return, if:elif:else:pass and try:except:pass).
The view sees everything defined in the model plus the variables returned by the controller.
{{=something}} will render something in HTML after escaping special characters.

不需要缩近，一个代码块以末尾为冒号的行开始，到以开始为pass的行结束(例如:def :return, if:elif:else:pass和try:except:pass)。
view可以看到在model中定义的所有东西加上由controller返回的变量。
{{=something}} 将把 something 渲染成为HTML，之后对特殊字符进行转义。

Notice that
{{=A(message,_href=link)}}
注意
{{=A(message,_href=link)}}
is an HTML helper. It simply writes the
message
它是一个html辅助函数。会简单地替你输出
message
tag for you.
标签。
Create a view test/show.html that contains:
创建 test/show.html ，包含:

It will look like this:
看上去象这样:

Finally create a test/new_recipe.html that contains:
最后创建一个 test/new_recipe.html ，包含:

It will look like this:
看上去象这样:

Notice how web2py capitalized the names of the
fields in the form and generated a SELECT/OPTION for the category field
based on the specified requirements.
注意web2py在表格中是如何把字段名首字母大写的，并且根据指定的需求为category字段生成了一个 SELECT/OPTION 字段。
If you do no like the [web2py]cookbook banner or the CSS you can edit them both in the layout.html file.
如果你不喜欢 [web2py]cookbook 的广告条或它的CSS，你可以在 layout.html 文件中编辑它们。
Some Magic
一些魔术
If you try to submit a form that does not meet
the requirements (for example try to submit an empty recipe), web2py
will notify you about that.
如果你试图提交一个不满足需求(例如试图提交一个空的菜谱)的表单，web2py会通知你相关的提示信息。

Conclusions
结论
We have written a working web2py application
with only the browser, a few clicks and a total of 53 lines of code. We
also got for free a database administrative interface that allows to
insert, select, update and delete individual records or record sets.
我们已经编写了一个可工作的web2py应用程序，仅仅通过浏览器，几个点击和总共53行代码。我们还得到了一个自由使用的数据库管理界面，它可以让你插入、选择、更新和删除单条记录或记录集。
web2py also includes easy to use functions to
import/export tables in CSV, to generate RSS feeds and RTF files
(compatible with MS Word), and to handle JSON for AJAX.
web2py也包括了容易使用的函数来导入和导出CSV格式的表数据，生成RSS feed和RTF文件(与MS Word兼容)，处理JSON用于AJAX。
To read more about web2py visit the web page:
http://mdp.cti.depaul.edu
如果想要了解更多关于web2py，请访问网页:
http://mdp.cti.depaul.edu
If you have questions, please join our Google group:
http://groups.google.com/group/web2py?hl=en
如果你有问题，请加入Google用户组:
http://groups.google.com/group/web2py?hl=en
Appendix. The Database API
附录 数据库API
Connect to a sqlite3 database in file test.db
>>> db=SQLDB("sqlite://test.db")
连接到sqlite3数据库文件test.db
>>> db=SQLDB("sqlite://test.db")
or connect to a MySQL database
>>> db=SQLDB("mysql://username:password@host:port/dbname")
或连接到MySQL数据库
>>> db=SQLDB("mysql://username:password@host:port/dbname")
or connect to a PostgreSQL database
>>> db=SQLDB("postgres://username:password@host:port/dbname")
或连接到PostgreSQL数据库
>>> db=SQLDB("postgres://username:password@host:port/dbname")
Available field types
>>> tmp=db.define_table('users',\
    SQLField('stringf','string',length=32,required=True),\
    SQLField('booleanf','boolean',default=False),\
    SQLField('passwordf','password'),\
    SQLField('textf','text'),\
    SQLField('blobf','blob'),\
    SQLField('uploadf','upload'),\
    SQLField('integerf','integer'),\
    SQLField('doublef','double'),\
    SQLField('datef','date',default=datetime.date.today()),\
    SQLField('timef','time'),\
    SQLField('datetimef','datetime'),\
    migrate='test_user.table')
可用字段类型
>>> tmp=db.define_table('users',\
    SQLField('stringf','string',length=32,required=True),\
    SQLField('booleanf','boolean',default=False),\
    SQLField('passwordf','password'),\
    SQLField('textf','text'),\
    SQLField('blobf','blob'),\
    SQLField('uploadf','upload'),\
    SQLField('integerf','integer'),\
    SQLField('doublef','double'),\
    SQLField('datef','date',default=datetime.date.today()),\
    SQLField('timef','time'),\
    SQLField('datetimef','datetime'),\
    migrate='test_user.table')
A field is an object of type SQLField
>>> SQLField('fieldname', 'fieldtype', length=32,\
             default=None,required=False,requires=[])
一个字段就是一个SQLField类型的对象
>>> SQLField('fieldname', 'fieldtype', length=32,\
             default=None,required=False,requires=[])
Drop the table
>>> db.users.drop()
删除表
>>> db.users.drop()
Examples of insert, select, update, delete
>>> tmp=db.define_table('person',\
          SQLField('name'), \
          SQLField('birth','date'),\
          migrate='test_person.table')
>>> person_id=db.person.insert(name="Marco",birth='2005-06-22')
>>> person_id=db.person.insert(name="Massimo",birth='1971-12-21')
>>> rows=db().select(db.person.ALL)
>>> for row in rows: print row.name
     Marco
Massimo
>>> me=db(db.person.id==person_id).select()[0]
>>> me.name
'Massimo'
>>> db(db.person.name=='Massimo').update(name='massimo')
>>> db(db.person.name=='Marco').delete() # test delete
插入、选择、更新、删除的例子
>>> tmp=db.define_table('person',\
          SQLField('name'), \
          SQLField('birth','date'),\
          migrate='test_person.table')
>>> person_id=db.person.insert(name="Marco",birth='2005-06-22')
>>> person_id=db.person.insert(name="Massimo",birth='1971-12-21')
>>> rows=db().select(db.person.ALL)
>>> for row in rows: print row.name
     Marco
Massimo
>>> me=db(db.person.id==person_id).select()[0]
>>> me.name
'Massimo'
>>> db(db.person.name=='Massimo').update(name='massimo')
>>> db(db.person.name=='Marco').delete() # test delete
Update a single record
>>> me.update_record(name="Max")
>>> me.name
'Max'
更新单个记录
>>> me.update_record(name="Max")
>>> me.name
'Max'
Complex search conditions
>>> rows=db((db.person.name=='Max')&\
            (db.person.birth
>>> rows=db((db.person.name=='Max')| \
            (db.person.birth
>>> me=db(db.person.id==person_id).select(db.person.name)[0]
>>> me.name
'Max'
>>> rows=db(db.person.birth.month()==12).select()
>>> rows=db(db.person.birth.year()>1900).select()
>>> rows=db(db.person.birth==None).select()
>>> rows=db(db.person.birth!=None).select()
>>> rows=db(db.person.name.upper()=='MAX').select()
>>> rows=db(db.person.name.like('%ax')).select()
>>> rows=db(db.person.name.upper().like('%AX')).select()
>>> rows=db(~db.person.name.upper().like('%AX')).select()
复杂搜索条件
>>> rows=db((db.person.name=='Max')&\
            (db.person.birth
>>> rows=db((db.person.name=='Max')| \
            (db.person.birth
>>> me=db(db.person.id==person_id).select(db.person.name)[0]
>>> me.name
'Max'
>>> rows=db(db.person.birth.month()==12).select()
>>> rows=db(db.person.birth.year()>1900).select()
>>> rows=db(db.person.birth==None).select()
>>> rows=db(db.person.birth!=None).select()
>>> rows=db(db.person.name.upper()=='MAX').select()
>>> rows=db(db.person.name.like('%ax')).select()
>>> rows=db(db.person.name.upper().like('%AX')).select()
>>> rows=db(~db.person.name.upper().like('%AX')).select()
Usage of orderby, groupby and limitby
>>> people=db().select(db.person.name,orderby=db.person.name)
>>> order=db.person.name|~db.person.birth
>>> people=db().select(db.person.name,orderby=order)
>>> people=db().select(db.person.name,orderby=order,\
                       groupby=db.person.name)
>>> people=db().select(db.person.name,orderby=order,limitby=(0,100))
orderby, groupby 和 limitby 的使用
>>> people=db().select(db.person.name,orderby=db.person.name)
>>> order=db.person.name|~db.person.birth
>>> people=db().select(db.person.name,orderby=order)
>>> people=db().select(db.person.name,orderby=order,\
                       groupby=db.person.name)
>>> people=db().select(db.person.name,orderby=order,limitby=(0,100))
Example of one to many relation
>>> tmp=db.define_table('dog', \
          SQLField('name'), \
          SQLField('birth','date'), \
          SQLField('owner',db.person),\
          migrate='test_dog.table')
>>> dog_id=db.dog.insert(name='Snoopy',birth=None,owner=person_id)
一对多关系的例子
>>> tmp=db.define_table('dog', \
          SQLField('name'), \
          SQLField('birth','date'), \
          SQLField('owner',db.person),\
          migrate='test_dog.table')
>>> dog_id=db.dog.insert(name='Snoopy',birth=None,owner=person_id)
A simple JOIN
>>> rows=db(db.dog.owner==db.person.id).select()
>>> for row in rows: print row.person.name,row.dog.name
Max Snoopy
简单JOIN(连接)
>>> rows=db(db.dog.owner==db.person.id).select()
>>> for row in rows: print row.person.name,row.dog.name
Max Snoopy
Example of many to many relation
>>> tmp=db.define_table('author',SQLField('name'),\
                        migrate='test_author.table')
>>> tmp=db.define_table('paper',SQLField('title'),\
                        migrate='test_paper.table')
>>> tmp=db.define_table('authorship',\
        SQLField('author_id',db.author),\
        SQLField('paper_id',db.paper),\
        migrate='test_authorship.table')
>>> aid=db.author.insert(name='Massimo')
>>> pid=db.paper.insert(title='QCD')
>>> tmp=db.authorship.insert(author_id=aid,paper_id=pid)
多对多关系的例子
>>> tmp=db.define_table('author',SQLField('name'),\
                        migrate='test_author.table')
>>> tmp=db.define_table('paper',SQLField('title'),\
                        migrate='test_paper.table')
>>> tmp=db.define_table('authorship',\
        SQLField('author_id',db.author),\
        SQLField('paper_id',db.paper),\
        migrate='test_authorship.table')
>>> aid=db.author.insert(name='Massimo')
>>> pid=db.paper.insert(title='QCD')
>>> tmp=db.authorship.insert(author_id=aid,paper_id=pid)
SQLSet
>>> authored_papers=db((db.author.id==db.authorship.author_id)&\
                       (db.paper.id==db.authorship.paper_id))
>>> rows=authored_papers.select(db.author.name,db.paper.title)
>>> for row in rows: print row.author.name, row.paper.title
Massimo QCD
Search with belongs
>>> set=(1,2,3)
>>> rows=db(db.paper.id.belongs(set)).select(db.paper.ALL)
>>> print rows[0].title
QCD
用belongs进行搜索
>>> set=(1,2,3)
>>> rows=db(db.paper.id.belongs(set)).select(db.paper.ALL)
>>> print rows[0].title
QCD
Nested selects
>>> nested_select=db()._select(db.authorship.paper_id)
>>> rows=db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
>>> print rows[0].title
QCD
嵌套选择
>>> nested_select=db()._select(db.authorship.paper_id)
>>> rows=db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
>>> print rows[0].title
QCD
Output in CSV format
>>> str(authored_papers.select(db.author.name,db.paper.title))
'author.name,paper.title\r\nMassimo,QCD\r\n'
以CSV格式输出
>>> str(authored_papers.select(db.author.name,db.paper.title))
'author.name,paper.title\r\nMassimo,QCD\r\n'