一、Mysql操作工具类
直接上代码，测试正常
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import logging
import os
from datetime import datetime
class MySQLTool:
def __init__(self, host, port, user, password, database):
"""
初始化MySQL连接
:param host: 数据库主机地址
:param port: 数据库端口
:param user: 数据库用户名
:param password: 数据库密码
:param database: 数据库名称
"""
self.host = host
self.port = port
self.user = user
self.password = password
self.database = database
# 创建数据库连接
self.engine = create_engine(
f"mysql+pymysql://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}"
)
# 初始化日志
self._init_logging()
def _init_logging(self):
"""
初始化日志配置
"""
# 创建日志目录
log_dir = os.path.join(os.getcwd(), "../log")
if not os.path.exists(log_dir):
os.makedirs(log_dir)
# 按日期归类日志文件
today = datetime.now().strftime("%Y-%m-%d")
log_file = os.path.join(log_dir, f"{today}.log")
# 配置日志
logging.basicConfig(
level=logging.INFO,
format="%(asctime)s - %(name)s - %(levelname)s - %(message)s",
handlers=[
logging.FileHandler(log_file,encoding='UTF-8'), # 输出到文件
logging.StreamHandler() # 输出到控制台
]
)
self.logger = logging.getLogger(self.__class__.__name__)
def write_dataframe_to_table(self, dataframe, table_name, if_exists='append', index=False):
"""
将DataFrame写入MySQL数据库表
:param dataframe: Pandas DataFrame
:param table_name: 目标表名
:param if_exists: 如果表存在时的操作，可选值为 'fail', 'replace', 'append'，默认为 'append'
:param index: 是否将DataFrame的索引写入数据库，默认为 False
"""
try:
with self.engine.connect() as connection:
dataframe.to_sql(table_name, con=connection, if_exists=if_exists, index=index)
self.logger.info(f"数据成功写入表: {table_name}")
except SQLAlchemyError as e:
self.logger.error(f"写入数据时发生错误: {e}")
def execute_query(self, query, params=None):
"""
执行SQL查询并返回结果
:param query: SQL查询语句
:param params: 查询参数（可选）
:return: 查询结果（列表形式）
"""
try:
with self.engine.connect() as connection:
result = connection.execute(text(query), params) if params else connection.execute(text(query))
self.logger.info(f"执行查询成功: {query}")
return result.fetchall()
except SQLAlchemyError as e:
self.logger.error(f"执行查询时发生错误: {e}")
return []
def execute_update(self, query, params=None):
"""
执行SQL更新操作（如INSERT、UPDATE、DELETE）
:param query: SQL语句
:param params: 参数（可选）
:return: 受影响的行数
"""
try:
with self.engine.connect() as connection:
result = connection.execute(text(query), params) if params else connection.execute(text(query))
connection.commit()
self.logger.info(f"执行更新成功: {query}")
return result.rowcount
except SQLAlchemyError as e:
self.logger.error(f"执行更新时发生错误: {e}")
return 0
def close(self):
"""
关闭数据库连接
"""
self.engine.dispose()
self.logger.info("数据库连接已关闭")
# 示例用法
if __name__ == "__main__":
# 初始化MySQL工具类
mysql_tool = MySQLTool(
host="localhost",
port=3306,
user="root",
password="root",
database="data"
)
# 示例DataFrame
data = {
"id": [1, 2, 3],
"name": ["Alice", "Bob", "Charlie"],
"age": [25, 30, 35]
}
df = pd.DataFrame(data)
# 将DataFrame写入数据库表
mysql_tool.write_dataframe_to_table(df, table_name="users", if_exists="replace")
# 查询数据
query_result = mysql_tool.execute_query("SELECT * FROM users")
print("查询结果:", query_result)
# 插入数据
insert_query = "INSERT INTO users (id, name, age) VALUES (:id, :name, :age)"
insert_params = {"id": 4, "name": "David", "age": 40}
affected_rows = mysql_tool.execute_update(insert_query, insert_params)
print(f"插入了 {affected_rows} 行数据")
# 更新数据
update_query = "UPDATE users SET age = :age WHERE name = :name"
update_params = {"age": 45, "name": "Alice"}
affected_rows = mysql_tool.execute_update(update_query, update_params)
print(f"更新了 {affected_rows} 行数据")
# 删除数据
delete_query = "DELETE FROM users WHERE name = :name"
delete_params = {"name": "Bob"}
affected_rows = mysql_tool.execute_update(delete_query, delete_params)
print(f"删除了 {affected_rows} 行数据")
# 关闭连接
mysql_tool.close()
控制台文件：
日志文件：
二、文件工具类
#!/user/bin/env python3
# -*- coding: utf-8 -*-
import os
class FileTool:
def __init__(self, base_dir="../data"):
self.base_dir = base_dir
# 确保基础目录存在
if not os.path.exists(self.base_dir):
os.makedirs(self.base_dir)
def write_file(self, file_type, file_name, content):
# 构建子目录路径
sub_dir = os.path.join(self.base_dir, file_type)
# 确保子目录存在
if not os.path.exists(sub_dir):
os.makedirs(sub_dir)
# 构建文件路径
file_path = os.path.join(sub_dir, file_name)
# 写入文件内容
with open(file_path, 'w',encoding='utf-8') as file:
file.write(content)
print(f"文件已写入: {file_path}")
# 示例用法
if __name__ == "__main__":
tool = FileTool()
# # 写入文本文件
# tool.write_file("txt", "example.txt", "这是一个文本文件的内容。")
#
# # 写入JSON文件
# tool.write_file("json", "example.json", '{"key": "value"}')
# 写入CSV文件
tool.write_file("csv", "example.csv", "name,age\nAlice,30\nBob,25")
