Is it just me, or is Sqlite3 goofy?

[mensanator]

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.

I was, nevertheless, looking forward to Sqlite3. And now
that gmpy has been upgraded, I can go ahead and install
Python 2.5.

So I open the manual to Section 13.13 where I find the first
example of how to use Sqlite3:

<code>
conn = sqlite3.connect(':memory:')

c = conn.cursor()

# Create table
c.execute('''create table stocks
(date timestamp, trans varchar, symbol varchar,
qty decimal, price decimal)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
</code>

Seems pretty simple, yet I was befuddled and bewildered
by this example. So much so that I added a query to see exactly
what was going on.

<code>
# added by me
c.execute('select * from stocks')
d = c.fetchone()
for t in d:
print type(t),t
</code>

Original code - what's wrong with this picture?
<type 'unicode'> 2006-01-05
<type 'unicode'> BUY
<type 'unicode'> RHAT
<type 'int'> 100
<type 'float'> 35.14

Why is the date returning as a string? Aren't the built in
converters supposed to handle that? Yes, if you enable
detect_types.

Added detect_types=sqlite3.PARSE_DECLTYPES

Traceback (most recent call last):
File "C:\Python25\sqlite_first_example.py", line 30, in <module>
c.execute('select * from stocks')
File "C:\Python25\lib\sqlite3\dbapi2.py", line 66, in
convert_timestamp
datepart, timepart = val.split(" ")
ValueError: need more than 1 value to unpack

Aha, that explains why they weren't enabled.

This failed because
- the value inserted was wrong format?
- and the builtin converter can't split it cuz it has no spaces?
when it worked it was because
- detect_types was not set, so converter not invoked when queried?

Yes, the format in the example was datetime.date and the field type
should have been cast [date], not [timestamp] which needs HH:MM:SS
for the converter to work properly (but only if detect_types
enabled).

If a correct format was inserted, converter would have worked

<type 'datetime.datetime'> 2006-09-04 13:30:00
<type 'unicode'> BUY
<type 'unicode'> RHAT
<type 'int'> 100
<type 'float'> 35.14

Or, had the field been properly cast as [date] instead of [timestamp]
it would also have worked.

<type 'datetime.date'> 2006-09-04
<type 'unicode'> BUY
<type 'unicode'> RHAT
<type 'int'> 100
<type 'float'> 35.14

Ah, this now partly explains the original result, since detect_types
is off by default, the field cast, not being a native sqlite3 type
was ignored and the data queried back as TEXT.

<type 'unicode'> 2006-09-04
<type 'unicode'> BUY
<type 'unicode'> RHAT
<type 'int'> 100
<type 'float'> 35.14

Ok, next issue, what the **** are [varchar] and [decimal]?
They are certainly not Sqlite3 native types. If they are
user defined types, where are the converters and adapters?
Does Sqlite3 simply ignore a cast that isn't registered?

Note that although both qty and price were cast as [decimal]
they queried back as int and float.

Note also that it's "obvious" from the query example on page 13.13
that the example is FUBAR
- the date is a unicode string, not a datetime.date type
- the price is binary floating point, not decimal

... print row
...
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
</quote>

Here we have an example of things apparently working
for the wrong reason. A classic example of the programmer
who *thinks* he knows how it works because he wrote it.
This kind of sloppiness wouldn't last 5 minutes in a production
environment.

But why did Sqlite3 make qty an int and price a float?
Hard to say since THE FURTHER EXAMPLES IN THE DOCS don't
even bother to cast the field types. I guess I'm supposed
to guess how things are supposed to work. Can I trust that
default settings will be what I want?

Ha! Can I trust the baby with a hammer?

First, note that the script example in section 13.13.3

<quote>
import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
create table person(
firstname,
lastname,
age
);

create table book(
title,
author,
published
);

insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency
'Douglas Adams',
1987
);
""")
</quote>

contains not one but TWO syntax errors! A single quote after
the word Agency is missing as is the comma that should be at
the end of that line. Seems that no one actually ever tried
this example.

That's easily fixed. But I was curious about why the fields
don't have type casts. After the previous debacle and
knowing that this code was never tested, I am not going to
assume it works. Better add a query to make sure.

cur.execute("select title, author, published from book")
d = cur.fetchall()
for i in d: print i
print

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)

Ok, so if not cast, the fields must default (and probably also when
a cast is made that hasn't been defined).

But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting. I tried (incorrectly) to insert another record:

cur.execute("insert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Douglas Adams','1987')")

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?

Here's MY example, creating a Cartesian Product

<code>
import sqlite3
letters = [(2,),('10',),('20',),(200,)]
con = sqlite3.connect(":memory:")
con.text_factory = str
con.execute("create table letter(c integer)")
con.executemany("insert into letter(c) values (?)", letters)
print 'Queried: ',
for row in con.execute("select c from letter"):
print row,
print
print
print 'Sorted: ',
for row in con.execute("select c from letter order by c"):
print row[0],
print
print
print 'Cartesian Product: ',
for row in con.execute("select a.c, b.c, c.c from letter as a, letter
as b, letter as c"):
print row[0]+row[1]+row[2],
</code>

Note that the list of data to be inserted contains both strings and
ints. But because the field was correctly cast as [integer], Sqlite3
actually stored integers in the db. We can tell that from how the
"order by" returned the records.

Queried: (2,) (10,) (20,) (200,)

Sorted: 2 10 20 200

Cartesian Product: 6 14 24 204 14 22 32 212 24 32 42 222 204 212
222 402 14 22 32 212 22 30 40 220 32 40 50 230 212 220 230 410 24
32 42 222 32 40 50 230 42 50 60 240 222 230 240 420 204 212 222
402 212 220 230 410 222 230 240 420 402 410 420 600

Because if I cast them as [text] the sort order changes (and my
Cartesian Product becomes concatenation instead of summation).

Queried: ('2',) ('10',) ('20',) ('200',)

Sorted: 10 2 20 200

Cartesian Product: 222 2210 2220 22200 2102 21010 21020 210200
2202 22010 22020 220200 22002 220010 220020 2200200 1022 10210
10220 102200 10102 101010 101020 1010200 10202 102010 102020
1020200 102002 1020010 1020020 10200200 2022 20210 20220 202200
20102 201010 201020 2010200 20202 202010 202020 2020200 202002
2020010 2020020 20200200 20022 200210 200220 2002200 200102
2001010 2001020 20010200 200202 2002010 2002020 20020200 2002002
20020010 20020020 200200200

But if I omit the cast altogether, then the db stores the input
exactly as it was inserted, so the c field contains both
text and integers wreaking havoc with my sort order, making
records un-queryable using "where" and causing my Cartesian
Product to crash.

Queried: (2,) ('10',) ('20',) (200,)

Sorted: 2 200 10 20

Cartesian Product: 6

Traceback (most recent call last):
File "C:\Python25\user\sqlite_test2.py", line 225, in <module>
print row[0]+row[1]+row[2],
TypeError: unsupported operand type(s) for +: 'int' and 'str'

Yeah, I know, I've heard it before.

"This behavior is by design."

It's still fuckin' goofy.

 

[Marc 'BlackJack' Rintsch]

In <[email protected]>,

But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting. I tried (incorrectly) to insert another record:

cur.execute("insert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Douglas Adams','1987')")

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?

It's not a bug, it's a feature. And answered as third point in the FAQ:

http://www.sqlite.org/faq.html#q3

I think your whole experience is based on it. Live with it or use a
real RDBMS.

If you are so fond of static typing, why are you using Python in the first
place? Just see it as consistency -- dynamically typed language →
dynamically typed DB columns. ;-)

Ciao,
Marc 'BlackJack' Rintsch

 

[Steve Holden]

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. [...]

Ok, next issue, what the **** are [varchar] and [decimal]? [..]

It's still fuckin' goofy.

Language ...

regards
Steve

 

[Bruno Desthuilliers]

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. In it's category,
it beats Access and MySQL hands down. Now if you want a real RDBMS,
you've just failed to choose the right tool. May I suggest PostgreSQL ?

(snip useless rant)

 

[mensanator]

In <[email protected]>,


It's not a bug, it's a feature. And answered as third point in the FAQ:

http://www.sqlite.org/faq.html#q3

Oh, so it is "This behaviour is by design".

I think your whole experience is based on it.

But shouldn't a significant feature like that be explained
in the Python manuals? Why should I go dig up Sqlite
FAQs to learn what should have been in the manuals?

Live with it or use a real RDBMS.

I don't mind living with it as long as it's documented.

If you are so fond of static typing, why are you using Python in the first
place? Just see it as consistency -- dynamically typed language →
dynamically typed DB columns. ;-)

Did you miss this statement in Section 13.13?

"If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy. "

Obviously, this is a new useage of "relatively easy" with which
I have been previously unaware.

 

[skip]

I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released. Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption. That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.

Skip

 

[mensanator]

I don't know, but I will take a stab at a plausible explanation. First,
sqlite support has only been in Python for a month or three. Its first
official unveiling will be when 2.5 is released.

Although possibly too late for the final release, now would be a
good time to straighten out the documentation.

Second, it's common when
wrapping functionality into Python to rely on the documentation for the
thing being wrapped. The thinner the wrapper, the more you tend to rely on
the underlying documentation. Also, the more functionally rich the thing
you've wrapped, the more you rely on the underlying documentation. I
wouldn't be at all surprised if the pysqlite author operated under that
assumption.

Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.

That the Python developers didn't pick up on the issue is not
surprising. I'm not sure how many of them are (py)sqlite users, probably
relatively few.

I would be surprised if they had never used ANY database. A little
thing like dynamic field typing will simply make it impossible to
migrate your Sqlite data to a *real* database.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?

 

[skip]

That's fine. Reformat your rant as a documentation bug report on
SourceForge:

http://sourceforge.net/projects/python

If you mention specific sqlite documentation urls you think should be
referenced in the pysqlite docs it would make it easier to accept.

Skip

 

[Steve Holden]

Although possibly too late for the final release, now would be a
good time to straighten out the documentation.

And you would be the best person to do it, since you're teh one this has
bitten in the tender parts.

Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.

Whoa, there! This isn't commercial software we are talking about. While
I appreciate the need to continually better Python's documentation, the
"should" implies a moral imperative that the (volunteer) developers are
unikely to find compelling.

I would be surprised if they had never used ANY database. A little
thing like dynamic field typing will simply make it impossible to
migrate your Sqlite data to a *real* database.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?

That's the ticket. Great idea. Changes to the documentation can be
suggested in plain ASCII, you don't have to grok the LaTeX markup.

regards
Steve

 

[Steve Holden]

Although possibly too late for the final release, now would be a
good time to straighten out the documentation.

And you would be the best person to do it, since you're the one this has
bitten in the tender parts.

Ok, that's certainly plausible. But it's not an excuse. The thinner the
documentation, the greater the emphasis should be made to point
the reader to a more adequate source. Simply listing the Sqlite home
page at the bottom of the page is hardly good enough. It should be
explicitly stated in bold letters that the reader should go read the
Sqlite FAQ because it radically differs from *real* databases and
provide a seperate link to it in the body of the documentation.

Whoa, there! This isn't commercial software we are talking about. While
I appreciate the need to continually better Python's documentation, the
"should" implies a moral imperative that the (volunteer) developers are
unlikely to find compelling.

I would be surprised if they had never used ANY database. A little
thing like dynamic field typing will simply make it impossible to
migrate your Sqlite data to a *real* database.

What I'll do is re-format my rant, suggest how *I* would do the
documentation, fix the errors I found in the examples and send it
off to the Python bug tracking as suggested in the manuals.

How's that as a plan?

That's the ticket. Great idea. Changes to the documentation can be
suggested in plain ASCII, you don't have to grok the LaTeX markup.

regards
Steve

 

[Bruno Desthuilliers]

But shouldn't a significant feature like that be explained
in the Python manuals?

Why should it ? It's a SQLite feature, not a Python one.

Why should I go dig up Sqlite
FAQs to learn what should have been in the manuals?

Why should you read the manuals at all then ?

I don't mind living with it as long as it's documented.

It is. In SQLite manual. Or do you hope the Python manual to also fully
document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ?

 

[Ben Sizer]

It is. In SQLite manual. Or do you hope the Python manual to also fully
document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ?

With those other applications, you have a separate download. With
sqlite, you don't, on Windows at least. Surely all the 'included
batteries' should have local documentation, especially with the type
conversions.

 

[Tim Roberts]

(e-mail address removed) wrote:
(snip)

Why should it ? It's a SQLite feature, not a Python one.

You have missed the key point that, as of Python 2.5, SQLite 3 is part of
the Python 2.5 standard library. So, at this point, it IS a Python
feature.

It is. In SQLite manual. Or do you hope the Python manual to also fully
document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ?

Warning: misplaced sarcasm detected....

 

[mensanator]

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

<quote *emphasis added*>
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. *
**********************************************************
</quote>

"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."

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?

 

[Steve Holden]

Ah, *you* haven't read the documentation either!

"as SQL-compliant as possible"?

ROTFLMAO!

documentation
Available Documentation
Distinctive Features

<quote *emphasis added*>
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. *
**********************************************************
</quote>

"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

 

[Ben Sizer]

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. *
**********************************************************
</quote>

"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.

 

[Magnus Lycka]

While I can understand your frustration, I think it is
important to think about the tone in our postings here.
Hydrocephalus is one of the most common birth defects,
and it's not terribly unlikely that someone who reads
this has a family member or someone else in his proximity
who suffers from this condition.

"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.

Still, many people with lots of experience in databases
use it, and prefer it for certain kinds of applications.
All systems have limitations and deviations, and those
limitations and deviations are stated more clearly for
SQLite than for most commercial products at least. The
market leader Oracle still can't store empty strings in
VARCHAR fields for instance. They are silently converted
to NULL. I'm pretty sure that has been in clear violation
to the official spec since 1986 at least.

As far as I understand, noone here is forcing you to use
SQLite, and with your long experience of MS Access I'd
expect you to be fairly used to "almost SQL"... It's
some time since I used Jet/Access now, but I had much
more problems with that than I've had with SQLite.

SQLite is built in Tcl, by someone who appreciates the
way Tcl works, with its weak typing. I don't think Tcl's
type handling is nearly as clever as Python's, but I
think it's a good thing that Python's standard lib finally
has a DB-API compliant module, and while I would have
preferred something that was closer to standard SQL, I
don't know of a better candidate than SQLite.

It's good that it's usable without a server setup, and
that it's very light weight. A Jet engine is obviously
not an option, and I would have preferred SQLite even
if Jet was open source and worked on all platforms.
(Well, if JET *was* open source, I suspect it would
have been fixed by now.) It's possible that one could
have used the embedded version of Firebird instead, but
in my experience that's not nearly as lean or easy to
deploy.

With your long experience of Access and SQL Server I'm
sure you know well that any attempt to build a working
database application requires extensive knowledge of
the backend to understand its peculiarities and
limitations.

The list of software projects where not quite competent
developers built Access applications that worked ok in
small scale tests and failed catastrophically in real
life is looong...

Of course, if you've stayed with one vendor for 15 years,
I can imagine that you've forgotten how long it took you
Having worked with half a dozen backends or so, I'm no
longer surprised that SQL can be interpreted in so many
ways... I agree that SQLite is unique in it's approach
to typing, but if you are aware of this, it's really not
a big problem.

 

[A.M. Kuchling]

I've made the following edits:

Index: whatsnew25.tex
===================================================================
--- whatsnew25.tex (revision 51828)
+++ whatsnew25.tex (working copy)
@@ -2116,14 +2116,16 @@
SQLite embedded database, has been added to the standard library under
the package name \module{sqlite3}.

-SQLite is a C library that provides a SQL-language database that
-stores data in disk files without requiring a separate server process.
+SQLite is a C library that provides a lightweight disk-based database
+that doesn't require a separate server process and allows accessing
+the database using a nonstandard variant of the SQL query language.
+Some applications can use SQLite for internal data storage. It's also
+possible to prototype an application using SQLite and then port the
+code to a larger database such as PostgreSQL or Oracle.
+
pysqlite was written by Gerhard H\"aring and provides a SQL interface
compliant with the DB-API 2.0 specification described by
-\pep{249}. This means that it should be possible to write the first
-version of your applications using SQLite for data storage. If
-switching to a larger database such as PostgreSQL or Oracle is
-later necessary, the switch should be relatively easy.
+\pep{249}.

If you're compiling the Python source yourself, note that the source
tree doesn't include the SQLite code, only the wrapper module.

 

[mensanator]

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.

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.

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.

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.

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.

 

[Steve Holden]

(e-mail address removed) 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