Is it just me, or is Sqlite3 goofy?

[mensanator at aol.com]

Dennis Lee Bieber wrote:
> Guess I lied...
>
> On Sat, 09 Sep 2006 05:22:20 GMT, Dennis Lee Bieber
> <wlfraed at ix.netcom.com> declaimed the following in comp.lang.python:
>
> 	Talking to myself again, I see...
>
> <snip>
>             rs = cr.execute("""insert into invoice_1
>                             (CustNo, Title, Author, Year, Price)
>                             values (?,?,?,?,?)""",
>                         r)
>
> Whoops, r => rv, though the exceptions raised made it moot
>
>             rs = cr.execute("""insert into invoice_2
>                             (CustNo, Title, Author, Year, Price)
>                             values (?,?,?,?,?)""",
>                         r)
>
> Same comment
>
> 	Out of curiousity, I converted to using MySQL(db) as a test. As
> expected, the pre-insert validation code worked with same results (well,
> the price was declared decimal, and Python 2.4 appears to handle that as
> a Decimal("value") on return <G>)
>
> 	Now, taking out the pre-validation and type conversion, supplying
> all data as it came from the CSV file:
>
> -=-=-=-=-=-=-
> Inserting:
> ['066', '101 Ways to Start A Fight', 'some Irish gentleman', '1919',
> '19.95']
> ['032', 'A Sale of Two Titties', 'Charles Dikkens', '1855', '20.00']
> ['001', 'Olsens Standard Book of British Birds (Expurgated)"', 'Olsen',
> 'None', '99.95']
> ['066', 'Ethel the Aardvark Goes Quantity Surveying', 'None', '1975',
> '9.99']
> ['032', 'David Coperfield', 'Edmund Wells', '1955', '3.95']
> ['>68', 'Strawmen and Dorothy', '', '2006', '49.89']
> ['033', "The Emperor's Old Clothes", 'Grimm Hound', '1887', 'Priceless']
>
> Select all from Invoice_1 (CustNo is CHARACTER)
> (1L, '066', '101 Ways to Start A Fight', 'some Irish gentleman', 1919L,
> Decimal("19.95"))
> (2L, '032', 'A Sale of Two Titties', 'Charles Dikkens', 1855L,
> Decimal("20.00"))
> (3L, '001', 'Olsens Standard Book of British Birds (Expurgated)"',
> 'Olsen', 0L, Decimal("99.95"))
> (4L, '066', 'Ethel the Aardvark Goes Quantity Surveying', 'None', 1975L,
> Decimal("9.99"))
> (5L, '032', 'David Coperfield', 'Edmund Wells', 1955L, Decimal("3.95"))
> (6L, '>68', 'Strawmen and Dorothy', '', 2006L, Decimal("49.89"))
> (7L, '033', "The Emperor's Old Clothes", 'Grimm Hound', 1887L,
> Decimal("0.00"))
>
> Select all from Invoice_2 (CustNo is INTEGER)
> (1L, 66L, '101 Ways to Start A Fight', 'some Irish gentleman', 1919L,
> Decimal("19.95"))
> (2L, 32L, 'A Sale of Two Titties', 'Charles Dikkens', 1855L,
> Decimal("20.00"))
> (3L, 1L, 'Olsens Standard Book of British Birds (Expurgated)"', 'Olsen',
> 0L, Decimal("99.95"))
> (4L, 66L, 'Ethel the Aardvark Goes Quantity Surveying', 'None', 1975L,
> Decimal("9.99"))
> (5L, 32L, 'David Coperfield', 'Edmund Wells', 1955L, Decimal("3.95"))
> (6L, 0L, 'Strawmen and Dorothy', '', 2006L, Decimal("49.89"))
> (7L, 33L, "The Emperor's Old Clothes", 'Grimm Hound', 1887L,
> Decimal("0.00"))
> -=-=-=-=-=-=-
>
> 	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
>
>
> 	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).
>
> (Apparently we have fallen afoul of this clause from the old
> O'Reilly/MySQL black/brown book: "When asked to store a value in a
> numeric column that is outside the column type's allowable range, MySQL
> clips the value to the appropriate endpoint of the range and stores the
> resulting value instead." -- seems character data "clips" to zero.
>

Are you saying that MySQL is goofy? ;-)

Based on these replies, I'm pulling back and retrenching.

As I said before, I'm not entering 500,000 records by writing
INSERT statements for each record, so reading csv files is
a more realistic test. Nevertheless, I am still convinced that
the documentation (or lack thereof) is mainly responsible for
my confusion. I was, after all, mimicing the examples given
(which still have errors).

I think an explanation of how Sqlite3 differs from SQL and
a better set of examples is still warranted.