優雅的sql 結果轉成 array with dictionary

今天看到 tornado project 裡附的 blog 的 demo source code, 發現一個有趣的寫法, 真的滿優雅的, 省去了多的人工的轉換, 可以直接讓 sql command 變成一組 array + dictionary, 對我來說很直覺與方便.

demo:
https://github.com/tornadoweb/tornado/tree/stable/demos/blog

舉例來說, 這個範例用的檢查使用者是否登入用的 code:

def row_to_obj(self, row, cur):
    """Convert a SQL row to an object supporting dict and attribute access."""
    obj = tornado.util.ObjectDict()
    for val, desc in zip(row, cur.description):
        obj[desc.name] = val
    return obj

async def execute(self, stmt, *args):
    """Execute a SQL statement.

    Must be called with ``await self.execute(...)``
    """
    with (await self.application.db.cursor()) as cur:
        await cur.execute(stmt, args)

async def query(self, stmt, *args):
    """Query for a list of results.

    Typical usage::

        results = await self.query(...)

    Or::

        for row in await self.query(...)
    """
    with (await self.application.db.cursor()) as cur:
        await cur.execute(stmt, args)
        return [self.row_to_obj(row, cur) for row in await cur.fetchall()]

async def queryone(self, stmt, *args):
    """Query for exactly one result.

    Raises NoResultError if there are no results, or ValueError if
    there are more than one.
    """
    results = await self.query(stmt, *args)
    if len(results) == 0:
        raise NoResultError()
    elif len(results) > 1:
        raise ValueError("Expected 1 result, got %d" % len(results))
    return results[0]

async def prepare(self):
    # get_current_user cannot be a coroutine, so set
    # self.current_user in prepare instead.
    user_id = self.get_signed_cookie("blogdemo_user")
    if user_id:
        self.current_user = await self.queryone(
            "SELECT * FROM authors WHERE id = %s", int(user_id)
        )

他把 sql 指令分二種, 一個會回傳資料的,就是 query, 另一種是直接執行 command 的, 是 execute(), 上面這個 block 裡的短短幾行, 對我來說有3個優點:

  • 程式碼變的精簡, 這有好有壞, 看的懂的人就覺得還好, 看不懂精簡的 code 的人, 會覺得難懂, 實際上動手做做實驗後, 就會知道這些神奇的看不懂的符號的各自用途與功能.
  • 程式的彈性變高, 例如有時候在登入傳回的資料要增加欄位, 這時完全不需要另外寫變數去接, 也不用分開寫 sql command, 直接修改 sql command 加入想要 join 的 table, 就取得想要的資料了, 覺得超方便.
  • 這個寫法,居然通用於 mysql/mssql/sqlite.

有了上面的資料後, 對於格式化變數內容, 可以使用 template 進行內容的置換, 詳細用法:
https://www.tornadoweb.org/en/stable/guide/templates.html

A Tornado template is just HTML (or any other text-based format) with Python control sequences and expressions embedded within the markup:

<html>
   <head>
      <title>{{ title }}</title>
   </head>
   <body>
     <ul>
       {% for item in items %}
         <li>{{ escape(item) }}</li>
       {% end %}
     </ul>
   </body>
 </html>

If you saved this template as “template.html” and put it in the same directory as your Python file, you could render this template with:

class MainHandler(tornado.web.RequestHandler):
    def get(self):
        items = ["Item 1", "Item 2", "Item 3"]
        self.render("template.html", title="My title", items=items)

Tornado templates support control statements and expressions. Control statements are surrounded by {% and %}, e.g. {% if len(items) > 2 %}. Expressions are surrounded by {{ and }}, e.g. {{ items[0] }}.

Control statements more or less map exactly to Python statements. We support ifforwhile, and try, all of which are terminated with {% end %}. We also support template inheritance using the extends and block statements, which are described in detail in the documentation for the tornado.template.

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *