最後更新: 2015-01-24

DB

webpy  的此功能用起上來有點雞肋

 

 

---

connect DB

 

mysql:

db = web.database(dbn='mysql', user='username', pw='password', db='dbname')

sqlite

db = web.database(dbn='sqlite', db='YOUR_DB')

# 當 database 不存在時, 那會沒有 Error 而去建立它 ....

 

---

select

 

# 拿到整個 table

entries = db.select('mytable')

 

# 查看所行的 SQL

>>> results = db.select('users', _test=True)

0.0 (2): SELECT * FROM users

# result 是 object 來

>>> print results

<web.utils.IterBetter instance at 0x991a76c>

# 拿一隻 record

print results[0]

<Storage {'username': u'test4', 'remark': None, 'enable': 1, 'uid': 4, 'gid': 2, 'password': u'4567', 'last_modify': u'2012-11-15 14:20:34'}>

# 求助

help(result)

# Remark

IterBetter                            # iter_instance[1] 後, 就不能存取  [0] 及 [1] 了
                                          # bool(iter_instance) 可查看仍有無 record
                                          # i = iter_instance[1] 可以複製它的內容

Storage                               # 除了有 dict (people['name'])一樣的存取方式外, 它支援用 "." 存取 i.e. people.name

 

其他工能:

vars                                       <-- myd = {'uid':1}
                                                   results = db.select('mytable', myd, where="uid = $uid")

what="id, name"

where="id<10"                       <-- 可以用 ">", "<="

order="Col_Name DESC"

group="Col_name"

limit=10

offset=10

_test=True                              <-- 不是拿出 Data, 而是顯示要行的 SQL statment

---

IterBetter to list

 

由於 result 係 better 來, 只能 call 一次, 所以把它轉成 list 可以重用

dir (object)

['__doc__', '__getitem__', '__init__', '__iter__', '__len__', '__module__', '__nonzero__', 'c', 'i', 'list']

newresult = object.list()

 

---

IN

                self.conn.delete(
                    'admin',
                    vars={'accounts': accounts, },
                    where='username IN $accounts',
                )

 

---

join table

e.g.1

resultOfTotal = self.conn.select(
       ['alias','mailbox'],
       vars=sql_vars,
       what='COUNT(address) AS total',
       where='alias.address=mailbox.username AND goto NOT LIKE address',
)

e.g.2

    resultOfTotal = self.conn.select(
        'alias AS a LEFT JOIN mailbox AS m ON a.address = m.username',
        vars=sql_vars,
        what='COUNT(a.address) AS total',
        where='m.username IS NULL AND a.domain=$domain AND a.goto != a.address AND a.address != a.domain',
    )

---

update:

• 支援 vars ( 在 where 會用到 vars 的 dict )
• Database 的 Colume 不用加 "" 括它的
• 要 update 的 colume 的 variable 不用加 "" 括它的
• 不支持 limit ...

Example1:

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

Example2:

db.update(
    'mailbox',
    vars={'username': self.mail, },
    where='username=$username',
    password=updates['password'],
)

 

---

delete:

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

• 不支持 limit=?

 

---

insert

寫法1:

sequence_id = db.insert('mytable', username="Bob", password="1234")

寫法2:

mydict = {'username':"Bob", 'password':"1234"}

sequence_id = db.insert('mytable', **mydict)

 

---

query:

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

results = db.query("SELECT * FROM users WHERE id=$id", vars={'id':10})

 

---

transaction:

import web

db = web.database(dbn="postgres", db="webpy", user="foo", pw="")
t = db.transaction()
try:
    db.insert('person', name='foo')
    db.insert('person', name='bar')
except:
    t.rollback()
    raise
else:
    t.commit()

 

---

Using a Dict(sqlwhere)

sqlwhere

web.db.sqlwhere({'a': 1, 'b': 2}, grouping=' OR ')

            相當於

'a = 1 OR b = 2'

# Default for grouping is ' AND '

i.e.

where_dict = {'col1': 1, col2: 'sometext'}

where_sql = web.db.sqlwhere(where_dict)

db.delete('mytable', where=where_sql)

 

---

Count Record:

import web
db = web.database(dbn='postgres', db='mydata', user='dbuser', pw='')
results = db.query("SELECT COUNT(*) AS total_users FROM users")
print results[0].total_users