How to concatenate three feilds records from one sqlite table and insert that in to one feild of the second table?
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.
Solution
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_())
- Python error with request post : 'Connection aborted, timeout('The write operation timed out')
- How to determine the data type of a value in a list of list of dicts?
- Undertanding python import process (importing custom os module)
- How do I turn a Python program into a CLI without having to type python3 to execute?
- datetime.strptime() is blocking asyncio.Queue.get()
- Is there a way to get a line number from an ElementTree Element
- How to long press (Press and Hold) mouse left key using only Selenium in Python
- Changing the size and position of widgets according to window size in Kivy
- Problems with section check using configparser in Python
- what does these print(check_output(["ls", "../input"]).decode("utf8")) mean?
- have some indication for overlapping bars in seaborn histplot
- Python3 Pop Nested Json Key Give a String Path
- Prime factorization using list comprehension in Python
- Covariance matrix for circular variables?
- Python - TypedDict type hint keys
- How to add several cookies to response in FastHTML?
- `map` causing infinite loop in Python 3
- How to parse a function with ply in Python?
- Reason for no relative imports in python scripts
- SQLAlchemy Column Types As Generic (BIT Type)
- Python: Create strikethrough / strikeout / overstrike string type
- How to upload token.pickle to Google cloud run function via .zip file?
- Does python3 asyncio use a work stealing scheduler like Rust Tokio?
- Reading a pdf in AWS lambda using PyMuPDF
- Get variable to which an object is assigned from inside the object?
- Does Postgres support nested or autonomous transactions?
- Python app crashed with "PyEval_RestoreThread: the function must be called with the GIL held, but the GIL is released"
- What alternative is there to execfile in Python 3? / How to include a Python file?
- How to run a Python 3 tkinter app without opening any Terminal windows in Mac OS X?
- How to define a string method for a many to many relation in Django?