SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)

Sat Sep 9 01:47:09 EDT 2006

Dennis Lee Bieber wrote:
> On 8 Sep 2006 16:46:03 -0700, "mensanator at aol.com" <mensanator at aol.com>
> declaimed the following in comp.lang.python:
>
> 	<snip>
>
> 	After a sequence of hypothetical results of occult SQL you show
> this...
> >
> > invoices = [(1,'066','101 Ways to Start A Fight','some Irish
> > gentleman',1919,19.95), \
> > (2,'032','A Sale of Two Titties','Charles Dikkens',1855,20.00), \
> > (3,'001',"Olsen's Standard Book of British Birds
> > (Expurgated)","Olsen",None,99.95), \
> > (4,066,'Ethel the Aardvark Goes Quantity Surveying',None,1975,9.99), \
> > (5,'032','David Coperfield','Edmund Wells',1955,3.95)]
> >
>
> 	... A Python list of tuples!
>
> > Oops! Forgot the quotes around the customer id for item 4.
> > But why didn't it become 66? Because the leading 0 made
> > it octal! A little static typing would have helped here.
> >
> 	Do you routinely populate your databases by editing python lists of
> tuples?

I don't routinely do anything, as I've only been playing with it for
4 days.

> And if you do, why not show us the table definition and insert
> statements that go with the above data?

I was simply following the examples in the Python manuals.
If the examples are poor usage, maybe they shouldn't be in
the manuals.

>
> 	Or do you get the data from some file... What format is that file?

No file. Point is moot.

>
> 	Would you agree that the following is a clean representation of your
> example data, when considered as a CSV data source? (I left off the
> primary key -- we'll just let SQLite add that value). I DID, however,
> normalize the quoting by changing ' to ". (please ignore the line wrap
> on the (Expurgated) -- it does not exist in the data file)
>
> invoice.csv
> -=-=-=-=-=-
> "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","Olsen"s 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
> -=-=-=-=-=-
>
> 	Now, since you seem to believe that a "customer number" is a string
> data type (implied by the presence of quotes around all but the "bad
> data" record), I've so defined it in the table definition... Ah, but
> what the heck, let's create a table with it defined as an integer too...
>
> SQLiteTest.py
> -=-=-=-=-=-=-=-
> from pysqlite2 import dbapi2 as sql
> import csv
>
> TABLE_DEF_1 = """
>     create table invoice_1
>     (
>         ID integer primary key,
>         CustNo char,
>         Title char,
>         Author char,
>         Year integer,
>         Price float
>     ) """
>
> TABLE_DEF_2 = """
>     create table invoice_2
>     (
>         ID integer primary key,
>         CustNo integer,
>         Title char,
>         Author char,
>         Year integer,
>         Price float
>     ) """
>
> db = sql.connect("test.db")
> cr = db.cursor()
> try:
>     rs = cr.execute(TABLE_DEF_1)
>     db.commit()
> except:     #I know, I should be explicit
>     pass    #assume table already exists
>
> try:
>     rs = cr.execute(TABLE_DEF_2)
>     db.commit()
> except:     #I know, I should be explicit
>     pass    #assume table already exists
>
>
> fin = open("invoice.csv", "rb")
> indata = csv.reader(fin)
>
> print "\nInserting:"
> for r in indata:
>     print r
>     if len(r) != 5:
>         print "^^^Bad Record"
>     else:
>         rs = cr.execute("""insert into invoice_1
>                             (CustNo, Title, Author, Year, Price)
>                             values (?,?,?,?,?)""",
>                         r)
>         rs = cr.execute("""insert into invoice_2
>                             (CustNo, Title, Author, Year, Price)
>                             values (?,?,?,?,?)""",
>                         r)
> db.commit()
>
> fin.close()
>
> print "\nSelect all from Invoice_1 (CustNo is CHARACTER)"
> rs = cr.execute("select * from invoice_1")
> for r in cr:
>     print r
>
> print "\nSelect all from Invoice_2 (CustNo is INTEGER)"
> rs = cr.execute("select * from invoice_2")
> for r in cr:
>     print r
>
> db.close()
> -=-=-=-=-=-=-=-
>
> 	Now, let us run the above program, using the above data file! Again,
> watch out for line wrapping (my comments will be blocked off with """ )
>
> E:\UserData\Dennis Lee Bieber\My Documents\Python Progs>python
> SQLiteTest.py
>
> 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']
>
> """
> 	Well, look at that... ALL the data from the file is coming in as
> character strings... customer number, year, price, title, author... It's
> ALL character! The difference between quoted and unquoted numbers has
> been lost.
> """

How 'bout that? Maybe I should try harder to make a
better example.

>
> Select all from Invoice_1 (CustNo is CHARACTER)
> (1, u'066', u'101 Ways to Start A Fight', u'some Irish gentleman', 1919,
> 19.949999999999999)
> (2, u'032', u'A Sale of Two Titties', u'Charles Dikkens', 1855, 20.0)
> (3, u'001', u'Olsens Standard Book of British Birds (Expurgated)"',
> u'Olsen', u'None', 99.950000000000003)
> (4, u'066', u'Ethel the Aardvark Goes Quantity Surveying', u'None',
> 1975, 9.9900000000000002)
> (5, u'032', u'David Coperfield', u'Edmund Wells', 1955,
> 3.9500000000000002)
>
> """
> 	No strange results there -- the year and price aren't stored as
> strings, even though they were string data when inserted.
> """
> Select all from Invoice_2 (CustNo is INTEGER)
> (1, 66, u'101 Ways to Start A Fight', u'some Irish gentleman', 1919,
> 19.949999999999999)
> (2, 32, u'A Sale of Two Titties', u'Charles Dikkens', 1855, 20.0)
> (3, 1, u'Olsens Standard Book of British Birds (Expurgated)"', u'Olsen',
> u'None', 99.950000000000003)
> (4, 66, u'Ethel the Aardvark Goes Quantity Surveying', u'None', 1975,
> 9.9900000000000002)
> (5, 32, u'David Coperfield', u'Edmund Wells', 1955, 3.9500000000000002)
>
> """
> 	And look here... Again no strange results -- SQLite didn't even
> treat the leading 0 as a signal that the customer number is octal. They
> come out as integers without leading 0s though -- but then again, I'm
> not using a formatting statement on the output...
> E:\UserData\Dennis Lee Bieber\My Documents\Python Progs>
> """
>
> >
> > Sure, errors happen with static typing. After all, the values still
> > have to match. Dynamic typing allows for more potential errors and,
> > thanks to Murpy's Law, I will have a much bigger problem with data
> > integrity.
> >
> 	SQLite's "dynamic typing" does not mean "random" or "unpredictable"
> typing.
>
> 	The basic rules are fairly simple.
>
> 	IF the data field is declared as a numeric type, AND the input data
> can be coerced to numeric without error, it is stored and returned as a
> numeric value -- one would have to pass in a data value that contained
> non-numeric characters for it to become a character string.

As if that never happens.

>
> 	IF the data field is declared as a character type, AND the input
> data is a numeric, it is converted to a character representation and
> stored/returned as character.
>
> (less basic involves the application of data type converters which are
> probably user supplied)
>
>
> 	As for your example of invoices and customers, surely the
> application isn't trusting the user to type in a raw "customer number"
> for the invoice without first validating it by attempting to retrieve
> that customer from a customer table. Or, more likely, using the customer
> name to look up the number in the customer table, meaning the customer
> number -- whatever it is -- /will/ match the invoice data as it was
> taken directly from the database.
>
> 	Any data supplied to you in file format, if humanly readable, is
> going to be character strings when fed to SQLite UNLESS your code first
> performs some sort of conversion on it -- and I suspect you'll detect
> the bad data when doing that conversion. And, by the way, the octal
> detection only happens for numeric literals IN a Python statement, not
> in conversion of an external string data item to numeric.

Ok, it was a bad example.

>
> >>> int("066")
> 66
> >>> int(066)
> 54
> >>>
>
> 	Of course, if you're being supplied binary data files, you are
> probably using the struct module to extract the numeric data fields...
> But how, I wonder, would one get a non-numeric value using a numeric
> specification on a string of raw bytes?
> --
> 	Wulfraed	Dennis Lee Bieber		KD6MOG
> 	wlfraed at ix.netcom.com		wulfraed at bestiaria.com
> 		HTTP://wlfraed.home.netcom.com/
> 	(Bestiaria Support Staff:		web-asst at bestiaria.com)
> 		HTTP://www.bestiaria.com/