- 浏览: 101750 次
- 性别:
- 来自: 杭州
-
文章分类
最新评论
-
kuchaguangjie：
great, thanks!!!
python学习笔记之module && package -
newlifewant：
nice article! thanks for sha ...
python学习笔记之module && package -
arganzheng：
备战备荒 写道貌似第二种的单例是县城不安全的吧。使用Synch ...
单例的两种线程安全实现 -
arganzheng：
@备战备荒: 如果还不相信，这里有一篇step-by-step ...
单例的两种线程安全实现 -
arganzheng：
备战备荒 写道貌似第二种的单例是县城不安全的吧。这里有个ste ...
单例的两种线程安全实现
pylons（包括TurboGears）实战笔记
Why Pylons
Small and Simple enough! Well documentation! Easy to migrate to TurboGear and, yes, Rails.
How
发现TurboGears的文档非常给力，基本上给出了从零搭建一个网站(一个TurboGears Project)的过程，而由于pylons和TG都是使用Paster，所以基本是一样的（至少在构建步骤和配置文件上）。
Quickstarting a TurboGears 2 project
(http://www.turbogears.org/2.0/docs/main/QuickStart.html)
具体如下：
0. 创建一个虚拟环境（推荐）
1. 创建项目结构——类似于antx gen
对于TG：
$paster quickstart
The paster quickstart command will create a basic project directory for you to use to get started on your TurboGears 2 application.
对于pylons：
Create a new project named HelloWorld with the following command:
$ paster create -t pylons HelloWorld
对于pylons，可以使用--list-templates查看可选的模板
$paster create --list-templates
Available templates:
basic_package: A basic setuptools-enabled package
paste_deploy: A web application deployed through paste.deploy
pylons: Pylons application template
pylons_minimal: Pylons minimal application template
2. 引入项目需要的依赖——类似于antx或者maven
2.1 配置文件：对于pylons和TG，都是setup.py。（类似于antx的project.xml或者maven的pom.xml）
The setup.py and setup.cfg files control various aspects of how your Pylons application is packaged when you distribute it. They also contain metadata about the project.
The setup.py file has a section which explicitly declares the dependencies of your application. The quickstart template has a few built in dependencies, and as you add new python libraries to your application's stack, you'll want to add them here too.
例如，如果你要使用FormBuild这个模块，那么可以在setup.py中作如下配置：
You should also add it as a dependency to your project by editing the setup.py file and adding FormBuild to the end of the install_requires argument：
install_requires=[
"Pylons>=0.9.7",
"SQLAlchemy>=0.5,<=0.5.99",
"Mako",
"FormBuild>=2.0,<2.99",
],
你当然可以直接使用
$easy_install "SQLAlchemy>=0.5,<=0.5.99"
安装，但是不如同一个依赖管理，一键搭建轻松
2.2 安装依赖包：
对于pylons和TG都是一样的命令：
Then in order to make sure all those dependencies are installed you will want to run（Set up your project in development mode by entering this command）:
$python setup.py develop
paster会根据里面的依赖配置，执行类似如下命令：
$easy_install "SQLAlchemy>=0.5,<=0.5.99"
3. 初始化web应用环境——给每个模块（依赖）一个执行初始化的时机（如SQLAlchemy），其实会执行每个module下的_init.py文件，如model/init.py_。
python module
The model directory contains an _init_.py file which makes that directory name into a python module (so you can use the Python expression import model).
说明：这一步往往需要执行多次，每次做了什么修改，如数据模型修改，都可以跑一下。
Another key piece of TG2 application setup infrastructure is the paster setup-appcommand which takes a configuration file and runs websetup.py in that context. This allows you to use websetup.py to create database tables, pre-populate require data into your database, and otherwise make things nice for people fist setting up your app.
Note If it's the first time you're going to use the application, and you told quickstart to include authentication+authorizaiton, you will have to run setup-app to set it up (e.g., create a test database):
$paster setup-app development.ini
websetup.py
The websetup.py contains any code that should be executed when an end user has installed your Pylons application and needs to initialize it. It frequently contains code to create the database tables required by your application, for example.
If this is the first time you're starting the application you have to run the following command to create and initialize your test database:
$paster setup-app development.ini
This will create the database using the information stored in the development.ini file which by default makes single file SQLite database in the local file system. In addition to creating the database, it runs whatever extra database loaders or other setup are defined in websetup.py. In a quickstarted project with Auth enabled it creates a couple of basic users, groups, and permissions for you to use as an example.
It also shows how you can add new data automatically to the database when you need to add bootstrap data of your own.
4. 启动服务器
At this point your project should be operational, and you're ready to start up the app. To start a TurboGears 2 app, cd to the new directory (helloworld) and issue command paster serve to serve your new application:
$paster serve development.ini
As soon as that's done point your browser at http://localhost:8080/ and you'll see a nice welcome page with the inform(flash) message and current time.
By default the paster serve command is not in auto-reload mode as the CherryPy server used to be. If you also want your application to auto-reload whenever you change a source code file just add the --reload option to paster serve:
$paster serve --reload development.ini
You can easily edit development.ini to change the default server port used by the built-in web server:
[server:main]
...
port = 8080
至此，你的项目骨架已经搭建起来了。接下来的工作就是所谓MVC构建了。
5. MVC构建
下面的顺序是随机的，大家根据自己的习惯开发。一般来说，Domain Driven Development会从model开始开发，Page Driven Development会从view开始开发。笔者是属于后台开发人员，所以一般选择前者。
5.1 创建model
现在几乎所有的动态语言web框架都采用了某种ORM框架，在python的世界里，sqlalchemy是最受欢迎和推荐的。这里也只介绍这个框架。
Once you have configured the engine, it is time to configure the model. This is easy to do; you simply add all your classes, tables, and mappers to the end of model/_init_.py.
5.1 创建Controller：
$paster controller controllerName
对于TG：
貌似没有构建骨架的命令，不过我怀疑是跟pylons一样的。
5.2 创建view
6. 分层架构＆模块化
这个根据具体业务需求决定了。不过在使用了ORM的动态语言web框架中，DAO与Model（DO/DTO）是紧密结合的。所以可能没有单独的所谓的DAL层了。
7. 总结
回过头来再看一下pylons和TG的搭建过程，有J2EE经验的同学会惊讶的发现其实跟使用maven或者ant构建工具的Java web项目搭建过程是如此惊人的一致。另外，一些思想，如viewHelper，在一些框架中也能经常见到。
杂七杂八
You can access your models from within the python/ipython shell by typing:
$paster shell development.ini
from your root directory. If ipython is installed within your virtual environment, it will be the default python shell.
一些补充资料
SQLAlchemy
观于sqlalchemy的介绍，SQLAlchemy 0.5.8 官方文档 (http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/index.html)和pylonsbook(http://pylonsbook.com/en/1.1/introducing-the-model-and-sqlalchemy.html)的介绍比较给力。特别是后者，一个介绍web框架的文档，能从一个独立使用sqlalchemy的角度做了深入浅出的介绍，非常难得。
SQLAlchemy架构
其中 The abstraction layer consists of the SQL Expression API and the Metadata and Type APIs, which help isolate your Python code from the details of the underlying database engine.
使用Engine API链接数据库进行操作
The lowest-level API you are likely to use is the Engine API. This represents a low-level abstraction of a database engine, allowing you to use the same API to create connections to different RDBMSs for sending SQL statements and for retrieving results.
engine_test.py
from sqlalchemy.engine import create_engine
engine = create_engine('sqlite:///:memory:')
connection = engine.connect()
connection.execute(
"""
CREATE TABLE users (
username VARCHAR PRIMARY KEY,
password VARCHAR NOT NULL
);
"""
)
connection.execute(
"""
INSERT INTO users (username, password) VALUES (?, ?);
""",
"foo", "bar"
)
result = connection.execute("select username from users")
for row in result:
print "username:", row['username']
connection.close()
To work with an engine, you need to have a connection to it. The connection in the example is an instance of a SQLAlchemy Connection object, and result is a SQLAlchemy ResultProxy object (very much like a DB-API cursor) that allows you to iterate over the results of the statement you have executed.
运行这个脚本，得到如下输出：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python engine_test.py
username: foo
When using create_engine(), you can specify different data source names (DSNs) to connect to different databases. For example, with SQLite, you can use sqlite:///relative/path to specify a file relative to the current working directory. You can use sqlite:////absolute/path to specify an absolute path. SQLite also has a memory mode that doesn't use the filesystem at all and loses all its information when the program exits. This can be very useful for testing. To use it, specify sqlite:///:memory: as the argument to create_engine(). The create_engine() function can also be used in a similar way with other RDBMSs. For example, to connect to the database my_database on a MySQL server at some.domain.com with the username foo and the password bar, you could use mysql://foo:bar@some.domain.com/my_database .
Different databases use different markers (known as param styles) to label where the variables you pass to execute() should be inserted. The example above used SQLite which uses ? as the param style but if you tried to use MySQL or PostgreSQL you would need to use %s as the param style instead. The SQL would then look like this:
connection.execute(
"""
INSERT INTO users (username, password) VALUES (%s, %s);
""",
"foo", "bar"
)
Using """ characters to mark the begining and end of the SQL allows you to use " and ' characters as part of the SQL and also allows you to add line breaks if your SQL statements get very long.
使用MetaData定义你的表结构
metadata_test.py
from sqlalchemy import schema, types
metadata = schema.MetaData()
#To represent a Table, use the Table class:
page_table = schema.Table('page', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('name', types.Unicode(255), default=u''),
schema.Column('title', types.Unicode(255), default=u'Untitled Page'),
schema.Column('content', types.Text(), default=u''),
)
## MetaData object's methods:
# getting a list of Tables in the order (or reverse) of their dependency:
for t in metadata.sorted_tables:
print "Table name: ", t.name
## Table' s methods
for c in page_table.columns:
print "Column: ", c.name, c.type
for pk in page_table.primary_key:
print "primay_key: ", pk
for fk in page_table.foreign_keys:
print "foreign_keys: ", fk
Here you've created a metadata object from schema.MetaData, which will hold all the information about the tables, columns, types, foreign keys, indexes, and sequences that make up the database structure.
在虚拟环境中运行这个脚本，得到如下输入：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python metadata_test.py
Table name: page
Column: id Integer()
Column: name Unicode(length=255)
Column: title Unicode(length=255)
Column: content Text(length=None, convert_unicode=False, assert_unicode=None)
primay_key: page.id
sqlalchemy基本上支持所有的数据库类型定义，还允许用户自定义数据库类型，具体参见官方文档Column and Data Types
邦定MetaData到引擎
At this stage, the metadata is just information; it doesn't relate to any properties of a real database. To connect the metadata to a real database, you need to bind the metadata object to an engine.
metadata_bind_test.py
from sqlalchemy import schema, types
from sqlalchemy.engine import create_engine
metadata = schema.MetaData()
#To represent a Table, use the Table class:
page_table = schema.Table('page', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('name', types.Unicode(255), default=u''),
schema.Column('title', types.Unicode(255), default=u'Untitled Page'),
schema.Column('content', types.Text(), default=u''),
)
## MetaData object's methods:
# getting a list of Tables in the order (or reverse) of their dependency:
for t in metadata.sorted_tables:
print "Table name: ", t.name
## Table' s methods
for c in page_table.columns:
print "Column: ", c.name, c.type
for pk in page_table.primary_key:
print "primay_key: ", pk
for fk in page_table.foreign_keys:
print "foreign_keys: ", fk
# 使用in-memory SQLite database
engine = create_engine('sqlite:///:memory:')
# 邦定metadata到数据库引擎，这样metadata就可以利用引擎真实操作数据库了（反过来，数据库引擎也可以利用metadata的信息作有意义的操作）
metadata.bind = engine
＃如果metadata中定义的表还没有存在数据库中，可以使用metadata.create_all方法来自动创建他们
＃checkfirst=True表示create the table only if it doesn't already exist.
metadata.create_all(checkfirst=True)
Tip It is worth being aware that you can have SQLAlchemy automatically convert all string types to handle Unicode automatically if you set up the engine like this:
create_engine('sqlite:///:memory:', convert_unicode=True)
Reflecting Tables
反过来，如果数据库中定义的表还没有在metadata中声明，你也可以SQLAlchemy自动加载/反映(autoload=True, reflect)这些信息。需要注意的是此时的metadata必须已经邦定一个engine或者connection。
comment_table = schema.Table('comment', metadata, autoload=True)
具体参见官方文档Reflecting Tables
SQL Expression API
sqlexpression_test.py
from metadata_bind_test import engine, page_table
print "\nSQL Expression Example\n"
connection = engine.connect()
ins = page_table.insert(
values=dict(name=u'test', title=u'Test Page', content=u'Some content!')
)
print ins
result = connection.execute(ins)
print "\nSelecting Results\n"
from sqlalchemy.sql import select
s = select([page_table])
result = connection.execute(s)
for row in result:
print row
print "\nUpdating Results\n"
from sqlalchemy import update
u = update(page_table, page_table.c.title==u'Test Page')
connection.execute(u, title=u"New Title")
print "\nAfter Update\n"
s = select([page_table])
result = connection.execute(s)
print result.fetchall()
print "\nDeleting Row\n"
from sqlalchemy import delete
d = delete(page_table, page_table.c.title==u'New Title')
connection.execute(d)
print "\nAfter Delete\n"
s = select([page_table])
result = connection.execute(s)
print result.fetchall()
connection.close()
运行，得到如下输出：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python sqlexpression_test.py
Table name: page
Column: id Integer()
Column: name Unicode(length=255)
Column: title Unicode(length=255)
Column: content Text(length=None, convert_unicode=False, assert_unicode=None)
primay_key: page.id
2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 PRAGMA table_info("page")
2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 ()
2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0
CREATE TABLE page (
id INTEGER NOT NULL,
name VARCHAR(255),
title VARCHAR(255),
content TEXT,
PRIMARY KEY (id)
)
2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 ()
2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT
SQL Expression Example
INSERT INTO page (name, title, content) VALUES (?, ?, ?)
2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 INSERT INTO page (name, title, content) VALUES (?, ?, ?)
2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'test', u'Test Page', u'Some content!']
2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT
Selecting Results
2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content
FROM page
2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 []
(1, u'test', u'Test Page', u'Some content!')
Updating Results
2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 UPDATE page SET title=? WHERE page.title = ?
2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'New Title', u'Test Page']
2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT
After Update
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content
FROM page
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 []
[(1, u'test', u'New Title', u'Some content!')]
Deleting Row
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 DELETE FROM page WHERE page.title = ?
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'New Title']
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT
After Delete
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content
FROM page
2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 []
[]
可以看到，ins对象自动生成正确的插入SQL语句。实际上connection.execute()方法可以接受SQL语句，所以我们也可以这么写：
connection = engine.connect()
sql = "INSERT INTO page (name, title, content ) VALUES ('%s', '%s', '%s')" % ("test", "Test Page", "Some Content")
result = connection.execute(sql)
connection.close()
在上面这个例子中，我们是手动打开关闭链接，实际上可以让metadata帮我们做这个事情：
ins = page_table.insert(
values=dict(na
分享到：
发表评论
Why Pylons
Small and Simple enough! Well documentation! Easy to migrate to TurboGear and, yes, Rails.
How
发现TurboGears的文档非常给力，基本上给出了从零搭建一个网站(一个TurboGears Project)的过程，而由于pylons和TG都是使用Paster，所以基本是一样的（至少在构建步骤和配置文件上）。
Quickstarting a TurboGears 2 project(http://www.turbogears.org/2.0/docs/main/QuickStart.html)
具体如下：
0. 创建一个虚拟环境（推荐）
1. 创建项目结构——类似于antx gen
对于TG：
$paster quickstart
The paster quickstart command will create a basic project directory for you to use to get started on your TurboGears 2 application.
对于pylons：
Create a new project named HelloWorld with the following command:
$ paster create -t pylons HelloWorld
|
对于pylons，可以使用--list-templates查看可选的模板
$paster create --list-templates
Available templates:
basic_package: A basic setuptools-enabled package
paste_deploy: A web application deployed through paste.deploy
pylons: Pylons application template
pylons_minimal: Pylons minimal application template
|
2. 引入项目需要的依赖——类似于antx或者maven
2.1 配置文件：对于pylons和TG，都是setup.py。（类似于antx的project.xml或者maven的pom.xml）
The setup.py and setup.cfg files control various aspects of how your Pylons application is packaged when you distribute it. They also contain metadata about the project.
The setup.py file has a section which explicitly declares the dependencies of your application. The quickstart template has a few built in dependencies, and as you add new python libraries to your application's stack, you'll want to add them here too.
例如，如果你要使用FormBuild这个模块，那么可以在setup.py中作如下配置：
You should also add it as a dependency to your project by editing the setup.py file and adding FormBuild to the end of the install_requires argument：
install_requires=[
"Pylons>=0.9.7",
"SQLAlchemy>=0.5,<=0.5.99",
"Mako",
"FormBuild>=2.0,<2.99",
],
|
你当然可以直接使用
$easy_install "SQLAlchemy>=0.5,<=0.5.99"
|
2.2 安装依赖包：
对于pylons和TG都是一样的命令：
Then in order to make sure all those dependencies are installed you will want to run（Set up your project in development mode by entering this command）:
$python setup.py develop
|
paster会根据里面的依赖配置，执行类似如下命令：
$easy_install "SQLAlchemy>=0.5,<=0.5.99"
|
3. 初始化web应用环境——给每个模块（依赖）一个执行初始化的时机（如SQLAlchemy），其实会执行每个module下的_init.py文件，如model/init.py_。
|
python module The model directory contains an _init_.py file which makes that directory name into a python module (so you can use the Python expression import model). |
说明：这一步往往需要执行多次，每次做了什么修改，如数据模型修改，都可以跑一下。
Another key piece of TG2 application setup infrastructure is the paster setup-appcommand which takes a configuration file and runs websetup.py in that context. This allows you to use websetup.py to create database tables, pre-populate require data into your database, and otherwise make things nice for people fist setting up your app.
Note If it's the first time you're going to use the application, and you told quickstart to include authentication+authorizaiton, you will have to run setup-app to set it up (e.g., create a test database):$paster setup-app development.ini
websetup.py
The websetup.py contains any code that should be executed when an end user has installed your Pylons application and needs to initialize it. It frequently contains code to create the database tables required by your application, for example.
If this is the first time you're starting the application you have to run the following command to create and initialize your test database:$paster setup-app development.iniThis will create the database using the information stored in the development.ini file which by default makes single file SQLite database in the local file system. In addition to creating the database, it runs whatever extra database loaders or other setup are defined in websetup.py. In a quickstarted project with Auth enabled it creates a couple of basic users, groups, and permissions for you to use as an example.
It also shows how you can add new data automatically to the database when you need to add bootstrap data of your own.
4. 启动服务器
At this point your project should be operational, and you're ready to start up the app. To start a TurboGears 2 app, cd to the new directory (helloworld) and issue command paster serve to serve your new application:
$paster serve development.ini
As soon as that's done point your browser at http://localhost:8080/ and you'll see a nice welcome page with the inform(flash) message and current time.
|
By default the paster serve command is not in auto-reload mode as the CherryPy server used to be. If you also want your application to auto-reload whenever you change a source code file just add the --reload option to paster serve:
$paster serve --reload development.ini |
|
You can easily edit development.ini to change the default server port used by the built-in web server:
[server:main] ... port = 8080 |
至此，你的项目骨架已经搭建起来了。接下来的工作就是所谓MVC构建了。
5. MVC构建
下面的顺序是随机的，大家根据自己的习惯开发。一般来说，Domain Driven Development会从model开始开发，Page Driven Development会从view开始开发。笔者是属于后台开发人员，所以一般选择前者。
5.1 创建model
现在几乎所有的动态语言web框架都采用了某种ORM框架，在python的世界里，sqlalchemy是最受欢迎和推荐的。这里也只介绍这个框架。
Once you have configured the engine, it is time to configure the model. This is easy to do; you simply add all your classes, tables, and mappers to the end of model/_init_.py.
5.1 创建Controller：
$paster controller controllerName
对于TG：
貌似没有构建骨架的命令，不过我怀疑是跟pylons一样的。
5.2 创建view
6. 分层架构＆模块化
这个根据具体业务需求决定了。不过在使用了ORM的动态语言web框架中，DAO与Model（DO/DTO）是紧密结合的。所以可能没有单独的所谓的DAL层了。
7. 总结
回过头来再看一下pylons和TG的搭建过程，有J2EE经验的同学会惊讶的发现其实跟使用maven或者ant构建工具的Java web项目搭建过程是如此惊人的一致。另外，一些思想，如viewHelper，在一些框架中也能经常见到。
杂七杂八
|
You can access your models from within the python/ipython shell by typing:
$paster shell development.ini from your root directory. If ipython is installed within your virtual environment, it will be the default python shell. |
一些补充资料
SQLAlchemy
观于sqlalchemy的介绍，SQLAlchemy 0.5.8 官方文档 (http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/index.html)和pylonsbook(http://pylonsbook.com/en/1.1/introducing-the-model-and-sqlalchemy.html)的介绍比较给力。特别是后者，一个介绍web框架的文档，能从一个独立使用sqlalchemy的角度做了深入浅出的介绍，非常难得。
SQLAlchemy架构
其中 The abstraction layer consists of the SQL Expression API and the Metadata and Type APIs, which help isolate your Python code from the details of the underlying database engine.
使用Engine API链接数据库进行操作
The lowest-level API you are likely to use is the Engine API. This represents a low-level abstraction of a database engine, allowing you to use the same API to create connections to different RDBMSs for sending SQL statements and for retrieving results.
from sqlalchemy.engine import create_engine engine = create_engine('sqlite:///:memory:') connection = engine.connect() connection.execute( """ CREATE TABLE users ( username VARCHAR PRIMARY KEY, password VARCHAR NOT NULL ); """ ) connection.execute( """ INSERT INTO users (username, password) VALUES (?, ?); """, "foo", "bar" ) result = connection.execute("select username from users") for row in result: print "username:", row['username'] connection.close()
To work with an engine, you need to have a connection to it. The connection in the example is an instance of a SQLAlchemy Connection object, and result is a SQLAlchemy ResultProxy object (very much like a DB-API cursor) that allows you to iterate over the results of the statement you have executed.
运行这个脚本，得到如下输出：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python engine_test.py username: foo
When using create_engine(), you can specify different data source names (DSNs) to connect to different databases. For example, with SQLite, you can use sqlite:///relative/path to specify a file relative to the current working directory. You can use sqlite:////absolute/path to specify an absolute path. SQLite also has a memory mode that doesn't use the filesystem at all and loses all its information when the program exits. This can be very useful for testing. To use it, specify sqlite:///:memory: as the argument to create_engine(). The create_engine() function can also be used in a similar way with other RDBMSs. For example, to connect to the database my_database on a MySQL server at some.domain.com with the username foo and the password bar, you could use mysql://foo:bar@some.domain.com/my_database .
|
Different databases use different markers (known as param styles) to label where the variables you pass to execute() should be inserted. The example above used SQLite which uses ? as the param style but if you tried to use MySQL or PostgreSQL you would need to use %s as the param style instead. The SQL would then look like this:
connection.execute(
"""
INSERT INTO users (username, password) VALUES (%s, %s);
""",
"foo", "bar"
)
Using """ characters to mark the begining and end of the SQL allows you to use " and ' characters as part of the SQL and also allows you to add line breaks if your SQL statements get very long. |
使用MetaData定义你的表结构
from sqlalchemy import schema, types metadata = schema.MetaData() #To represent a Table, use the Table class: page_table = schema.Table('page', metadata, schema.Column('id', types.Integer, primary_key=True), schema.Column('name', types.Unicode(255), default=u''), schema.Column('title', types.Unicode(255), default=u'Untitled Page'), schema.Column('content', types.Text(), default=u''), ) ## MetaData object's methods: # getting a list of Tables in the order (or reverse) of their dependency: for t in metadata.sorted_tables: print "Table name: ", t.name ## Table' s methods for c in page_table.columns: print "Column: ", c.name, c.type for pk in page_table.primary_key: print "primay_key: ", pk for fk in page_table.foreign_keys: print "foreign_keys: ", fk
Here you've created a metadata object from schema.MetaData, which will hold all the information about the tables, columns, types, foreign keys, indexes, and sequences that make up the database structure.
在虚拟环境中运行这个脚本，得到如下输入：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python metadata_test.py
Table name: page
Column: id Integer()
Column: name Unicode(length=255)
Column: title Unicode(length=255)
Column: content Text(length=None, convert_unicode=False, assert_unicode=None)
primay_key: page.id
sqlalchemy基本上支持所有的数据库类型定义，还允许用户自定义数据库类型，具体参见官方文档Column and Data Types
邦定MetaData到引擎
At this stage, the metadata is just information; it doesn't relate to any properties of a real database. To connect the metadata to a real database, you need to bind the metadata object to an engine.
from sqlalchemy import schema, types from sqlalchemy.engine import create_engine metadata = schema.MetaData() #To represent a Table, use the Table class: page_table = schema.Table('page', metadata, schema.Column('id', types.Integer, primary_key=True), schema.Column('name', types.Unicode(255), default=u''), schema.Column('title', types.Unicode(255), default=u'Untitled Page'), schema.Column('content', types.Text(), default=u''), ) ## MetaData object's methods: # getting a list of Tables in the order (or reverse) of their dependency: for t in metadata.sorted_tables: print "Table name: ", t.name ## Table' s methods for c in page_table.columns: print "Column: ", c.name, c.type for pk in page_table.primary_key: print "primay_key: ", pk for fk in page_table.foreign_keys: print "foreign_keys: ", fk # 使用in-memory SQLite database engine = create_engine('sqlite:///:memory:') # 邦定metadata到数据库引擎，这样metadata就可以利用引擎真实操作数据库了（反过来，数据库引擎也可以利用metadata的信息作有意义的操作） metadata.bind = engine ＃如果metadata中定义的表还没有存在数据库中，可以使用metadata.create_all方法来自动创建他们 ＃checkfirst=True表示create the table only if it doesn't already exist. metadata.create_all(checkfirst=True)
|
Tip It is worth being aware that you can have SQLAlchemy automatically convert all string types to handle Unicode automatically if you set up the engine like this: create_engine('sqlite:///:memory:', convert_unicode=True) |
|
Reflecting Tables 反过来，如果数据库中定义的表还没有在metadata中声明，你也可以SQLAlchemy自动加载/反映(autoload=True, reflect)这些信息。需要注意的是此时的metadata必须已经邦定一个engine或者connection。 comment_table = schema.Table('comment', metadata, autoload=True) 具体参见官方文档Reflecting Tables
|
SQL Expression API
from metadata_bind_test import engine, page_table print "\nSQL Expression Example\n" connection = engine.connect() ins = page_table.insert( values=dict(name=u'test', title=u'Test Page', content=u'Some content!') ) print ins result = connection.execute(ins) print "\nSelecting Results\n" from sqlalchemy.sql import select s = select([page_table]) result = connection.execute(s) for row in result: print row print "\nUpdating Results\n" from sqlalchemy import update u = update(page_table, page_table.c.title==u'Test Page') connection.execute(u, title=u"New Title") print "\nAfter Update\n" s = select([page_table]) result = connection.execute(s) print result.fetchall() print "\nDeleting Row\n" from sqlalchemy import delete d = delete(page_table, page_table.c.title==u'New Title') connection.execute(d) print "\nAfter Delete\n" s = select([page_table]) result = connection.execute(s) print result.fetchall() connection.close()
运行，得到如下输出：
(env)forrest@ubuntu:~/Install/virtualenv-1.4.5/env/SimpleSite/simplesite/model$ python sqlexpression_test.py Table name: page Column: id Integer() Column: name Unicode(length=255) Column: title Unicode(length=255) Column: content Text(length=None, convert_unicode=False, assert_unicode=None) primay_key: page.id 2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 PRAGMA table_info("page") 2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 () 2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 CREATE TABLE page ( id INTEGER NOT NULL, name VARCHAR(255), title VARCHAR(255), content TEXT, PRIMARY KEY (id) ) 2011-01-06 21:01:41,556 INFO sqlalchemy.engine.base.Engine.0x...31d0 () 2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT SQL Expression Example INSERT INTO page (name, title, content) VALUES (?, ?, ?) 2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 INSERT INTO page (name, title, content) VALUES (?, ?, ?) 2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'test', u'Test Page', u'Some content!'] 2011-01-06 21:01:41,557 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT Selecting Results 2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content FROM page 2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 [] (1, u'test', u'Test Page', u'Some content!') Updating Results 2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 UPDATE page SET title=? WHERE page.title = ? 2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'New Title', u'Test Page'] 2011-01-06 21:01:41,558 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT After Update 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content FROM page 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 [] [(1, u'test', u'New Title', u'Some content!')] Deleting Row 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 DELETE FROM page WHERE page.title = ? 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 [u'New Title'] 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 COMMIT After Delete 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 SELECT page.id, page.name, page.title, page.content FROM page 2011-01-06 21:01:41,559 INFO sqlalchemy.engine.base.Engine.0x...31d0 [] []
可以看到，ins对象自动生成正确的插入SQL语句。实际上connection.execute()方法可以接受SQL语句，所以我们也可以这么写：
connection = engine.connect() sql = "INSERT INTO page (name, title, content ) VALUES ('%s', '%s', '%s')" % ("test", "Test Page", "Some Content") result = connection.execute(sql) connection.close()
在上面这个例子中，我们是手动打开关闭链接，实际上可以让metadata帮我们做这个事情：
ins = page_table.insert(
values=dict(na
分享到：
发表评论
相关推荐
The Definitive Guide to Pylons.pdf
In this book, cofounder and lead developer James Gardner brings you a comprehensive introduction to Pylons, the web framework that uses the best of Ruby, Python, and Perl and the emerging WSGI ...
主要介绍了Python Web框架Pylons中使用MongoDB 的例子,大家参考使用
python库，解压后可用。 资源全名：Pylons-0.8.2-py2.3.egg
资源来自pypi官网。 资源全名：Pylons-0.8.2-py2.3.egg
笔记定向塔已与repoze.bfg合并，并且现在处于仅维护模式。 强烈建议新项目从新的合并的Web框架。安装。 如果要从源代码安装，可以运行以下命令： $ python setup.py install 如果尚未安装该模块，它将显示一条消息...
Apress.the.Definitive.Guide.to.Pylons.Dec.2008
pyramid是最新python web开发框架，pylons升级版。
博文链接：https://zsp.iteye.com/blog/178446
可用于UnityVR开发，3D游戏开发，高清天空盒子Skybox素材，游戏环境背景素材，无水印。 让你身临其境的天空盒子，各类题材丰富，都是辛苦搜罗所得的高清exr格式，可以直接用于Unity开发，特别是VR游戏的开发。...
pyramid1.2源码安装包。pyramid是最新python web 开发框架。是pylons的升级版。
zope, pyramid, pylons框架常用模版系统 chameleon zpt的程序员指南
它安装了一个演示 Substance D 应用程序以及用于编译 CSS/JS 资产以最终包含在 Substance D 中的源代码。 安装 从 GitHub 中检出这个包（必须是 Substance D 团队 Pylons 组织的成员）： $ git clone git@github....
(3)the pylons (or towers) supporting the cable system; (4)the anchor blocks (or anchor piers) supporting the cable system vertically and horizontally, or only vertically, at the extreme ends.
pylons默认的模板就是用的它；相比而言，Django 内建的模板引擎，为了维持所谓模板语法的纯粹性和简单性，更纯粹的满足 MVC 模式的规定，牺牲了很多灵活性，一些高级的功能不得不利用 tag 和 filter 来实现，其写法...
博主介绍说：“日志是一个应用程序的重要组成部分，今天在看pylons对日志的处理时，受到启发，于是plog就诞生了。很多php框架都忽略了日志的重要性(如kohana)，往往只是能用，自定义和可扩展性不够，等到程序出了...
流程信仰者 - 所有评语的细节都会记录在数据库中，包括每一个评语需要的action及完成状态。并且会有状态报告，以及各种各样的统计。 架构师和开发者 -可以查看所有Revier项目的细节，也可以查看特定文件的评语，...