Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: > Bryan Olson wrote: >> Ben Sizer wrote: >>> It's not a crackpot theory. It's a completely reasonable theory. SQL is >>> based on relational algebra, which provides a mathematical set of >>> operators for grouping data that is stored in separate sets. That data >>> is selected and projected according to its value, and nothing else. The >>> concept of it having a 'type' has been overlaid on top of this, >>> presumably to facilitate efficient implementation, which tends to >>> require fixed-width rows (and hence columns). It's not necessary in any >>> sense, and it's reasonable to argue that if it was trivial to implement >>> variable width columns as efficiently as fixed width columns, that >>> explicit data types might never have needed to exist. >> The mathematical definition of the relational model includes >> that data values are drawn from specific sets. > > Well, I did say relational algebra, which from what I understand > predates the official 'relational model'. Relational algebra got into it when you said "SQL is based on relational algebra". SQL is based on the relation model. Incidentally SQL's expressions are closer to relation calculus than to relational algebra. Furthermore, relation algebra does deal with types. One can, for example, infer the type of a result from the type of the operands. >> Implementing variable width columns has nothing to do with it. > > On a practical level, it has lots to do with it! There are subtler points on that, but it's not the issue here. >> Here's >> the reference: >> >> 1.3. A Relational View of Data >> >> The term relation is used here in its accepted mathematical >> sense. Given sets S1, S2, ···, Sn, (not necessarily >> distinct), R is a relation on these n sets if it is a set >> of n-tuples each of which has its first element from S1, >> its second element from S2, and so on [1]. We shall refer to >> Sj as the jth domain of R. > > Does it specify anywhere that sets S1...Sn cannot each be the universal > set? No; it indicates that they can be different, and when they are, value not of the given set are not legal. > To put it another way - although the spec implies the existence of > limited set domains, and data types enforce limited domains, I don't > think a requirement to allow limited domains is a requirement for > static data types. One might argue that a one-atom-type-only DBMS can still be relational, but that's not what SQLite offers. As we've seen, one does declare the type of a column, and the DBMS seems to prefer the stated type in that it will sometimes convert values of other types. -- --Bryan -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Bryan Olson wrote: > Ben Sizer wrote: > > It's not a crackpot theory. It's a completely reasonable theory. SQL is > > based on relational algebra, which provides a mathematical set of > > operators for grouping data that is stored in separate sets. That data > > is selected and projected according to its value, and nothing else. The > > concept of it having a 'type' has been overlaid on top of this, > > presumably to facilitate efficient implementation, which tends to > > require fixed-width rows (and hence columns). It's not necessary in any > > sense, and it's reasonable to argue that if it was trivial to implement > > variable width columns as efficiently as fixed width columns, that > > explicit data types might never have needed to exist. > > The mathematical definition of the relational model includes > that data values are drawn from specific sets. Well, I did say relational algebra, which from what I understand predates the official 'relational model'. > Implementing variable width columns has nothing to do with it. On a practical level, it has lots to do with it! > Here's > the reference: > > 1.3. A Relational View of Data > > The term relation is used here in its accepted mathematical > sense. Given sets S1, S2, ···, Sn, (not necessarily > distinct), R is a relation on these n sets if it is a set > of n-tuples each of which has its first element from S1, > its second element from S2, and so on [1]. We shall refer to > Sj as the jth domain of R. Does it specify anywhere that sets S1...Sn cannot each be the universal set? To put it another way - although the spec implies the existence of limited set domains, and data types enforce limited domains, I don't think a requirement to allow limited domains is a requirement for static data types. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Mike Owens wrote: > The bottom line: to use *any* database effectively, big or small, one > has to read its documentation, not the SQL standard. note that the SQL standard tells you to read the documentation for the database you're using, in at least 149 places (*). *) See Annex B. I only have a draft edition; the number of items in the final version may differ. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 12 Sep 2006 00:15:41 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Just to be fair... > > You do hear many people claiming exactly that, and the primary > complaint is often exactly the same one that's being levelled against > sqlite here (it's incredibly lax with types and does sometimes > mystifying conversions rather than pointing out programmer errors--and > yes that's intentionally loaded language that I don't necessarily agree > with, it's a common argument though.). True enough. Yet not a single conversion is undocumented in SQLite, nor is its type affinity. It works exactly as advertised. And MySQL's chief type conversion gotchas -- primarily dates/times I think -- were also clearly addressed in the documentation, and where changed in later versions of MySQL. Then there is, as I think another person pointed out on this thread, Oracle's silent conversion of empty strings to NULLs in VARCHAR fields -- talk about mystifying. What about Oracle's native date format, which also has no relation to the standard? I have little experience with Oracle, but from what I can tell, to get it to display dates in standard (ISO) format, you have to set the NLS_DATE_FORMAT at the session or database level, which requires either additional SQL commands, or administrative intervention. More so-called non-SQL compliant behavior, although hardly a surprise (or even a problem) to someone who is experienced with Oracle. The bottom line: to use *any* database effectively, big or small, one has to read its documentation, not the SQL standard. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Marty wrote:
> On 9/11/06, Mike Owens <[EMAIL PROTECTED]> wrote:
>> I coworker pointed me to this thread.
>
> Joy for us.
>
>>
>> < snipped good information >
>
> In all seriousness, the information you present here is great, and
> much appreciated. Your sarcastic, condescending tone kind of gets in
> the way of the message, though.
What about jokes on "waterheadretard" then ?
--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Oops! Sorry for the top-post! -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Mike Owens wrote: > And if you say SQLite misrepresents itself, > then what do you say about MySQL, which until version 5 didn't have > views or triggers? In fact, it didn't even have subselects until > version 4. For a period of years, SQLite had more mainstream SQL > features than MySQL. Yet you don't see people going around claiming > that MySQL is not an SQL database -- that it's misrepresenting itself. Just to be fair... You do hear many people claiming exactly that, and the primary complaint is often exactly the same one that's being levelled against sqlite here (it's incredibly lax with types and does sometimes mystifying conversions rather than pointing out programmer errors--and yes that's intentionally loaded language that I don't necessarily agree with, it's a common argument though.). The lack of subselects was also a major sticking point for a lot of people, as are other major missing SQL features. Not having used sqlite I can't comment on it in particular. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 9/11/06, Marty <[EMAIL PROTECTED]> wrote: > In all seriousness, the information you present here is great, and > much appreciated. Your sarcastic, condescending tone kind of gets in > the way of the message, though. Sarcastic, perhaps. Condesceding, I think not. It is ridiculous that people can simply say whatever they want to about software they've taken little time to learn. I did not see one single post by mensanator to the SQLite mailing list, or by anyone else on this thread who criticized SQLite. Rather, it was SQLite is just crappy, or not a database, or not an SQL database, or other expletives. And while mensanator had other claims about Python's documentation, this general frustration then carelessly took a turn to SQLite. And as for the alleged problems in SQLite, there was (to my estimation) much less effort expended in finding a solution than there was in badmouthing an otherwise wonderful piece of software. If you are too impatient to read the documentation, fine. If you don't want to consult the experienced people on the SQLite mailing list (who are glad to help), fine. But DON'T remain willfully ignorant AND blame SQLite for not working the way your intuition demands. For years I've watched people badmouth SQLite whose claims are uninformed, unfounded, or downright unfair. Had a single accuser here posted this alleged problem with SQLite to the SQLite mailing list, I probably would have remained silent here (and answered it more politely there). > And here is the crux of the issue. Sqlite doesn't follow the standard > for sql. The name certainly implies that it would. This doesn't make > it a crappy product, but it is certainly misleading. Newsflash: No database follows the complete SQL standard, not even Oracle. By the logic in this thread, there is no such thing as an SQL database. > I must admit, that after 10 years of oracle experience, I don't necessarily > read all > of the documentation for a new dbms I'm trying out, particularly a > light weigth variety. I get in, and try things. Sometimes I get > bitten, but I learn better that way. I would hope then that when you don't read the documentation, and you get bitten, you know better than to blame the software. > I would expect, however, for each > product with 'sql' in the name, to, at least by default, adhere to the > standard. And expectations are what set this conversation up. These expectations are simply unrealistic. If someone is simply too lazy to read the documentation or use the mailing list, then they can only blame themselves. And if you say SQLite misrepresents itself, then what do you say about MySQL, which until version 5 didn't have views or triggers? In fact, it didn't even have subselects until version 4. For a period of years, SQLite had more mainstream SQL features than MySQL. Yet you don't see people going around claiming that MySQL is not an SQL database -- that it's misrepresenting itself. So no, SQLite most certainly does not misrepresent itself. It is an open source, embedded, relational database that uses SQL as its query language. Plain and simple. Just because it may not implement part of the standard you or someone else likes does not strip it being an SQL database. > But, I don't expect that anything productive will come from the rest > of this thread. Your post had the stink of zealotry all over it, and > we all know what happens when a zealots favorite is questioned. Expecting people to get the facts before badmouthing something is hardly zealotry. I am tired of seeing SQLite taking the blame when certain people choose simply to assume rather than read (or consult others). They see database-level locking, they ASSUME it's too slow for any kind of write concurrency applications. They see type affinity, they ASSUME it's just substandard or useless, and then by further soritical leaps, discount it as even an SQL database? Rather than reading, or testing, or asking people who know, they get frustrated and go straight to blaming it. That is completely unfair. The purpose of this post is to demonstrate that: 1. SQLite was not at fault here, nor insufficient for the purpose stated. 2. There are people who can easily provide the very help you need, provided you ask them, and try to keep your derogatory comments to a minimum. 3. There is no substitute for reading the documentation and learning the product. This is doubly true of relational databases. I'd love to see someone who uses MSSQL or Oracle try to install and use PostgreSQL or Firebird with nothing but instinct. I currently use three of these databases in production, and I couldn't survive without reading documentation. > Again, thanks for the info. It'll serve me well when I'm playing with > sqlite later. You are more than welcome. Glad to help. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 9/11/06, Mike Owens <[EMAIL PROTECTED]> wrote: > I coworker pointed me to this thread. Joy for us. > > < snipped good information > In all seriousness, the information you present here is great, and much appreciated. Your sarcastic, condescending tone kind of gets in the way of the message, though. And here is the crux of the issue. Sqlite doesn't follow the standard for sql. The name certainly implies that it would. This doesn't make it a crappy product, but it is certainly misleading. I must admit, that after 10 years of oracle experience, I don't necessarily read all of the documentation for a new dbms I'm trying out, particularly a light weigth variety. I get in, and try things. Sometimes I get bitten, but I learn better that way. I would expect, however, for each product with 'sql' in the name, to, at least by default, adhere to the standard. And expectations are what set this conversation up. But, I don't expect that anything productive will come from the rest of this thread. Your post had the stink of zealotry all over it, and we all know what happens when a zealots favorite is questioned. Again, thanks for the info. It'll serve me well when I'm playing with sqlite later. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
I coworker pointed me to this thread.
>>>and why it isn't SQL.
>> It isn't SQL simply because SQL won't let you insert text
>> into a numeric field.
> Yup, I have to agree that's pretty crappy. (Makes mental note to limit
> use of SQLite).
Ever heard of check constraints? That's another feature of this
crappy, non-SQL database. They are one of at least three different
approaches you can take to implement strict typing using SQLite's SQL
and C library facilities.
[EMAIL PROTECTED]:/tmp# ./sqlite3
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> create table stupid(x INTEGER check(typeof(x)=='integer'));
sqlite> create table little(x REAL check(typeof(x)=='real'));
sqlite> create table database(x INTEGER check(typeof(x)!='text'));
sqlite> -- insert text
sqlite> insert into stupid values('SQLite sucks!');
SQL error: constraint failed
sqlite> insert into stupid values("1");
sqlite> --whattayaknow
sqlite> -- insert text
sqlite> insert into little values('This isn''t SQL!');
SQL error: constraint failed
sqlite> insert into little values(1.0);
sqlite> --how about that
sqlite> -- insert text
sqlite> insert into database values('Dynamic typing just doesn''t work!');
SQL error: constraint failed
sqlite> insert into database values(x'');
sqlite> -- something's going on here
sqlite> -- so what do we have in the tables?
sqlite> .h on
sqlite> .m col
sqlite> select x, typeof(x) from stupid;
x typeof(x)
-- --
1 integer
sqlite> -- integers only
sqlite> select x, typeof(x) from little;
x typeof(x)
-- --
1.0 real
sqlite> -- reals only
sqlite> select x, typeof(x) from database;
x typeof(x)
-- --
blob
sqlite> -- anything but text
That darned dynamic typing. It works like almost any other database if
you want it to. (Makes mental note to spend more time reading SQLite
documentation before bashing SQLite.)
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote:
> Bruno Desthuilliers wrote:
>> [EMAIL PROTECTED] wrote:
>>> Probably just me. I've only been using Access and SQL Server
>>> for 12 years, so I'm sure my opinions don't count for anything.
>>>
>> SQLite never pretended to be a full-blown RDBMS - just a lightweight
>> simple embedded database as SQL-compliant as possible.
>
> Ah, *you* haven't read the documentation either!
I use SQLite and PgSQL (and, alas, MySQL) on a daily basis. So I've *of
course* read the doc (and frequently re-read it).
> "as SQL-compliant as possible"?
Yes. And yes, I know about the typing issue. If you want to make sure
you can migrate your data to a real RDBMS, then it's up to you to take
appropriate precautions (FormEncode can be a real helper here...).
This (documented enough) issue apart, we have far less troubles with
SQLite than we have with MySQL, on both the dev and admin sides.
And FWIW, insulting peoples won't buy you much.
(snip useless rant again)
--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: > Paul McNett wrote: > >>[EMAIL PROTECTED] wrote: >> >>>Do you know what INNER JOIN means? >>> >>>Do you know how important it is to a relational database? >>> >>>Can you explain how an INNER JOIN can even work, in theory, >>>with dynamic data types? >> >>Let's stop the pissing contest and just see how it works. After all, >>this is Python and we can easily just try it out. Here's my example. >>Please tell me how this causes unexpected results, > > > When we use a best case scenario, we get what we expect. > > >>and why it isn't SQL. > > > It isn't SQL simply because SQL won't let you insert text > into a numeric field. > Yup, I have to agree that's pretty crappy. (Makes mental note to limit use of SQLite). regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Paul McNett wrote:
> [EMAIL PROTECTED] wrote:
> > Do you know what INNER JOIN means?
> >
> > Do you know how important it is to a relational database?
> >
> > Can you explain how an INNER JOIN can even work, in theory,
> > with dynamic data types?
>
> Let's stop the pissing contest and just see how it works. After all,
> this is Python and we can easily just try it out. Here's my example.
> Please tell me how this causes unexpected results,
When we use a best case scenario, we get what we expect.
> and why it isn't SQL.
It isn't SQL simply because SQL won't let you insert text
into a numeric field.
> Please modify my example to get it to cause a catastrophe,
Make it worse case? Sure, I can do that.
> and post it
> here so we can see the errors of our ways and be properly humbled.
#-- Preliminaries:
## from pysqlite2 import dbapi2 as sqlite
import sqlite3 as sqlite
## con = sqlite.connect("test.db")
con = sqlite.connect(":memory:")
cur = con.cursor()
#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
cur.execute("create table customers (id integer primary key
autoincrement, name char)")
cur.execute("create table categories (id integer primary key
autoincrement, name char)")
cur.execute("create table cust_cat (id integer primary key
autoincrement, cust_id integer, cat_id integer)")
##cur.execute("create table cust_cat (id integer, cust_id,
cat_id)")
##cur.execute("create table customers (id, name char)")
##cur.execute("create table categories (id, name char)")
##
### Ok, THIS fails. Because the JOINs were originally made against
fields
### that were cast as integers so the 'hinting' of sqlite must
operate in a JOIN
### allowing ints to JOIN against strings. Take away the casts and
the JOIN
### fails. Unfortunately, not every situation will involve JOINing
primary keys.
##
##[(1, u'Ziggy Marley'), (2, u'David Bowie')]
##[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##[(None, 1, 3), (None, 1, u'2'), (None, u'2', u'1'), (None, u'2',
u'3')]
##
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[]
##[]
##[]
##[]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
#-- Insert some test data into customer and categories:
cur.execute("insert into customers (name) values ('Ziggy Marley')")
cur.execute("insert into customers (name) values ('David Bowie')")
cur.execute("insert into categories (name) values ('Glam Rock')")
cur.execute("insert into categories (name) values ('Nuevo Reggae')")
cur.execute("insert into categories (name) values ('Male Singers')")
## # if int cast removed, manually insert ids
##cur.execute("insert into customers (id, name) values (1,'Ziggy
Marley')")
##cur.execute("insert into customers (id, name) values (2,'David
Bowie')")
##cur.execute("insert into categories (id, name) values (1,'Glam
Rock')")
##cur.execute("insert into categories (id, name) values (2,'Nuevo
Reggae')")
##cur.execute("insert into categories (id, name) values (3,'Male
Singers')")
cur.execute("select * from customers")
#-- Take a look at the data (and retrieve the pk's):
print cur.fetchall()
#[(1, u'Ziggy Marley'), (2, u'David Bowie')]
cur.execute("select * from categories")
print cur.fetchall()
#[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
##cur.execute("insert into cust_cat (cust_id, cat_id) values (1, 3)")
##cur.execute("insert into cust_cat (cust_id, cat_id) values (1, '2')")
##cur.execute("insert into cust_cat (cust_id, cat_id) values ('2',
'1')")
##cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', 3)")
##cc = [(1,3),(1,'2'),('2','1'),('2','3')]
cc = [(1,3),(1,'>2'),('>2','>1'),('>2','>3')]
### And this also fails (differently). The 'hinting' of sqlite that
operates
### during a JOIN only works if the string looks like an integer.
And, no,
### the use of the '>' is not a contrived example like 'fred'. I
often get a
### result like '>200' in what is supposed to be a numeric field.
##
##[(1, u'Ziggy Marley'), (2, u'David Bowie')]
##[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##[(1, 1, 3), (2, 1, u'>2'), (3, u'>2', u'>1'), (4, u'>2', u'>3')]
##
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[]
##[]
##[]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
cur.executemany("insert into cust_cat (cust_id, cat_id) values
(?,?)",cc)
cur.execute("select * from cust_cat")
print cur.fetchall()
print
#-- Run some queries:
cur.execute("""
select customers.id as cust_id,
customers.name as cust_name,
categories.id as cat_id,
categories.name as cat_name
from customers
inner join cust_cat
on cust_cat.cust_id = customers.id
inner join categories
on categories.id = cust_cat.cat_id
order by 2,4""")
print cur.fe
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Dennis Lee Bieber wrote:
> On 8 Sep 2006 16:46:03 -0700, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> declaimed the following in comp.lang.python:
>
>
>
> 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
>
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
George Sakkis wrote: > [EMAIL PROTECTED] wrote: > > > 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. > > If this was a java or c++ list, all this rant would be more > understandable, but bashing dynamic typing in a dynamic language list > seems pointless at best (as this has been beaten to death over and over > again), flamebait at worst. But I'm not bashing Python's use of dynamic typing. But if the SQL Language Specification says static typing, then static typing it is. Period. > It should be clear by now that there are > two (at least) alternatives: > 1. Validate the data in python before (or at the same time when) > feeding the DB. Data integrity is an issue even with static typing. It's a bigger issue with dynamic typing. > 2. Forget sqlite and use a statically typed DBMS; it's not like there > is a shortage of them. I have no intention of forgetting sqlite simply because it's now part of the standard library. I have now qualms about using it *now* because I understand it better. But reaching that level of understanding was like pulling teeth. Documentation shouldn't talk down to the reader. It's always bad when you confuse the smart people. The ignorant are supposed to be confused. It's job of the documentation to educate the ignorant. Hiding the idiosynchrocies of Sqlite3 from the user who's already familiar with SQL is simply unacceptable. > > Massaging your SQL statements to make up for the lack of type checking > (even if this is always possible) would be a bad idea for more than > one reasons (complexity,portability,performance), so you'd better not > go down this road. > > George -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: > 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. If this was a java or c++ list, all this rant would be more understandable, but bashing dynamic typing in a dynamic language list seems pointless at best (as this has been beaten to death over and over again), flamebait at worst. It should be clear by now that there are two (at least) alternatives: 1. Validate the data in python before (or at the same time when) feeding the DB. 2. Forget sqlite and use a statically typed DBMS; it's not like there is a shortage of them. Massaging your SQL statements to make up for the lack of type checking (even if this is always possible) would be a bad idea for more than one reasons (complexity,portability,performance), so you'd better not go down this road. George -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: > It's not a crackpot theory. It's a completely reasonable theory. SQL is > based on relational algebra, which provides a mathematical set of > operators for grouping data that is stored in separate sets. That data > is selected and projected according to its value, and nothing else. The > concept of it having a 'type' has been overlaid on top of this, > presumably to facilitate efficient implementation, which tends to > require fixed-width rows (and hence columns). It's not necessary in any > sense, and it's reasonable to argue that if it was trivial to implement > variable width columns as efficiently as fixed width columns, that > explicit data types might never have needed to exist. The mathematical definition of the relational model includes that data values are drawn from specific sets. Implementing variable width columns has nothing to do with it. Here's the reference: 1.3. A Relational View of Data The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ···, Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on [1]. We shall refer to Sj as the jth domain of R. [Footnote 1:] More concisely, R is a subset of the Cartesian product S1* S2 * ... * Sn. -- E. F. Codd. "A Relational Model of Data for Large Shared Data Banks." /Communications of the ACM/, Vol. 13, No. 6, June 1970, pp. 377-387. -- --Bryan -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Marc 'BlackJack' Rintsch wrote: > In <[EMAIL PROTECTED]>, > [EMAIL PROTECTED] wrote: > > > Ben Sizer wrote: > >> I don't think so... it doesn't take much to say that the module > >> implements a subset of SQL but stores ignores data types. > > > > So I can't just say > > > > WHERE qty=12 > > > > I have to say > > > > WHERE (qty=12) OR (qty="12") > > No you just write the first and don't stuff strings into that column. > It's your choice after all. Not when I don't control the source of the data. Despite the laboratory having been given a static type definition for data deliverables, I sporadically find text in my numeric fields. And surely you don't think I write INSERT statements for each of the over 500,000 records I have? The records are inserted programatically. Static types mean the exceptions...raise exceptions. How am I supposed to identify the exceptions if SQLite simply quietly converts the non-numeric data to text? > > > Do you know what INNER JOIN means? > > > > Do you know how important it is to a relational database? > > > > Can you explain how an INNER JOIN can even work, in theory, > > with dynamic data types? > > The same as with static types!? Where's the problem? > > table_a: > > idname > - -- > 42bob > 'foo' methusalem > '42' gaylord > > table_b: > > idage > - -- > 4223 > 'foo' 1000 > 0.5 None > > Join on `id`: > > idname age > - -- > 42bob23 > 'foo' methusalem 1000 Numbers JOINing numbers and text JOINing text doesn't illustrate the problem. The problem is when SQLite allows bad data to be inserted. The problem isn't that fields JOIN when they are not supposed to, it's when they fail to JOIN when they are supposed to. > > > The implications of violating the SQL Language Specification > > are much more serious than you think. > > Or not so serious than you think. Well, I can make up examples also. Bill for customer:Tom Smith -- 0003 Olsen's Standard Book of British Birds (Expurgated) $ 99.95 Bill for customer: Dick Smith -- 0002 A Sale of Two Titties$ 20.00 0005 David Coperfield $ 3.95 Bill for customer: Harry Smith -- 0001 101 Ways to Start A Fight$ 19.95 Hmm...looks like item 4 isn't being billed to anyone. That's no way to run a business. If I do a LEFT JOIN instead of an INNER JOIN: Bill for customer: None None -- 0004 Ethel the Aardvark Goes Quantity Surveying $ 9.99 Bill for customer:Tom Smith -- 0003 Olsen's Standard Book of British Birds (Expurgated) $ 99.95 Bill for customer: Dick Smith -- 0002 A Sale of Two Titties$ 20.00 0005 David Coperfield $ 3.95 Bill for customer: Harry Smith -- 0001 101 Ways to Start A Fight$ 19.95 I see the missing item, but still don't know who to bill it to. What if I dumped the invoice table? 4 54 Ethel the Aardvark Goes Quantity Surveying None 1975 9.99 3 001 Olsen's Standard Book of British Birds (Expurgated) Olsen None 99.95 2 032 A Sale of Two Titties Charles Dikkens 1855 20.0 5 032 David Coperfield Edmund Wells 1955 3.95 1 066 101 Ways to Start A Fight some Irish gentleman 1919 19.95 Aha, it was sold to customer 54, so now I just need to dump the customer table: 001 Tom Smith 42 032 Dick Smith 28 066 Harry Smith 66 only to discover there is no customer 54! How can that happen? 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)] 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. Now, IF the same error is repeated in EVERY table that's JOINed, THEN the dynamic typing won't be a problem. But that never happens in practice. I populate one table and ensure it's correct. Someone else populates the other table and screws it up so the JOIN never works. 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 b
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote:
> Do you know what INNER JOIN means?
>
> Do you know how important it is to a relational database?
>
> Can you explain how an INNER JOIN can even work, in theory,
> with dynamic data types?
Let's stop the pissing contest and just see how it works. After all,
this is Python and we can easily just try it out. Here's my example.
Please tell me how this causes unexpected results, and why it isn't SQL.
Please modify my example to get it to cause a catastrophe, and post it
here so we can see the errors of our ways and be properly humbled.
#-- Preliminaries:
>>> from pysqlite2 import dbapi2 as sqlite
>>> con = sqlite.connect("test.db")
>>> cur = con.cursor()
#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
>>> cur.execute("create table customers (id integer primary key
autoincrement, name char)")
>>> cur.execute("create table categories (id integer primary key
autoincrement, name char)")
>>> cur.execute("create table cust_cat (id integer primary key
autoincrement, cust_id integer, cat_id integer)")
#-- Insert some test data into customer and categories:
>>> cur.execute("insert into customers (name) values ('Ziggy Marley')")
>>> cur.execute("insert into customers (name) values ('David Bowie')")
>>> cur.execute("insert into categories (name) values ('Glam Rock')")
>>> cur.execute("insert into categories (name) values ('Nuevo Reggae')")
>>> cur.execute("insert into categories (name) values ('Male Singers')")
>>> cur.execute("select * from customers")
#-- Take a look at the data (and retrieve the pk's):
>>> cur.fetchall()
[(1, u'Ziggy Marley'), (2, u'David Bowie')]
>>> cur.execute("select * from categories")
>>> cur.fetchall()
[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
>>> cur.execute("insert into cust_cat (cust_id, cat_id) values (1, 3)")
>>> cur.execute("insert into cust_cat (cust_id, cat_id) values (1, '2')")
>>> cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', '1')")
>>> cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', 3)")
#-- Run some queries:
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id order by 2,4")
>>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock'), (2, u'David Bowie', 3, u'Male
Singers'), (1, u'Ziggy Marley', 3, u'Male Singers'), (1, u'Ziggy
Marley', 2, u'Nuevo Reggae')]
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
1 order by 2,4")
>>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'1' order by 2,4")
>>> cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'2' order by 2,4")
>>> cur.fetchall()
[(1, u'Ziggy Marley', 2, u'Nuevo Reggae')]
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'3' order by 2,4")
>>> cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male
Singers')]
>>> cur.execute("select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
3 order by 2,4")
>>> cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male
Singers')]
If I have skipped the test case that will fail, please enlighten me.
--
Paul McNett
http://paulmcnett.com
http://dabodev.com
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
In <[EMAIL PROTECTED]>, [EMAIL PROTECTED] wrote: > Ben Sizer wrote: >> I don't think so... it doesn't take much to say that the module >> implements a subset of SQL but stores ignores data types. > > So I can't just say > > WHERE qty=12 > > I have to say > > WHERE (qty=12) OR (qty="12") No you just write the first and don't stuff strings into that column. It's your choice after all. > Do you know what INNER JOIN means? > > Do you know how important it is to a relational database? > > Can you explain how an INNER JOIN can even work, in theory, > with dynamic data types? The same as with static types!? Where's the problem? table_a: idname - -- 42bob 'foo' methusalem '42' gaylord table_b: idage - -- 4223 'foo' 1000 0.5 None Join on `id`: idname age - -- 42bob23 'foo' methusalem 1000 > The implications of violating the SQL Language Specification > are much more serious than you think. Or not so serious than you think. Ciao, Marc 'BlackJack' Rintsch -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: > Ben Sizer wrote: >>[EMAIL PROTECTED] wrote: [...] >>>What are the chances that anything I send in as a bug report >>>will simply be ignored? Kind of like the Emporer's New Clothes, eh? >>>It would be an admission of ignorance and stupidity on the part >>>of the Python Development Team, wouldn't it? >> >>Why get so bitter over this? I agree the docs need fixing but you make >>it sound like this was a deliberate attempt to make you waste your >>time. > > > I'm not bitter, just being a squeaky wheel. > Ain't that the truth :-) > And I'd be more than happy to make PROPER corrections to the > docs if I thought the effort wouldn't be wasted. But in looking over > this thread, it certainly appears that there are very few who > understand the issue. > Well, as I've already suggested, sound corrections and/or additions to the documentation are *very* unlikely to be refused. I suspect you need to be a little more pragmatic with regard to SQLite. If it doesn't suit your needs, that's fine. If you can suggest changes that will avoid others experiencing the same disappointment you have, even better. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: > [EMAIL PROTECTED] wrote: > > Bruno Desthuilliers wrote: > > > SQLite never pretended to be a full-blown RDBMS - just a lightweight > > > simple embedded database as SQL-compliant as possible. > > > > Ah, *you* haven't read the documentation either! > > > > "as SQL-compliant as possible"? > > > > ROTFLMAO! > > No need to be rude really. In this context "as SQL-compliant as > possible" means, "as SQL-compliant as it is possible to be within the > project's restrictions", which presumably refer to code size and speed. > It's a reasonable trade-off. And I accept those restrictions. I haven't complained about SQLite being a subset of SQL. It's just that it's not SQL, so it can't even be a subset. > > > ** > > * The authors argue that static typing is a bug in the * > > * SQL specification that SQLite has fixed in a backwards * > > * compatible way.* > > ** > > > > > > "Fixed"? Up until now, I didn't think it was possible for > > crackpot theories to be implemented in computer science. > > This is absolutely the craziest thing I've ever heard. > > It's not a crackpot theory. It's a completely reasonable theory. SQL is > based on relational algebra, which provides a mathematical set of > operators for grouping data that is stored in separate sets. That data > is selected and projected according to its value, and nothing else. Ok, it's not crackpot with respect to relational algebra. > The > concept of it having a 'type' has been overlaid on top of this, > presumably to facilitate efficient implementation, which tends to > require fixed-width rows (and hence columns). It's not necessary in any > sense, and it's reasonable to argue that if it was trivial to implement > variable width columns as efficiently as fixed width columns, that > explicit data types might never have needed to exist But they do exist as far as the SQL Language Specification is concerned. Isn't this just like the parallel postualate in Euclidean Geometry? Sure, the parallel postulate isn't absolute truth in geometry theory, but once you make it an axiom, then calling it a bug and trying to fix it while claiming it's still Euclidean Geometry is crackpot math. If the SQLite author wants to make a new system based on a different set of relational algebra axioms, that's fine. But claiming the SQL Language Specification axioms are "bugs" that need to be "fixed" is crackpot. > > > So much for > > "If switching to a larger database such as PostgreSQL or Oracle > > is later necessary, the switch should be relatively easy." > > If you rely too much on a language-enforced data type rather than the > values of the underlying data, perhaps Python is not for you! Suppose someone said "this is a new implementation of Python". And all your programs crashed in it. Then, buried deep inside the documentation (so as not to affect sales), you find that the author has an issue with dynamic data types and in HIS version of Python, all data types are static. Wouldn't you argue that what he has isn't Python? > Personally I've migrated from SQLite to MySQL a couple of times (on > small projects, granted) and not found it to be a problem at all. And yet, I, knowing how SQL is supposed to work, had all kinds of problems. Why do you think I started this thread? Because the kind of things that work in SQL-compliant systems weren't working in SQLite. > > > Fixing the documentation is now becoming an enormous task. > > I don't think so... it doesn't take much to say that the module > implements a subset of SQL but stores ignores data types. So I can't just say WHERE qty=12 I have to say WHERE (qty=12) OR (qty="12") otherwise I can't guarantee that my query will return the records I want. Oh, and that will cause a Type mis-match error in MS-Access meaning I literally can't migrate this query from SQLite to MS-Access (or any other SQL-compliant database). Do you know what INNER JOIN means? Do you know how important it is to a relational database? Can you explain how an INNER JOIN can even work, in theory, with dynamic data types? The implications of violating the SQL Language Specification are much more serious than you think. > > > What are the chances that anything I send in as a bug report > > will simply be ignored? Kind of like the Emporer's New Clothes, eh? > > It would be an admission of ignorance and stupidity on the part > > of the Python Development Team, wouldn't it? > > Why get so bitter over this? I agree the docs need fixing but you make > it sound like this was a deliberate attempt to make you waste your > time. I'm not bitter, just being a squeaky wheel. And I'd be more than happy to make PROPER corrections to the docs if I thought the effort wouldn't be wasted. But in looking over this thread, it certainly appears that there are very few who understand the issue. >
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: > Bruno Desthuilliers wrote: > > SQLite never pretended to be a full-blown RDBMS - just a lightweight > > simple embedded database as SQL-compliant as possible. > > Ah, *you* haven't read the documentation either! > > "as SQL-compliant as possible"? > > ROTFLMAO! No need to be rude really. In this context "as SQL-compliant as possible" means, "as SQL-compliant as it is possible to be within the project's restrictions", which presumably refer to code size and speed. It's a reasonable trade-off. > ** > * The authors argue that static typing is a bug in the * > * SQL specification that SQLite has fixed in a backwards * > * compatible way.* > ** > > > "Fixed"? Up until now, I didn't think it was possible for > crackpot theories to be implemented in computer science. > This is absolutely the craziest thing I've ever heard. It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist. > So much for > "If switching to a larger database such as PostgreSQL or Oracle > is later necessary, the switch should be relatively easy." If you rely too much on a language-enforced data type rather than the values of the underlying data, perhaps Python is not for you! Personally I've migrated from SQLite to MySQL a couple of times (on small projects, granted) and not found it to be a problem at all. > Fixing the documentation is now becoming an enormous task. I don't think so... it doesn't take much to say that the module implements a subset of SQL but stores ignores data types. > What are the chances that anything I send in as a bug report > will simply be ignored? Kind of like the Emporer's New Clothes, eh? > It would be an admission of ignorance and stupidity on the part > of the Python Development Team, wouldn't it? Why get so bitter over this? I agree the docs need fixing but you make it sound like this was a deliberate attempt to make you waste your time. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: > Bruno Desthuilliers wrote: > >>[EMAIL PROTECTED] wrote: >> >>>Probably just me. I've only been using Access and SQL Server >>>for 12 years, so I'm sure my opinions don't count for anything. >>> >> >>SQLite never pretended to be a full-blown RDBMS - just a lightweight >>simple embedded database as SQL-compliant as possible. > > > Ah, *you* haven't read the documentation either! > > "as SQL-compliant as possible"? > > ROTFLMAO! > >>From SQLite Homepage > documentation > Available Documentation > Distinctive Features > > > This page highlights some of the characteristics of SQLite > that are unusual and which make SQLite different from many > other SQL database engines. > > Manifest typing > > Most SQL database engines use static typing. A datatype is > associated with each column in a table and only values of > that particular datatype are allowed to be stored in that > column. SQLite relaxes this restriction by using manifest > typing. > > In manifest typing, the datatype is a property of the > value itself, not of the column in which the value is > stored. SQLite thus allows the user to store any value > of any datatype into any column regardless of the declared > type of that column. (There are some exceptions to this > rule: An INTEGER PRIMARY KEY column may only store > integers. And SQLite attempts to coerce values into the > declared datatype of the column when it can.) > > *** > * The SQL language specification calls for static typing. * > *** > > So some people feel that the use of manifest typing is a > bug in SQLite. But the authors of SQLite feel very > strongly that this is a feature. > > ** > * The authors argue that static typing is a bug in the * > * SQL specification that SQLite has fixed in a backwards * > * compatible way.* > ** > > > "Fixed"? Up until now, I didn't think it was possible for > crackpot theories to be implemented in computer science. > This is absolutely the craziest thing I've ever heard. > > Manifest typing doesn't work *in theory*!! Sure, through > diligent data integrity management it can be made to work > in practice. But when that's applied, guess what? All your > fields end up having the same data type anyway. Duh. > > And Python's inclusion of this into the standard library > is like the AMA endorsing homeopathy. It should have > been pointed out in the Python Manuals that SQLite3 is > > > NOT < > > SQL-compliant, never will be, and true SQL-compliant code > > > CANNOT < > > be made to work in this environment. So much for > "If switching to a larger database such as PostgreSQL or Oracle > is later necessary, the switch should be relatively easy." > Sorry. I have written code that started out on SQLite and migrated it to other databases. Sure, your milage may vary. But the wailing and gnashing of teeth is complete unnecessary. > That does not, of course, mean I don't have a use for a light, > simple database that's part of the standard library. But I could > have saved myself a lot of wailing and gnashing of teeth if I > hadn't been lied to by the Python documentation that, like you, > has the mistaken belief that SQLite3 is SQL-compliant. > > Fixing the documentation is now becoming an enormous task. > > What are the chances that anything I send in as a bug report > will simply be ignored? Kind of like the Emporer's New Clothes, eh? > It would be an admission of ignorance and stupidity on the part > of the Python Development Team, wouldn't it? > No, when they need ignorance and stupidity they normally look to the user base ;-) I'm sure your doc patch will receive the same courteous consideration that my early suggestions for amendment did, and your name will join mine in the list of contributors. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
