Is it just me, or is Sqlite3 goofy?

[Paul Boddie]

Dennis Lee Bieber wrote:
>
>  Talking to myself again, I see...

Not quite. ;-)

[...]

>  How interesting... With MySQL/MySQLdb I did NOT get exceptions or
> error results on inserting bad numeric data supplied as character string
> format (ie, as read from the CSV). Instead, MySQL SILENTLY converted
> them to ZEROS
>
>  A price of "Priceless" becomes Decimal("0.00").
>
>  The Customer number of ">68" became 0L

This kind of thing is "classic" MySQL behaviour.

>  Which would one rather have to work with -- a database that copied
> invalid numerics as string literals (which, in my mind, makes it much
> easier to correct the data later, using "update .... set field = correct
> where field = invalid") or a database that silently converts them all to
> 0 values. (Of course, I now expect to have a rejoinder about "Using a
> REAL database instead of MySQL" -- but unless said person wishes to
> start making the same comments about SQLite on at least as regular a
> basis, I believe the objection itself is invalid for this example).

Given subsequent research into SQLite's affinity modes and their
presumed status as future features, the solution in that database
system's case is to apply validation in the driver/module or through
driver extensions, and there is apparently some flexibility in the
pysqlite2 modules for changing the way data types are handled, although
a cursory inspection of the documentation doesn't appear to suggest a
convenient, ready-made solution.

As for MySQL, the situation is possibly more awkward: one expects the
database system to complain about certain things, which it probably
does from time to time, but it would seem wasteful to duplicate
whatever validation the database system does do just to cover those
situations where the system misbehaves.

Paul