sqlite3 or mysqldb?

Discussion in 'Python' started by John Salerno, Aug 17, 2006.

  1. John Salerno

    John Salerno Guest

    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.
     
    John Salerno, Aug 17, 2006
    #1
    1. Advertising

  2. John Salerno

    Paul Boddie Guest

    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
     
    Paul Boddie, Aug 17, 2006
    #2
    1. Advertising

  3. John Salerno

    Guest

    > 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
     
    , Aug 17, 2006
    #3
  4. John Salerno

    Jarek Zgoda Guest

    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.

    --
    Jarek Zgoda
    http://jpa.berlios.de/
     
    Jarek Zgoda, Aug 17, 2006
    #4
  5. John Salerno

    John Salerno Guest

    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.
     
    John Salerno, Aug 18, 2006
    #5
  6. John Salerno

    Ravi Teja Guest

    > 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.
     
    Ravi Teja, Aug 18, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Pratt

    sqlite3 decode error

    David Pratt, Nov 8, 2005, in forum: Python
    Replies:
    0
    Views:
    351
    David Pratt
    Nov 8, 2005
  2. David Pratt

    Re: sqlite3 decode error

    David Pratt, Nov 8, 2005, in forum: Python
    Replies:
    0
    Views:
    565
    David Pratt
    Nov 8, 2005
  3. Replies:
    2
    Views:
    444
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    494
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    258
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page