[PyQt] Extracting QSqlQuery results in PyQt4

mir amicitas amicitas at gmail.com
Wed Oct 8 20:50:44 BST 2008


I am trying to use SQLite in a PyQt4 application. I am running into a
problem where it is taking a really long time to get the data out of
the QSqlQuery object after a query is completed. Here is a code
snippet with the relevant parts:

#################################################################
        self.selected_columns = self.table_definition['track']
        select_string = ','.join(self.selected_columns)

        query_str = 'SELECT %s FROM track %s'%(select_string, where_string)

        start_time = time.clock()
        self.query.prepare(query_str)
        self.query.exec_()
        util.message('Query took:', time.clock() - start_time, 'seconds')

        print ''
        print query_str

        results = []
        header = self.selected_columns

        self.query.first()

        while self.query.isValid():
            record = [self.query.value(index).toString() for index in
range(len(header))]
            results.append(record)
            self.query.next()

        util.message('Selected', len(results), 'records.')

        util.message('Unpacking Took:', time.clock() - start_time, 'seconds')

#################################################################

While the query is quick, extracting the data takes a really long
time. Way longer than I think it should take.

Here are the results:
#################################################################
Query took: 0.00094797466213 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 16668 records.
Unpacking Took: 5.29902267251 seconds
#################################################################

#################################################################
Query took: 0.0317866355896 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM
track WHERE (album LIKE '%it%' OR title LIKE '%it%' OR artist LIKE
'%it%')
Selected 3262 records.
Unpacking Took: 1.4495204451 seconds
#################################################################

I also to tried just cycling through the code instead of actually
extracting the data:

#################################################################
while self.query.next():
        pass
#################################################################

Results:
#################################################################
Query took: 0.171307952858 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 0 records.
Unpacking Took: 1.24349849063 seconds
#################################################################

This is certainly faster, but still this is a long time to just to
cycle though the records.

If I use a simplified extraction:
#################################################################
while self.query.next():
            record = [self.query.value(index) for index in range(len(header))]
            results.append(record)
#################################################################

Results:
#################################################################
Query took: 0.00130084772843 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 16667 records.
Unpacking Took: 3.68243214225 seconds
#################################################################

I also tried doing the loop (including string conversion) with out
appending the results. That took 4.5 seconds.

What this means is that the time break down is:
1.2 seconds just to loop through the records.
1.7 seconds to extract the data from the records.
0.8 seconds to convert to a string.
0.7 seconds to create the python list.

While I probably can't reduce the time it takes to create the python
list much, and I can avoid the string conversion, is there anyway to
reduce the looping and extracting?

I have tried using setForwardOnly() but I did not notice a difference.

Does anyone have any idea on why this is taking so long or how I can
improve things?

Thanks in advance,

-- amicitas


More information about the PyQt mailing list