from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QApplication, QWidget, QTreeWidgetItem
import sys
import sqlite3


class Ui_Form(object):
    def setupUi(self, Form):
        Form.setObjectName("Form")
        Form.resize(662, 599)
        self.tableWidget = QtWidgets.QTableWidget(Form)
        self.tableWidget.setGeometry(QtCore.QRect(0, 100, 671, 501))
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(5)
        self.tableWidget.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(1, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(2, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(3, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(4, item)
        self.comboBox = QtWidgets.QComboBox(Form)
        self.comboBox.setGeometry(QtCore.QRect(10, 10, 131, 31))
        self.comboBox.setObjectName("comboBox")
        self.comboBox.addItem("")
        self.comboBox.addItem("")
        self.comboBox.addItem("")
        self.pushButton = QtWidgets.QPushButton(Form)
        self.pushButton.setGeometry(QtCore.QRect(510, 30, 121, 41))
        self.pushButton.setObjectName("pushButton")
        self.lineEdit = QtWidgets.QLineEdit(Form)
        self.lineEdit.setGeometry(QtCore.QRect(10, 50, 131, 31))
        self.lineEdit.setObjectName("lineEdit")
        self.label = QtWidgets.QLabel(Form)
        self.label.setGeometry(QtCore.QRect(180, 30, 111, 16))
        self.label.setObjectName("label")
        self.lineEdit_2 = QtWidgets.QLineEdit(Form)
        self.lineEdit_2.setGeometry(QtCore.QRect(170, 50, 113, 31))
        self.lineEdit_2.setObjectName("lineEdit_2")

        self.retranslateUi(Form)
        QtCore.QMetaObject.connectSlotsByName(Form)

    def retranslateUi(self, Form):
        _translate = QtCore.QCoreApplication.translate
        Form.setWindowTitle(_translate("Form", "Form"))
        item = self.tableWidget.horizontalHeaderItem(0)
        item.setText(_translate("Form", "id"))
        item = self.tableWidget.horizontalHeaderItem(1)
        item.setText(_translate("Form", "title"))
        item = self.tableWidget.horizontalHeaderItem(2)
        item.setText(_translate("Form", "year"))
        item = self.tableWidget.horizontalHeaderItem(3)
        item.setText(_translate("Form", "genre"))
        item = self.tableWidget.horizontalHeaderItem(4)
        item.setText(_translate("Form", "duration"))
        self.comboBox.setItemText(0, _translate("Form", "title"))
        self.comboBox.setItemText(1, _translate("Form", "year"))
        self.comboBox.setItemText(2, _translate("Form", "duration"))
        self.pushButton.setText(_translate("Form", "Старт"))
        self.label.setText(_translate("Form", "Параметры запроса"))


class Widget(QWidget, Ui_Form):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.pushButton.clicked.connect(self.start)

    def start(self):
        criterion, concern, parametr = self.comboBox.currentText(), self.lineEdit.text(), self.lineEdit_2.text()
        res = self.get_info([criterion, concern, parametr])
        l = []
        for arg_list in res:
            rowPosition = self.tableWidget.rowCount()
            self.tableWidget.insertRow(rowPosition)
            for i in range(len(arg_list)):
                self.tableWidget.setItem(rowPosition, i, QtWidgets.QTableWidgetItem(str(arg_list[i])))

    def get_info(self, st):
        lst = st
        import os.path
        BASE_DIR = os.path.dirname(os.path.abspath(__file__))
        db_path = os.path.join(BASE_DIR, "films.db")

        with sqlite3.connect(db_path) as con:
            cur = con.cursor()

        res = cur.execute(f"""SELECT * FROM Films WHERE {lst[0]} = {lst[1]} WHERE {lst[0]} """ + lst[2]).fetchall()
        return res


if __name__ == '__main__':
    app = QApplication(sys.argv)
    wid = Widget()
    wid.show()
    sys.exit(app.exec())