老王的python笔记之pymysql

pymysql连接数据库的基础封装类。

class conndb:
    def __init__(self, dbinfo):
        self.db, self.user, self.pwd, self.host, port = dbinfo
        self.port = int(port)

    def __GetConnect(self):
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.pwd, database=self.db,
                                    charset="utf8", connect_timeout=30)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql.encode("utf8"))
        res = cur.fetchall()
        re = cur.description
        self.conn.close()
        a = []
        for b in re:
            a.append(b[0])
        c = [a, res]
        return c

    def ExecNonQuery(self, sql):
        cur = self.__GetConnect()
        res = cur.execute(sql.encode("utf8"))
        self.conn.commit()
        self.conn.close()
        return res

    def ExecInsert(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql.encode("utf8"))
        res = self.conn.insert_id()
        self.conn.commit()
        self.conn.close()
        return res

    def ManyExecNonQuery(self, sql, args):
        cur = self.__GetConnect()
        try:
            cur.executemany(sql.encode("utf8"), args)
            self.conn.commit()
        except Exception as e:
            print '执行sql:%s时出错：%s' % (sql, e)
        self.conn.close()

    def ExecQueryForPd(self,sql):
        conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.pwd, database=self.db,
                               charset="utf8", connect_timeout=30)
        df = pd.read_sql(sql, con=conn)
        conn.close()
        return df

使用数据库的方法

class laowangdb:
    def __init__(self, dbinfo):
        self.ms = conndb(dbinfo)
        self.dbinfo = dbinfo

    def query(self, sql):#查询
        return self.ms.ExecQuery(sql)

    def update(self, sql):#修改
        return self.ms.ExecNonQuery(sql)

    def insert(self, sql):#插入
        return self.ms.ExecInsert(sql)

    def manyupdate(self, sql, arg):#批量更新
        return self.ms.ManyExecNonQuery(sql, arg)

    def data2dict(self, res):#查询数据格式化，元组转字典
        dicts = []
        title, data = res
        if data:
            len_title = len(title)
            for dat in data:
                tem = {}
                for i in range(len_title):
                    tem[title[i]] = dat[i]
                dicts.append(tem)
            return dicts
        else:
            return {}

    def big_manyupdate(self, sql, arg):#大批量更新，使用多线程的形式
        per_block = 1000
        arg_list = [arg[i:i + per_block] for i in range(0, len(arg), per_block)]
        names = locals()
        for i, a in enumerate(arg_list):
            conn = laowangdb(self.dbinfo)
            names['t' + str(i)] = threading.Thread(target=conn.manyupdate, args=(sql, a))
            names['t' + str(i)].start()

    def big_manyupdate_wait(self, sql, arg):#大批量更新，阻塞线程
        per_block = 1000
        arg_list = [arg[i:i + per_block] for i in range(0, len(arg), per_block)]
        names = locals()
        for i, a in enumerate(arg_list):
            conn = laowangdb(self.dbinfo)
            names['t' + str(i)] = threading.Thread(target=conn.manyupdate, args=(sql, a))
            names['t' + str(i)].start()
        for i, a in enumerate(arg_list):
            names['t' + str(i)].join()

最终的调用：

dbinfo=['数据库名称','用户名','密码','服务器地址',端口号]

db=laowangdb(db_info)

sql='''select * from test;'''

res=db.query(sql)

print res