CODEWITHSUNDEEP
pythonsqlpyqt4qtableviewqsqlquery
royatirek
royatirek

Reputation: 2769

How to show contents from a database file into console in pyqt

I am using PyCharm editor 2017.3. My python version is 3.4
My main aim is to load contents from a database file into the tableView but before trying it I am trying to load contents from a test database file to python console.

Here I using inbuilt plugin of PyQt4 known as QSQL

from PyQt4.QtGui import *
site_pack_path = "C:\\Python34\\Lib\\site-packages"
QApplication.addLibraryPath('{0}\\PyQt4\\plugins'.format(site_pack_path))
from PyQt4.QtSql import *
import sys

Then I have created a database file and trying to load contents from it into console.All the below mention things are written in same function.

db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('sports.db')

if not db.open():
    QMessageBox.critical("Cannot open database")

    return False

query = QSqlQuery()

query.exec_("create table sportsmen(id int primary key, "
            "firstname varchar(20), lastname varchar(20))")

query.exec_("insert into sportsmen values(101, 'Roger2', 'Federer')")
query.exec_("insert into sportsmen values(102, 'Christiano', 'Ronaldo')")
query.exec_("insert into sportsmen values(103, 'Ussain', 'Bolt')")
query.exec_("insert into sportsmen values(104, 'Sachin', 'Tendulkar')")
query.exec_("insert into sportsmen values(105, 'Saina', 'Nehwal')")





print(query.exec_("SELECT * FROM sportsmen"))
return True

Though database file named sports.db is created and even populated with test data but I cannot get contents from there into my console.
Console shows no error and prints True

Upvotes: 0

Views: 570

Answers (1)

eyllanesc
eyllanesc

Reputation: 243993

According to the docs:

bool QSqlQuery.exec_(self, QString query)

Executes the SQL in query. Returns true and sets the query state to active if the query was successful; otherwise returns false. The query string must use syntax appropriate for the SQL database being queried (for example, standard SQL).

[...]

That is, it returns the state of the query, if we want to obtain the values we must use query.value() passing it the column that we want to obtain(), besides the next() method is true as long as there is data.

if query.exec_("SELECT * FROM sportsmen"):
    rec = query.record()
    while query.next():
        for ix in range(rec.count()):
            val = query.value(ix)
            print(rec.fieldName(ix), val)
else:
    print(query.lastError().text())

Output:

id 101
firstname Roger2
lastname Federer
id 102
firstname Christiano
lastname Ronaldo
id 103
firstname Ussain
lastname Bolt
id 104
firstname Sachin
lastname Tendulkar
id 105
firstname Saina
lastname Nehwal

Upvotes: 1

Related Questions