MySQL vrs SQLite

Discussion in 'Python' started by Michael, May 5, 2004.

  1. Michael

    Michael Guest

    I'm considering changing a Python program of mine, that uses MySQL as
    it's db backend, to use SQLite. I'm mostly wanting something that can
    make my program a complete package without requiring the user install
    and configure an external db program. Has anyone experience with these?
    They both follow the same DB-API so the code should be pretty easy to
    port, right? Any draw backs to making this change?
     
    Michael, May 5, 2004
    #1
    1. Advertising

  2. In article <>, Michael wrote:
    > I'm considering changing a Python program of mine, that uses MySQL as
    > it's db backend, to use SQLite. I'm mostly wanting something that can
    > make my program a complete package without requiring the user install
    > and configure an external db program. Has anyone experience with these?
    > They both follow the same DB-API so the code should be pretty easy to
    > port, right? Any draw backs to making this change?


    I haven't used SQLite, through Python or otherwise, but I think it's
    important to note nonetheless that it is supposedly a "typeless" database,
    as opposed to MySQL, which (though perhaps tolerant to a fault) conforms
    your data to the datatypes of your columns. The following paragraph from the
    SQLite web site makes me rather uncomfortable:

    """SQLite is "typeless". This means that you can store any kind of data you
    want in any column of any table, regardless of the declared datatype of that
    column. (See the one exception to this rule in section 2.0 below.) This
    behavior is a feature, not a bug. A database is suppose to store and
    retrieve data and it should not matter to the database what format that data
    is in. The strong typing system found in most other SQL engines and codified
    in the SQL language spec is a misfeature - it is an example of the
    implementation showing through into the interface. SQLite seeks to overcome
    this misfeature by allowing you to store any kind of data into any kind of
    column and by allowing flexibility in the specification of datatypes."""

    -- http://www.hwaci.com/sw/sqlite/datatypes.html

    If you are doing any type of database work where you need to deal with
    different types of data (ie. not just strings) and you are concerned with
    the integrity of your data, I recommend that you take a good hard look at
    this "feature, not a bug" and determine if SQLite meets your project's
    requirements. Like I said, I've never used it, so this is all conjecture.

    --
    ..:[ dave benjamin: ramen/[sp00] -:- spoomusic.com -:- ramenfest.com ]:.
    : please talk to your son or daughter about parametric polymorphism. :
     
    Dave Benjamin, May 5, 2004
    #2
    1. Advertising

  3. Michael

    Alan Gauld Guest

    On Wed, 05 May 2004 22:41:35 -0000, Dave Benjamin
    <> wrote:
    > In article <>, Michael wrote:
    > > I'm considering changing a Python program of mine, that uses MySQL as
    > > it's db backend, to use SQLite. .... Any draw backs to making this change?


    No direct experience but doesn't SQLite store its data in memory?
    If you have a large database that could lead to problems on PCs
    with a small memory installation or running lots of apps?

    On the other hand if the data volume is small SQLite could offer
    significant performance gains over a disk oriented architecture
    like MySQL etc.

    Just a thought.

    Alan G.
    Author of the Learn to Program website
    http://www.freenetpages.co.uk/hp/alan.gauld
     
    Alan Gauld, May 6, 2004
    #3
  4. >>> doesn't SQLite store its data in memory?
    No ! It's the database-engine who is in embeddable. The data are stored in
    one file per database.
     
    Michel Claveau/Hamster, May 6, 2004
    #4
  5. Michael

    richard Guest

    Michael wrote:
    > I'm considering changing a Python program of mine, that uses MySQL as
    > it's db backend, to use SQLite. I'm mostly wanting something that can
    > make my program a complete package without requiring the user install
    > and configure an external db program. Has anyone experience with these?


    Yes, my project, the Roundup Issue Tracker (http://roundup.sf.net) has
    interfaces to both MySQL and SQLite (and postgresql, metakit, anydbm, ...)

    In short, sqlite is a *very* capable little database, as long as it only
    ever has one user. More than one user, and it'll block access so only one
    user may access it at a time.

    It scales very well - better than MySQL, and about equal with postgresql as
    far as I can tell. See:

    http://www.mechanicalcat.net/richard/log/Python/Roundup_benchmark_time_again

    for some comparative benchmarks.


    > They both follow the same DB-API so the code should be pretty easy to
    > port, right?


    In theory, yes. In practise, the DB-API layer simple can't hide the various
    little inconsistencies between databases (usually datatypes and incomplete
    SQL implementations). The Roundup code includes large "common" interface
    which covers 95% of its generic RDBMS interface, and then individual
    modules for each of the specific RDBMSes. Postgresql is the thinnest layer,
    then SQLite and then MySQL deviates the most from the common
    implementation.


    > Any draw backs to making this change?


    SQLite has some limitations on the SQL it implements. Then again, so does
    MySQL. The SQLite website has a concise page indicating the unimplement SQL
    features. ALTER TABLE is about the most annoying omission. On the other
    hand, MySQL doesn't support sub-selects, but sqlite does.

    SQLite treats all data as strings, but note that it does some internal
    "typecasting" such that a column of numbers will be sorted numerically. You
    will need to implement your own data conversion though. It's usually as
    trivial as a simple mapping containing conversion functions like (from the
    Roundup source):

    sql_to_hyperdb_value = {
    hyperdb.String : str,
    hyperdb.Date : lambda x: date.Date(str(x)),
    hyperdb.Link : str,
    hyperdb.Interval : date.Interval,
    hyperdb.Password : lambda x: password.Password(encrypted=x),
    hyperdb.Boolean : int,
    hyperdb.Number : rdbms_common._num_cvt,
    }


    Richard
     
    richard, May 6, 2004
    #5
  6. Michael

    richard Guest

    Alan Gauld wrote:
    > On Wed, 05 May 2004 22:41:35 -0000, Dave Benjamin
    > <> wrote:
    >> In article <>, Michael
    >> wrote:
    >> > I'm considering changing a Python program of mine, that uses MySQL as
    >> > it's db backend, to use SQLite. .... Any draw backs to making this
    >> > change?

    >
    > No direct experience but doesn't SQLite store its data in memory?


    No, you're thinking of gadfly :)


    Richard
     
    richard, May 6, 2004
    #6
  7. Michel Claveau/Hamster wrote:
    >>>>doesn't SQLite store its data in memory?

    >
    > No ! It's the database-engine who is in embeddable. The data are stored in
    > one file per database.


    You *can* also store the data in memory by specifiying :memory:, i. e.:

    cx = sqlite.connect(":memory:")

    -- Gerhard
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, May 6, 2004
    #7
  8. [Dave, quoting the SQLite docs]
    > """SQLite is "typeless". This means that you can store any kind of data you
    > want in any column of any table, regardless of the declared datatype of that
    > column. (See the one exception to this rule in section 2.0 below.) This
    > behavior is a feature, not a bug. A database is suppose to store and
    > retrieve data and it should not matter to the database what format that data
    > is in. The strong typing system found in most other SQL engines and codified
    > in the SQL language spec is a misfeature - it is an example of the
    > implementation showing through into the interface. SQLite seeks to overcome
    > this misfeature by allowing you to store any kind of data into any kind of
    > column and by allowing flexibility in the specification of datatypes."""


    How about this?

    """Python containers (eg. lists, sets, tuples) are "typeless". This means
    that you can store any kind of data you want in any container, without a
    declared datatype for that container. This behavior is a feature, not a bug.
    A container is suppose to store and retrieve data and it should not matter
    to the container what format that data is in. The static typing systems
    found in many other programming languages and codified in many language
    specs is a misfeature - it is an example of the implementation showing
    through into the interface. Python seeks to overcome this misfeature by
    allowing you to store any kind of data into any container and by allowing
    flexibility in the specification of datatypes."""

    See every third posting to comp.lang.python over the past ten years for why
    this kind of thing may or may not be a good idea. Most of the arguments
    that apply to dynamic typing in Python also apply to typelessness in
    databases (the main one being: it's up to your unit tests, not anything
    else, to make sure your program works). If Python's dynamic typing fits
    your brain, it seems likely that a typeless database should do so too.

    --
    Richie Hindle
     
    Richie Hindle, May 6, 2004
    #8
  9. Richard,

    Thank you for posting information on SQL lite, especially the relative
    sizes of your different store layers. This is really good
    information.
     
    Kevin Dahlhausen, May 6, 2004
    #9
  10. In article <>, Richie Hindle wrote:
    >
    > [Dave, quoting the SQLite docs]
    >> """SQLite is "typeless". This means that you can store any kind of data you
    >> want in any column of any table, regardless of the declared datatype of that
    >> column. (See the one exception to this rule in section 2.0 below.) This
    >> behavior is a feature, not a bug. A database is suppose to store and
    >> retrieve data and it should not matter to the database what format that data
    >> is in. The strong typing system found in most other SQL engines and codified
    >> in the SQL language spec is a misfeature - it is an example of the
    >> implementation showing through into the interface. SQLite seeks to overcome
    >> this misfeature by allowing you to store any kind of data into any kind of
    >> column and by allowing flexibility in the specification of datatypes."""

    >
    > How about this?
    >
    > """Python containers (eg. lists, sets, tuples) are "typeless". This means
    > that you can store any kind of data you want in any container, without a
    > declared datatype for that container. This behavior is a feature, not a bug.
    > A container is suppose to store and retrieve data and it should not matter
    > to the container what format that data is in. The static typing systems
    > found in many other programming languages and codified in many language
    > specs is a misfeature - it is an example of the implementation showing
    > through into the interface. Python seeks to overcome this misfeature by
    > allowing you to store any kind of data into any container and by allowing
    > flexibility in the specification of datatypes."""


    First of all, a minor nit: the word you want here is "supposed", not
    "suppose". =)

    Database relations are not generic containers. For a tuple to exist in a
    relation, it must satisfy a set of predicates that have been defined for
    that relation. These predicates include primary key, foreign key, and unique
    constraints. They also include (IMHO) the domains of the values for any
    particular column. A "typeless" database would limit the kinds of assertions
    I could make about a relation, and to me this is a misfeature.

    > See every third posting to comp.lang.python over the past ten years for why
    > this kind of thing may or may not be a good idea. Most of the arguments
    > that apply to dynamic typing in Python also apply to typelessness in
    > databases (the main one being: it's up to your unit tests, not anything
    > else, to make sure your program works). If Python's dynamic typing fits
    > your brain, it seems likely that a typeless database should do so too.


    I reject the claim that typing issues in programming languages are the same
    for databases.

    There are definite advantages to programming in dynamically typed languages
    like Python; most importantly, they are lean on syntax and don't get in your
    way like the popular statically typed languages. The main thing that makes
    programming in statically typed languages a pain is the amount of explicit
    type annotations and casts typically required to pull it off. This is more
    of a flaw in the respective type systems of these languages than with static
    typing in general. Languages in the ML family have shown that a type system
    can be out of your way (via type inference) yet still allow for compile-time
    checks. The "duck typing" concept that Python and Ruby programmers get so
    excited about can be accomplished using OCaml's objects because OCaml's type
    inference system is powerful enough to support this.

    Don't get me wrong; I love Python, and I love dynamically typed languages.
    There are still lots of things you can do in a dynamically typed language
    that would be difficult or impossible to accomplish in a statically typed
    language (without heavy use of reflection and dynamic casts, anyway).

    However, you simply cannot extend this issue to cover databases as well. For
    instance, SQL does type inference also. The only time you need to specify
    the types of data is when you declare your schema. The types of columns
    returned by queries are inferred by the database automatically. No explicit
    type annotations are necessary here. So, SQL does not suffer from my main
    pet peeve with statically typed languages, even inside of a "typeful"
    database.

    If the database has the expressiveness to make assertions about the data it
    contains, these assertions become assumptions to the programs that use the
    database. If you like simple, terse Python programs, you'll enjoy using a
    database that guarantees that your assertions were met, because these
    assertions do not need to be checked by your program.

    Databases may be shared by multiple programs written in multiple programming
    languages. How are you going to write a unit test to ensure that someone
    doesn't write a program in the future that sticks "hello" in a customer's
    age field? Put simply, if the database does not ensure that this will not
    happen, you'd better guard against this by using an int() cast and catching
    the ValueError exception, in which case you're basically back to type
    annotation.

    Your claims about types in database being a feature remind me of MySQL's
    earlier claims about why they didn't support foreign key constraints (if you
    write your code properly, you don't need them; they just get in the way).
    Now, you'll notice that they have thoroughly removed such claims from their
    documentation.

    I think it's fine if you want to have a typeless database. I was just
    advising the OP to take a look at his requirements and make sure that this
    is acceptable. Surely you don't expect him to assume that since he already
    uses Python, a dynamically typed (read: not *typeless*) language, he does
    not need to consider the repercussions that switching from MySQL to SQLite
    will have on his program, because of a few weak analogies and claims of
    brain-fitting?

    --
    ..:[ dave benjamin: ramen/[sp00] -:- spoomusic.com -:- ramenfest.com ]:.
    : please talk to your son or daughter about parametric polymorphism. :
     
    Dave Benjamin, May 6, 2004
    #10
  11. Sorry, a few minor corrections...

    In article <>, Dave Benjamin wrote:
    > First of all, a minor nit: the word you want here is "supposed", not
    > "suppose". =)


    This typo occurs in the documentation on the web site, so my nit was most
    likely misdirected.

    > Your claims about types in database being a feature remind me of MySQL's


    s/types/typelessness/

    --
    ..:[ dave benjamin: ramen/[sp00] -:- spoomusic.com -:- ramenfest.com ]:.
    : please talk to your son or daughter about parametric polymorphism. :
     
    Dave Benjamin, May 6, 2004
    #11
  12. Michael

    Michael Guest


    >In short, sqlite is a *very* capable little database, as long as it only
    >ever has one user. More than one user, and it'll block access so only one
    >user may access it at a time.
    >
    >

    It does block though - it doesn't loss data if more than one program
    tries to access it at once? In this case I have multiple programs that
    need to write to, and read from, the db in a reliable way. Most of the
    actions should be small so I don't think blocking should pose a big
    question but data loss would be very bad.

    >SQLite treats all data as strings, but note that it does some internal
    >"typecasting" such that a column of numbers will be sorted numerically. You
    >will need to implement your own data conversion though. It's usually as
    >trivial as a simple mapping containing conversion functions like (from the
    >Roundup source):
    >
    >

    I always type cast, and otherwise clean, all data coming in and out of
    my db functions anyway (for security reasons) so that isn't a problem.
    Since SQLite stores data as strings that means data such as numbers will
    take up more space than in a db such as MySQL?
     
    Michael, May 7, 2004
    #12
  13. Michael

    richard Guest

    Michael wrote:
    > It does block though - it doesn't loss data if more than one program
    > tries to access it at once?


    Yes.


    > Since SQLite stores data as strings that means data such as numbers will
    > take up more space than in a db such as MySQL?


    Yep.


    Richard
     
    richard, May 7, 2004
    #13
  14. At some point, Michael <> wrote:

    >>In short, sqlite is a *very* capable little database, as long as it only
    >>ever has one user. More than one user, and it'll block access so only one
    >>user may access it at a time.
    >>

    > It does block though - it doesn't loss data if more than one program
    > tries to access it at once? In this case I have multiple programs that
    > need to write to, and read from, the db in a reliable way. Most of the
    > actions should be small so I don't think blocking should pose a big
    > question but data loss would be very bad.


    From the sqlite library FAQ on sqlite.org:

    (7) Can multiple applications or multiple instances of the same
    application access a single database file at the same time?

    Multiple processes can have the same database open at the same
    time. Multiple processes can be doing a SELECT at the same time.
    But only one process can be making changes to the database at
    once.

    So, your process will block only if something else is changing the
    database. It also journals the changes to the database, so interrupted
    transactions are rolled back.

    >>SQLite treats all data as strings, but note that it does some internal
    >>"typecasting" such that a column of numbers will be sorted numerically. You
    >>will need to implement your own data conversion though. It's usually as
    >>trivial as a simple mapping containing conversion functions like (from the
    >>Roundup source):
    >>

    > I always type cast, and otherwise clean, all data coming in and out of
    > my db functions anyway (for security reasons) so that isn't a problem.
    > Since SQLite stores data as strings that means data such as numbers
    > will take up more space than in a db such as MySQL?


    Depends. If your numbers as strings are less than four bytes, they
    should take less space :)

    Also, by default, the Python wrapper (pysqlite.sf.net) converts rows
    declared as integer and float to the appropiate Python types. And
    there's a way to pass type info before a statement so it does the
    conversion for you.

    --
    |>|\/|<
    /--------------------------------------------------------------------------\
    |David M. Cooke
    |cookedm(at)physics(dot)mcmaster(dot)ca
     
    David M. Cooke, May 7, 2004
    #14
  15. [Dave]
    > [long and reasonable argument snipped]


    > Surely you don't expect him to assume that since he already
    > uses Python, a dynamically typed (read: not *typeless*) language, he does
    > not need to consider the repercussions that switching from MySQL to SQLite
    > will have on his program, because of a few weak analogies and claims of
    > brain-fitting?


    Not at all. I wasn't advocating blindly accepting typelessness as something
    that doesn't need to be considered. I was saying that many of the same
    practices that go into writing a decent Python application also apply to
    using a typeless database. To put it another way: although it might at
    first appear that there's a whole slew of new dangers involved, in fact
    they're mostly the same dangers that Python programmers are already familiar
    with. Having read your arguments, I still think that's true.

    --
    Richie Hindle
     
    Richie Hindle, May 7, 2004
    #15
    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. Richard Myers

    Drop Down List vrs Z-Order

    Richard Myers, Jul 16, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    615
    Martin Honnen
    Jul 16, 2005
  2. Toff McGowen

    ViewState vrs Datagrid Combo Boxes

    Toff McGowen, Nov 15, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    385
    Toff McGowen
    Nov 15, 2005
  3. Timothy

    Gif vrs. png, text issues

    Timothy, Jan 2, 2005, in forum: HTML
    Replies:
    10
    Views:
    831
    Mitja
    Jan 3, 2005
  4. Mark Probert

    backtick vrs native ruby

    Mark Probert, Feb 17, 2005, in forum: Ruby
    Replies:
    2
    Views:
    124
    Stefan Schmiedl
    Feb 18, 2005
  5. Tom Cloyd

    class vrs. method

    Tom Cloyd, Dec 7, 2008, in forum: Ruby
    Replies:
    8
    Views:
    126
    Tom Cloyd
    Dec 7, 2008
Loading...

Share This Page