MySQL vrs SQLite

[David M. Cooke]

At some point, Michael <mogmios at mlug.missouri.edu> 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