Python Dao.execute方法代碼示例

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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]

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 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)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def delete_role(role_id):
     sql = "DELETE FROM roles WHERE id=?;"
     vals = (role_id,)
     return Dao.execute(sql, vals)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def get_all():
     sql = "SELECT * FROM employees ORDER BY name;"
     return Dao.execute(sql)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def add_user(d):
     sql = ("INSERT INTO users "
            "(username, password, role_id) "
            "VALUES (?,?,?);")
     vals = (d['username'], User.__hash_pw(d['password']), d['role_id'])
     return Dao.execute(sql, vals)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def update_role(d):
     sql = ("UPDATE roles "
            "SET name=?, description=? "
            "WHERE id=?;")
     vals = (d['name'], d['description'], d['id'])
     return Dao.execute(sql, vals)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def add_role(d):
     sql = ("INSERT INTO roles "
            "(name, description) "
            "VALUES (?,?);")
     vals = (d['name'], d['description'])
     return Dao.execute(sql, vals)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def get_roles():
     sql = 'SELECT id, name AS value, description FROM roles;'
     return Dao.execute(sql)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def change_password(user_id, new_password):
     sql = ("UPDATE users "
            "SET password=? "
            "WHERE id=?")
     vals = (User.__hash_pw(new_password), user_id)
     return Dao.execute(sql, vals)

# 需要導入模塊: from models.dao import Dao [as 別名]
# 或者: from models.dao.Dao import execute [as 別名]
 def delete_user(user_id):
     sql = "DELETE FROM users WHERE id=?;"
     vals = (user_id,)
     return Dao.execute(sql, vals)