sqlite3 or mysqldb?

[Paul Boddie]

John Salerno wrote:
> I did a little experimentation with MySQL, and yesterday I was reading
> up on SQLite. Since they both use the SQL language, does this mean that
> the queries you write will be the same for both modules?

They should be, but database system producers tend to enjoy varying the
syntax for their own reasons.

> I'm sure there are slight differences for how you connect to DBs, but since they both
> use the same DB API 2.0, and both use SQL, I was wondering how easily
> you could 'switch' them out if you needed to go from one to the other.

If you write using a conservative, standardised dialect of SQL, you
should be able to move between database systems without too many
difficulties. The first challenge, then, is to make sure you're aware
of what is standard and what the vendor has made up. Although MySQL 5.x
supports much more of the relevant standards than previous release
series, the manuals are very bad at telling you what they've made up
and what actually works on other systems. I therefore recommend that
you also consult other database system manuals, notably the PostgreSQL
manual which I have found to be more coherent.

> (I know there are slight differences between the two in terms of SQL
> commands understood, but I'm mainly referring to the most important
> things, like simply accessing and changing DB information.)

There's plenty of scope for writing non-standard SQL even in the most
common operations. Moreover, defining tables can be awkward because the
set of supported data types and the names used can vary in a seemingly
unnecessary fashion between systems.

> I was using mysqldb just because MySQL seems to be a pretty big
> standard, but now that sqlite3 is coming with Python 2.5, I might
> switch, since it seems to be easier to use.

You can consider MySQL a pseudostandard, but ignoring the actual SQL
standards will cause you difficulties if you decide you want to adopt a
different kind of database system later on. With respect to
portability, I've found sqlite3 and PostgreSQL to be surprisingly
compatible with regard to the SQL both database systems support, and I
can certainly recommend that combination wholeheartedly.

Paul