#!/opt/homebrew/bin/python3 # -*- coding:utf-8 -*- from sqlalchemy import Column, Integer, String, DateTime, create_engine from sqlalchemy.orm import sessionmaker, declarative_base from concurrent.futures import ThreadPoolExecutor from datetime import datetime, timedelta import os import json import uuid dataDB = os.path.join(os.path.dirname(__file__),'data.db') engine = create_engine(f'sqlite:///{dataDB}') SessionMaker = sessionmaker(bind=engine)() Base = declarative_base() class Product(Base): __tablename__ = 'product' id = Column(String, primary_key=True, doc='产品ID') info = Column(String, nullable=False, doc='产品详情') class History(Base): __tablename__ = 'history' id = Column(String, primary_key=True, doc='历史ID') name = Column(String, nullable=False, doc='历史名称') info = Column(String, nullable=False, doc='历史详情') datetime = Column(DateTime, nullable=False, doc='历史时间') class Session(): def __init__(self): super().__init__() self.executor = ThreadPoolExecutor() def addByClass(self, cls, _data): try: if not _data: return False, None data = cls() for k, v in _data.items(): if k == '_sa_instance_state': continue setattr(data, k, v) SessionMaker.add(data) SessionMaker.commit() data.id return True, data.__dict__ except Exception as e: print(e) return False, None finally: SessionMaker.close() def queryByIds(self, cls, ids): try: if not cls or not ids: return False, None data = SessionMaker.query(cls).filter(cls.id.in_(ids)).all() if not data: return False, None result = [] for item in data: if '_sa_instance_state' in item.__dict__: del item.__dict__['_sa_instance_state'] result.append(item.__dict__) return True, result except Exception as e: return False, None def queryById(self, cls, _id): try: if not cls or not _id: return False, None data = SessionMaker.query(cls).get(_id) if not data: return False, None if '_sa_instance_state' in data.__dict__: del data.__dict__['_sa_instance_state'] return True, data.__dict__ except Exception as e: return False, None def queryByDateHour(self, cls, date_str, hour_str): try: print("date_str:",date_str,"hour_str:",hour_str) if not cls or not date_str or not hour_str: return False, None date = datetime.strptime(date_str, "%Y-%m-%d") hour = int(hour_str) start_datetime = datetime(date.year, date.month, date.day, hour, 0, 0) end_datetime = start_datetime + timedelta(hours=1) print("start_datetime:",start_datetime, "end_datetime:",end_datetime) data = SessionMaker.query(cls).filter( cls.datetime >= start_datetime, cls.datetime < end_datetime ).all() if not data: return False, None time_list = [item.datetime.strftime("%Y-%m-%d %H:%M:%S.%f") for item in data] return True, time_list except Exception as e: print(f"Error occurred: {e}") return False, None def queryByDatetime(self, cls, datetime_str): try: if not cls or not datetime_str: return False, None # 将字符串转换为datetime对象 try: # 支持包含毫秒的格式: 2025-03-14 17:48:50.992612 target_datetime = datetime.strptime(datetime_str, "%Y-%m-%d %H:%M:%S.%f") except ValueError: # 尝试不带毫秒的格式 try: target_datetime = datetime.strptime(datetime_str, "%Y-%m-%d %H:%M:%S") except ValueError: print(f"Invalid datetime format: {datetime_str}. Expected format: %Y-%m-%d %H:%M:%S or %Y-%m-%d %H:%M:%S.%f") return False, None # 精确匹配查询 data = SessionMaker.query(cls).filter(cls.datetime == target_datetime).first() if not data: return False, None # 处理查询结果 if '_sa_instance_state' in data.__dict__: del data.__dict__['_sa_instance_state'] return True, data.__dict__ except Exception as e: print(f"queryByDatetime error: {e}") return False, None def queryByTimestampRange(self, cls, minutes): try: if not cls or not minutes: return False, None # 验证分钟数参数 try: minutes_int = int(minutes) if minutes_int <= 0: print(f"Minutes must be positive: {minutes}") return False, None except ValueError: print(f"Invalid minutes format: {minutes}") return False, None # 获取当前时间 current_datetime = datetime.now() # 计算指定分钟数前的时间 minutes_ago = current_datetime - timedelta(minutes=minutes_int) # 查询时间范围内的数据 data = SessionMaker.query(cls).filter( cls.datetime >= minutes_ago, cls.datetime <= current_datetime ).all() if not data: return False, None # 格式化结果为数据列表 data_list = [] for item in data: # 删除SQLAlchemy实例状态属性 if '_sa_instance_state' in item.__dict__: del item.__dict__['_sa_instance_state'] data_list.append(item.__dict__) return True, data_list except Exception as e: print(f"queryByTimestampRange error: {e}") return False, None def queryByAll(self, cls): try: if not cls: return False, None dataAll = SessionMaker.query(cls).all() if dataAll is None: return False, None dataList = list() for data in dataAll: if '_sa_instance_state' in data.__dict__: del data.__dict__['_sa_instance_state'] dataList.append(data.__dict__) return True, dataList except Exception as e: return False, None def updateById(self, cls, _id, _data): try: if not cls or not _id or not _data: return False, None data = SessionMaker.query(cls).filter_by(id=_id).first() for k, v in _data.items(): if k == '_sa_instance_state': continue if k not in data.__dict__: continue if data.__dict__[k] == v: continue setattr(data, k, v) SessionMaker.commit() if "id" in _data: _data = SessionMaker.query(cls).get(_data["id"]) else: _data = SessionMaker.query(cls).get(_id) return True, data.__dict__ except Exception as e: return False, None finally: SessionMaker.close() def deleteById(self, cls, _id): try: if not _id: return False, None data = SessionMaker.query(cls).filter_by(id=_id).first() if not data: return False, None SessionMaker.delete(data) SessionMaker.commit() return True, _id except Exception as e: return False, None finally: SessionMaker.close() def deleteByIds(self, cls, ids): try: if not ids: return False, None data = SessionMaker.query(cls).filter(cls.id.in_(ids)).all() if not data: return False, None for item in data: SessionMaker.delete(item) SessionMaker.commit() return True, ids except Exception as e: return False, None finally: SessionMaker.close() def deleteAllTable(self): try: self.delete(Product) except Exception as e: return False def delete(self, cls): try: SessionMaker.query(cls).delete() SessionMaker.commit() return True except Exception as e: return False finally: SessionMaker.close() Session = Session()