CT_project/入口液晶显示屏/mysqlhelper.py

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# author:SunXiuWen
# make_time:2019/1/13
import pymysql
from DBUtils.PooledDB import PooledDB
import db_config as config

"""
@功能：创建数据库连接池
"""

class MyConnectionPool(object):
    __pool = None

    # 创建数据库连接conn和游标cursor
    def __enter__(self):
        self.conn = self.__getconn()
        self.cursor = self.conn.cursor()

    # 创建数据库连接池
    def __getconn(self):
        if self.__pool is None:
            self.__pool = PooledDB(
                creator=config.DB_CREATOR,
                mincached=config.DB_MIN_CACHED,
                maxcached=config.DB_MAX_CACHED,
                maxshared=config.DB_MAX_SHARED,
                maxconnections=config.DB_MAX_CONNECYIONS,
                blocking=config.DB_BLOCKING,
                maxusage=config.DB_MAX_USAGE,
                setsession=config.DB_SET_SESSION,
                host=config.DB_TEST_HOST,
                port=config.DB_TEST_PORT,
                user=config.DB_TEST_USER,
                passwd=config.DB_TEST_PASSWORD,
                db=config.DB_TEST_DBNAME,
                use_unicode=True,
                charset=config.DB_CHARSET
            )
        return self.__pool.connection()

    # 释放连接池资源
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    # 从连接池中取出一个连接
    def getconn(self):
        conn = self.__getconn()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        return cursor, conn


"""执行语句查询有结果返回结果没有返回0；增/删/改返回变更数据条数，没有返回0"""



class MySqLHelper(object):
    def __init__(self):
        self.db = MyConnectionPool()  # 从数据池中获取连接

    def __new__(cls, *args, **kwargs):
        if not hasattr(cls, 'inst'):  # 单例
            cls.inst = super(MySqLHelper, cls).__new__(cls, *args, **kwargs)
        return cls.inst

    # 封装执行命令
    def execute(self, sql, param=None, autoclose=False):
        """
        【主要判断是否有参数和是否执行完就释放连接】
        :param sql: 字符串类型，sql语句
        :param param: sql语句中要替换的参数"select %s from tab where id=%s" 其中的%s就是参数
        :param autoclose: 是否关闭连接
        :return: 返回连接conn和游标cursor
        """
        cursor, conn = self.db.getconn()  # 从连接池获取连接
        count = 0
        try:
            # count : 为改变的数据条数
            if param:
                count = cursor.execute(sql, param)
            else:
                count = cursor.execute(sql)
            conn.commit()
            if autoclose:
                self.close(cursor, conn)
        except Exception as e:
            print("db error_msg:", e.args)
        return cursor, conn, count

    # 释放连接
    def close(self, cursor, conn):
        """释放连接归还给连接池"""
        cursor.close()
        conn.close()

    # 查询所有
    def selectall(self, sql, param=None):
        try:
            cursor, conn, count = self.execute(sql, param)
            res = cursor.fetchall()
            return res
        except Exception as e:
            print("db error_msg:", e.args)
            self.close(cursor, conn)
            return count

    # 查询单条
    def selectone(self, sql, param=None):
        try:
            cursor, conn, count = self.execute(sql, param)
            res = cursor.fetchone()
            self.close(cursor, conn)
            return res
        except Exception as e:
            print("db error_msg:", e.args)
            self.close(cursor, conn)
            return count

    # 增加
    def insertone(self, sql, param):
        try:
            cursor, conn, count = self.execute(sql, param)
            conn.commit()
            self.close(cursor, conn)
            return count
        except Exception as e:
            print("db error_msg:", e.args)
            conn.rollback()
            self.close(cursor, conn)
            return count

    # 删除
    def delete(self, sql, param=None):
        try:
            cursor, conn, count = self.execute(sql, param)
            conn.commit()
            self.close(cursor, conn)
            return count
        except Exception as e:
            print("db error_msg:", e.args)
            conn.rollback()
            self.close(cursor, conn)
            return count

    # 修改
    def update(self, sql, param=None):
        try:
            cursor, conn, count = self.execute(sql, param)
            conn.commit()
            self.close(cursor, conn)
            return count
        except Exception as e:
            print("db error_msg:", e.args)
            conn.rollback()
            self.close(cursor, conn)
            return count


if __name__ == '__main__':
    db = MySqLHelper()
    while True:
        sql1 = 'select * from space where unit=%s'
        args = '2'
        ret = db.selectone(sql=sql1, param=args)
        print(ret)  # (79, 2, 1, 2.2, None, 2, 1)
    # 查询单条
    sql1 = 'select * from space where unit=%s'
    args = '2'
    ret = db.selectone(sql=sql1, param=args)
    print(ret)  # (79, 2, 1, 2.2, None, 2, 1)
    #查询所有
    sql2 = "select * from space"
    ret = db.selectall(sql=sql2)
    print(ret)
    # 增加
    sql3 = 'insert into vehicle (car_number,primary_key) VALUES (%s,%s)'
    ret = db.insertone(sql3, ('鄂A6X3B0','DASDASDEFDFSDASDADASDAS'))
    print(ret)
    # 删除
    sql4 = 'delete from vehicle WHERE car_number=%s'
    args = '鄂A6X3B0'
    ret = db.delete(sql4, args)
    print(ret)
    # 修改
    sql5 = 'update command_queue set export_id=%s WHERE car_number LIKE %s'
    args = ('100', 'WK0001')
    ret = db.update(sql5, args)
    print(ret)