import sys
import asyncio
from PyQt5.QtGui import QColor
from PyQt5.QtWidgets import QDialog, QApplication, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit, QPushButton, \
QListWidget, QMessageBox, QTableWidget, QTableWidgetItem, QHeaderView, QAbstractItemView, QMenu, QCheckBox
from PyQt5.QtCore import Qt
from PyQt5 import QtGui
import mysql.connector as connector
import datetime
def connect():
try:
db = connector.connect(host='localhost', user='root', passwd='123456')
print(f"Connection to database is successful: {db}")
return db
except connector.Error as error:
print(f"Something went wrong: {error}. Please, try again")
def create_database(db, database_name):
cursor = db.cursor()
cursor.execute(f"CREATE DATABASE IF NOT EXISTS {database_name};")
cursor.close()
def create_tasks_table(db, database_name):
cursor = db.cursor()
cursor.execute(f"CREATE TABLE IF NOT EXISTS {database_name}.tasks("
f"id INT AUTO_INCREMENT PRIMARY KEY,"
f"title VARCHAR(50) NOT NULL,"
f"description TEXT NOT NULL,"
f"date DATETIME NOT NULL"
f");")
cursor.close()
class MainWindow(QDialog):
def __init__(self, db):
super().__init__()
self.left = 200
self.top = 400
self.height = 400
self.width = 800
self.title = "Task manager"
self.db = db
self.init_window()
self.init_ui()
self.show()
self.load_tasks("SELECT * FROM task_manager_db.tasks;")
def on_data_changed(self, item):
new_value = item.text()
id = self.tasks_table.item(item.row(), 0).text()
if item.column() == 1:
changed_column = 'title'
elif item.column() == 2:
changed_column = 'description'
else:
changed_column = 'completed'
new_value = 1 if item.checkState() else 0
print(new_value)
if new_value == 1:
self.tasks_table.item(item.row(), 0).setBackground(QColor(102, 221, 170))
else:
self.tasks_table.item(item.row(), 0).setBackground(QColor(255, 255, 255))
# color = QColor(102, 221, 170) if not new_value else QColor(255, 255, 255)
cursor = self.db.cursor()
query = f'UPDATE task_manager_db.tasks SET {changed_column}=\'{new_value}\' WHERE id={id};'
try:
cursor.execute(query)
self.db.commit()
except connector.Error as err:
print(err)
cursor.close()
exit(1)
finally:
# self.tasks_table.item(item.row(), 0).setBackground(QColor(55, 55, 55))
cursor.close()
def init_ui(self):
root_layout = QVBoxLayout()
# title layout init
title_layout = QHBoxLayout()
title_label = QLabel('Title')
self.title_error = QLabel()
self.title_error.setStyleSheet('color:red')
self.title_edit = QLineEdit()
title_layout.addWidget(title_label)
title_layout.addWidget(self.title_edit)
title_layout.addWidget(self.title_error)
# description layout init
description_layout = QHBoxLayout()
description_label = QLabel('Description')
self.description_error = QLabel()
self.description_error.setStyleSheet('color:red')
self.description_edit = QLineEdit()
description_layout.addWidget(description_label)
description_layout.addWidget(self.description_edit)
description_layout.addWidget(self.description_error)
buttons_layout = QHBoxLayout()
buttons_layout.setAlignment(Qt.AlignHCenter)
self.sort_title_button = QPushButton('Title \u2193')
self.sort_title_button.clicked.connect(self.sort_title)
self.sort_description_button = QPushButton('Description \u2193')
self.sort_description_button.clicked.connect(self.sort_description)
self.sort_date_button = QPushButton('Date \u2193')
self.sort_date_button.clicked.connect(self.sort_date)
add_task_button = QPushButton('Add task')
add_task_button.clicked.connect(self.add_task)
buttons_layout.addWidget(self.sort_title_button)
buttons_layout.addWidget(self.sort_description_button)
buttons_layout.addWidget(self.sort_date_button)
buttons_layout.addWidget(add_task_button)
# self.tasks_list = QListWidget()
self.tasks_table = QTableWidget()
# self.tasks_table.itemChanged.connect(self.on_data_changed)
self.tasks_table.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tasks_table.setMouseTracking(True)
self.tasks_table.setColumnCount(5)
# self.tasks_table.itemSelectionChanged.connect(self.itemChanged)
# self.tasks_table.itemSelectionChanged.connect(self.change_item)
# self.tasks_table.itemDoubleClicked.connect(self.double_clicked_item)
# self.tasks_table.itemPressed.connect(self.entered_item)
self.tasks_table.setHorizontalHeaderLabels(['id', 'title', 'description', 'date', 'completed'])
self.tasks_table.horizontalHeader().setSectionResizeMode(0, QHeaderView.Stretch)
self.tasks_table.horizontalHeader().setSectionResizeMode(1, QHeaderView.Stretch)
self.tasks_table.horizontalHeader().setSectionResizeMode(2, QHeaderView.Stretch)
self.tasks_table.horizontalHeader().setSectionResizeMode(3, QHeaderView.Stretch)
self.tasks_table.horizontalHeader().setSectionResizeMode(4, QHeaderView.Stretch)
self.no_tasks_table = QLabel('No tasks in table')
self.no_tasks_table.hide()
delete_button = QPushButton('Delete row')
delete_button.clicked.connect(self.delete_row)
root_layout.addLayout(title_layout)
root_layout.addLayout(description_layout)
root_layout.addLayout(buttons_layout)
# root_layout.addWidget(self.tasks_list)
root_layout.addWidget(self.tasks_table)
root_layout.addWidget(self.no_tasks_table)
root_layout.addWidget(delete_button)
self.setLayout(root_layout)
def change_item(self):
pass
# def double_clicked_item(self, item):
# new_value = item.text()
# print(new_value)
def sort_date(self):
self.tasks_table.setRowCount(0)
if self.sort_date_button.text() == 'Date \u2193':
query = 'SELECT * FROM task_manager_db.tasks ORDER BY date ASC;'
self.load_tasks(query)
self.sort_date_button.setText('Date \u2191')
else:
query = 'SELECT * FROM task_manager_db.tasks ORDER BY date DESC;'
self.load_tasks(query)
self.sort_date_button.setText('Date \u2193')
def sort_description(self):
self.tasks_table.setRowCount(0)
if self.sort_description_button.text() == 'Description \u2193':
query = 'SELECT * FROM task_manager_db.tasks ORDER BY description ASC;'
self.load_tasks(query)
self.sort_description_button.setText('Description \u2191')
else:
query = 'SELECT * FROM task_manager_db.tasks ORDER BY description DESC;'
self.load_tasks(query)
self.sort_description_button.setText('Description \u2193')
def sort_title(self):
self.tasks_table.setRowCount(0)
if self.sort_title_button.text() == 'Title \u2193':
query = "SELECT * FROM task_manager_db.tasks ORDER BY title ASC;"
self.load_tasks(query)
self.sort_title_button.setText('Title \u2191')
else:
query = "SELECT * FROM task_manager_db.tasks ORDER BY title DESC;"
self.load_tasks(query)
self.sort_title_button.setText('Title \u2193')
def delete_row(self):
if self.tasks_table.selectedItems():
cursor = self.db.cursor()
id = int(self.tasks_table.selectedItems()[0].text())
query = "DELETE FROM task_manager_db.tasks WHERE id=%s;"
try:
cursor.execute(query, (id,))
self.db.commit()
self.tasks_table.removeRow(self.tasks_table.currentRow())
if not self.tasks_table.rowCount():
self.tasks_table.hide()
self.no_tasks_table.show()
cursor.close()
except connector.Error as error:
print(error)
except Exception as error:
print(error)
def itemChanged(self):
pass
def load_tasks(self, query):
cursor = self.db.cursor()
cursor.execute(query)
tasks = cursor.fetchall()
if len(tasks) > 0:
self.tasks_table.show()
self.no_tasks_table.hide()
for id, title, description, date, completed in tasks:
row_position = self.tasks_table.rowCount()
self.tasks_table.insertRow(row_position)
idItem = QTableWidgetItem()
idItem.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
idItem.setText(str(id))
self.tasks_table.setItem(row_position, 0, idItem)
self.tasks_table.setItem(row_position, 1, QTableWidgetItem(title))
self.tasks_table.setItem(row_position, 2, QTableWidgetItem(description))
dateItem = QTableWidgetItem()
dateItem.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
dateItem.setText(date.strftime('%Y-%m-%d %H:%M:%S'))
self.tasks_table.setItem(row_position, 3, dateItem)
completed_check_box = QTableWidgetItem()
completed_check_box.setFlags(Qt.ItemIsUserCheckable | Qt.ItemIsEnabled)
completed_check_box.setCheckState(Qt.Checked if completed else Qt.Unchecked)
completed_check_box.setData(Qt.UserRole, completed_check_box.checkState())
if completed:
for index in range(0, 4):
self.tasks_table.item(row_position, index).setBackground(QColor(102, 221, 170))
self.tasks_table.setItem(row_position, 4, completed_check_box)
else:
self.tasks_table.hide()
self.no_tasks_table.show()
cursor.close()
self.tasks_table.itemChanged.connect(self.on_data_changed)
def init_window(self):
self.setGeometry(self.left, self.top, self.width, self.height)
self.setWindowTitle(self.title)
def add_task(self):
self.title_error.clear()
self.description_error.clear()
title = self.title_edit.text()
description = self.description_edit.text()
has_error = False
if not title:
has_error = True
self.title_error.setText('Enter the title')
if not description:
has_error = True
self.description_error.setText('Enter the description')
if not has_error:
cursor = self.db.cursor()
query = "INSERT INTO task_manager_db.tasks(title, description, date) VALUES (%s, %s, %s)"
values = (title, description, datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S'))
cursor.execute(query, values)
db.commit()
if cursor.rowcount:
message = QMessageBox()
message.setIcon(QMessageBox.Information)
message.setText("Task has been added successfully")
message.setStandardButtons(QMessageBox.Ok)
message.buttonClicked.connect(self.on_ok_click)
message.exec_()
# title (description) date
# self.tasks_list.addItem(f'{title}({description})')j
cursor.execute("SELECT * FROM task_manager_db.tasks ORDER BY id DESC LIMIT 1")
data = cursor.fetchall()[0]
print(data)
row_position = self.tasks_table.rowCount()
self.tasks_table.insertRow(row_position)
self.tasks_table.setItem(row_position, 0, QTableWidgetItem(str(data[0])))
self.tasks_table.setItem(row_position, 1, QTableWidgetItem(data[1]))
self.tasks_table.setItem(row_position, 2, QTableWidgetItem(data[2]))
dateItem = QTableWidgetItem()
dateItem.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
dateItem.setText(datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S'))
self.tasks_table.setItem(row_position, 3, dateItem)
completed_check_box = QTableWidgetItem()
completed_check_box.setFlags(Qt.ItemIsUserCheckable | Qt.ItemIsEnabled)
completed_check_box.setCheckState(Qt.Unchecked)
completed_check_box.setData(Qt.UserRole, completed_check_box.checkState())
self.tasks_table.setItem(row_position, 4, completed_check_box)
cursor.close()
def on_ok_click(self):
self.tasks_table.show()
self.no_tasks_table.hide()
if __name__ == "__main__":
db = connect()
create_database(db, "task_manager_db")
create_tasks_table(db, "task_manager_db")
app = QApplication(sys.argv)
window = MainWindow(db)
sys.exit(app.exec())