Python dao.Dao類代碼示例

 def edit_user_info(self, user_name=None, password=None):
     """編輯用戶信息"""
     if user_name:
         self.user_name = user_name
     if password:
         self.password = password
     return Dao.save_all()

 def add(d):
     del d['id']
     sql = "INSERT INTO assignments (%s) VALUES (%s);" % (
         ','.join(d.keys()), '?' + ',?' * (len(d) - 1)
     )
     vals = list(d.values())
     return Dao.execute(sql, vals)

 def db_connect(self):
     """返回一個數據庫連接，跟上麵的session不一樣，想要裸寫sql就調用這個。
     execute("把sql語句寫進來就好了")，返回的結果需要注意，是ResultProxy。了解詳細見下麵連接
     http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html?highlight=resultproxy#sqlalchemy.engine.ResultProxy。
     但是可以調用first(), all()等，差不多了一樣用。隻是返回的類似dict或mapping，先dict(res.items())比較好。
     """
     return Dao.db_connect()

 def login(username, password):
     sql = "SELECT * FROM users WHERE username=?;"
     vals = (username, )
     rex = Dao.execute(sql, vals)
     if not rex or len(rex) != 1:
         raise Exception('Invalid login!')
     if not User.__verify_pw(password, rex[0]['password']):
         raise Exception('Invalid login!')
     return rex[0]

 def update(d):
     asnid = d['id']
     del d['id']
     sql = ("UPDATE assignments "
            "SET %s "
            "WHERE id=?;") % (
         ','.join(f + '=?' for f in d.keys()))
     vals = list(d.values()) + [asnid]
     return Dao.execute(sql, vals)

 def update(d):
     empid = d['id']
     del d['id']
     sql = ("UPDATE employees "
            "SET %s "
            "WHERE id=?;") % (
         ','.join(f + '=?' for f in d.keys()))
     vals = list(d.values()) + [empid]
     return Dao.execute(sql, vals)

 def setUp(self):
     """初始化數據庫表結構， 完成數據庫session，緩存及redis連接"""
     Dao.init_db_uri()
     self.session = Dao.session()
     Dao.init_table_schema()
     self.cache = OPCache()
     self.rds = Dao.redis()
     self.session.rollback()

 def get_for_timeframe(first_month, last_month):
     sql = ("SELECT a.id AS id, "
            "a.employee_id AS employee_id, "
            "a.project_id AS project_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "e.name AS employee, "
            "p.nickname AS project "
            "FROM assignments AS a "
            "JOIN employees AS e ON a.employee_id=e.id "
            "JOIN projects AS p ON a.project_id=p.id "
            "WHERE a.first_month BETWEEN ? AND ? "
            "OR a.last_month BETWEEN ? AND ?;")
     vals = (first_month, last_month, first_month, last_month)
     return Dao.execute(sql, vals)

 def get_for_project(prjid, month=None):
     sql = ("SELECT a.id AS id, "
            "a.employee_id AS employee_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "a.notes AS notes, "
            "e.name AS employee "
            "FROM assignments AS a "
            "JOIN employees AS e ON a.employee_id= e.id "
            "WHERE a.project_id=? ")
     vals = [prjid]
     if month:
         sql += "AND a.last_month >= ? "
         vals += [month]
     sql += "ORDER BY e.name;"
     return Dao.execute(sql, vals)

 def get_for_employee(empid, month=None):
     sql = ("SELECT a.id AS id, "
            "a.project_id AS project_id, "
            "a.first_month AS first_month, "
            "a.last_month AS last_month, "
            "a.effort AS effort, "
            "a.notes AS notes, "
            "p.nickname AS project "
            "FROM assignments AS a "
            "JOIN projects AS p "
            "ON a.project_id= p.id "
            "WHERE a.employee_id=? ")
     vals = [empid]
     if month:
         sql += ("AND a.first_month <= ? "
                 "AND a.last_month >= ? ")
         vals += [month, month]
     sql += "ORDER BY p.nickname;"
     return Dao.execute(sql, vals)

 def set_name(self, name):
     """修改名稱"""
     if not name:
         return False
     self.district_name = name
     return Dao.save_all()

 def delete(self):
     """設置為刪除"""
     self.is_delete = 1
     return Dao.save_all()

 def recover(self):
     """反刪除"""
     self.is_delete = 0
     return Dao.save_all()

            login_url='/login/',
        )

        tornado.web.Application.__init__(self, urls, **settings)


if __name__ == '__main__':
    """"""
    options.define(name='config', default='dev')
    options.define(name='port', default=38001)
    options.define(name='process', default=1)
    options.define(name='server', default='total')

    tornado.options.parse_command_line()
    config = Config(options.config)
    Dao.init_db_uri(options.config)
    # TODO for dev and test
    Dao.init_schema()

    app = Application()
    app.config = options.config

    log.info('Starting box api server... Listening port: {}'.format(options.port))
    try:
        server = HTTPServer(app, xheaders=True)
        server.bind(int(options.port))
        server.start(num_processes=int(options.process))
        tornado.ioloop.IOLoop.instance().start()
    except Exception as e:
        log.error('box api can not running:\n{}'.format(e), exc_info=True)
        tornado.ioloop.IOLoop.instance().stop()

 def edit_name(self, name):
     """改名"""
     if name:
         self.category_name = name
     return Dao.save_all()