MySQLdb的操作类

 win10/64位 + python3.6   （https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient） 

 

#-*- coding=utf-8 -*-
import MySQLdb
from MySQLdb import cursors
import sys
class PMySQL():
    #初始化，链接数据库并设置出字符集，并且获取游标
    def __init__(self):
        try:
            self.db = MySQLdb.connect('localhost','root','123456','demodb',cursorclass=cursors.DictCursor,charset='utf8')
            if self.db :
                self.cursor = self.db.cursor()
        except Exception as e:
            print(e)
            sys.exit(1)

    #查询方法
    def select(self,sql):
        data_list = []
        try:
            self.cursor.execute(sql)
            self.db.commit()
            data = self.cursor.fetchall()
            if data is not None:
                for i in data:
                    data_list.append(i)
            return data_list
        except Exception as e:
            return data_list
    #插入方法
    def insert(self,sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return self.cursor.lastrowid
        except Exception as e:
            self.db.rollback()
            return 0

    #更新方法
    def update(self,sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return self.cursor.rowcount
        except Exception as e:
            self.db.rollback()
            return 0

    #删除方法
    def delete(self,sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return self.cursor.rowcount()
        except Exception as e:
            self.db.rollback()
            return 0
    #批量插入
    def insert_batch(self,table,data):
        try:
            if data:
                f = '' #顺序获取的字段名称
                info = ''
                for d in data:
                   list2 = []
                   list3 = []
                   for k,v in d.items():
                       list2.append(k)
                       list3.append(v)
                   f = ','.join(list2)
                   info += str(tuple(list3))+','
                datainfo = info.rstrip(',')
                sql = 'insert into {}({}) values {}'.format(table,f,datainfo)
                self.cursor.execute(sql)
                self.db.commit()
                return self.cursor.rowcount
            else:
                print('数据为空')
                sys.exit(1)
        except:
            self.db.rollback
            return 0

    def __del__(self):
        try:
            if self.db:
                self.db.close()
        except Exception as e:
            print(e)
            sys.exit(1)

if __name__ == '__main__':
    table = 'user'
    list1 = [{"name":"zhangsan","pid":0,"path":"0,"},{"name":"zhangsan","pid":0,"path":"0,"},{"name":"zhangsan","pid":0,"path":"0,"}]
    p = PMySQL()
    t = p.insert_batch(table,list1)
    print(t)