如何将具有一对多关系的 2 个 SQLite 表显示为一个 QTableWidget [英] How to display 2 SQLite tables with one to many relation into one QTableWidget
问题描述
我在 Db 中有以下两个表,具有一对多的关系:
I have following two tables in Db, with one to many relationship:
self.c.execute("CREATE TABLE IF NOT EXISTS dispatch("
"id_dispatch INTEGER PRIMARY KEY AUTOINCREMENT,"
"d_date TEXT,"
"d_mill TEXT,"
"d_buyer TEXT,"
"d_addFreight INTEGER,"
"d_subFreight INTEGER,"
"d_amount INTEGER,"
"d_cd INTEGER,"
"d_comm INTEGER,"
"d_netAmount INTEGER)")
self.c.execute("CREATE TABLE IF NOT EXISTS dispatch_products("
"dispatch_id INTEGER NOT NULL REFERENCES DISPATCH(id_dispatch),"
"product INTEGER,"
"quantity INTEGER,"
"rate INTEGER)")
我正在尝试将这两个表合二为一并显示在一个表中,使用以下代码:
I'm trying to combine these two table into one and display in one table, using following code:
def loadTable(self):
connection = sqlite3.connect('main_databaseSample.db')
query = "CREATE VIEW myview as SELECT d_buyer," \
"d_date, product, quantity, rate, d_amount, d_addFreight, d_subFreight, d_netAmount from " \
"dispatch, dispatch_products WHERE dispatch.id_dispatch = dispatch_products.dispatch_id"
query = "SELECT * FROM myview"
result = connection.execute(query)
self.tableWidget.setRowCount(0)
for row_number, row_data in enumerate(result):
self.tableWidget.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.tableWidget.setItem(row_number, column_number,QtWidgets.QTableWidgetItem(str(data)))
connection.close()
问题:包含多个产品的订单重复出现.如何消除具有一对多关系的多个条目?
PROBLEM: Orders which have multiple products are repeated. How to eliminate multiple entries which have one to many relationship?
样本数据:
dispatch table:
ID date mill buyer addF subF amount cd comm netAmount
1 15-10 abc A 0 0 100 0 0 100
2 16-10 xyz B 0 0 200 0 0 200
dispatch_products table:
Dispatch_ID product qty rate
1 M 40 1
1 A 60 1
2 S 50 4
Code Output:
buyer date product quantity rate amount addFreight subFreight NetAmount
A 15-10 M 40 1 100 0 0 100
A 15-10 A 60 1 100 0 0 100
B 16-10 S 50 4 200 0 0 200
Expected Output:
buyer date product quantity rate amount addFreight subFreight NetAmount
A 15-10 M 40 1 100 0 0 100
A 60 1
B 16-10 S 50 4 200 0 0 200
推荐答案
任务是检测是否有相同的元素,我们必须向 View 添加一个元素,dispatch_id 或 id_dispatch 并让它们排序.检测到 dispatch_id 的变化后,使用 setSpan()
加入必要的行,如下所示:
The task is to detect that there are equal elements for it we must add one more element to the View, the dispatch_id or id_dispatch and get them sorted. After detecting the change of dispatch_id join the necessary rows using setSpan()
as shown below:
from PyQt5 import QtCore, QtWidgets
import sqlite3
class Widget(QtWidgets.QWidget):
def __init__(self, parent=None):
super(Widget, self).__init__(parent)
button = QtWidgets.QPushButton("Press me")
button.clicked.connect(self.load_table)
self.tableWidget = QtWidgets.QTableWidget()
lay = QtWidgets.QVBoxLayout(self)
lay.addWidget(button)
lay.addWidget(self.tableWidget)
@QtCore.pyqtSlot()
def load_table(self):
self.tableWidget.setRowCount(0)
self.tableWidget.setColumnCount(9)
connection = sqlite3.connect('main_databaseSample.db')
connection.execute("DROP VIEW IF EXISTS myview;")
query = "CREATE VIEW myview as SELECT dispatch_id, d_buyer," \
"d_date, product, quantity, rate, d_amount, d_addFreight, d_subFreight, d_netAmount from " \
"dispatch, dispatch_products WHERE dispatch.id_dispatch = dispatch_products.dispatch_id " \
"ORDER BY dispatch_id ASC;"
connection.execute(query)
query = "SELECT * FROM myview"
result = connection.execute(query)
last_id = -1
start_row = 0
for row, row_data in enumerate(result):
self.tableWidget.insertRow(row)
current_id, *other_values = row_data
for col, data in enumerate(other_values):
it = QtWidgets.QTableWidgetItem(str(data))
self.tableWidget.setItem(row, col, it)
if last_id != current_id and last_id != -1:
self.apply_span(start_row, row - start_row)
start_row = row
last_id = current_id
if start_row != row:
self.apply_span(start_row, self.tableWidget.rowCount() - start_row)
def apply_span(self, row, nrow):
if nrow <= 1:
return
for c in (0, 1, 5, 6, 7, 8):
self.tableWidget.setSpan(row, c, nrow, 1)
for r in range(row+1, row+nrow):
t = self.tableWidget.takeItem(r, c)
del t
if __name__ == '__main__':
import sys
app = QtWidgets.QApplication(sys.argv)
w = Widget()
w.show()
sys.exit(app.exec_())
添加更多数据我们可以看到如下结果
Adding more data we can see the following result
这篇关于如何将具有一对多关系的 2 个 SQLite 表显示为一个 QTableWidget的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!