Dennis Lee Bieber wrote:
> On 8 Sep 2006 16:46:03 -0700, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> 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
> [EMAIL PROTECTED] [EMAIL PROTECTED]
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff: [EMAIL PROTECTED])
> HTTP://www.bestiaria.com/
--
http://mail.python.org/mailman/listinfo/python-list
