0

I am fetching data from a SQL table where one column header uses a slash - it is named "Massflow_g/h". Unfortunately I am not the owner of the table and for reasons the table headers cannot be changed any more. With QSqlQuery I get the data, with QSqlQueryModel I display the data inside a QTableView. I only want specific columns of the table displayed - thus I need to address and select this specific column in my SQL query. But with the slash in combination with PyQt5 it seems impossible... This issue was asked in a similar way several times in the past - but I still could not find a working solution. Please, can someone help here?

My code (w/o the part to access the database) is below. From the db I get a first table displayed. From this I choose a single line. Clicking the "confirm" button displays me then the detailed data for this specific line (detail data are stored in a second table - the ID is the link between the first and the second table).

import sys

from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import Qt, pyqtSlot, QRect

from PyQt5.QtCore import QSize, Qt
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery, QSqlQueryModel

from PyQt5.QtWidgets import QApplication, QMainWindow, QTableView
from PyQt5.QtWidgets import QWidget, QVBoxLayout, QMessageBox, QPushButton

#####
# missing part - here the db is opened via db = QSqlDatabase.addDatabase(...) & db.open
#####

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        self.UpperTable = QTableView()
        self.LowerTable = QTableView()

        self.ConfirmButton = QPushButton("Confirm Choice")
        self.ConfirmButton.clicked.connect(self.ReadChosenLine)

        layout = QVBoxLayout()
        layout.addWidget(self.UpperTable)
        layout.addWidget(self.ConfirmButton)
        layout.addWidget(self.LowerTable)
    
        self.model1 = QSqlQueryModel()
        self.UpperTable.setModel(self.model1)

        self.model2 = QSqlQueryModel()
        self.LowerTable.setModel(self.model2)  

        query1 = QSqlQuery("SELECT "
                           "Compressor_ID, Model, Generation "
                           "FROM dbo.BasicDataTable", db=db)     
        self.model1.setQuery(query1)
 

        widget = QWidget()
        widget.setLayout(layout)
        self.setCentralWidget(widget)
                         

    def ReadChosenLine (self, ConfirmButton):
        Zeilennummern = self.UpperTable.selectionModel().selectedRows() 
        for Zähler in Zeilennummern:
            PrimaryKey = self.UpperTable.model().data(self.UpperTable.model().index(Zähler.row(), 0))
            self.TestDetails(PrimaryKey)


    def TestDetails(self, PrimaryKey):
        query2 = QSqlQuery("SELECT *"
                           ##  "Tevap_super_C "# , Massflow_g/h " 
                           "FROM dbo.MeasurementData "
                           "WHERE Parts_ID = '{PrimaryKey}'""".format(PrimaryKey=PrimaryKey), db=db)
        self.model2.setQuery(query2)

        TableHeader = []
        for i in range(self.model2.columnCount()):
            TableHeaderSingleColumn = self.model2.headerData(i, QtCore.Qt.Horizontal)
            TableHeader.append(TableHeaderSingleColumn)
        print(TableHeader)
        # here we get all headers - the problematic one is shown in the screenshot

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

Screenshot of the problematic header: enter image description here

Changing the SQL query to:

        query2 = QSqlQuery("SELECT "
                           "Tevap_super_C "# , Massflow_g/h " 
                           "FROM dbo.MeasurementData 

my query still works, only this column "Tevap_super_C" is displayed:

enter image description here

Not working versions (trials to display the column "Massflow_g/h":

        query2 = QSqlQuery("SELECT "
                           "Tevap_super_C , Massflow_g/h " 
        query2 = QSqlQuery("SELECT "
                           "Tevap_super_C , Massflow_g\/h " 

also it does not work with two backslashes....

Then this does not work:

        massflow = r'Massflow_g/h'
        query2 = QSqlQuery("SELECT "
                           "Tevap_super_C , massflow "# Massflow_g\\/h " 

Next one is interesting, it writes "massflow" into every row:

        massflow = r'Massflow_g/h'
        query2 = QSqlQuery("SELECT "
                           "Tevap_super_C , 'massflow' " # Massflow_g\\/h " 
                           "FROM dbo.PD_Measured "

enter image description here and so on...

I already used "select *" and then "self.table.setColumnHidden" - to display only the columns of interest - but then my further program does not work (I need to give each header of my table a filter button). So this workaround is not helpful, too...

3
  • Try using quotes around that header. I'm not sure that single quotes would work as double should, in that case either escape the quotes (\"Massflow_g/h\"), or use single quotes for the string. Commented Sep 16, 2024 at 14:15
  • Hi stackoverflow.com/users/2001654/musicamante, yes, double quotes before and after the header indeed do the trick! Only that the double quotes need to be escaped, thus the construct: backslash + double quote -- header -- backslash + double quote (as you have shown above) Thanks a lot for the help! To make it complete: single quotes with backslash ("Tevap_super_C , \'Massflow_g/h\' " ) fill the table with "Massflow_g/h" - so this one does not work. Pure single quotes ("Tevap_super_C , 'Massflow_g/h' " ) also do not work - same effect as above Commented Sep 16, 2024 at 15:25
  • As said, you could also just switch the quote mode: use simple quotes for the string, and plain double quotes for the column: 'SELECT Tevap_super_C , "Massflow_g/h"'. Commented Sep 16, 2024 at 19:10

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.