这个函数中设置日期的过滤条件，代码不起作用，什么也过滤不了咋回事啊
def oneweekUpdate(self):
today=datetime.datetime.today()
main_pattern = '%%Y-%%m-%%d'
oneweek=(today+datetime.timedelta(days=7))
self.model.setFilter("datetime.datetime.strptime(reminder, '%s')>= '%s' AND datetime.datetime.strptime('%s', main_pattern) <= '%s'" %(main_pattern,today,main_pattern,oneweek))
self.model.select()
这个括号里面的条件改怎么写那，试过很多都不行，reminder代表数据库中的提醒日期，比如2023-05-25：
” self.model.setFilter("datetime.datetime.strptime(reminder, '%s')>= '%s' AND datetime.datetime.strptime('%s', main_pattern) <= '%s'" %(main_pattern,today,main_pattern,oneweek))
from tkinter import messagebox
from tkinter import ttk
import tkinter as tk
import time,os,sys,sqlite3,subprocess
from PyQt5.QtCore import (QFile, QVariant, Qt)
from PyQt5.QtWidgets import *
from PyQt5.QtSql import (QSqlDatabase, QSqlQuery, QSqlTableModel)
from tkinter import *
from tkinter.ttk import *
from add_dialog import AddDialog
import csv,openpyxl,datetime,pr
from openpyxl import Workbook,load_workbook
import pandas as pd
from importlib import reload
from PyQt5.QtGui import QCursor
from PyQt5.QtCore import QDate
from PyQt5 import QtCore
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5 import QtGui
MAC = True
try:
from PyQt5.QtGui import qt_mac_set_native_menubar
except ImportError:
MAC = False
class SpinBoxDelegate(QStyledItemDelegate):
def __init__(self, parent=None, *args):
super(SpinBoxDelegate, self).__init__(parent, *args)
def paint(self, painter, option, index):
if index.column()==6 and index.data(Qt.DisplayRole ) in date_list_a:
color_list_a.append(index.row())
if index.column()==6 and index.data(Qt.DisplayRole ) in date_list_b:
color_list_b.append(index.row())
if index.row() in color_list_a:
red_flag = 'red_flag'
self.countryIcon = self.getCountry(red_flag)
p = QStyleOptionViewItem()
p.index = index
p.rect = option.rect
p.features = QStyleOptionViewItem.HasDecoration | QStyleOptionViewItem.HasDisplay
p.text = str(index.data())
p.font=QFont("Microsoft YaHei",10,QFont.Black)#QFont.Bold
#p.decorationSize = QSize(42,90) # 设置装饰图标的大小。
p.icon = self.countryIcon # 设置装饰图标路径名
p.state = option.state
p.showDecorationSelected = True # 开启选中时，单元格高亮显示
# :若项目被选择，则高亮绘制其矩形
p.palette==QPalette()
p.palette.setColor(QtGui.QPalette.HighlightedText, QtCore.Qt.green) # 文本部件选中的文本颜色
p.palette.setColor(QtGui.QPalette.Text, QtCore.Qt.red)#未选择时的颜色
p.decorationPosition = QStyleOptionViewItem.Right # 图片在文字的左边
p.displayAlignment = Qt.AlignLeft| Qt.AlignCenter # 设置文字的位置
p.decorationAlignment =Qt.AlignLeft | Qt.AlignCenter # 设置文字的位置
QApplication.style().drawControl(QStyle.CE_ItemViewItem, p, painter)
elif index.row() in color_list_b:
red_flag = 'red_flag'
self.countryIcon = self.getCountry(red_flag)
p = QStyleOptionViewItem()
p.index = index
p.rect = option.rect
p.features = QStyleOptionViewItem.HasDecoration | QStyleOptionViewItem.HasDisplay
p.text = str(index.data())
p.font=QFont("Microsoft YaHei",10,QFont.Black)#QFont.Bold
#p.decorationSize = QSize(42,90) # 设置装饰图标的大小。
p.icon = self.countryIcon # 设置装饰图标路径名
p.state = option.state
p.showDecorationSelected = True # 开启选中时，单元格高亮显示
# :若项目被选择，则高亮绘制其矩形
p.palette==QPalette()
p.palette.setColor(QtGui.QPalette.HighlightedText, QtCore.Qt.green) # 文本部件选中的文本颜色
p.palette.setColor(QtGui.QPalette.Text, QtCore.Qt.blue)#未选择时的颜色
p.decorationPosition = QStyleOptionViewItem.Right # 图片在文字的左边
p.displayAlignment = Qt.AlignLeft| Qt.AlignCenter # 设置文字的位置
p.decorationAlignment =Qt.AlignLeft | Qt.AlignCenter # 设置文字的位置
QApplication.style().drawControl(QStyle.CE_ItemViewItem, p, painter)
else:
p = QStyleOptionViewItem()
p.index = index
p.rect = option.rect
p.features = QStyleOptionViewItem.HasDecoration | QStyleOptionViewItem.HasDisplay
p.text = str(index.data())
p.font=QFont("Microsoft YaHei",10,QFont.Black)#QFont.Bold
#p.decorationSize = QSize(42,90) # 设置装饰图标的大小。
p.state = option.state
p.showDecorationSelected = True # 开启选中时，单元格高亮显示
# :若项目被选择，则高亮绘制其矩形
p.palette==QPalette()
p.palette.setColor(QtGui.QPalette.HighlightedText, QtCore.Qt.green) # 文本部件选中的文本颜色
p.palette.setColor(QtGui.QPalette.Text, QtCore.Qt.black)#未选择时的颜色
p.decorationPosition = QStyleOptionViewItem.Right # 图片在文字的左边
p.displayAlignment = Qt.AlignLeft| Qt.AlignCenter # 设置文字的位置
p.decorationAlignment =Qt.AlignLeft | Qt.AlignCenter # 设置文字的位置
QApplication.style().drawControl(QStyle.CE_ItemViewItem, p, painter)
def getCountry(self, red_flag):
self.countryIcon = QIcon("./image/"+ red_flag+".png")
return self.countryIcon
item, offical_name, date, progress,status,ps,reminder = range(7)
class ChildWindow(QWidget):
def __init__(self):
super(ChildWindow, self).__init__()
self.initUI()
def initUI(self):
self.cal = QCalendarWidget(self)
# 设定日历最小日期
self.cal.setMinimumDate(QDate(1980, 1, 1))
# 设定日历最大日期
self.cal.setMaximumDate(QDate(3000, 1, 1))
# 显示日历网格
self.cal.setGridVisible(True)
self.cal.move(20, 20)
# 选定一个日期，会发射一个QtCore.QDate信号，
self.cal.clicked[QtCore.QDate].connect(self.showDate)
self.lbl = QLabel(self)
# selectedDate获取当前日期，默认是运行当前
date = self.cal.selectedDate()
self.lbl.setText(date.toString("yyyy-MM-dd dddd"))
self.lbl.move(20, 300)
self.setGeometry(100,100,400,350)
self.setWindowTitle('设提醒日期')
form._signal.connect(self.getData)
def showDate(self, date):
self.lbl.setText(date.toString("yyyy-MM-dd dddd")) # 在lb1控件上显示所选择的日期
self.setdate=date.toString("yyyy-MM-dd")
print(self.setdate)
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
cur=con.cursor()
sql1 = "update notepad_hiking_db set reminder='%s'\
where offical_name='%s' " %(self.setdate,self.parameter)
cur.execute(sql1)
con.commit()
form.view_update()
def getData(self, parameter):
print(parameter)
self.parameter=parameter
class window(QDialog):
_signal = QtCore.pyqtSignal(str)
def __init__(self, parent=None):
super(window, self).__init__(parent)
self.resize(1150, 860)
font=QFont()
self.setFont(QFont("Arial",11))
self.data_list = []
self.model = QSqlTableModel(self)
self.model.setTable("notepad_hiking_db")
#self.model.setSort(item, Qt.AscendingOrder) #可以重新
self.model.setHeaderData(item, Qt.Horizontal, "事项")
self.model.setHeaderData(offical_name, Qt.Horizontal,"对象")
self.model.setHeaderData(date, Qt.Horizontal,"建立日期")
self.model.setHeaderData(progress, Qt.Horizontal,"进度")
self.model.setHeaderData(status, Qt.Horizontal,"状态")
self.model.setHeaderData(ps, Qt.Horizontal,"备注")
self.model.setHeaderData(reminder, Qt.Horizontal,"提醒")
self.model.select()
self.view = QTableView(self)
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.setColumnHidden(item, False)
self.view.setColumnWidth(0, 90)
self.view.setColumnWidth(1, 100)
self.view.setColumnWidth(2, 90)
self.view.setColumnWidth(3, 360)
self.view.setColumnWidth(4, 60)
self.view.setColumnWidth(5, 290)
self.view.setContextMenuPolicy(Qt.CustomContextMenu) #设置策略为自定义菜单
self.view.customContextMenuRequested.connect(self.ContextMenu)
buttonBox = QDialogButtonBox()
oneweekButton = buttonBox.addButton("&一周内",
QDialogButtonBox.ActionRole)
twoweekButton = buttonBox.addButton("&二周内",
QDialogButtonBox.ActionRole)
addButton = buttonBox.addButton("&添加",
QDialogButtonBox.ActionRole)
deleteButton = buttonBox.addButton("&Delete",
QDialogButtonBox.ActionRole)
copyButton = buttonBox.addButton("&复制添加",
QDialogButtonBox.ActionRole)
sortButton = buttonBox.addButton("&Sort",
QDialogButtonBox.ActionRole)
finishButton = buttonBox.addButton("&加载",
QDialogButtonBox.ActionRole)
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
sortButton.setFocusPolicy(Qt.NoFocus)
menu = QMenu(self)
sortByCategoryAction = menu.addAction("Sort by &progress")
sortByDateAction = menu.addAction("Sort by &date")
sortByIDAction = menu.addAction("Sort by &ITEM")
sortButton.setMenu(menu)
closeButton = buttonBox.addButton(QDialogButtonBox.Close)
csvButton = buttonBox.addButton("&导出CSV",
QDialogButtonBox.ActionRole)
hbox = QHBoxLayout()
self.search_button= QPushButton('搜索',self)
self.search_button.clicked.connect(self.queryRecord)
self.search_text = QLineEdit()
hbox.addWidget(self.search_text)
hbox.addWidget(self.search_button)
hbox.addWidget(buttonBox)
self.layout = QVBoxLayout(self)
self.layout.addLayout(hbox)
self.layout.addWidget(self.view)
self.setLayout(self.layout)
addButton.clicked.connect(self.addRecord)
copyButton.clicked.connect(self.copyRecord)
finishButton.clicked.connect(self.add_list)
deleteButton.clicked.connect(self.deleteRecord)
sortByCategoryAction.triggered.connect(lambda:self.sort(progress))
sortByDateAction.triggered.connect(lambda:self.sort(date))
sortByIDAction.triggered.connect(lambda:self.sort(item))
closeButton.clicked.connect(self.close)
csvButton.clicked.connect(self.db_csv)
self.setWindowTitle("新程事务记录明细表2022-10-25")
self.setWindowFlags(Qt.WindowMinimizeButtonHint)
self.view.show()
time.sleep(0.1)
self.reminder()
self.color_mark()
oneweekButton.clicked.connect(self.oneweekUpdate)
twoweekButton.clicked.connect(lambda:self.color_mark(date))
def color_mark(self):
self.delegate=SpinBoxDelegate() #委托
self.view.setItemDelegate(self.delegate)
def oneweekUpdate(self):
today=datetime.datetime.today()
main_pattern = '%%Y-%%m-%%d'
oneweek=(today+datetime.timedelta(days=7))
self.model.setFilter("datetime.datetime.strptime(reminder, '%s')>= '%s' AND datetime.datetime.strptime('%s', main_pattern) <= '%s'" %(main_pattern,today,main_pattern,oneweek))
self.model.select()
def ContextMenu(self):
self.view.contextMenu = QMenu() #初始化tableView菜单
action1 = self.view.contextMenu.addAction(u"订提醒日期") #添加菜单内容
action1.triggered.connect(self.datetime_setting) #菜单点击触发槽函数
#action2 = self.view.contextMenu.addAction(u"小窗显示客户信息") #添加菜单内容
#action2.triggered.connect(self.btnClicked) #菜单点击触发槽函数
self.view.contextMenu.popup(QCursor.pos()) #根据鼠标坐标显示右击菜单
self.view.contextMenu.show()
def datetime_setting(self):
rows = {index.row() for index in self.view.selectionModel().selectedIndexes()}
print(rows)
for row in rows:
index = self.view.model().index(row, 1)
self.offical_name=index.data()
print(self.offical_name)
self.chile_Win = ChildWindow()
self.chile_Win.show()
data_str=self.offical_name
self._signal.emit(data_str)
def reminder(self):
self.db_csv()
daytimetoday = datetime.datetime.now()
today = datetime.datetime.strftime(daytimetoday, "%Y-%m-%d")
#print(today)
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
cur=con.cursor()
cur.execute("SELECT * FROM notepad_hiking_db")
rows = cur.fetchall()
#QMessageBox.warning(self,"警告对话框","警告对话框正文",QMessageBox.Yes | QMessageBox.No,QMessageBox.Yes)
global date_list_a,date_list_b,color_list_a,color_list_b
color_list_a,color_list_b=[],[]
date_list_a,date_list_b=[],[]
dt=-1
for row in rows:
#print(row)
try:
dt+=1
daytimesetdate = datetime.datetime.strptime(row[6], "%Y-%m-%d")
date_difference=(daytimesetdate-daytimetoday).days
#print(date_difference)
if 0<=date_difference<=7:
date_list_a.append(row[6])
if 7<date_difference<=14:
date_list_b.append(row[6])
except:
pass
def db_csv(self):
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
cur=con.cursor()
cur.execute("SELECT * FROM notepad_hiking_db") # execute the query
rows = cur.fetchall() # collect the data
wb=Workbook()
ws=wb.active
ws.append(['事项', '对像', '建立日期', '进度', '状态 ', '备注','提醒日期'])
for rows in rows:
#print(rows)
ws.append(rows) # write your SQLite data
wb.save('./制单基础数据/'+'事务记录.xlsx')
data = pd.read_excel('./制单基础数据/'+'事务记录.xlsx')
data.to_csv('./制单基础数据/'+'事务记录.csv',index=False,encoding='utf_8_sig')
def copyRecord(self):
rows = [index.row() for index in self.view.selectionModel().selectedIndexes()]
print(rows)
record=[]
n=-1
try:
for row in rows:
n+=1
index = self.view.model().index(row, n)
data=index.data()
record.append(data)
print(record)
except:
pass
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
m=record
cur=con.cursor()
item, offical_name, date, progress,status,ps=m[0],m[1],m[2],m[3],m[4],m[5],m[6]
cur.execute("INSERT INTO notepad_hiking_db(item, offical_name, date, progress,status,ps,reminder) VALUES \
('{}','{}','{}','{}','{}','{}','{}')"\
.format(item, offical_name, date, progress,status,ps,reminder))
con.commit()
con.close()
self.view.close()
self.model = QSqlTableModel(self)
self.model.setTable("notepad_hiking_db.db")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
self.view = QTableView(self)
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.show()
self.layout.addWidget(self.view)
self.data_list.clear()
self.view_update()
def add_list(self):
reload(pr)
from pr import notepad_data_list
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
for m in notepad_data_list:
print(m)
cur=con.cursor()
item, offical_name, date, progress,status,ps,reminder=m[0],m[1],m[2],m[3],m[4],m[5],m[6]
cur.execute("INSERT INTO notepad_hiking_db(item, offical_name, date, progress,status,ps,reminder) VALUES \
('{}','{}','{}','{}','{}','{}','{}')"\
.format(item, offical_name, date, progress,status,ps,reminder))
con.commit()
con.close()
self.view_update()
def view_update(self):
self.view.close()
self.model = QSqlTableModel(self)
self.model.setTable("notepad_hiking_db")
self.model.setSort(item, Qt.AscendingOrder)
self.model.setHeaderData(item, Qt.Horizontal, "事项")
self.model.setHeaderData(offical_name, Qt.Horizontal,"对象")
self.model.setHeaderData(date, Qt.Horizontal,"建立日期")
self.model.setHeaderData(progress, Qt.Horizontal,"进度")
self.model.setHeaderData(status, Qt.Horizontal,"状态")
self.model.setHeaderData(ps, Qt.Horizontal,"备注")
self.model.select()
self.view = QTableView(self)
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.layout.addWidget(self.view)
self.view.setColumnWidth(0, 90)
self.view.setColumnWidth(1, 100)
self.view.setColumnWidth(2, 90)
self.view.setColumnWidth(3, 390)
self.view.setColumnWidth(4, 60)
self.view.setColumnWidth(5, 290)
self.view.setContextMenuPolicy(Qt.CustomContextMenu) #设置策略为自定义菜单
self.view.customContextMenuRequested.connect(self.ContextMenu)
self.view.show()
def addRecord(self):
from add_dialog_notepad import AddDialog
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
AddDialog.get_add_dialog(self)
print(self.data_list)
for m in self.data_list:
cur=con.cursor()
item, offical_name, date, progress,status,ps,reminder=m[0],m[1],m[2],m[3],m[4],m[5],m[6]
cur.execute("INSERT INTO notepad_hiking_db(item, offical_name, date, progress,status,ps,reminder) VALUES \
('{}','{}','{}','{}','{}','{}','{}')"\
.format(item, offical_name, date, progress,status,ps,reminder))
con.commit()
con.close()
self.view_update()
def deleteRecord(self):
index = self.view.currentIndex()
if not index.isValid():
return
record = self.model.record(index.row())
category = record.value(item)
desc = record.value(offical_name)
if (QMessageBox.question(self, "Reference Data",
("Delete {0} from category {1}?"
.format(desc,category)),
QMessageBox.Yes|QMessageBox.No) ==
QMessageBox.No):
return
self.model.removeRow(index.row())
self.model.submitAll()
self.model.select()
def sort(self, column):
self.model.setSort(column, Qt.AscendingOrder)
self.model.select()
self.reminder()
self.color_mark()
def queryRecord(self):
affair= self.search_text.text()
print(affair)
s ="item like '%"+affair+"%'"
self.model.setFilter(s)
self.model.select()
def main():
print(os.path.dirname(__file__))
app = QApplication(sys.argv)
filename = os.path.join('./制单基础数据/', "notepad_hiking_db.db")
create = not QFile.exists(filename)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(filename)
if not db.open():
QMessageBox.warning(None, "notepad_hiking_db.db",
"Database Error: {0}".format(db.lastError().text()))
sys.exit(1)
if create:
print('添加数据。。。。。。。。。。。。。。。。。。')
con = sqlite3.connect(database='./制单基础数据/'+'notepad_hiking_db.db')
QApplication.processEvents()
cur=con.cursor()
sql=("""CREATE TABLE IF NOT EXISTS notepad_hiking_db (
item VARCHAR(80) NOT NULL,
offical_name VARCHAR(90) NOT NULL,
date VARCHAR(90) NOT NULL,
progress VARCHAR(90) NOT NULL,
status VARCHAR(90) NOT NULL,
ps VARCHAR(90) NOT NULL,
reminder VARCHAR(90) NOT NULL)""")
cur.execute(sql)
cur.close()
con.commit()
con.close()
global form
form = window()
form.show()
app.exec_()
if __name__ == '__main__':
main()
以下答案由GPT-4.5Plus大模型(语音)与博主A努力中.......共同编写：
在PyQt5中,setFilter方法用于为模型设置过滤条件。要过滤日期范围,可以这样写:
python
today = datetime.date.today()
oneweek = today + datetime.timedelta(days=7)
self.model.setFilter(
"datetime >= '%s' AND datetime <= '%s'" % (today, oneweek)
)
self.model.select()
这段代码的意思是:
之所以不起作用,可能是因为你的代码有几个问题:
修改后,整段代码应为:
python
import datetime
today = datetime.date.today()
oneweek = today + datetime.timedelta(days=7)
self.model.setFilter(
"datetime >= '%s' AND datetime <= '%s'" % (today.strftime('%Y-%m-%d'), oneweek.strftime('%Y-%m-%d'))
)
self.model.select()
希望这个解释和示例代码能帮助你解决在PyQt5的setFilter方法中过滤日期范围的问题。
使用 QDate 类来表示日期，并使用 QDateEdit 编辑器来显示和编辑日期
from PyQt5.QtCore import QDate, QTime
from PyQt5.QtWidgets import QApplication, QMainWindow, QDateEdit, QDialog
class DateRangeFilter(QDialog):
def __init__(self, parent=None):
super().__init__(parent)
# 创建一个 QDateEdit 编辑器，用于显示当前日期
self.date_edit = QDateEdit()
self.date_edit.setDate(QDate())
self.date_edit.setAlignment(Qt.AlignRight)
# 创建一个 QDate 对象，表示起始日期
start_date = QDate(2022, 1, 1)
# 创建一个 QTime 对象，表示结束日期
end_date = QDate(2023, 12, 31)
# 创建一个 QDate 对象，表示筛选范围
range_date = QDate(start_date.year(), start_date.month(), start_date.day())
# 创建一个 QTime 对象，表示筛选范围的结束时间
range_end_time = QTime(end_date.hour(), end_date.minute(), end_date.second())
# 将起始日期和结束日期分别赋值给 QDateEdit 编辑器的日期属性
self.date_edit.setDate(start_date)
self.date_edit.setTime(start_date)
# 将筛选范围分别赋值给 QDateEdit 编辑器的时间属性
self.date_edit.setDate(range_date)
self.date_edit.setTime(range_end_time)
可以使用filter，比如
data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
new_data = list(filter(lambda x: x % 2 == 0, data)) # 筛选出偶数
print(new_data)