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

Search Discussions

  • Paul Boddie at Aug 17, 2006 at 3:35 pm

    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
  • John Salerno at Aug 18, 2006 at 2:25 pm

    Paul Boddie wrote:

    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.
  • Andychambers2002 at Aug 17, 2006 at 7:58 pm

    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 at Aug 17, 2006 at 8:40 pm

    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.
  • Ravi Teja at Aug 18, 2006 at 8:35 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedAug 17, '06 at 3:23p
activeAug 18, '06 at 8:35p
posts6
users5
websitepython.org

People

Translate

site design / logo © 2022 Grokbase