What will I lose or gain from switching database APIs? (from pywin32 and pysqlite to QSql)

Question

I am writing a Python (2.5) GUI Application that does the following:

• Imports from Access to an Sqlite database
• Saves ui form settings to an Sqlite database

Currently I am using pywin32 to read Access, and pysqlite2/dbapi2 to read/write Sqlite.

However, certain Qt objects don't automatically cast to Python or Sqlite equivalents when updating the Sqlite database. For example, a QDate, QDateTime, QString and others raise an error. Currently I am maintaining conversion functions.

I investigated using QSql, which appears to overcome the casting problem. In addition, it is able to connect to both Access and Sqlite. These two benefits would appear to allow me to refactor my code to use less modules and not maintain my own conversion functions.

What I am looking for is a list of important side-effects, performance gains/losses, functionality gains/losses that any of the SO community has experienced as a result from the switch to QSql.

One functionality loss I have experienced thus far is the inability to use Access functions using the QODBC driver (e.g., 'SELECT LCASE(fieldname) from tablename' fails, as does 'SELECT FORMAT(fieldname, "General Number") from tablename')

Answer 1

When dealing with databases and PyQt UIs, I'll use something similar to model-view-controller model to help organize and simplify the code.

View module

• uses/holds any QObjects that are necessary for the UI
• contain simple functions/methods for updating your QTGui Object, as well as extracting input from GUI objects

Controller module

• will perform all DB interactions
• the more complex code lives here

By using a MVC, you will not need to rely on the QT Library as much, and you will run into less problems linking QT with Python.

So I guess my suggestion is to continue using pysqlite (since that's what you are used to), but refactor your design a little so the only thing dealing with the QT libraries is the UI. From the description of your GUI, it should be fairly straightforward.