編寫表DAO類

有幾個表寫幾個DAO，每個DAO裡有CRUD四項操作 account_dao 放在 com.dao包裡

"""賬號表DAO"""

# coding=utf-8
from com.dao.base_dao import BaseDao
import pymysql


# 建立賬號表DAO類，執行CRUD四種方法
class AccountDao(BaseDao):

    def __init__(self):
        super().__init__()

    # 查詢賬號是否存在
    def r_id(self, userid):
        account = None
        try:
            with self.conn.cursor() as cur:
                sql = 'select userid, password from account where userid = %s'
                aft = cur.execute(sql, userid)
                if aft > 0:
                    row = cur.fetchone()
                    if row is not None:
                        account = {'userid': row[0], 'password': row[1]}
                else:
                    print('無此賬號')

        except pymysql.DatabaseError as e:
            print('查詢失敗！', e)
        finally:
            self.close()
        return account

    # 查詢全部賬號
    def r_all(self):
        accounts = []
        try:
            with self.conn.cursor() as cur:
                sql = 'select userid, password from account'
                aft = cur.execute(sql)
                if aft > 0:
                    print('查詢到{0}個賬號'.format(aft))
                    result_set = cur.fetchall()
                    for row in result_set:
                        account = {'userid': row[0], 'password': row[1]}
                        accounts.append(account)
                else:
                    print('無資料')
        except pymysql.DatabaseError as e:
            print('查詢失敗!', e)
        finally:
            self.close()
        return accounts

    # 新增賬號
    def c_account(self, userid, password):
        try:
            with self.conn.cursor() as cur:
                sel_id = 'select * from account where userid = %s'
                aft = cur.execute(sel_id, userid)
                if aft > 0:
                    print('此賬號已存在: {0}'.format(userid))
                else:
                    sql = 'insert into account(userid, password) values(%s, %s)'
                    cur.execute(sql, (userid, password))
                    self.conn.commit()
                    print('新增成功')
        except pymysql.DatabaseError as e:
            self.conn.rollback()
            print('新增失敗！', e)
        finally:
            self.close()

    # 修改密碼
    def u_password(self, userid, password):
        try:
            with self.conn.cursor() as cur:
                sql = 'update account set password=%s where userid = %s'
                aft = cur.execute(sql, (password, userid))
                if aft > 0:
                    self.conn.commit()
                    print('修改成功', aft)
                else:
                    print('內容沒有更新')
        except pymysql.DatabaseError as e:
            self.conn.rollback()
            print('修改失敗！', e)
        finally:
            self.close()

    # 刪除賬戶
    def d_account(self, userid):
        try:
            with self.conn.cursor() as cur:
                sql = 'delete from account where userid = %s'
                aft = cur.execute(sql, userid)
                if aft > 0:
                    self.conn.commit()
                    print('刪除成功')
                else:
                    print('無此賬號！')
        except pymysql.DatabaseError as e:
            self.conn.rollback()
            print('刪除失敗！', e)
        finally:
            self.close()