CODEWITHSUNDEEP
pythonpyqtqsqlquery
Sarah
Sarah

Reputation: 161

PyQt: How to get the row values from a select query

I am creating an application in PyQt where I want to create a list, and the list items are the result of an SQL query. I am trying to do this from the following code:

import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *

def main():  
    app  = QApplication(sys.argv)
    win=QWidget()
    layout=QHBoxLayout()
    db = QSqlDatabase.addDatabase("QODBC")
    win.setWindowTitle("Connect to MSSQL Database Example")   
    db.setHostName('LAPTOP-B79DRPA3')
    db.setDatabaseName('local')
    db.open()
    if (db.open()==False):     
      QMessageBox.critical(None, "Database Error",
                db.lastError().text())   

    query = QSqlQuery () 
    query.exec_  ("select lydelse, bransch from [Sarah].[dbo].fraga")

    listWidget = QListWidget()
    index=0
    while (query.next()):
        listWidget.addItem(str(query.value(index)))
        index = index+1
    listWidget.setSelectionMode(QAbstractItemView.ExtendedSelection)
    layout.addWidget(listWidget)
    win.setLayout(layout)
    win.show()
    return app.exec_()

if __name__ == '__main__':
    main()

But what is happening is here is that the output is a list with items from different columns. What I want is that I should be able to select a column and add all the row items to the list as list-items :

index=0
while (query.next()):
    listWidget.addItem(str(query.value(index)))
    index = index+1

Upvotes: 2

Views: 7207

Answers (1)

ekhumoro
ekhumoro

Reputation: 120598

When you use a select statement with explicit column names, the columns/fields in the query result will be in the same order, and numbered from left to right, starting at zero. Thus, in the statement:

select lydelse, bransch from [Sarah].[dbo].fraga

the lydelse column is 0 and the bransch column is 1. So to get all the values from the lydelse column, you would simply do:

query = QSqlQuery() 
query.exec_("select lydelse, bransch from [Sarah].[dbo].fraga")
while query.next():
    print(query.value(0))

However, if you used a select * statement, this may not work, because the columns would be in no particular order. In that case, your code should look like this:

query = QSqlQuery() 
query.exec_("select * from [Sarah].[dbo].fraga"):
index = query.record().indexOf('lydelse')
while query.next():
    print(query.value(index))

Upvotes: 6

Related Questions