python 数据库操作

import pymysql.cursors
class DBUtil():
    def __init__(self):
        self.connect=None
        self.cursor=None
    def get_con(self):
        try:
            self.connect = pymysql.Connect(
                host='localhost',
                port=3306,
                user='root',
                passwd='123456',
                db='test',
                charset='utf8'
            )
        except Exception as result:
            print(result)
        else:
            # 获取游标
            self.cursor = self.connect.cursor()
            # print("打开数据库")
    def query_data(self):
        # 查询数据
        # sql = "SELECT name,saving FROM trade WHERE account = '%s' "
        # data = ('13512345678',)
        # self.cursor.execute(sql % data)
        sql = "SELECT * FROM trade "
        self.cursor.execute(sql)
        # fetchone返回单个元组，即一条记录，没有结果返回None，fetchall返回多个元组，即多条记录，没有结果返回()
        #例如：fetchone返回(AA,123),fetchall返回((AA,123),(bb,111)(cc,222))
        for row in self.cursor.fetchall():
            print("ID：%d\t Name:%s\tAccount:%s\tSaving:%.2f\tExpend:%.2f\tIncome:%.2f" % row)
        print('共查找出', self.cursor.rowcount, '条数据')

    def insert_data(self):
        # 插入数据
        sql = "INSERT INTO trade (name, account, saving) VALUES ( '%s', '%s', %.2f )"
        data = ('w喔喔', '13512345001', 10000)
        self.cursor.execute(sql % data)
        self.connect.commit()
        print('成功插入', self.cursor.rowcount, '条数据')

    def update_data(self):
        # 修改数据
        sql = "UPDATE trade SET saving = %.2f WHERE account = '%s' "
        data = (8888, '13512345001')
        self.cursor.execute(sql % data)
        self.connect.commit()
        print('成功修改', self.cursor.rowcount, '条数据')

    def delete_data(self):
        # 删除数据
        sql = "DELETE FROM trade WHERE account = '%s' LIMIT %d"
        data = ('13512345001', 1)
        self.cursor.execute(sql % data)
        self.connect.commit()
        print('成功删除',self.cursor.rowcount, '条数据')

    def close_database(self):
        # 关闭连接
        self.cursor.close()
        self.connect.close()
        print("关闭连接")

    def transaction_data(self):
        # 事务处理
        sql_1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '18012345678' "
        sql_2 = "UPDATE trade SET expend = expend + 1000 WHERE account = '18012345678' "
        sql_3 = "UPDATE trade SET income = income + 2000 WHERE account = '18012345678' "

        try:
            self.cursor.execute(sql_2)  # 支出增加1000
            self.cursor.execute(sql_1)  # 储蓄增加1000
            self.cursor.execute(sql_3)  # 收入增加2000
        except Exception as e:
            self.connect.rollback()  # 事务回滚
            print('事务处理失败', e)
        else:
            self.connect.commit()  # 事务提交
            print('事务处理成功', self.cursor.rowcount)
if __name__ == '__main__':
    db=DBUtil()
    db.get_con()
    db.insert_data()
    db.query_data()
    db.update_data()
    db.query_data()
    db.delete_data()
    db.query_data()
    db.close_database()