import os
import sqlite3
# Create a database connection
conn = sqlite3.connect('todo.db')
# Create a todo table
cur = conn.cursor()
cur.execute('''
CREATE TABLE todo (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
due_date DATETIME NOT NULL,
completed BOOLEAN NOT NULL DEFAULT FALSE
)
''')
# Insert some sample data
cur.execute('''
INSERT INTO todo (title, due_date) VALUES
('Buy groceries', '2023-05-25'),
('Clean the house', '2023-05-26'),
('Do the laundry', '2023-05-27')
''')
# Commit the changes to the database
conn.commit()
# Close the database connection
conn.close()
# Define a function to get all todos
def get_todos():
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('SELECT * FROM todo')
todos = cur.fetchall()
conn.close()
return todos
# Define a function to add a todo
def add_todo(title, due_date):
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('INSERT INTO todo (title, due_date) VALUES (?, ?)', (title, due_date))
conn.commit()
conn.close()
# Define a function to delete a todo
def delete_todo(id):
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('DELETE FROM todo WHERE id = ?', (id,))
conn.commit()
conn.close()
# Define a function to mark a todo as completed
def mark_todo_completed(id):
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('UPDATE todo SET completed = 1 WHERE id = ?', (id,))
conn.commit()
conn.close()
# Define a function to get the number of todos
def get_number_of_todos():
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('SELECT COUNT(*) FROM todo')
number_of_todos = cur.fetchone()[0]
conn.close()
return number_of_todos
# Define a function to get the number of completed todos
def get_number_of_completed_todos():
conn = sqlite3.connect('todo.db')
cur = conn.cursor()
cur.execute('SELECT COUNT(*) FROM todo WHERE completed = 1')
number_of_completed_todos = cur.fetchone()[0]
conn.close()
return number_of_completed_todos
# Define a function to get the number of incomplete todos
def get_number_of_incomplete_todos():
return get_number_of_todos() - get_number_of_completed_todos()
标签:cur,get,编程,number,面向对象,todos,打卡,todo,conn
From: https://www.cnblogs.com/sugar-refinery/p/17421495.html