278 lines
9.4 KiB
Python
278 lines
9.4 KiB
Python
#!/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()
|