[PyQt] Associating 2 Db Tables With Each Other

[JokerSob]

There is a problem I've been working on and I don't know how to do it. First of all, I will try to explain in order to form an idea:

Imagine you have several cars or you have a rental car company.You may want to track the mileage of your cars and keep the data up to date via the GUI. For example, let's have X - Y - Z cars :

X : 12345 => 12351

Y : 23415

Z : 13524

Here I will only narrate through our X car. We rented our X car to someone or entrusted it to go somewhere. While recording the exit via the GUI, the current 12345 kilometers of the current X car's license plate is automatically written to our registration table, and when our car comes back, when we update the entry record, when we manually type 12351 in the entry kilometer field and complete the record, I want to be able to update the kilometer of this car with the kilometer we just wrote in the DB field.Next time, when creating an exit record for this car, normally 12351 kilometers will be automatically written on the screen. To make it more understandable, you can see the codes I have worked on below :

main.py

import sys
import os
import platform
import datetime
from PySide2 import QtCore, QtGui, QtWidgets
from PySide2.QtCore import QCoreApplication, QPropertyAnimation, QDate, QDateTime, QMetaObject, QObject, QPoint, QRect, QSize, QTime, QUrl, Qt, QEvent, QTimer
from PySide2.QtGui import QBrush, QColor, QConicalGradient, QCursor, QFont, QFontDatabase, QIcon, QKeySequence, QLinearGradient, QPalette, QPainter, QPixmap, QRadialGradient
from PySide2.QtWidgets import *
import sqlite3

# GUI FILE
from ui_main import Ui_MainWindow

## ==> Cars Plate DataBase
# Company Cars Plate ComboBox
con = sqlite3.connect('DataBase.db')
cur = con.cursor()
cur.execute ("CREATE TABLE IF NOT EXISTS CarsPlate (PLATE TEXT NOT NULL, CARS_OUT_KM INTEGER NOT NULL, ID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT)")
con.commit()
con.close()

def InsertCarsPlate(self):
    con = sqlite3.connect('DataBase.db')
    cur = con.cursor()
    CarsPlate = self.ui.line_CarsDbUpdateAdd.text()
    CarsKilometer = self.ui.line_CarsKilometer.text()
    
    cur.execute("INSERT INTO CarsPlate (PLATE, CARS_OUT_KM, ID) VALUES (?, ?, NULL)", (CarsPlate, CarsKilometer))
    
    con.commit()
    self.ui.combo_CarsPlate.clear()

    CarsPlateComboList(self)
    
def CarsPlateComboList(self):
    con = sqlite3.connect('DataBase.db')
    cur = con.cursor()
    cur.execute("SELECT PLATE FROM CarsPlate")
    carsplate = cur.fetchall()
    for RowIndex, RowData in enumerate(carsplate):
        for ColumnIndex, ColumnData in enumerate(RowData):
            self.ui.combo_CarsPlate.addItem(ColumnData)

    con.commit()
    con.close()
    
## ==> Cars DataBase
con = sqlite3.connect('DataBase.db')
cur = con.cursor()
cur.execute ("CREATE TABLE IF NOT EXISTS Cars (PLATE TEXT NOT NULL, CARS_OUT_KM INTEGER NOT NULL, CARS_IN_KM INTEGER NOT NULL, ID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT)")
con.commit()
con.close() 

def InsertCompanyCars(self):
    con = sqlite3.connect('DataBase.db')
    cur = con.cursor()
   
    CarsPlate = self.ui.combo_CarsPlate.currentText()
    Cars0utKilometer = self.ui.line_CarsKilometer.text()
    CarsInKilometer = self.ui.line_CarsInKilometer.text()

    cur.execute("INSERT INTO Cars (PLATE, CARS_OUT_KM, CARS_IN_KM, ID) VALUES (?, ?, ?, NULL)", (CarsPlate, Cars0utKilometer, CarsInKilometer))
    
    con.commit()
    CompanyCarsList(self)
    
def CheckOutCompanyCars(self):
    con = sqlite3.connect('DataBase.db')
    cur = con.cursor() 
    
    selected = self.ui.table_CarsData.selectedItems()
    record = selected[3].text()
    CarsInKilometer = self.ui.line_CarsKilometer.text()

    cur.execute("UPDATE Cars SET CARS_IN_KM=? WHERE ID=?",(CarsInKilometer, record))
    con.commit()
    CompanyCarsList(self)
    
def CompanyCarsList(self):
    con = sqlite3.connect('DataBase.db')
    cur = con.cursor()
    cur.execute("SELECT * FROM Cars")
    
    self.ui.table_CarsData.setRowCount(0)
    for RowIndex, RowData in enumerate(cur):
        self.ui.table_CarsData.insertRow(RowIndex)
        for ColumnIndex, ColumnData in enumerate(RowData):
            self.ui.table_CarsData.setItem(RowIndex, ColumnIndex, QTableWidgetItem(str(ColumnData))) 
    
class MainWindow(QMainWindow):
    def __init__(self):
        QMainWindow.__init__(self)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        
        
        CarsPlateComboList(self)
        
        self.ui.Btn_CarsDbAdd.clicked.connect(lambda: InsertCarsPlate(self))
        
        self.ui.Btn_CarsIn.clicked.connect(lambda: InsertCompanyCars(self))
        
        self.ui.Btn_CarsOut.clicked.connect(lambda: CheckOutCompanyCars(self))
        
        
        self.show()
        ## ==> END ##

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MainWindow()
    sys.exit(app.exec_())

ui_main.py

from PySide2.QtCore import *
from PySide2.QtGui import *
from PySide2.QtWidgets import *


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        if not MainWindow.objectName():
            MainWindow.setObjectName(u"MainWindow")
        MainWindow.resize(1166, 600)
        self.centralwidget = QWidget(MainWindow)
        self.centralwidget.setObjectName(u"centralwidget")
        self.verticalLayout = QVBoxLayout(self.centralwidget)
        self.verticalLayout.setSpacing(0)
        self.verticalLayout.setObjectName(u"verticalLayout")
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.frame = QFrame(self.centralwidget)
        self.frame.setObjectName(u"frame")
        self.frame.setMinimumSize(QSize(0, 50))
        self.frame.setMaximumSize(QSize(16777215, 50))
        self.frame.setFrameShape(QFrame.NoFrame)
        self.frame.setFrameShadow(QFrame.Raised)

        self.verticalLayout.addWidget(self.frame)

        self.frame_2 = QFrame(self.centralwidget)
        self.frame_2.setObjectName(u"frame_2")
        self.frame_2.setFrameShape(QFrame.NoFrame)
        self.frame_2.setFrameShadow(QFrame.Raised)
        self.horizontalLayout = QHBoxLayout(self.frame_2)
        self.horizontalLayout.setSpacing(6)
        self.horizontalLayout.setObjectName(u"horizontalLayout")
        self.horizontalLayout.setContentsMargins(0, 0, 0, 0)
        self.frame_4 = QFrame(self.frame_2)
        self.frame_4.setObjectName(u"frame_4")
        self.frame_4.setFrameShape(QFrame.NoFrame)
        self.frame_4.setFrameShadow(QFrame.Raised)
        self.verticalLayout_4 = QVBoxLayout(self.frame_4)
        self.verticalLayout_4.setObjectName(u"verticalLayout_4")
        self.verticalLayout_2 = QVBoxLayout()
        self.verticalLayout_2.setObjectName(u"verticalLayout_2")
        self.horizontalLayout_5 = QHBoxLayout()
        self.horizontalLayout_5.setObjectName(u"horizontalLayout_5")
        self.label = QLabel(self.frame_4)
        self.label.setObjectName(u"label")

        self.horizontalLayout_5.addWidget(self.label)

        self.line_CarsDbUpdateAdd = QLineEdit(self.frame_4)
        self.line_CarsDbUpdateAdd.setObjectName(u"line_CarsDbUpdateAdd")

        self.horizontalLayout_5.addWidget(self.line_CarsDbUpdateAdd)


        self.verticalLayout_2.addLayout(self.horizontalLayout_5)

        self.horizontalLayout_6 = QHBoxLayout()
        self.horizontalLayout_6.setObjectName(u"horizontalLayout_6")
        self.label_4 = QLabel(self.frame_4)
        self.label_4.setObjectName(u"label_4")

        self.horizontalLayout_6.addWidget(self.label_4)

        self.line_CarsKilometer = QLineEdit(self.frame_4)
        self.line_CarsKilometer.setObjectName(u"line_CarsKilometer")

        self.horizontalLayout_6.addWidget(self.line_CarsKilometer)


        self.verticalLayout_2.addLayout(self.horizontalLayout_6)

        self.Btn_CarsDbAdd = QPushButton(self.frame_4)
        self.Btn_CarsDbAdd.setObjectName(u"Btn_CarsDbAdd")

        self.verticalLayout_2.addWidget(self.Btn_CarsDbAdd)

        self.horizontalLayout_4 = QHBoxLayout()
        self.horizontalLayout_4.setObjectName(u"horizontalLayout_4")
        self.label_2 = QLabel(self.frame_4)
        self.label_2.setObjectName(u"label_2")

        self.horizontalLayout_4.addWidget(self.label_2)

        self.combo_CarsPlate = QComboBox(self.frame_4)
        self.combo_CarsPlate.setObjectName(u"combo_CarsPlate")

        self.horizontalLayout_4.addWidget(self.combo_CarsPlate)


        self.verticalLayout_2.addLayout(self.horizontalLayout_4)

        self.horizontalLayout_3 = QHBoxLayout()
        self.horizontalLayout_3.setObjectName(u"horizontalLayout_3")
        self.label_3 = QLabel(self.frame_4)
        self.label_3.setObjectName(u"label_3")

        self.horizontalLayout_3.addWidget(self.label_3)

        self.line_CarsInKilometer = QLineEdit(self.frame_4)
        self.line_CarsInKilometer.setObjectName(u"line_CarsInKilometer")

        self.horizontalLayout_3.addWidget(self.line_CarsInKilometer)


        self.verticalLayout_2.addLayout(self.horizontalLayout_3)

        self.horizontalLayout_2 = QHBoxLayout()
        self.horizontalLayout_2.setObjectName(u"horizontalLayout_2")
        self.Btn_CarsIn = QPushButton(self.frame_4)
        self.Btn_CarsIn.setObjectName(u"Btn_CarsIn")

        self.horizontalLayout_2.addWidget(self.Btn_CarsIn)

        self.Btn_CarsOut = QPushButton(self.frame_4)
        self.Btn_CarsOut.setObjectName(u"Btn_CarsOut")

        self.horizontalLayout_2.addWidget(self.Btn_CarsOut)


        self.verticalLayout_2.addLayout(self.horizontalLayout_2)


        self.verticalLayout_4.addLayout(self.verticalLayout_2)


        self.horizontalLayout.addWidget(self.frame_4)

        self.frame_5 = QFrame(self.frame_2)
        self.frame_5.setObjectName(u"frame_5")
        self.frame_5.setFrameShape(QFrame.NoFrame)
        self.frame_5.setFrameShadow(QFrame.Raised)
        self.verticalLayout_3 = QVBoxLayout(self.frame_5)
        self.verticalLayout_3.setObjectName(u"verticalLayout_3")
        self.table_CarsData = QTableWidget(self.frame_5)
        if (self.table_CarsData.columnCount() < 4):
            self.table_CarsData.setColumnCount(4)
        __qtablewidgetitem = QTableWidgetItem()
        self.table_CarsData.setHorizontalHeaderItem(0, __qtablewidgetitem)
        __qtablewidgetitem1 = QTableWidgetItem()
        self.table_CarsData.setHorizontalHeaderItem(1, __qtablewidgetitem1)
        __qtablewidgetitem2 = QTableWidgetItem()
        self.table_CarsData.setHorizontalHeaderItem(2, __qtablewidgetitem2)
        __qtablewidgetitem3 = QTableWidgetItem()
        self.table_CarsData.setHorizontalHeaderItem(3, __qtablewidgetitem3)
        self.table_CarsData.setObjectName(u"table_CarsData")
        self.table_CarsData.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.table_CarsData.setTabKeyNavigation(False)
        self.table_CarsData.setProperty("showDropIndicator", False)
        self.table_CarsData.setDragDropOverwriteMode(False)
        self.table_CarsData.setSelectionMode(QAbstractItemView.SingleSelection)
        self.table_CarsData.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.table_CarsData.horizontalHeader().setStretchLastSection(True)

        self.verticalLayout_3.addWidget(self.table_CarsData)


        self.horizontalLayout.addWidget(self.frame_5)


        self.verticalLayout.addWidget(self.frame_2)

        self.frame_3 = QFrame(self.centralwidget)
        self.frame_3.setObjectName(u"frame_3")
        self.frame_3.setMinimumSize(QSize(0, 65))
        self.frame_3.setMaximumSize(QSize(16777215, 65))
        self.frame_3.setFrameShape(QFrame.NoFrame)
        self.frame_3.setFrameShadow(QFrame.Raised)

        self.verticalLayout.addWidget(self.frame_3)

        MainWindow.setCentralWidget(self.centralwidget)

        self.retranslateUi(MainWindow)

        QMetaObject.connectSlotsByName(MainWindow)
    # setupUi

    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(QCoreApplication.translate("MainWindow", u"MainWindow", None))
        self.label.setText(QCoreApplication.translate("MainWindow", u"ComboDbAdd : ", None))
        self.label_4.setText(QCoreApplication.translate("MainWindow", u"Kilometre : ", None))
        self.Btn_CarsDbAdd.setText(QCoreApplication.translate("MainWindow", u"ADD", None))
        self.label_2.setText(QCoreApplication.translate("MainWindow", u"Car Plate : ", None))
        self.label_3.setText(QCoreApplication.translate("MainWindow", u"New KM :", None))
        self.Btn_CarsIn.setText(QCoreApplication.translate("MainWindow", u"CAR OUT", None))
        self.Btn_CarsOut.setText(QCoreApplication.translate("MainWindow", u"CAR IN", None))
        ___qtablewidgetitem = self.table_CarsData.horizontalHeaderItem(0)
        ___qtablewidgetitem.setText(QCoreApplication.translate("MainWindow", u"CAR PLATE", None));
        ___qtablewidgetitem1 = self.table_CarsData.horizontalHeaderItem(1)
        ___qtablewidgetitem1.setText(QCoreApplication.translate("MainWindow", u"CAR OUT KM", None));
        ___qtablewidgetitem2 = self.table_CarsData.horizontalHeaderItem(2)
        ___qtablewidgetitem2.setText(QCoreApplication.translate("MainWindow", u"CAR IN KM", None));
        ___qtablewidgetitem3 = self.table_CarsData.horizontalHeaderItem(3)
        ___qtablewidgetitem3.setText(QCoreApplication.translate("MainWindow", u"ID", None));
    # retranslateUi

I would appreciate it if you could enlighten me on how to solve this problem that I have been working on and how I should proceed.