Sqlite3 Python中的DateExpiry [英] DateExpiry in Sqlite3 Python

问题描述

我试图弄清楚如何使用SQLite3语句编写函数，该语句负责提前通知我任何药物的到期日期（假设30天）。我确实是这样做的，但不能正常工作

I try to figure out how to write function with SQLite3 statement which is responsible for informing me about expiry date of any medicine in advance let's suppose 30 days. I did sth like this but it doesn't work properly

l1top = Label(fr,text="Number of serie:")
l1top.grid(row=0,column=0,padx=20,sticky=E,pady=10)
l2top = Label(fr,text="Name of medicine:")
l2top.grid(row=1,column=0,padx=20,sticky=E,pady=10)
l3top = Label(fr,text="Dose")
l3top.grid(row=3,column=0,padx=20,sticky=E,pady=10)
l4top = Label(fr,text="Type of medicine")
l4top.grid(row=4,column=0,padx=20,sticky=E,pady=10)
l5top = Label(fr,text="Packages:")
l5top.grid(row=5,column=0,padx=20,sticky=E,pady=10)
l5top = Label(fr,text="Bottles:")
l5top.grid(row=6,column=0,padx=20,sticky=E,pady=10)
l6top = Label(fr,text="Expiry Date:")
l6top.grid(row=7,column=0,padx=20,sticky=E,pady=10)
def expiry():
    conn = sqlite3.connect("pharmacy.db")
    cur = conn.cursor()
    cur.execute('SELECT date FROM medicine WHERE date <= 30')
    matched = [rec[0] for rec in cur]
    conn.close()
    items = [row for row in tree.get_children() if tree.item(row, 'values')[6] in matched]
    tree.selection_set(items)
expiry()

上面的代码选择不正确，因为它仅根据天匹配，但不包括小部件DateEntry（下面）中的整个日期。如何重写SQLite语句，该语句将捕获整个日期并匹配所有到期日期在30天后到期的产品，并在红色上高亮显示最后一个带有日期的列（[6]）。

The code above doesn't select properly because it matches only according to days but it does not include the whole date from the widget DateEntry(below). How to rewrite the SQLite statement that it grabs the whole date and matches all products with date which expiry ends in 30 days and highlights on red the last column ([6]) with date.

e6 = DateEntry(fr,width=12,bg="darkblue",fg="white",year=2020,state="readonly",date_pattern="dd/mm/yyyy",textvariable=six)
e6.grid(row=7,column=1,pady=10)

推荐答案

如果列 date 的格式为 DD-MM-YYYY ，首先必须将其更改为 YYYY-MM-DD ，因为这是SQLite唯一有效的格式：

If the format of the column date is DD-MM-YYYY, first you must change it to YYYY-MM-DD, because this is the only valid format for SQLite:

UPDATE medicine 
SET date = SUBSTR(date, -4) || '-' || SUBSTR(date, 4, 2) || '-' || SUBSTR(date, 1, 2);

，然后使用函数 DATE（）获取行其中日期在 now 和 now + 30天之间：

and then use the function DATE() to get the rows where date is between now and now + 30 days:

SELECT date 
FROM medicine 
WHERE date BETWEEN DATE('now') AND DATE('now', '+30 day')

这篇关于Sqlite3 Python中的DateExpiry的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！