sqlite3 or mysqldb?

[John Salerno]

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? 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.

(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.)

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.

(And again, I'm such an amateur programmer that really I'm using these
things just to learn them. It's not like I control my company's entire
employee records or anything.)

Thanks.

 

[Paul Boddie]

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

 

[andychambers2002]

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.

Yes and No. Sqlite takes less to configure and manage but you have to
consider your needs for concurrent processing. If memory/disk space is
no object then I would stick to mysql.

If its learning SQL that you want, you should try postgres. It has a
very
interesting "RULE" system that you can play with.

Regards,
Andy

 

[Jarek Zgoda]

John Salerno napisa³(a):

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? 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.

(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.)

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.

(And again, I'm such an amateur programmer that really I'm using these
things just to learn them. It's not like I control my company's entire
employee records or anything.)

To learn SQL SQLite should be enough - it has all the basics, just as
MySQL, while it doesn't require any server/client configuration
(encoding configuration in MySQL is real PITA). But if you want any
"serious SQL", go with any freely available *real SQL server*, like
Firebird or PostgreSQL. I'd consider Firebird, as it's pretty lightweight.

In theory, switching from one db backend to another should go without
problem (at least at ANSI SQL level), but usually requires much work, so
it's rather rare practice. While basics, like DML or DDL syntax, remain
similar, often particular backends require specific tweaks and
optimizations to get desired level of efficiency. You know, this part of
application is a bottleneck.

 

[John Salerno]

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.

Good point. I forgot that sqlite doesn't have as strict of data typing
as mysql, so that might cause some problems as well. Oh well, basically
I'm just looking for something to learn from, so it's still probably
better to go with a simpler one that I will still be able to apply to
the more complex ones if needed.

 

[Ravi Teja]

To learn SQL SQLite should be enough - it has all the basics, just as

MySQL, while it doesn't require any server/client configuration
(encoding configuration in MySQL is real PITA). But if you want any
"serious SQL", go with any freely available *real SQL server*, like
Firebird or PostgreSQL. I'd consider Firebird, as it's pretty lightweight.

Firebird can be used as an embedded database just like SQLite as well.
This gives a much more powerful database that can still be used without
the administration overhead. Aside from flexibility, the reason I
prefer FireBird is that it has much more sophisticated visual tools
available.