lundi 13 juillet 2015

Speed up SQLite select with Python and wx.ListCtrl

I'm using Python 2.7.5+ on Kubuntu 13.10 with wx 2.8.12.1.

I have not big database (about 3150 rows) where I have only a single table of employees (firstname, lastname, address, phone, email). In my application I store it in SQLite database and use wx.ListCtrl to show rows to user.

However, loading such database to wx.ListCtrl takes ages (more than about 10 seconds I think). The question is: is it possible to improve the select speed?

Firstly, in my Python code, I create a table:

def createEmployeesTable(self):
        connection = sqlite.connect(self.dbname)
        try:
            with connection:
                cursor = connection.cursor()
                sql = '''\
                    CREATE TABLE IF NOT EXISTS Employees (
                        ID INTEGER PRIMARY KEY NOT NULL,
                        FIRSTNAME TEXT,
                        LASTNAME TEXT,
                        EMAIL TEXT,
                        ADDRESS TEXT,
                        PHONE TEXT)
                '''
                cursor.execute(sql)
        finally:
            connection.close()

Then, I insert some employees' data and finally do the select to show data in popup window with wx.ListCtrl on it:

def getAllEmployees(self):
        employees = []
        connection = sqlite.connect(self.dbname)
        try:
            with connection:
                cursor = connection.cursor()
                sql = "SELECT firstname, lastname, email, phone, address FROM Employees"
                cursor.execute(sql)
                for (firstname, lastname, email, phone, address, ) in cursor:
                    employees.append(Employee(firstname, lastname, email, phone, address))
        finally:
            connection.close()
        return employees

Many thanks for help.

Aucun commentaire:

Enregistrer un commentaire