pymysql类

import pymysql

'''
简化mysql 的数据库的操作封装成类调用
主要一个类的实例为一个数据库的连接,
处理这个流程
优化封装成静态方法调用避免多次连接数据库造成连接资源浪费
'''


class mysqlDB(object):

    def __init__(self, host, user, passwd, db, port=3306, charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = passwd
        self.charset = charset
        self.connection()

    # 创建一个连接
    def connection(self):
        try:
            self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db,
                                        charset=self.charset)
            self.cur = self.conn.cursor()
        except pymysql.Error as e:
            self.Error = str(e.args[0]) + str(e.args[1])
            raise

    # 关闭连接
    def closeconn(self):
        self.cur.close()
        self.conn.close()

    # 查询一条数据
    def getOneData(self, sql):
        try:
            self.cur.execute(sql)
            return self.cur.fetchone()
        except pymysql.Error as e:
            self.Error = str(e.args[0]) + str(e.args[1])
            raise

    # 查询多条数据
    def getAllData(self, sql):
        try:
            self.cur.execute(sql)
            return self.cur.fetchall()
        except pymysql.Error as e:
            self.Error = str(e.args[0]) + str(e.args[1])
            raise

    # 添加/删除/修改
    def crud(self, sql):
        try:
            n = self.cur.execute(sql)
            self.conn.commit()
            return n
        except pymysql.Error as e:
            self.conn.rollback()
            self.Error = str(e.args[0]) + str(e.args[1])
            raise

    # 批量插入
    def execMany(self, sql, vals):
        try:
            n = self.cur.executemany(sql, vals)
            print(n)
            self.conn.commit()
        except pymysql.Error as e:
            self.conn.rollback()
            self.Error = str(e.args[0]) + str(e.args[1])
            raise


class mysqlTB(object):
    def __init__(self, db):
        self.db = db

    def getAllData(self, sql):
        self.db.connection()
        data = self.db.getAllData(sql)
        self.db.closeconn()
        return data

    def crud(self,sql):
        self.db.connection()
        try:
            self.db.crud(sql)
        except Exception as e:
            print('插入数据报错！', str(e))
        finally:
            self.db.closeconn()


if __name__ == '__main__':

    # ['211', '李四'],
    # ['311', '王五']
    # insert_data = [[311, '王五']]
    try:
        mydb = MysqlDB('180.103.66.133', 'root', 'Dg#D21bT$s', 'daguan_fileconvert')
        # sql = "select * from test "
        # res = mydb.getAllData(sql)
        # print(res)
        # for item in res:
        #     print("name:%s,age:%s" % (item[0], item[1]))
        # print('------------第二次哟------------')
        # mydb.crud("insert into test(id,address) value ('%d','%s')" % (12,'LOKing'))
        insert_data = [
            [98, '张三1'],
            [100, '李四1'],
            [27, '王五1']
        ]
        sql = "INSERT INTO test(age,address) VALUES (%s,%s)"

        # print('insert_data:',insert_data)
        mydb.execMany(sql, insert_data)
        # sql = 'INSERT INTO test(age, address) VALUES (%d, %s)'

        # mydb.execMany(sql, insert_dat
        #         # for item in res:a)
        #     print("name:%s,age:%s" % (item[0], item[1]))
    except Exception:
        print("出错啦:" + mydb.Error)
    finally:
        mydb.closeconn()