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 -- http://mail.python.org/mailman/listinfo/python-list
