web.py數(shù)據(jù)庫操作指南

easy_install web.py

python setup.py install

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

# 查詢表
entries = db.select('mytable')

# where 條件
myvar = dict(name="Bob")
results = db.select('mytable', myvar, where="name = $name")
results = db.select('mytable', where="id>100")

# 查詢具體列
results = db.select('mytable', what="id,name")

# order by
results = db.select('mytable', order="post_date DESC")

# group
results = db.select('mytable', group="color")

# limit
results = db.select('mytable', limit=10)

# offset
results = db.select('mytable', offset=10)

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

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

# count
results = db.query("SELECT COUNT(*) AS total_users FROM users")
print results[0].total_users

# join
results = db.query("SELECT * FROM entries JOIN users WHERE entries.author_id = users.id")

# 防止SQL注入可以這么干
results = db.query("SELECT * FROM users WHERE id=$id", vars={'id':10})

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')

t = db.transaction()
try:
 db.insert('person', name='foo')
 db.insert('person', name='bar')
except:
 t.rollback()
 raise
else:
 t.commit()

# Python 2.5+ 可以用with
from __future__ import with_statement
with db.transaction():
 db.insert('person', name='foo')
 db.insert('person', name='bar')