Python操作中间件脚手架

参考项目结构以及目录

操作MySQL

• con_mysql.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

# pip3 install mysql-connector -i https://pypi.tuna.tsinghua.edu.cn/simple

import mysql.connector
from mysql.connector import pooling
from typing import List, Dict
import json
from datetime import datetime


"""
获取数据库链接
"""
def CreateMysqlPool(conf: Dict[str, str]) -> pooling.MySQLConnectionPool:
    # 配置信息
    pool = None
    try:
        # 创建连接池
        pool = pooling.MySQLConnectionPool(
            pool_name="my-python-pool",
            pool_size=5,  # 连接池大小
            **conf
        )
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    return pool

def GetMysqlConnect(pool: pooling.MySQLConnectionPool) -> mysql.connector.connection.MySQLConnection:
    mydbConnect = pool.get_connection()
    print('获取数据库链接信息:', mydbConnect)
    return mydbConnect

def getDbInfos(pool: pooling.MySQLConnectionPool) -> List[Dict[str, any]]:
    print("=========SHOW DATABASES==========")
    connect = GetMysqlConnect(pool)
    cursor = connect.cursor(dictionary=True)  # 使用字典游标
    result = []
    try:
        cursor.execute("SHOW DATABASES")
        result = cursor.fetchall()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # 关闭游标和连接
        cursor.close()
        connect.close()
    return result


def ExecuteSQL(pool: pooling.MySQLConnectionPool, query: str) -> List[Dict[str, any]]:
    connect = GetMysqlConnect(pool)
    cursor = connect.cursor(dictionary=True)  # 使用字典游标
    result = []
    try:
        cursor.execute(query)
        result = cursor.fetchall()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        # 关闭游标和连接
        cursor.close()
        connect.close()
    return result

class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.isoformat()
        return json.JSONEncoder.default(self, obj)
    
# 示例查询
if __name__ == "__main__":
    config = {
        'host': 'localhost',  # 数据库主机地址
        'user': 'root',       # 数据库用户名
        'password': 'rootroot',   # 数据库密码
        'database': 'test',  # 数据库名称（可选）
        'port': 3306,         # 数据库端口（可选，默认是 3306）
        'charset': 'utf8mb4',  # 字符集（可选）
        'auth_plugin': 'mysql_native_password'  # 指定认证插件
    }

    pool = CreateMysqlPool(config)
    query = "SELECT * FROM enterprise_info limit 10"
    result = ExecuteSQL(pool, query)

    # 处理查询结果
    if result:
        for row in result:
            print(json.dumps(row, indent=4, ensure_ascii=False, cls=DateTimeEncoder))

    result = getDbInfos(pool)      
    # 处理查询结果
    if result:
        for row in result:
            print(json.dumps(row, indent=4, ensure_ascii=False, cls=DateTimeEncoder))   

• PyMySQ.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

# pip3 install PyMySQL -i https://pypi.tuna.tsinghua.edu.cn/simple
# pip3 install pymysql DBUtils -i https://pypi.tuna.tsinghua.edu.cn/simple

import pymysql
# 从 DBUtils 2.0 版本开始，导入方式发生了变化。您应该使用以下方式导入 PooledDB：
from dbutils.pooled_db import PooledDB
import json
from typing import List, Dict
from datetime import datetime



"""
创建连接池
"""
def create_pool(config: Dict[str, str]) -> PooledDB:
    # 创建连接池
    pool = PooledDB(
        creator=pymysql,
        maxconnections=5,  # 连接池大小
        **config
    )
    print('获取数据库链接池信息:', pool)
    return pool

"""
从连接池中获取连接
"""
def GetMysqlConnect(pool: PooledDB):
    # 从连接池中获取连接
    connection = pool.connection()
    print('获取数据库链接信息:', connection)
    return connection

"""
获取数据库信息
"""
def getDbInfos(pool: PooledDB) -> List[Dict[str, any]]:
    connection = GetMysqlConnect(pool)
    cursor = connection.cursor(pymysql.cursors.DictCursor)  # 使用字典游标
    result = []
    try:
        print("=========SHOW DATABASES==========")
        cursor.execute("SHOW DATABASES")
        result = cursor.fetchall()
    except pymysql.MySQLError as err:
        print(f"Error: {err}")
    finally:
        # 关闭游标
        cursor.close()
        connection.close()
    return result

"""
执行查询
"""
def execute_query(pool: PooledDB, query: str) -> List[Dict[str, any]]:
    connection = GetMysqlConnect(pool)
    cursor = connection.cursor(pymysql.cursors.DictCursor)  # 使用字典游标
    result = []
    try:
        cursor.execute(query)
        result = cursor.fetchall()
    except pymysql.MySQLError as err:
        print(f"Error: {err}")
    finally:
        # 关闭游标
        cursor.close()
        connection.close()
    return result

class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.isoformat()
        return json.JSONEncoder.default(self, obj)
    

# 示例查询
if __name__ == "__main__":
    # 配置信息
    config = {
        'host': 'localhost',
        'user': 'root',
        'password': 'rootroot',
        'database': 'test',
        'port': 3306,
        'charset': 'utf8mb4'
    }

    # 创建连接池
    pool = create_pool(config)

    # 获取连接
    connection = GetMysqlConnect(pool)


    # 执行查询
    query = "SELECT * FROM enterprise_info limit 3"
    result = execute_query(pool, query)

    # 处理查询结果
    if result:
        for row in result:
             print(json.dumps(row, indent=4, ensure_ascii=False, cls=DateTimeEncoder))   

    result = getDbInfos(pool)      
    # 处理查询结果
    if result:
        for row in result:
            print(json.dumps(row, indent=4, ensure_ascii=False, cls=DateTimeEncoder))   

操作redis

• redis_con.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

# pip3 install redis -i https://pypi.tuna.tsinghua.edu.cn/simple
# pip3 install redis redis-py-cluster -i https://pypi.tuna.tsinghua.edu.cn/simple

import redis
from redis.sentinel import Sentinel
from rediscluster import RedisCluster
import time
import json
from typing import Dict, List, Any, Union, Optional, Tuple, Type, TypeVar, Callable,MappingView, ItemsView, KeysView

"""
创建 Redis 连接池
"""
def CreateRedisPool(config: Dict[str, any]) -> redis.ConnectionPool:
    # 创建连接池
    try:
        # 创建连接池
        pool = redis.ConnectionPool(
            host=config['host'],
            port=int(config['port']),  
            db=config['db'],
            password=config['password'],
            decode_responses=config['decode_responses'],
            max_connections=config['max_connections'],
            socket_timeout=config['socket_timeout'],
            socket_connect_timeout=config['socket_connect_timeout'],
            retry_on_timeout=config['retry_on_timeout']
        )
        return pool
    except Exception as e:
        print(f"Error creating Redis connection pool: {e}")
        raise

"""
创建哨兵连接池
"""
def create_sentinel_pool(config: Dict[str, any]) -> redis.StrictRedis:
    # 创建哨兵连接池
    try:
        # 创建哨兵连接池
        sentinel = Sentinel([(config['host'], config['port'])], socket_timeout=config['socket_timeout'])
        r = sentinel.master_for(config['service_name'], socket_timeout=config['socket_timeout'], password=config['password'], decode_responses=config['decode_responses'])
        return r
    except Exception as e:
        print(f"Error creating Redis sentinel connection: {e}")
        raise

"""
创建集群连接池
"""
def create_cluster_pool(config: Dict[str, any]) -> RedisCluster:
    # 创建集群连接池
    try:
        # 创建集群连接池
        startup_nodes = [{"host": config['host'], "port": config['port']}]
        r = RedisCluster(startup_nodes=startup_nodes, decode_responses=config['decode_responses'], password=config['password'])
        return r
    except Exception as e:
        print(f"Error creating Redis cluster connection: {e}")
        raise

"""
创建主从连接池
"""
def create_master_slave_pool(config: Dict[str, any]) -> redis.ConnectionPool:
    try:
        # 创建主从连接池
        pool = redis.ConnectionPool(
            host=config['host'],
            port=config['port'],
            db=config['db'],
            password=config['password'],
            decode_responses=config['decode_responses'],
            max_connections=config['max_connections'],
            socket_timeout=config['socket_timeout'],
            socket_connect_timeout=config['socket_connect_timeout'],
            retry_on_timeout=config['retry_on_timeout'],
        )
        return pool
    except Exception as e:
        print(f"Error creating Redis connection pool: {e}")
        raise

"""
获取 Redis 连接
"""
def GetRedisConnection(pool: redis.ConnectionPool) -> redis.Redis:
    # 从连接池中获取连接
    try:
        # 从连接池中获取连接
        r = redis.Redis(connection_pool=pool)
        print('获取 Redis 连接:', r)
        return r
    except Exception as e:
        print(f"Error getting Redis connection: {e}")
        raise

def example_operation(r: redis.Redis):
    # 示例操作
    try:
        # 示例操作
        r.set('foo', 'bar')
        value = r.get('foo')
        print('获取到的值:', value)
    except Exception as e:
        print(f"Error during Redis operation: {e}")
    finally:
        r.close()

"""
设置键值对，设置键值对，并指定 TTL 为 60 秒 setDict(r, key, value, ttl=60)
"""
def SetStrDict(r: redis.Redis, key: str, value: Dict, ttl: int = None):
    try:
        # 将字典数据进行 JSON 序列化
        value_json = json.dumps(value)
        # 设置键值对，并指定 TTL
        if ttl is not None:
            r.set(key, value_json, ex=ttl)
            print(f"设置键值对: {key} -> {value_json}，TTL: {ttl}")
        else:
            r.set(key, value_json)
            print(f"设置键值对: {key} -> {value_json}")
    except Exception as e:
        print(f"Error setting key-value in Redis: {e}")


"""
设置键值对，设置键值对，并指定 TTL 为 60 秒 setDict(r, key, value, ttl=60)
"""
def SetStr(r: redis.Redis, key: str, value: str, ttl: int = None):
    try:
        # 设置键值对，并指定 TTL
        if ttl is not None:
            r.set(key, value, ex=ttl)
            print(f"设置键值对: {key} -> {value}，TTL: {ttl}")
        else:
            r.set(key, value)
            print(f"设置键值对: {key} -> {value}")
    except Exception as e:
        print(f"Error setting key-value in Redis: {e}")

""" 
获取键值对
"""
def GetStrDict(r: redis.Redis, key: str) -> Dict:
    try:
        # 获取键值对
        value_json = r.get(key)
        if value_json:
            # 将 JSON 字符串反序列化为字典
            value = json.loads(value_json)
            # print(f"获取到的值: {key} -> {value}")
            return value
        else:
            print(f"键 {key} 不存在")
            return {}
    except Exception as e:
        print(f"Error getting key-value from Redis: {e}")
        return {}

""" 
获取键值对
"""
def GetStr(r: redis.Redis, key: str) -> str:
    try:
        # 获取键值对
        value = r.get(key)
        if value:
            print(f"获取到的值: {key} -> {value}")
            return value
        else:
            print(f"键 {key} 不存在")
            return {}
    except Exception as e:
        print(f"Error getting key-value from Redis: {e}")
        return {}

def KeyExists(r: redis.Redis, key: str) -> bool:
    try:
        exists = r.exists(key)
        if exists:
            print(f"键 {key} 存在")
        else:
            print(f"键 {key} 不存在")
        return exists
    except Exception as e:
        print(f"Error checking if key exists in Redis: {e}")
        return False

if __name__ == "__main__":
    # 配置信息
    config = {
        'host': 'localhost',
        'port': 6380,
        'db': 10,
        'password': 'KYUIAPErZz2Xb0f8XIQOKfuv7dYURu',
        'decode_responses': True,
        'max_connections': 10,  # 指定线程池大小
        'socket_timeout': 5,  # 套接字超时时间
        'socket_connect_timeout': 5,  # 连接超时时间
        'retry_on_timeout': True,  # 超时重试
        'service_name': 'mymaster',
    }

    try:
        # 创建连接池
        pool = CreateRedisPool(config)

        # 获取 Redis 连接
        r = GetRedisConnection(pool)

        # 执行示例操作
        example_operation(r)

         # 获取 Redis 连接
        r = GetRedisConnection(pool)

        # 执行示例操作
        example_operation(r)

        example_operation(r)
        example_operation(r)
        example_operation(r)
        example_operation(r)

        # 设置键值对
        key = "my_key"
        value = {"name": "Alice", "age": 30, "city": "New York"}
        SetStrDict(r, key, value)

        SetStrDict(r, key+"_ttl", value, ttl=60)

        # 获取并打印设置的值
        retrieved_value = GetStrDict(r, key)
        print(f"获取到的值: {retrieved_value}")

        SetStr(r, "Hello", "Hello, Redis!")
        print( GetStr(r, "Hello") )

        flag = KeyExists(r, "Hello")
        if flag:
            print("Key exists")


    except Exception as e:
        print(f"Error in main execution: {e}")

读取yaml

• yaml.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import yaml

def GetYaml(yaml_path: str) -> dict:
    print('----GetYaml----') 
    # 打开 YAML 文件并读取内容
    with open(yaml_path, 'r') as file:
        config = yaml.safe_load(file)
    return config

主函数引用并启动服务

• main.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sys
import os
import json
import requests
import json
import time
import random  
import pandas as pd
from datetime import datetime,timezone
import yaml
import logging
import redis

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from pkg.mysql.con_mysql import CreateMysqlPool,GetMysqlConnect,ExecuteSQL
from pkg.redis.redis_con import CreateRedisPool,GetRedisConnection,SetStrDict,GetStrDict,KeyExists,SetStr,GetStr
from pkg.yaml.yaml import GetYaml


# 配置日志记录
log_file_path = '/Users/wgg/Desktop/Python/logs/handler_all_data.log'
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler(log_file_path),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger(__name__)


"""
初始化redis连接池
"""
def initRedisPool(config : dict) ->  redis.ConnectionPool:
    try:
        redis_pool = CreateRedisPool(config)
        #redis_conn = GetRedisConnection(redis_pool)
        return redis_pool
    except Exception as e:
        logger.error(f"Error getting Redis connection: {e}")
        raise




"""
业务处理忽略，定缓存成字典，放入redis
"""
def transform(config : dict, redis_pool :redis.ConnectionPool) -> None:
# 业务处理忽略
# 业务处理忽略

#  main 函数
if __name__ == "__main__":
    try:
        config = GetYaml('/Users/wgg/Desktop/Pythonconfig/configV2.yaml')
        #print("获取家谱地" + json.dumps(config))
        logger.info("获取mysql:\n")
        logger.info(json.dumps(config['mysql'], indent=4, ensure_ascii=False, sort_keys=False))
        logger.info("获取redis:\n")
        logger.info(json.dumps(config['redis'], indent=4, ensure_ascii=False, sort_keys=False))

        #pool = CreateRedisPool(config['redis'])
        #print("获取家谱地" + json.dumps(config))

        transform(config=config, redis_pool=initRedisPool(config['redis']))


  
    except Exception as e:
       logger.error('异常信息:', exc_info=True)

• yaml配置信息

mysql:
  host: 'localhost'  # 数据库主机地址
  user: 'root'       # 数据库用户名
  password: 'rootroot'   # 数据库密码
  database: 'test'  # 数据库名称（可选）
  port: 3306         # 数据库端口（可选，默认是 3306）
  charset: 'utf8mb4'  # 字符集（可选）
  auth_plugin: 'mysql_native_password'  # 指定认证插件

redis:
  host: 'localhost'
  port: 6380
  db: 10
  password: 'KYUIAPErZz2Xb0f8XIQOKfuv7dYURu'
  decode_responses: True
  max_connections: 10 # 指定线程池大小
  socket_timeout: 5  # 套接字超时时间
  socket_connect_timeout: 5  # 连接超时时间
  retry_on_timeout: True  # 超时重试
  service_name: 'mymaster'  # 服务名