How to concatenate three feilds records from one sqlite table and insert that in to one feild of the second table?

Question

Below is my example code:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
import sys


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.details_tableview = QtWidgets.QTableView(self.centralwidget)
        self.details_tableview.setGeometry(QtCore.QRect(30, 220, 381, 271))
        self.details_tableview.setObjectName("details_tableview")
        self.full_tableview = QtWidgets.QTableView(self.centralwidget)
        self.full_tableview.setGeometry(QtCore.QRect(440, 220, 341, 271))
        self.full_tableview.setObjectName("full_tableview")
        self.title_combo = QtWidgets.QComboBox(self.centralwidget)
        self.title_combo.setGeometry(QtCore.QRect(164, 41, 69, 20))
        self.title_combo.setObjectName("title_combo")
        self.title_combo.addItem("")
        self.title_combo.setItemText(0, "")
        self.title_combo.addItem("")
        self.title_combo.addItem("")
        self.save_btn = QtWidgets.QPushButton(self.centralwidget)
        self.save_btn.setGeometry(QtCore.QRect(100, 180, 75, 23))
        self.save_btn.setObjectName("save_btn")
        self.first_name_line = QtWidgets.QLineEdit(self.centralwidget)
        self.first_name_line.setGeometry(QtCore.QRect(164, 67, 133, 20))
        self.first_name_line.setObjectName("first_name_line")
        self.lastNameLabel = QtWidgets.QLabel(self.centralwidget)
        self.lastNameLabel.setGeometry(QtCore.QRect(102, 93, 50, 16))
        self.lastNameLabel.setObjectName("lastNameLabel")
        self.last_name_line = QtWidgets.QLineEdit(self.centralwidget)
        self.last_name_line.setGeometry(QtCore.QRect(164, 93, 133, 20))
        self.last_name_line.setObjectName("last_name_line")
        self.designationLabel = QtWidgets.QLabel(self.centralwidget)
        self.designationLabel.setGeometry(QtCore.QRect(102, 145, 56, 16))
        self.designationLabel.setObjectName("designationLabel")
        self.mobileLabel = QtWidgets.QLabel(self.centralwidget)
        self.mobileLabel.setGeometry(QtCore.QRect(102, 119, 30, 16))
        self.mobileLabel.setObjectName("mobileLabel")
        self.desig_line = QtWidgets.QLineEdit(self.centralwidget)
        self.desig_line.setGeometry(QtCore.QRect(164, 145, 133, 20))
        self.desig_line.setObjectName("desig_line")
        self.mobile_line = QtWidgets.QLineEdit(self.centralwidget)
        self.mobile_line.setGeometry(QtCore.QRect(164, 119, 133, 20))
        self.mobile_line.setObjectName("mobile_line")
        self.firstNameLabel = QtWidgets.QLabel(self.centralwidget)
        self.firstNameLabel.setGeometry(QtCore.QRect(102, 67, 51, 16))
        self.firstNameLabel.setObjectName("firstNameLabel")
        self.tilteLabel = QtWidgets.QLabel(self.centralwidget)
        self.tilteLabel.setGeometry(QtCore.QRect(102, 41, 20, 16))
        self.tilteLabel.setObjectName("tilteLabel")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.mobile_line.setValidator(QIntValidator())
        self.save_btn.clicked.connect(self.save_data)

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

        db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("employee.db")
        if db.open():
            query = QSqlQuery()
            query.exec_("CREATE TABLE IF NOT EXISTS details(title TEXT, first_name TEXT, last_name TEXT, mobile TEXT, designation TEXT)")
            query.exec_("CREATE TABLE IF NOT EXISTS view(full name TEXT, mobile TEXT, designation TEXT)")

        self.emp_model = QtSql.QSqlTableModel()
        self.emp_model.setTable("details")
        self.emp_model.select()
        self.details_tableview.setModel(self.emp_model)

        self.view_model = QtSql.QSqlTableModel()
        self.view_model.setTable("view")
        self.view_model.select()
        self.full_tableview.setModel(self.view_model)


    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.title_combo.setItemText(1, _translate("MainWindow", "Mr."))
        self.title_combo.setItemText(2, _translate("MainWindow", "Ms."))
        self.save_btn.setText(_translate("MainWindow", "Save"))
        self.lastNameLabel.setText(_translate("MainWindow", "Last Name"))
        self.designationLabel.setText(_translate("MainWindow", "Designation"))
        self.mobileLabel.setText(_translate("MainWindow", "Mobile"))
        self.firstNameLabel.setText(_translate("MainWindow", "First Name"))
        self.tilteLabel.setText(_translate("MainWindow", "Tilte"))

    def save_data(self):
        data_ = [self.title_combo.currentText(), self.first_name_line.text(), self.last_name_line.text(), self.mobile_line.text(), self.desig_line.text()]
        print(data_)
        r = self.emp_model.record()
        r.setValue("title", self.title_combo.currentText())
        r.setValue("first_name", self.first_name_line.text())
        r.setValue("last_name", self.last_name_line.text())
        r.setValue("mobile", self.mobile_line.text())
        r.setValue("designation", self.desig_line.text())
        
        self.emp_model.insertRecord(-1, r)
        self.emp_model.select()
        
if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

I am using QtSql.QSqlTableModel, QSqlQuery(), QTableView. Is it possible to concatenate required columns data from one table into second table column. For example in the first table there are five columns and i need to concatenate "title", "First name " and "last name" as "full name" and that should be inserted in to as"full name" column into the second table. And need to insert "mobile" column values from first table to "mobile" column of second table And "Designation" column from first table to "designation" column of second table. If it is possible, how to do? If there is any solution that would be helpful. Please find the Image for reference.

Answer 1

If you want to create a binding between the rows of the tables then a possible solution is to create triggers where before the modification (insertion, update or deletion) of a table the other table is modified.

from PyQt5 import QtWidgets, QtSql

queries = (
    """
CREATE TABLE IF NOT EXISTS details(
    title TEXT, 
    first_name TEXT, 
    last_name TEXT, 
    mobile TEXT, 
    designation TEXT
)""",
    """
CREATE TABLE IF NOT EXISTS view(
    full name TEXT, 
    mobile TEXT, 
    designation TEXT)
""",
    """
CREATE TRIGGER IF NOT EXISTS after_insert_details 
   AFTER INSERT 
   ON details
BEGIN
    INSERT INTO view(full, mobile, designation)
         VALUES(NEW.first_name || " " ||New.last_name , NEW.mobile, NEW.designation);
END;""",
    """
CREATE TRIGGER IF NOT EXISTS after_update_details 
   AFTER UPDATE 
   ON details
BEGIN
    UPDATE view
     SET full = NEW.first_name || " " ||New.last_name , mobile = NEW.mobile, designation = NEW.designation
     WHERE NEW.rowid = rowid;
END;
""",
"""
CREATE TRIGGER IF NOT EXISTS after_delete_details 
    AFTER DELETE 
    ON details
BEGIN 
    DELETE FROM view 
    WHERE OLD.rowid = rowid;
END;
"""
)


def create_connection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("employee.db")
    if not db.open():
        print(db.lastError().text())
        return False
    return True


class ReadOnlyDelegate(QtWidgets.QStyledItemDelegate):
    def createEditor(self, parent, option, index):
        pass


class MainWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.title_combo = QtWidgets.QComboBox()
        self.title_combo.addItems(["Mr.", "Ms."])
        self.first_name_line = QtWidgets.QLineEdit()
        self.last_name_line = QtWidgets.QLineEdit()
        self.mobile_line = QtWidgets.QLineEdit()
        self.desig_line = QtWidgets.QLineEdit()

        self.save_btn = QtWidgets.QPushButton("Save")

        self.detail_view = QtWidgets.QTableView()
        self.detail_model = QtSql.QSqlTableModel()
        self.detail_model.setTable("details")
        self.detail_model.select()
        self.detail_view.setModel(self.detail_model)

        self.table_view = QtWidgets.QTableView()
        self.view_model = QtSql.QSqlTableModel()
        self.view_model.setTable("view")
        self.view_model.select()
        self.table_view.setModel(self.view_model)
        self.table_view.setItemDelegate(ReadOnlyDelegate(self.table_view))

        form_widget = QtWidgets.QWidget()
        form_layout = QtWidgets.QFormLayout(form_widget)
        form_layout.addRow("Title", self.title_combo)
        form_layout.addRow("First Name", self.first_name_line)
        form_layout.addRow("Last Name", self.last_name_line)
        form_layout.addRow("Mobile", self.mobile_line)
        form_layout.addRow("Designation", self.desig_line)
        form_layout.addRow(self.save_btn)
        form_widget.setFixedSize(form_widget.sizeHint())

        central_widget = QtWidgets.QWidget()
        self.setCentralWidget(central_widget)

        hlay1 = QtWidgets.QHBoxLayout()
        hlay1.addWidget(form_widget)
        hlay1.addStretch()

        hlay2 = QtWidgets.QHBoxLayout()
        hlay2.addWidget(self.detail_view)
        hlay2.addWidget(self.table_view)

        vboxlayout = QtWidgets.QVBoxLayout(central_widget)
        vboxlayout.addLayout(hlay1)
        vboxlayout.addLayout(hlay2)

        self.resize(640, 480)

        self.save_btn.clicked.connect(self.insert_row)

    def insert_row(self):
        r = self.detail_model.record()
        r.setValue("title", self.title_combo.currentText())
        r.setValue("first_name", self.first_name_line.text())
        r.setValue("last_name", self.last_name_line.text())
        r.setValue("mobile", self.mobile_line.text())
        r.setValue("designation", self.desig_line.text())
        self.detail_model.insertRecord(-1, r)
        self.detail_model.select()
        self.view_model.select()


if __name__ == "__main__":
    import sys

    app = QtWidgets.QApplication(sys.argv)
    if not create_connection():
        sys.exit(-1)
    for query_str in queries:
        query = QtSql.QSqlQuery(query_str)
        if not query.exec_():
            print(query.lastError().text())
    w = MainWindow()
    w.show()
    sys.exit(app.exec_())