pymysql 字典动态对数据库的操作

#!/usr/bin/python
#coding:utf-8

import time
import hashlib
import random
import os
import sys
import configparser

import pymysql
import platform

class Mysql():

    def __init__(self):
        self.conf = set_default()
        self.host = self.conf['host']
        self.port = self.conf['port']
        self.user = self.conf['user']
        self.pwd = self.conf['pwd']
        self.db = self.conf['db']
        self.table = None
        self._conn()
        self.cursor = self.table.cursor(cursor=pymysql.cursors.DictCursor)

    def _conn(self):
        try:
            self.table = pymysql.connect(host=self.host, user=self.user, password=self.pwd, port=self.port
                                         , db=self.db, charset='utf8')
            return True
        except:
            return False

    def _reConn(self, num=28800, stime=3):
        '''
        解决myslq 的持久链接问题
        :param num:
        :param stime:
        :return:
        '''
        _number = 0
        _status = True
        while _status and _number <= num:
            try:
                self.table.ping()    # 校验链接是否异常
                _status = False
            except:
                if self.table == True:    # 重新连接，并退出
                    _status = False
                    break
                _number += 1
                time.sleep(stime)    # 链接不成功，休眠3s，继续循环，直到成功或3次结束

    def insert(self, table, item):
        self._reConn()
        keys = ', '.join(item.keys())
        values = ', '.join(['%s'] * len(item))
        sql = 'insert into {table}({keys}) values({values})'.format(table=table, keys=keys, values=values)
        try:
            self.cursor.execute(sql, tuple(item.values()))
            self.table.commit()
            # print('插入成功')
        except Exception as e:
            print('插入失败, 失败表名：%s' % table)
            print('失败原因：%s' % e)
            self.table.rollback()
        finally:
            self.table.close()

    def add_column(self, table, column, column_type):
        self._reConn()
        sql = 'alter table %s add %s %s;' % (table, column, column_type)
        try:
            self.cursor.execute(sql)
            self.table.commit()
        except Exception as e:
            print(e)
            print('新增字段失败')
            self.table.rollback()
        finally:
            self.table.close()


    def update(self, table, key1, value1, key2, value2):
        self._reConn()
        sql = 'update %s set %s="%s" where %s="%s"' % (table, key1, value1, key2, value2)
        try:
            # print(sql)
            self.cursor.execute(sql)
            self.table.commit()
            # print('更新成功')
        except Exception as e:
            print('更新失败')
            print(sql)
            print(e)
            self.table.rollback()
        finally:
            self.table.close()

    def update_m(self, table, key1, value1, key2, value2, key3, value3):
        self._reConn()
        sql = 'update %s set %s="%s" where %s="%s" and %s="%s"' % (table, key1, value1, key2, value2, key3, value3)
        try:
            # # print(sql)
            self.cursor.execute(sql)
            self.table.commit()
            # print('更新成功')
        except Exception as e:
            print('更新失败')
            print(e)
            self.table.rollback()
        finally:
            self.table.close()

    def update_customer(self, sql):
        self._reConn()
        try:
            self.cursor.execute(sql)
            self.table.commit()
            # print('更新成功')
        except Exception as e:
            print('更新失败')
            print(e)
            self.table.rollback()
        finally:
            self.table.close()

    def update_m_three(self, table, key1, value1, key2, value2, key3, value3, key4, value4):
        self._reConn()
        sql = 'update %s set %s="%s" where %s="%s" and %s="%s" and %s="%s"' % (table, key1, value1, key2, value2, key3, value3, key4, value4)
        try:
            # # print(sql)
            self.cursor.execute(sql)
            self.table.commit()
            # # print('更新成功')
        except Exception as e:
            print('更新失败')
            print(e)
            self.table.rollback()
        finally:
            self.table.close()

    def select_one_with_two(self, column, table, key, value, key2, value2):
        self._reConn()
        sql = 'select %s from %s where %s="%s" and %s="%s"' % (column, table, key, value, key2, value2)
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            return data
        except Exception as e:
            print('查询失败')
        finally:
            self.table.close()

    def select_one(self, column, table, key, value):
        self._reConn()
        sql = 'select %s from %s where %s="%s"' %(column, table, key, value)
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            return data
        except Exception as e:
            print('查询失败')
        finally:
            self.table.close()
    def select_one_with_three(self, column, table, key1, value1, key2, value2, key3, value3):
        self._reConn()
        sql = 'select %s from %s where %s="%s" and %s="%s" and %s="%s"' %(column, table, key1, value1, key2, value2, key3, value3)
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchone()
            return data
        except Exception as e:
            print('查询失败')
        finally:
            self.table.close()


    def select_camera_plate_no(self, column, table, key=None, value=None, number=1):
        self._reConn()
        data = ''
        if value is None:
            sql = 'select %s from %s'%(column, table)
        elif value == 'null':
            sql = 'select %s from %s where %s is %s' %(column, table, key, value)
        else:
            sql = 'select %s from %s where %s = "%s"' % (column, table, key, value)
        # sql = 'select %s from %s where %s = "%s"' % (column, table, key, value)  # 测试
        try:
            self.cursor.execute(sql)
            if number == 1:
                data = self.cursor.fetchone()
            else:
                data = self.cursor.fetchall()
            return data
        except Exception as e:
            print(e)
            print('查询失败')
        finally:
            self.table.close()

    def select_many_with_two(self, column, table, key1, value1, key2, value2):
        '''
        查询多个结果，并指定两个条件
        :param column:
        :param table:
        :param key:
        :param value:
        :return:
        '''
        self._reConn()
        sql = 'select %s from %s where %s="%s" and %s="%s"' % (column, table, key1, value1, key2, value2)
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except Exception as e:
            print('查询失败')
        finally:
            self.table.close()

    def select_many(self, column, table, key, value):
        self._reConn()
        sql = 'select %s from %s where %s="%s"' % (column, table, key, value)
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except Exception as e:
            print('查询失败')
        finally:
            self.table.close()

    def delete_item(self, table, key1, value1, key2='', value2=''):
        self._reConn()
        if key2 == '':
            sql = 'delete from %s where %s="%s"' % (table, key1, value1)
            if value1 == 'null':
                sql = 'delete from %s where %s is %s' % (table, key1, value1)
        else:
            sql = 'delete from %s where %s="%s" and %s="%s"' % (table, key1, value1, key2, value2)
        try:
            self.cursor.execute(sql)
            self.table.commit()
        except Exception as e:
            print(e)
            print('删除失败')
            self.table.rollback()
        finally:
            self.table.close()