python-3.xsqlitepyqt5qsqlqueryqsqldatabase
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_())
- Missing sqlite3 after Python3 compile on Linux
- Type hints for pytests fixtures that return methods
- How to reduce the dimension of CSV file?
- pip does not recognize "install"
- Why not to create virtual environment outside home directory (error: externally-managed-environment on virtualenv in pip python3 package installation)
- How can I use newline '\n' in an f-string to format output?
- Getting stuck in Python while loop
- CashApp Payment Method Not Rendering in Django with Adyen Integration
- What's the difference between `raw_input()` and `input()` in Python 3?
- How to input 2 integers in one line in Python?
- How to install ipython in Debian 12 or Ubuntu 23.04 where pip3 prevents installation due to "externally-managed-environment"?
- Pythonic way to find maximum absolute value of list
- Why does the following run into an infinite recursion error?
- Fast way to find all connected subgraphs of given size in Python?
- Using Haystack and Ollama . ModuleNotFoundError: No module named 'ollama'
- TimescaleDB: Is it possible to call 'create_hypertable' from Python?
- Access the Alexa Shopping and To-Do Lists with Python3 request module
- How to change the button colors onclick event?
- hash method implementation not working along set() [Python]
- How can I make the layout of the interface fit other screen resolutions,?
- Python class function return super()
- Unable to import Pandas Profiling
- Django one of 2 fields must not be null
- problem with insert GIF in python(TKinter)?
- Pythonic way to hex dump files
- Error <Figure size 1000x600 with 1 Axes> even with plt.figure () before plt.plot
- On GitHub actions, "pip install playsound" failed with the "OSError: could not get source code" error
- Raspberry Pi (Python) - button press (and hold) to run a script loop
- how to create a venv with a different python version
- How does defining what a function will return work