Web. py database operation guide

Original article: chenxiaoyu. orgblogarchives226 official website address: webpy. orgweb. py is a compact and flexible framework. The latest stable version is 0.33. Here we will not introduce the web Development Section, but will introduce database-related operations. Many Pyer initially liked to encapsulate their own database operations. However

Http://chenxiaoyu.org/blog/archives/226/web. py is a small and flexible framework, the latest stable version is 0.33. Here we will not introduce the web Development Section, but will introduce database-related operations. Many Pyer initially liked to encapsulate their own database operations. However

Original article: http://chenxiaoyu.org/blog/archives/226

Official website address: http://webpy.org/

Web. py is a small and flexible framework, and the latest stable version is 0.33. Here we will not introduce the web Development Section, but will introduce database-related operations.

Many Pyer initially liked to encapsulate their own database operations. However, after observing the source code of web. py, we found that its database operations were quite compact and practical. We recommend that you try it out.

There is not much nonsense. Install it first. There are two ways:

1. easy_install MethodIf wood has this tool, you can refer to: http://chenxiaoyu.org/blog/archives/23

easy_install web.py

2. Download source code compilation. Address: http://webpy.org/static/web.py-0.33.tar.gz, extract and execute:

python setup.py install

The web. py installation is over. If you want to use the db function, you must use the corresponding database operation modules, such as MySQLdb and psycopg2. If you need to try the database pool function, You have to install DBUtils. You can install these modules through easy_install.

Let's start using it!

1. Import the module and define database connection.

import webdb = web.database(dbn='postgres', db='mydata', user='dbuser', pw='')

2. select query

# Query table entries = db. select ('mytable') # where condition myvar = dict (name = "Bob") results = db. select ('mytable', myvar, where = "name = $ name") results = db. select ('mytable', where = "id> 100") # query the specific column results = db. select ('mytable', what = "id, name") # order byresults = db. select ('mytable', order = "post_date DESC") # groupresults = db. select ('mytable', group = "color") # limitresults = db. select ('mytable', limit = 10) # offsetresults = db. select ('mytable', offset = 10)

3. Update

db.update('mytable', where="id = 10", value1 = "foo")

4. Delete

db.delete('mytable', where="id=10")

5. Complex Query

# Countresults = db. query ("select count (*) AS total_users FROM users") print results [0]. total_users # joinresults = db. query ("SELECT * FROM entries JOIN users WHERE entries. author_id = users. id ") # prevent SQL Injection by doing this results = db. query ("SELECT * FROM users WHERE id = $ id", vars = {'id': 10 })

6 multi-database operations (web. py is greater than 0.3)

db1 = web.database(dbn='mysql', db='dbname1', user='foo')db2 = web.database(dbn='mysql', db='dbname2', user='foo')print db1.select('foo', where='id=1')print db2.select('bar', where='id=5')

7. Transactions

T = db. transaction () try: db. insert ('person ', name = 'foo') db. insert ('person ', name = 'bar') into t: t. rollback () raiseelse: t. commit () # Use withfrom _ future _ import with_statementwith db for Python 2.5 +. transaction (): db. insert ('person ', name = 'foo') db. insert ('person ', name = 'bar ')