MySQL vrs SQLite

[richard]

Michael wrote:
> I'm considering changing a Python program of mine, that uses MySQL as
> it's db backend, to use SQLite. I'm mostly wanting something that can
> make my program a complete package without requiring the user install
> and configure an external db program. Has anyone experience with these?

Yes, my project, the Roundup Issue Tracker (http://roundup.sf.net) has
interfaces to both MySQL and SQLite (and postgresql, metakit, anydbm, ...) 

In short, sqlite is a *very* capable little database, as long as it only
ever has one user. More than one user, and it'll block access so only one
user may access it at a time.

It scales very well - better than MySQL, and about equal with postgresql as
far as I can tell. See:

http://www.mechanicalcat.net/richard/log/Python/Roundup_benchmark_time_again

for some comparative benchmarks.


> They both follow the same DB-API so the code should be pretty easy to
> port, right?

In theory, yes. In practise, the DB-API layer simple can't hide the various
little inconsistencies between databases (usually datatypes and incomplete
SQL implementations). The Roundup code includes large "common" interface
which covers 95% of its generic RDBMS interface, and then individual
modules for each of the specific RDBMSes. Postgresql is the thinnest layer,
then SQLite and then MySQL deviates the most from the common
implementation.


> Any draw backs to making this change? 

SQLite has some limitations on the SQL it implements. Then again, so does
MySQL. The SQLite website has a concise page indicating the unimplement SQL
features. ALTER TABLE is about the most annoying omission. On the other
hand, MySQL doesn't support sub-selects, but sqlite does.

SQLite treats all data as strings, but note that it does some internal
"typecasting" such that a column of numbers will be sorted numerically. You
will need to implement your own data conversion though. It's usually as
trivial as a simple mapping containing conversion functions like (from the
Roundup source):

    sql_to_hyperdb_value = {
        hyperdb.String : str,
        hyperdb.Date   : lambda x: date.Date(str(x)),
        hyperdb.Link   : str,
        hyperdb.Interval  : date.Interval,
        hyperdb.Password  : lambda x: password.Password(encrypted=x),
        hyperdb.Boolean   : int,
        hyperdb.Number    : rdbms_common._num_cvt,
    }


     Richard